Обсуждение: database introspection error

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

Jon Nelson <jnelson+pgsql@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

On Thu, Apr 21, 2011 at 11:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@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, =C2=A0a.attname
>> FROM pg_index i, pg_class c, pg_attribute a
>> WHERE i.indrelid =3D '16684' AND i.indexrelid =3D c.oid
>> =C2=A0 AND a.attrelid =3D 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_. =C2=A0The 'attname' va=
lue
>> 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. =C2=A0While 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). =C2=A0So it's not surprising
> to me that you're getting "stale" data here.

=46rom Michael Bayer (the guy behind SQLAlchemy):

"
what we're trying to accomplish is to get the actual, current names of
the columns referenced by the index.
"

Would the following query be more (most?) correct, assuming the oid of
the table is known?

          SELECT
              i.relname as relname,
              ix.indisunique, ix.indexprs, ix.indpred,
              a.attname as column_name
          FROM
              pg_class t,
              pg_class i,
              pg_index ix,
              pg_attribute a
          WHERE
              t.oid =3D ix.indrelid
              and i.oid =3D ix.indexrelid
              and a.attrelid =3D t.oid
              and a.attnum =3D ANY(ix.indkey)
              and t.relkind =3D 'r'
              and t.oid =3D $TABLE_OID_HERE
          ORDER BY
              t.relname,
              i.relname



--=20
Jon

Jon Nelson <jnelson+pgsql@jamponi.net> wrote:

> Would the following query be more (most?) correct, assuming the
> oid of the table is known?

It doesn't seem to work for indexes on expressions.

Try this:

create table t (id int not null primary key, txt text);
create index t_weird on t ((substring(txt, 1, id)));
create index t_txt_notnull on t (txt) where txt is not null;
SELECT
    i.relname as relname,
    ix.indisunique, ix.indexprs, ix.indpred,
    a.attname as column_name
  FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
  WHERE
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.oid = (select oid from pg_class where relname = 't')
  ORDER BY
    t.relname,
    i.relname
;

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
>> Would the following query be more (most?) correct, assuming the
>> oid of the table is known?

> It doesn't seem to work for indexes on expressions.

Depends on what you mean by "work".  If you're looking for attributes
that are used as simple index columns, it's probably fine.  If you want
to also find attributes that are used in index expressions, you might
want to make use of a join through pg_depend.  One disadvantage of
pg_depend is that it doesn't help for system catalog indexes (because
indexes built at bootstrap time aren't included in pg_depend) --- not
sure if that is something SQLAlchemy cares about.

            regards, tom lane