pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Inconsistent sql result
2+ messages / 2 participants
[nested] [flat]

* Inconsistent sql result
@ 2007-10-16 10:00 Patrick De Zlio <pchemla01@gmail.com>
  2007-10-16 16:32 ` Re: Inconsistent sql result Gerardo Herzig <gherzig@fmed.uba.ar>
  0 siblings, 1 reply; 2+ messages in thread

From: Patrick De Zlio @ 2007-10-16 10:00 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Inconsistent sql result
  2007-10-16 10:00 Inconsistent sql result Patrick De Zlio <pchemla01@gmail.com>
@ 2007-10-16 16:32 ` Gerardo Herzig <gherzig@fmed.uba.ar>
  0 siblings, 0 replies; 2+ messages in thread

From: Gerardo Herzig @ 2007-10-16 16:32 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-10-16 16:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-16 10:00 Inconsistent sql result Patrick De Zlio <pchemla01@gmail.com>
2007-10-16 16:32 ` Gerardo Herzig <gherzig@fmed.uba.ar>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox