Message-ID: <4714E780.8080305@fmed.uba.ar>
Date: Tue, 16 Oct 2007 13:32:00 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Inconsistent sql result
References: <58729a240710160300y2754d5eegd33397dc167eb815@mail.gmail.com>
In-Reply-To: <58729a240710160300y2754d5eegd33397dc167eb815@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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