Re: database introspection error

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