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

On Fri, 18 Feb 2022 at 01:28, Japin Li <japinli@hotmail.com> wrote:
> 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)
>>>
>
> 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?

Sorry for forgetting attach the patch.

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