Inconsistent sql result - Mailing list pgsql-sql

From Patrick De Zlio
Subject Inconsistent sql result
Date
Msg-id 58729a240710160300y2754d5eegd33397dc167eb815@mail.gmail.com
Whole thread Raw
Responses Re: Inconsistent sql result
List pgsql-sql
<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>

pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Refactored queries needing validation of syntactic equivalence
Next
From: Gerardo Herzig
Date:
Subject: Re: Inconsistent sql result