I solved my problem by using attnum::text instead of attname for pg_class.relkind = ‘i’ as a work-around to avoid a diff.
On Mon, Feb 22, 2021, at 18:21, Joel Jacobson wrote: > Hi, > > When renaming a column that is part of a primary key, > the primary key index's pg_attribute.attname value > isn't updated accordingly, the old value remains. > > This causes problems when trying to measure if the > effects of a migration script caused the same end result > as if installing the same version of the schema from scratch. > > The schema diffing tool reports a diff, and there is one, > but not actually diff that causes any problems, > since the primary key index's attname doesn't appear > to be used for anything, since the attnum is probably > used instead, which is correct. > > Below in an example to illustrate the problem: > > CREATE TABLE foo ( > foo_id integer NOT NULL, > CONSTRAINT foo_pk PRIMARY KEY (foo_id) > ); > > \d foo > > Table "public.foo" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > foo_id | integer | | not null | > Indexes: > "foo_pk" PRIMARY KEY, btree (foo_id) > > SELECT c.relname, a.attnum, c.relkind, a.attname > FROM pg_class AS c > JOIN pg_attribute AS a ON a.attrelid = c.oid > JOIN pg_namespace AS n ON n.oid = c.relnamespace > WHERE n.nspname = 'public' > ORDER BY 1,2; > > relname | attnum | relkind | attname > ---------+--------+---------+---------- > foo | -6 | r | tableoid > foo | -5 | r | cmax > foo | -4 | r | xmax > foo | -3 | r | cmin > foo | -2 | r | xmin > foo | -1 | r | ctid > foo | 1 | r | foo_id > foo_pk | 1 | i | foo_id > (8 rows) > > ALTER TABLE foo RENAME COLUMN foo_id TO bar_id; > ALTER TABLE foo RENAME CONSTRAINT "foo_pk" TO "bar_pk"; > ALTER TABLE foo RENAME TO bar; > > \d bar > > Table "public.bar" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > bar_id | integer | | not null | > Indexes: > "bar_pk" PRIMARY KEY, btree (bar_id) > > Looks good! But... > > SELECT c.relname, a.attnum, c.relkind, a.attname > FROM pg_class AS c > JOIN pg_attribute AS a ON a.attrelid = c.oid > JOIN pg_namespace AS n ON n.oid = c.relnamespace > WHERE n.nspname = 'public' > ORDER BY 1,2; > > relname | attnum | relkind | attname > ---------+--------+---------+---------- > bar | -6 | r | tableoid > bar | -5 | r | cmax > bar | -4 | r | xmax > bar | -3 | r | cmin > bar | -2 | r | xmin > bar | -1 | r | ctid > bar | 1 | r | bar_id > bar_pk | 1 | i | foo_id > (8 rows) > > On the last row, we can see that the > attname for the PRIMARY KEY index > still says "foo_id". > > While I could ignore PRIMARY KEY index > attname values, it is ugly and I hope there > is a way to avoid it. > > /Joel Kind regards, Joel
