pg_attribute.attname inconsistency when renaming primary key columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_attribute.attname inconsistency when renaming primary key columns

Joel Jacobson-3
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_attribute.attname inconsistency when renaming primary key columns

Joel Jacobson-3
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

Reply | Threaded
Open this post in threaded view
|

Re: pg_attribute.attname inconsistency when renaming primary key columns

Kyotaro Horiguchi-4
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_attribute.attname inconsistency when renaming primary key columns

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment