I'm using SQLAlchemy which has database introspection smarts.
Basically, you point it at a database and tell it to find out what
tables are there and introspect them, the table indices, etc...
It works super.
Until today.
Yesterday, I restructured one of my tables (specifically, I dropped
one column and renamed another).
I've done similar operations many dozens of times with no ill-effect,
but today something strange happened.
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.
SQLAlchemy then tries to match that column name up with the table
definition, and fails, because it doesn't exist.
The SQL I had issued earlier to rename the attribute:
ALTER TABLE foo RENAME COLUMN bar TO baz;
psql shows the correct (new) name, 'baz'.
The SQL above shows the old name, 'bar'.
The database has not suffered any un-graceful shutdowns and shows no ill effect.
Is the SQL that SQLAlchemy is issuing wrong or is there something else going on?
--
Jon
