pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Refuse ATTACH of a table referenced by a foreign key 7+ messages / 1 participants [nested] [flat]
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/a90bdd7a44d088a10b2faf5d7cdb85b8e4f0c662 Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/8c0944ac878d3d3e433da8feb2f7e134be8353f3 Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e97121d90e91032e9ff5d963202e835c96f298dd Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2ee02c98dd17f19ac5407e80c904d408931ab9ec Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/17ed92e1ff60392e9dbfbee79824e45ae0751a0b Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ada34d7146c343a143de811eefc4fdd190ce40a5 Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Refuse ATTACH of a table referenced by a foreign key @ 2024-08-08 23:36 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2024-08-08 23:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/344f9f5e2b181331ead4b7bcd792216ecc5f2946 Modified Files -------------- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ src/test/regress/expected/foreign_key.out | 17 +++++++++++++++++ src/test/regress/sql/foreign_key.sql | 17 +++++++++++++++++ 3 files changed, 51 insertions(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-08-08 23:36 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key Alvaro Herrera <alvherre@alvh.no-ip.org> 2024-08-08 23:36 pgsql: Refuse ATTACH of a table referenced by a foreign key 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