Обсуждение: Inconsistent sql result

<font face="Arial, Helvetica, sans-serif">Hi listers,<br /><br />As a PG administrator, I'm trying to read technical
datafrom pg_class table to monitor tables and indexes space.<br /><br />We are running a quite big postgres platform,
withmultiple databases, multiples schemes in each database, and hundreds tables. <br /><br />When I run the attach
pythonscript, I get 1809 rows as result. When I run the included (from the script) select query from phpPgAdmin or
pgAdminIII, I get 2010 rows as result.<br /><br />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
indexesattached on a table. <br /><br />Does anyone has a clue of why the same query, on same database gives different
resultdepending on it is included in a python script, or ran from a console?<br /><br />Many Thanks<br />Patrick<br
/></font><pre><fontface="Arial, Helvetica, sans-serif">#!/usr/bin/python2.4<br />#<br /><br />import sys<br />import
pgdb<br/><br />DEBUG = True<br />global db<br />#<br /># Database access <br />#<br />def opendb():<br />    global
db<br/>    # connect to database
 
<br />    port='5432'<br />    username='xxxxxxx'<br />    dbname='xxxxxxx'<br />    host='xx.xx.xx.xx'<br />
password='xxxxxxx'<br/><br />    try:<br />    db = pgdb.connect(database=dbname,host=host, user=username,
password=password)
<br />    except Exception, detail:<br />    db.rollback()<br />    if DEBUG:<br />        print 'Error occured while
connectingto database : %s' % detail<br />    sys.exit(0)<br /><br />#<br /># Close Database <br />#<br />def
closedb():<br/>    global db
 
<br /># Commit all changes before closing<br />    db.commit()<br />    db.close()<br /><br /><br /><br />if __name__==
'__main__':<br/><br />#<br /># Main<br />#<br />    opendb()<br />    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_nameFROM 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 "
 
<br />    cur = db.cursor()<br />    cur.execute(query)<br />    tables_details = cur.fetchall()<br />    nb_tables =
len(tables_details)<br/>    for table in tables_details:<br />        print table<br />    print "Tables
count=",nb_tables   <br />            
 
<br />    closedb()<br /></font></pre>

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