Re: database introspection error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: database introspection error
Date: 2011-04-21 16:28:51
Message-ID: 24590.1303403331@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-04-21 16:43:44 Re: BUG #5297: Add XATMI C API
Previous Message Tom Lane 2011-04-21 16:06:31 Re: BUG #5974: UNION construct type cast gives poor error message