pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18541: Reattaching a partition referenced by a foreign key fails with an error
7+ messages / 3 participants
[nested] [flat]
* BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
@ 2024-07-15 10:00 PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2024-07-15 10:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 18541
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 17beta2
Operating system: Ubuntu 22.04
Description:
The following script:
CREATE TABLE t1 (a int, PRIMARY KEY (a));
CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
PARTITION BY LIST (a);
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
ALTER TABLE t DETACH PARTITION t1;
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
ends up with the error complaining about check triggers:
ERROR: XX000: could not find ON INSERT check triggers of foreign key
constraint 16400
LOCATION: GetForeignKeyCheckTriggers, tablecmds.c:11260
Reproduced on REL_15_STABLE (starting from f4566345c) .. master.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
@ 2024-07-15 13:18 ` Tender Wang <tndrwang@gmail.com>
2024-08-07 18:44 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-07 22:22 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 7+ messages in thread
From: Tender Wang @ 2024-07-15 13:18 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> 于2024年7月15日周一 21:02写道：
> The following bug has been logged on the website:
>
> Bug reference: 18541
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 17beta2
> Operating system: Ubuntu 22.04
> Description:
>
> The following script:
> CREATE TABLE t1 (a int, PRIMARY KEY (a));
> CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
> PARTITION BY LIST (a);
> ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
> ALTER TABLE t DETACH PARTITION t1;
> ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
>
> ends up with the error complaining about check triggers:
> ERROR: XX000: could not find ON INSERT check triggers of foreign key
> constraint 16400
> LOCATION: GetForeignKeyCheckTriggers, tablecmds.c:11260
>
> Reproduced on REL_15_STABLE (starting from f4566345c) .. master.
>
> Hi Alexander,
I saw the same error in [1]. I guess it is same issue.
I send a patch in [1], but it may need a more work.
[1] https://www.postgresql.org/message-id/20230705233028.2f554f73%40karst
--
Tender Wang
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
@ 2024-08-07 18:44 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-07 18:50 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2024-08-07 18:44 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2024-Jul-15, Tender Wang wrote:
> PG Bug reporting form <noreply@postgresql.org> 于2024年7月15日周一 21:02写道：
>
> > The following script:
> > CREATE TABLE t1 (a int, PRIMARY KEY (a));
> > CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
> > PARTITION BY LIST (a);
> > ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
> > ALTER TABLE t DETACH PARTITION t1;
> > ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
> >
> > ends up with the error complaining about check triggers:
> > ERROR: XX000: could not find ON INSERT check triggers of foreign key
> > constraint 16400
> > LOCATION: GetForeignKeyCheckTriggers, tablecmds.c:11260
> I saw the same error in [1]. I guess it is same issue.
> I send a patch in [1], but it may need a more work.
I think this schema is nuts. Do you have a practical use for something
like this?
I am tempted to fix this by dictating that you can't join a table as a
partition if the partitioned table contains an FK that references that
table.
FWIW the patch I have fixes all the other reported problems with FKs and
partitioned tables, but not this one.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
2024-08-07 18:44 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2024-08-07 18:50 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2024-08-07 18:50 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2024-Aug-07, Alvaro Herrera wrote:
> I think this schema is nuts. Do you have a practical use for something
> like this?
FWIW if you try to set this up the other way around, you get a different
error:
create table t (a int primary key) partition by list (a);
create table t1 partition of t for values in (1);
alter table t add foreign key (a) references t1;
ERROR: cannot ALTER TABLE "t1" because it is being used by active queries in this session
so I'm not bothered by the restriction in functionality.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
@ 2024-08-07 22:22 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-08 14:27 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2024-08-07 22:22 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2024-Jul-15, Tender Wang wrote:
> PG Bug reporting form <noreply@postgresql.org> 于2024年7月15日周一 21:02写道：
> > The following script:
> > CREATE TABLE t1 (a int, PRIMARY KEY (a));
> > CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
> > PARTITION BY LIST (a);
> > ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
I propose to reject this with the attached patch, which I intend to
backpatch all the way down to 12.
FWIW, it's not the same problem that Jehan-Guillaume described in [1],
even though the error message being thrown is the same.
[1] https://postgr.es/m/20230705233028.2f554f73@karst
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Attachments:
[text/x-diff] 0001-Refuse-ATTACH-of-a-table-referenced-by-a-foreign-key.patch (0B, ../../202408072222.icgykv5yrql5@alvherre.pgsql/2-0001-Refuse-ATTACH-of-a-table-referenced-by-a-foreign-key.patch)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
2024-08-07 22:22 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2024-08-08 14:27 ` Tender Wang <tndrwang@gmail.com>
2024-08-08 23:37 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tender Wang @ 2024-08-08 14:27 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> 于2024年8月8日周四 06:22写道：
> On 2024-Jul-15, Tender Wang wrote:
>
> > PG Bug reporting form <noreply@postgresql.org> 于2024年7月15日周一 21:02写道：
>
> > > The following script:
> > > CREATE TABLE t1 (a int, PRIMARY KEY (a));
> > > CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
> > > PARTITION BY LIST (a);
> > > ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
>
> I propose to reject this with the attached patch, which I intend to
> backpatch all the way down to 12.
>
> FWIW, it's not the same problem that Jehan-Guillaume described in [1],
> even though the error message being thrown is the same.
>
Thanks for explanation. The attached patch looks good to me.
> [1] https://postgr.es/m/20230705233028.2f554f73@karst
>
>
--
Tender Wang
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
2024-08-07 22:22 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-08 14:27 ` Re: BUG #18541: Reattaching a partition referenced by a foreign key fails with an error Tender Wang <tndrwang@gmail.com>
@ 2024-08-08 23:37 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2024-08-08 23:37 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2024-Aug-08, Tender Wang wrote:
> Thanks for explanation. The attached patch looks good to me.
Thanks for looking! I have pushed this now.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-08-08 23:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-15 10:00 BUG #18541: Reattaching a partition referenced by a foreign key fails with an error PG Bug reporting form <noreply@postgresql.org>
2024-07-15 13:18 ` Tender Wang <tndrwang@gmail.com>
2024-08-07 18:44 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-07 18:50 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-07 22:22 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-08 14:27 ` Tender Wang <tndrwang@gmail.com>
2024-08-08 23:37 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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