Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group