Inconsistent result from request

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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()
---------------------------(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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux