Skip site navigation (1) Skip section navigation (2)

Re: database introspection error

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: database introspection error
Date: 2011-04-22 17:00:18
Message-ID: (view raw or whole thread)
Lists: pgsql-bugs
On Thu, Apr 21, 2011 at 11:28 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
>> SQLAlchemy encountered an error introspecting the tables. After
>> inspecting the SQL that it was running, I boiled it down to this:
>> SELECT c.relname,  a.attname
>> FROM pg_index i, pg_class c, pg_attribute a
>> WHERE i.indrelid = '16684' AND i.indexrelid = c.oid
>>   AND a.attrelid = i.indexrelid
>> ORDER BY c.relname, a.attnum;
>> I believe that SQL gives me the name of an index and the attribute
>> upon which that index is built for a particular relation (16684).
>> However, the *results* of that query are _wrong_.  The 'attname' value
>> for one row is wrong. It is the *previous* name of the column.
> That appears to be pulling out the names of the columns of the index,
> not the underlying table.  While older versions of Postgres will try to
> rename index columns when the underlying table column is renamed, that
> was given up as an unproductive activity awhile ago (mainly because
> there isn't always a 1-to-1 mapping anyway).  So it's not surprising
> to me that you're getting "stale" data here.

From Michael Bayer (the guy behind SQLAlchemy):

what we're trying to accomplish is to get the actual, current names of
the columns referenced by the index.

Would the following query be more (most?) correct, assuming the oid of
the table is known?

              i.relname as relname,
              ix.indisunique, ix.indexprs, ix.indpred,
              a.attname as column_name
              pg_class t,
              pg_class i,
              pg_index ix,
              pg_attribute a
              t.oid = ix.indrelid
              and i.oid = ix.indexrelid
              and a.attrelid = t.oid
              and a.attnum = ANY(ix.indkey)
              and t.relkind = 'r'
              and t.oid = $TABLE_OID_HERE
          ORDER BY


In response to


pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2011-04-22 17:19:05
Subject: Re: database introspection error
Previous:From: Lawrence CohanDate: 2011-04-21 19:50:22
Subject: Re: Postgres not using indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group