Re: pg_attribute.attname inconsistency when renaming primary key columns

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