Hi listers,
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()
