Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key

On Fri, 18 Feb 2022 at 00:38, Japin Li <japinli@hotmail.com> wrote:
> On Wed, 16 Feb 2022 at 22:38, PG Bug reporting form <noreply@postgresql.org> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      17409
>> Logged by:          Holly Roberts
>> Email address:      holly.roberts@starlingbank.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@eisentraut.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@gmail.com>
>     Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
>     Reviewed-by: Michael Paquier <michael@paquier.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.

The RememberClusterOnForRebuilding() use the tab->clusterOnIndex to check
the cluster index exist or not, however, the cluster index can occur more
than once, so I think we should check the clustered index by index name.
Here is a patch to fix it.  Any suggestions?

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.