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?
i.relname as relname,
ix.indisunique, ix.indexprs, ix.indpred,
a.attname as column_name
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
In response to
pgsql-bugs by date
|Next:||From: Kevin Grittner||Date: 2011-04-22 17:19:05|
|Subject: Re: database introspection error|
|Previous:||From: Lawrence Cohan||Date: 2011-04-21 19:50:22|
|Subject: Re: Postgres not using indexes|