ah no its actually this one....
SELECT
i.indoption[0] AS options,
CASE WHEN (o.opcdefault = FALSE) THEN
pg_get_indexdef(i.indexrelid, 1, true) || ' ' || o.opcname
ELSE
pg_get_indexdef(i.indexrelid, 1, true)
END AS coldef
FROM pg_index i
JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 1)
LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[0])
WHERE i.indexrelid = 8028912::oid
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 <[email protected]>
>> Date: Thu, Apr 21, 2011 at 11:28 AM
>> Subject: Re: [BUGS] database introspection error
>> To: Jon Nelson <[email protected]>
>> Cc: [email protected]
>>
>>
>> Jon Nelson <[email protected]> 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 [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
