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
