DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=WLvrLnBiM6hFd8pWSt7Ymc+uTs7uEN2n6958pC2/uwWqgUfTHpnkUeHY5l1kUiHrA+Q2/cAS6+AzM33RI3yet2DWBUO2uiyKhDe383c65LRnJ8dkvdbHg18BjkC1CU9Q0huWpUAG5kUocT5foW5pEI2i4eQ8SMpmN2tORKbsqAk=
Message-ID: <58729a240710160300y2754d5eegd33397dc167eb815@mail.gmail.com>
Date: Tue, 16 Oct 2007 12:00:10 +0200
From: "Patrick De Zlio" <pchemla01@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Inconsistent sql result
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_74581_26549037.1192528810910"

------=_Part_74581_26549037.1192528810910
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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()

------=_Part_74581_26549037.1192528810910
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<font face="Arial, Helvetica, sans-serif">Hi listers,<br><br>As a PG administrator, I&#39;m trying to read technical data from pg_class table to monitor tables and indexes space.<br><br>We are running a quite big postgres platform, with multiple databases, multiples schemes in each database, and hundreds tables.
<br><br>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.<br><br>When I try to focus
on specific table including where relname =&#39;tablename&#39; in both parts of
the join, I also get different numbers of rows. So I can&#39;t have the
full size of all indexes attached on a table.
<br><br>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?<br><br>Many Thanks<br>Patrick<br>
</font><pre><font face="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=&#39;5432&#39;<br>    username=&#39;xxxxxxx&#39;<br>    dbname=&#39;xxxxxxx&#39;<br>    host=&#39;xx.xx.xx.xx&#39;<br>    password=&#39;xxxxxxx&#39;<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 &#39;Error occured while connecting to database : %s&#39; % 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__== &#39;__main__&#39;:<br><br>#<br># Main<br>#<br>	opendb()<br>	query = &quot;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 &quot;
<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 &quot;Tables count=&quot;,nb_tables	<br>			
<br>	closedb()<br></font></pre>

------=_Part_74581_26549037.1192528810910--