From: | Japin Li <japinli(at)hotmail(dot)com> |
---|---|
To: | holly(dot)roberts(at)starlingbank(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | <peter(at)eisentraut(dot)org> |
Subject: | Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key |
Date: | 2022-02-17 16:38:21 |
Message-ID: | MEYP282MB16691B29F4FEA0CC45CD5FFAB6369@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Wed, 16 Feb 2022 at 22:38, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17409
> Logged by: Holly Roberts
> Email address: holly(dot)roberts(at)starlingbank(dot)com
> PostgreSQL version: 14.2
> Operating system: Debian 10.2.1-6
> Description:
>
> When attempting to change the data type of a column that has previously been
> clustered on, which is also referenced by a foreign key, then an exception
> is thrown.
>
> Reproduction steps using a fresh database:
> CREATE TABLE parent (
> parent_field INTEGER CONSTRAINT pk_parent PRIMARY KEY
> );
> CREATE TABLE child (
> child_field INTEGER,
> CONSTRAINT fk_child FOREIGN KEY (child_field) REFERENCES parent
> (parent_field)
> );
> CLUSTER parent USING pk_parent;
> ALTER TABLE parent ALTER COLUMN parent_field SET DATA TYPE BIGINT;
>
> This throws the following error:
> ERROR: relation 16458 has multiple clustered indexes
> 'SELECT 16458::regclass' returns 'parent';
> This has previously worked on various versions of postgres 12 and 13 for me
> (latest tried 13.6)
>
It seems the following commit cause this problem.
commit 8b069ef5dca97cd737a5fd64c420df3cd61ec1c9
Author: Peter Eisentraut <peter(at)eisentraut(dot)org>
Change get_constraint_index() to use pg_constraint.conindid
It was still using a scan of pg_depend instead of using the conindid
column that has been added since.
Since it is now just a catalog lookup wrapper and not related to
pg_depend, move from pg_depend.c to lsyscache.c.
Reviewed-by: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Reviewed-by: Michael Paquier <michael(at)paquier(dot)xyz>
Discussion: https://www.postgresql.org/message-id/flat/4688d55c-9a2e-9a5a-d166-5f24fe0bf8db%40enterprisedb.com
After some analyze, I found `ALTER TABLE parent ALTER COLUMN parent_field SET DATA TYPE BIGINT`
will split into `ALTER TABLE parent ALTER COLUMN parent_field SET DATA TYPE BIGINT` and
`ALTER TABLE public.child ADD CONSTRAINT fk_child FOREIGN KEY (child_field) REFERENCES parent(parent_field)`
statements.
When the second stement executed in RememberConstraintForRebuilding(), the
get_constraint_index() returns valid oid after 8b069ef5, however, before this
commit, it returns invalid oid.
The different is that the get_constraint_index() uses pg_depend to find
constraint index oid before 8b069ef5, after this commit it uses lsyscache
to find index oid.
I'm not sure this is a bug or not. Any thoughts?
Also Cc to Peter Eisentraut who commits this.
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
From | Date | Subject | |
---|---|---|---|
Next Message | Japin Li | 2022-02-17 17:28:07 | Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key |
Previous Message | Tomas Vondra | 2022-02-17 16:35:00 | Re: BUG #17406: Segmentation fault on GiST index after 14.2 upgrade |