Thread: Inconsistency between attname of index and attname of relation
Hello,
I've noticed that renaming an indexed column produces inconsistencies in the catalog. Namely, the attname of the attribute of the relation is properly updated, whereas the attname of the attribute in the index is not, and keeps the old value.
Example:
test # create table test (id int primary key);
CREATE TABLE
test # alter table test rename id to idnew;
ALTER TABLE
test # select attrelid::regclass, attname from pg_attribute where attrelid in ('test'::regclass, 'test_pkey'::regclass) and attnum > 0;
attrelid | attname
-----------+---------
test | idnew
test_pkey | id
CREATE TABLE
test # alter table test rename id to idnew;
ALTER TABLE
test # select attrelid::regclass, attname from pg_attribute where attrelid in ('test'::regclass, 'test_pkey'::regclass) and attnum > 0;
attrelid | attname
-----------+---------
test | idnew
test_pkey | id
We ran into that while using wal2json, which uses the replication id index attnames to identify which columns are part of the primary key. If the primary key column has been renamed, we end with no information about the identity of the tuple being updated / deleted.
I think this could be considered a bug in Postgres. If it isn't, what should be the proper way to retrieve this information ?
Ronan Dunklau <ronan_dunklau@ultimatesoftware.com> writes:
> I've noticed that renaming an indexed column produces inconsistencies in
> the catalog. Namely, the attname of the attribute of the relation is
> properly updated, whereas the attname of the attribute in the index is not,
> and keeps the old value.
If memory serves, we used to try to rename index columns, and gave up
on that because it caused problems of its own. That's (one reason) why
modern versions of psql show a "definition" column in \d of an index.
> I think this could be considered a bug in Postgres.
It is not.
> If it isn't, what
> should be the proper way to retrieve this information ?
psql uses pg_get_indexdef(), looks like.
regards, tom lane
Thank you for this quick answer, I'll report the bug to wal2json then.
Le ven. 5 juil. 2019 à 16:22, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Ronan Dunklau <ronan_dunklau@ultimatesoftware.com> writes:
> I've noticed that renaming an indexed column produces inconsistencies in
> the catalog. Namely, the attname of the attribute of the relation is
> properly updated, whereas the attname of the attribute in the index is not,
> and keeps the old value.
If memory serves, we used to try to rename index columns, and gave up
on that because it caused problems of its own. That's (one reason) why
modern versions of psql show a "definition" column in \d of an index.
> I think this could be considered a bug in Postgres.
It is not.
> If it isn't, what
> should be the proper way to retrieve this information ?
psql uses pg_get_indexdef(), looks like.
regards, tom lane
Em sex, 5 de jul de 2019 às 07:37, Ronan Dunklau <ronan_dunklau@ultimatesoftware.com> escreveu: > We ran into that while using wal2json, which uses the replication id index attnames to identify which columns are partof the primary key. If the primary key column has been renamed, we end with no information about the identity of thetuple being updated / deleted. > Ouch. That's a wal2json bug. I saw that you already opened an issue. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento