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.
