As a PG administrator, I'm trying to read technical data from pg_class table to monitor tables and indexes space.
We are running a quite big postgres platform, with multiple databases, multiples schemes in each database, and hundreds tables.
When I run the attach python script, I get 1809 rows as result. When I run the included (from the script) select query from phpPgAdmin or pgAdmin III, I get 2010 rows as result.
When I try to focus on specific table including where relname ='tablename' in both parts of the join, I also get different numbers of rows. So I can't have the full size of all indexes attached on a table.
Does anyone has a clue of why the same query, on same database gives different result depending on it is included in a python script, or ran from a console?
Many Thanks
Patrick
#!/usr/bin/python2.4
#
import sys
import pgdb
DEBUG = True
global db
#
# Database access
#
def opendb():
global db
# connect to database
port='5432'
username='xxxxxxx'
dbname='xxxxxxx'
host='xx.xx.xx.xx'
password='xxxxxxx'
try:
db = pgdb.connect(database=dbname,host=host, user=username, password=password)
except Exception, detail:
db.rollback()
if DEBUG:
print 'Error occured while connecting to database : %s' % detail
sys.exit(0)
#
# Close Database
#
def closedb():
global db
# Commit all changes before closing
db.commit()
db.close()
if __name__== '__main__':
#
# Main
#
opendb()
query = "SELECT relname, relnamespace, relkind, relfilenode, relpages, reltoastrelid, relname AS idx_table_name FROM pg_class UNION SELECT pg_c_i.relname, pg_c_i.relnamespace, pg_c_i.relkind, pg_c_i.relfilenode, pg_c_i.relpages, pg_c_i.reltoastrelid, pg_c_i_o.relname AS idx_table_name FROM pg_class pg_c_i, pg_index pg_i, pg_class pg_c_i_o WHERE pg_c_i.relfilenode = pg_i.indexrelid AND pg_i.indrelid = pg_c_i_o.relfilenode "
cur = db.cursor()
cur.execute(query)
tables_details = cur.fetchall()
nb_tables = len(tables_details)
for table in tables_details:
print table
print "Tables count=",nb_tables
closedb()
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
