On Thu, Apr 21, 2011 at 11:28 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pg...@jamponi.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
