pg_attribute.attname inconsistency when renaming primary key columns

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

At Mon, 22 Feb 2021 21:42:44 +0100, "Joel Jacobson" <[hidden email]> wrote in
> I solved my problem by using attnum::text instead of attname for pg_class.relkind = ‘i’ as a work-around to avoid a diff.

For your information, note that the attname of an index relation is
not the name of the target column in the base table.  If you created
an index with expression columns, the attributes would be named as
"expr[x]".  And the names are freely changeable irrelevantly from the
column names of the base table.

So to know the referred column name of an index column, do something
like the following instead.

SELECT ci.relname as indexname, ai.attname as indcolname,
       cr.relname as relname, ar.attname as relattname, ar.attnum
FROM pg_index i
JOIN pg_class cr ON (cr.oid = i.indrelid)
JOIN pg_class ci ON (ci.oid = i.indexrelid)
JOIN pg_attribute ai ON (ai.attrelid = ci.oid)
JOIN pg_attribute ar ON (ar.attrelid = cr.oid AND ar.attnum = ANY(i.indkey))
WHERE ci.relnamespace = 'public'::regnamespace;

indexname | indcolname | relname | relattname | attnum
-----------+------------+---------+------------+--------
 bar_pk    | foo_id     | bar     | bar_id     |      1
(1 row)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Wed, Feb 24, 2021 at 04:55:11PM +0900, Kyotaro Horiguchi wrote:
> At Mon, 22 Feb 2021 21:42:44 +0100, "Joel Jacobson" <[hidden email]> wrote in
> > I solved my problem by using attnum::text instead of attname for pg_class.relkind = ‘i’ as a work-around to avoid a diff.
>
> For your information, note that the attname of an index relation is
> not the name of the target column in the base table.  If you created
> an index with expression columns, the attributes would be named as
> "expr[x]".  And the names are freely changeable irrelevantly from the
> column names of the base table.

Yes, the attname associated to the index expressions makes that
weird, so you should not rely on that.  This reminds me of the
discussion that introduced ALTER INDEX SET STATISTICS, which uses
column numbers:
https://www.postgresql.org/message-id/CAPpHfdsSYo6xpt0F=ngAdqMPFJJhC7zApde9h1qwkdpHpwFisA@...

> So to know the referred column name of an index column, do something
> like the following instead.

FWIW, for any schema diff tool, I would recommend to completely ignore
attname, and instead extract the index attributes using
pg_get_indexdef() that can work on attribute numbers.  You can find a
lot of inspiration from psql -E to see the queries used internally for
things like \d or \di.  For example:
=# create table aa (a int);
=# create index aai on aa((a + a), (a - a));
=# SELECT attnum,
       pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef
     FROM pg_catalog.pg_attribute a
     WHERE a.attrelid = 'aai' ::regclass AND a.attnum > 0 AND NOT a.attisdropped
     ORDER BY a.attnum;
 attnum | indexdef
--------+----------
      1 | (a + a)
      2 | (a - a)
(2 rows)
=# ALTER TABLE aa RENAME COLUMN a to b;
=# SELECT attnum,
       pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef
     FROM pg_catalog.pg_attribute a
     WHERE a.attrelid = 'aai' ::regclass AND a.attnum > 0 AND NOT a.attisdropped
     ORDER BY a.attnum;
 attnum | indexdef
--------+----------
      1 | (b + b)
      2 | (b - b)
(2 rows)
--
Michael