Re: Inconsistent sql result

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