Well its definitely possible since pgAdmin gives you the right answer after a
rename. Here's its query:
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid,
indkey, indisclustered, indisunique, indisprimary, n.nspname,
indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as
tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN
desp.description WHEN 'u' THEN desp.description ELSE des.description END AS
description,
pg_get_expr(indpred, indrelid, true) as indconstraint, contype,
condeferrable, condeferred, amname
, substring(array_to_string(cls.reloptions, ',') from
'fillfactor=([0-9]*)') AS fillfactor
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
= cls.oid AND dep.refobjsubid = '0')
LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND
desp.objsubid = 0)
WHERE indrelid = 8028886::oid
AND cls.oid=8028912::oid
AND conname IS NULL
ORDER BY cls.relname
someone feel like distiling that down for our purposes ?
On Apr 21, 2011, at 1:16 PM, Michael Bayer wrote:
> I'm assuming that's a paraphrase of the query in get_indexes(). I'm not
> sure why he said, "find out what they're trying to accomplish", as it seems
> pretty obvious, but I will make it clear, what we're trying to accomplish is
> to get the actual, current names of the columns referenced by the index.
> Just like if you go to pgAdmin, click on a table->indexes->index, there's a
> display on the right that says "Columns". If you want to tell him that, so
> that he can tell me what the correct query is, that would be great. I
> didn't write these queries and poking around its not immediately apparent how
> else the pg_index rows relate back to things.
>
> If there is no such query and the Index represents some internal structure
> that cant be linked back to the original columns, we just have to remove the
> feature.
>
> Also, test case ? create table + index, alter column name, reflect ?
>
>
> On Apr 21, 2011, at 12:43 PM, Jon Nelson wrote:
>
>> Forwarded from the pgsql-bugs mailing list.
>> The short version is that after renaming a column, SQLAlchemy's
>> introspection failed.
>> I tried 0.6.5 and 0.6.7.
>>
>>
>> ---------- Forwarded message ----------
>> From: Tom Lane <t...@sss.pgh.pa.us>
>> Date: Thu, Apr 21, 2011 at 11:28 AM
>> Subject: Re: [BUGS] database introspection error
>> To: Jon Nelson <jnelson+pg...@jamponi.net>
>> Cc: pgsql-b...@postgresql.org
>>
>>
>> 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.
>>
>> You might want to have a discussion with the SQLAlchemy people about
>> what it is that they're trying to accomplish and how it might be done
>> in a more bulletproof fashion. The actual names of the columns of an
>> index are an implementation detail that shouldn't be relied on.
>>
>> regards, tom lane
>>
>>
>>
>> --
>> Jon
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
