On Thu, Apr 21, 2011 at 11:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@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, =C2=A0a.attname
>> FROM pg_index i, pg_class c, pg_attribute a
>> WHERE i.indrelid =3D '16684' AND i.indexrelid =3D c.oid
>> =C2=A0 AND a.attrelid =3D 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_. =C2=A0The 'attname' va=
lue
>> 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. =C2=A0While 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). =C2=A0So it's not surprising
> to me that you're getting "stale" data here.
=46rom 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 =3D ix.indrelid
and i.oid =3D ix.indexrelid
and a.attrelid =3D t.oid
and a.attnum =3D ANY(ix.indkey)
and t.relkind =3D 'r'
and t.oid =3D $TABLE_OID_HERE
ORDER BY
t.relname,
i.relname
--=20
Jon