Date: Fri, 18 Feb 2022 13:27:06 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Japin Li <japinli@hotmail.com>
Cc: holly.roberts@starlingbank.com, peter@eisentraut.org,
	pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17409: Unable to alter data type of clustered column which
 is referenced by foreign key
Message-ID: <Yg8gGrJebITuPKu8@paquier.xyz>
References: <17409-52871dda8b5741cb@postgresql.org>
 <MEYP282MB16691B29F4FEA0CC45CD5FFAB6369@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB1667E2B286DA22A16FD8A8FFB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB1667F5A392BB4F26E6B0D82AB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="84J2EfwlgTxF9KQ8"
Content-Disposition: inline
In-Reply-To: 
 <ME3P282MB1667F5A392BB4F26E6B0D82AB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
Archived-At: 
 <https://www.postgresql.org/message-id/Yg8gGrJebITuPKu8%40paquier.xyz>
Precedence: bulk


--84J2EfwlgTxF9KQ8
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Feb 18, 2022 at 01:30:46AM +0800, Japin Li wrote:
> On Fri, 18 Feb 2022 at 01:28, Japin Li <japinli@hotmail.com> wrote:
>> 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?
>=20
> Sorry for forgetting attach the patch.

Reusing the test case at the top of the thread, this can also be
triggered for replica identities, as of the code just at the top of
what you have patched.  See for example:
CREATE TABLE parent (
  parent_field INTEGER CONSTRAINT pk_parent PRIMARY KEY);
ALTER TABLE parent REPLICA IDENTITY USING INDEX pk_parent;
CREATE TABLE child (
  child_field INTEGER,
  CONSTRAINT fk_child FOREIGN KEY (child_field) REFERENCES parent (parent_f=
ield));
-- error here
ALTER TABLE parent ALTER COLUMN parent_field SET DATA TYPE BIGINT;

We surely should have test cases for all that.

Anyway, I'd need to think more about your suggestion, but is that
actually the best thing we can do?  In this case, we'd attempt to
register twice an index to rebuild within
RememberIndexForRebuilding(), for the same relation, but there is no
need to do so.  Shouldn't we try instead to do a better job at
scanning the pg_depend entries in ATExecAlterColumnType() and avoid
the risk to do the same job twice?  That would take care of the
replica identity case that I have just mentioned, and of the clustered
index case reported upthread.
--
Michael

--84J2EfwlgTxF9KQ8
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmIPIBoACgkQnvQgOdby
QH3oWhAAgzb3bEJl6sFhzHlBSjH5uwQYcAk3vBuc7GHr4GvqTfjXa7/kSGh/GFC4
Cun8RbJ/lIq00IzRN6X9ZdgU06RgH7q8BkZ6jPDUaQdnYuwP6jcoc7QW4lBhuy9l
G7UPGo/ArnxVLQ8TwKmknFus8TEScaDexm9M64w105AZOlD9QibhvE3HpdT/hzY0
M21Qx1Yb1QMTwCM/gdHRU+QZnYg176IKMYGLHAe4vBbiL4yxtjQYqcyVmTey1+Wf
SLK2QWXEnX2FxxvZ8Z2hzGD4Und96+wd3Js4CI3JdtBQ+Gt+XzFKyw/042NLZeya
RAHEymx2NDXe55rcIEO9AwTYdZnO+e2R0mJdCluWPsFU1WAkcBfV8pFS5xVh+KdM
mfxmFl6oZOCCI4YCqG1EbxzGO4N+FGQowsxKn/aSm43r5OYlPXJwkWaXlq63kxEJ
3ZzOxDXeWVukpZZvbPlwKWZ+dPmQ/HNw5tEhLhbOrYYmO3Bol44HnJQCEfWntywJ
dlAQ5gGLlzbqwYGHL9+l+OSV7EUJA+qm5dIJCe4DrVj1dxxoxx5JSKO4H+9mNjaC
vp/3dQjWn0m9K5BhuIOrOHfO1zLk7Cx7Z1LBkkr+shluaQ4TqoJt1AgDfgJHcdr7
q40lt8Q/e0+KLfoyC/4UaCpqVyuNaBgaYGaISQ/Gq4dGr82wpn4=
=EoKv
-----END PGP SIGNATURE-----

--84J2EfwlgTxF9KQ8--