Re: database introspection error

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