blob: 9cb8384dc29dc2b1ca3acda777fef0971ec3e238 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
#
# updateStats
# updates the statistic of number of distinct attribute values
# (this should really be done by the vacuum command)
# this is kind of brute force and slow, but it works
# since we use SELECT DISTINCT to calculate the number of distinct values
# and that does a sort, you need to have plenty of disk space for the
# intermediate sort files.
#
# - jolly 6/8/95
#
# update_attnvals
# takes in a table and updates the attnvals columns for the attributes
# of that table
#
# conn is the database connection
# rel is the table name
proc update_attnvals {conn rel} {
# first, get the oid of the rel
set res [pg_exec $conn "SELECT oid FROM pg_class where relname = '$rel'"]
if { [pg_result $res -numTuples] == "0"} {
puts stderr "update_attnvals: Relation named $rel was not found"
return
}
set oid [pg_result $res -getTuple 0]
pg_result $res -clear
# use this query to find the names of the attributes
set res [pg_exec $conn "SELECT * FROM $rel WHERE 'f'::bool"]
set attrNames [pg_result $res -attributes]
puts "attrNames = $attrNames"
foreach att $attrNames {
# find how many distinct values there are for this attribute
# this may fail if the user-defined type doesn't have
# comparison operators defined
set res2 [pg_exec $conn "SELECT DISTINCT $att FROM $rel"]
set NVALS($att) [pg_result $res2 -numTuples]
puts "NVALS($att) is $NVALS($att)"
pg_result $res2 -clear
}
pg_result $res -clear
# now, update the pg_attribute table
foreach att $attrNames {
# first find the oid of the row to change
set res [pg_exec $conn "SELECT oid FROM pg_attribute a WHERE a.attname = '$att' and a.attrelid = '$oid'"]
set attoid [pg_result $res -getTuple 0]
set res2 [pg_exec $conn "UPDATE pg_attribute SET attnvals = $NVALS($att) where pg_attribute.oid = '$attoid'::oid"]
}
}
# updateStats
# takes in a database name
# and updates the attnval stat for all the user-defined tables
# in the database
proc updateStats { dbName } {
# datnames is the list to be result
set conn [pg_connect $dbName]
set res [pg_exec $conn "SELECT relname FROM pg_class WHERE relkind = 'r' and relname !~ '^pg_'"]
set ntups [pg_result $res -numTuples]
for {set i 0} {$i < $ntups} {incr i} {
set rel [pg_result $res -getTuple $i]
puts "updating attnvals stats on table $rel"
update_attnvals $conn $rel
}
pg_disconnect $conn
}
|
