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(at)hotmail(dot)com> wrote:
> On Fri, 18 Feb 2022 at 00:38, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> 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)
>>>
>
> 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.