Re: Inconsistent sql result - Mailing list pgsql-sql

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