pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Patrick De Zlio <pchemla01@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Inconsistent result from request
Date: Sun, 14 Oct 2007 16:41:51 +0200
Message-ID: <58729a240710140741r6384478evb2a7d732804fd9e1@mail.gmail.com> (raw)

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

Attachments:

pgtables.py (text/x-python, 1.2K) download #!/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()

Message-ID: <58729a240710140741r6384478evb2a7d732804fd9e1@mail.gmail.com>
Permalink:  ../58729a240710140741r6384478evb2a7d732804fd9e1@mail.gmail.com/
Also on:    postgresql.org/message-id/58729a240710140741r6384478evb2a7d732804fd9e1@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: pchemla01@gmail.com
  Subject: Re: Inconsistent result from request
  In-Reply-To: <58729a240710140741r6384478evb2a7d732804fd9e1@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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