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: BANLkTik-DoXEhya3pCJv=0v-wAR+qwz9oQ@mail.gmail.com (view raw or flat)
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?

          SELECT
              i.relname as relname,
              ix.indisunique, ix.indexprs, ix.indpred,
              a.attname as column_name
          FROM
              pg_class t,
              pg_class i,
              pg_index ix,
              pg_attribute a
          WHERE
              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
              t.relname,
              i.relname



-- 
Jon

In response to

Responses

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-2014 The PostgreSQL Global Development Group