Patrick De Zlio wrote:
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()
Hi Patrick:
I tried your script and have the expected behaviour (both results
are identical). I didnt use pg_admin nor pgaccess, i just use the psql.
I have tried using pgdb and PyGresql, having the exact (good) behaviour.
So, sory but have to ask: Are you reaaaaaally shure that you are
executing the query on the same database?
Python postgres are basicly not much but wrappers to C functionality,
thats why the "error" you post looks very strange to me.
I tried it on
- python 2.4
- postgres 8.1.3
- pgdb and pyGreSql libraries
Can you check that? If you are really executing the exactly same query
on the exacly same database, you could build 2 temporary tables with the
query results, and then look for the diff and try to figure out what the
diffs are, and continue watching, i dont know, if you need to add a
search_path in order to search on all the schemas or something.
Cheers.
Gerardo
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
