pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Japin Li <japinli@hotmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: holly.roberts@starlingbank.com
Cc: peter@eisentraut.org
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key
Date: Fri, 11 Mar 2022 12:37:17 -0500
Message-ID: <934540.1647020237@sss.pgh.pa.us> (raw)
In-Reply-To: <933033.1647019404@sss.pgh.pa.us>
References: <17409-52871dda8b5741cb@postgresql.org>
	<MEYP282MB16691B29F4FEA0CC45CD5FFAB6369@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
	<ME3P282MB1667E2B286DA22A16FD8A8FFB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
	<ME3P282MB1667F5A392BB4F26E6B0D82AB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
	<Yg8gGrJebITuPKu8@paquier.xyz>
	<MEYP282MB1669405BB9B46FBED34EB309B6379@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
	<755474.1646959065@sss.pgh.pa.us>
	<MEYP282MB1669CD6C93FD1DCA85CAB2C1B60C9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
	<933033.1647019404@sss.pgh.pa.us>

I wrote:
> ... The old behavior is what
> ATExecAlterColumnType wants, I judge.  As this stands, we will
> rebuild indexes that don't need to be rebuilt, and indeed might
> be on other tables altogether from the one that is being modified
> (which opens all sorts of potential locking problems).

Oh, well, not so much:

regression=# create table atref (p1 int constraint pk_atref primary key);
CREATE TABLE
regression=# create table attbl (c1 int, constraint fk_atref foreign key (c1) references atref(p1));
CREATE TABLE
regression=# cluster atref using pk_atref;
CLUSTER
regression=# alter table attbl alter column c1 set data type bigint;
ERROR:  "pk_atref" is not an index for table "attbl"

Still, that's just as unpleasant as the originally-reported case.
(I've not figured out yet why the "cluster" step is required to
make this repro work.)

			regards, tom lane





view thread (16+ messages)  latest in thread

Message-ID: <934540.1647020237@sss.pgh.pa.us>
Permalink:  ../934540.1647020237@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/934540.1647020237@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, japinli@hotmail.com, michael@paquier.xyz, 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
  In-Reply-To: <934540.1647020237@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox