public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] remove deprecated v8.2 containment operators 3+ messages / 3 participants [nested] [flat]
* [PATCH] remove deprecated v8.2 containment operators @ 2020-04-12 03:57 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2020-04-12 03:57 UTC (permalink / raw) See also: ba920e1c9182eac55d5f1327ab0d29b721154277 684ad6a92fcc33adebdab65c4e7d72a68ba05408 3165426e54df02a6199c0a216546e5095e875a0a 2f70fdb0644c32c4154236c2b5c241bec92eac5e 591d282e8d3e0448ec1339c6b066e10953f040a2 --- contrib/cube/Makefile | 2 +- contrib/cube/cube--1.4--1.5.sql | 8 ++++++++ contrib/cube/cube.control | 2 +- contrib/hstore/Makefile | 1 + contrib/hstore/hstore--1.8--1.9.sql | 7 +++++++ contrib/hstore/hstore.control | 2 +- contrib/intarray/Makefile | 2 +- contrib/intarray/intarray--1.4--1.5.sql | 10 ++++++++++ contrib/intarray/intarray.control | 2 +- contrib/seg/Makefile | 2 +- contrib/seg/seg--1.3--1.4.sql | 8 ++++++++ contrib/seg/seg.control | 2 +- doc/src/sgml/cube.sgml | 8 -------- doc/src/sgml/hstore.sgml | 10 ---------- doc/src/sgml/intarray.sgml | 8 -------- doc/src/sgml/seg.sgml | 8 -------- 16 files changed, 41 insertions(+), 41 deletions(-) create mode 100644 contrib/cube/cube--1.4--1.5.sql create mode 100644 contrib/hstore/hstore--1.8--1.9.sql create mode 100644 contrib/intarray/intarray--1.4--1.5.sql create mode 100644 contrib/seg/seg--1.3--1.4.sql diff --git a/contrib/cube/Makefile b/contrib/cube/Makefile index 54f609db17..cf195506c7 100644 --- a/contrib/cube/Makefile +++ b/contrib/cube/Makefile @@ -7,7 +7,7 @@ OBJS = \ cubeparse.o EXTENSION = cube -DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql \ +DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql cube--1.4--1.5.sql \ cube--1.1--1.2.sql cube--1.0--1.1.sql PGFILEDESC = "cube - multidimensional cube data type" diff --git a/contrib/cube/cube--1.4--1.5.sql b/contrib/cube/cube--1.4--1.5.sql new file mode 100644 index 0000000000..54492e5d18 --- /dev/null +++ b/contrib/cube/cube--1.4--1.5.sql @@ -0,0 +1,8 @@ +/* contrib/cube/cube--1.4--1.5.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION cube UPDATE TO '1.5'" to load this file. \quit + +-- Remove @ and ~ +DROP OPERATOR @ (cube, cube); +DROP OPERATOR ~ (cube, cube); diff --git a/contrib/cube/cube.control b/contrib/cube/cube.control index 3e238fc937..50427ec117 100644 --- a/contrib/cube/cube.control +++ b/contrib/cube/cube.control @@ -1,6 +1,6 @@ # cube extension comment = 'data type for multidimensional cubes' -default_version = '1.4' +default_version = '1.5' module_pathname = '$libdir/cube' relocatable = true trusted = true diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile index c4e339b57c..97b228b65f 100644 --- a/contrib/hstore/Makefile +++ b/contrib/hstore/Makefile @@ -12,6 +12,7 @@ OBJS = \ EXTENSION = hstore DATA = hstore--1.4.sql \ + hstore--1.8--1.9.sql \ hstore--1.7--1.8.sql \ hstore--1.6--1.7.sql \ hstore--1.5--1.6.sql \ diff --git a/contrib/hstore/hstore--1.8--1.9.sql b/contrib/hstore/hstore--1.8--1.9.sql new file mode 100644 index 0000000000..7cd3467c55 --- /dev/null +++ b/contrib/hstore/hstore--1.8--1.9.sql @@ -0,0 +1,7 @@ +/* contrib/hstore/hstore--1.8--1.9.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION hstore UPDATE TO '1.9'" to load this file. \quit + +-- Remove @ +DROP OPERATOR @ (hstore, hstore); diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control index 89e3c746c4..b73c28aa4d 100644 --- a/contrib/hstore/hstore.control +++ b/contrib/hstore/hstore.control @@ -1,6 +1,6 @@ # hstore extension comment = 'data type for storing sets of (key, value) pairs' -default_version = '1.8' +default_version = '1.9' module_pathname = '$libdir/hstore' relocatable = true trusted = true diff --git a/contrib/intarray/Makefile b/contrib/intarray/Makefile index 01faa36b10..3817c1669a 100644 --- a/contrib/intarray/Makefile +++ b/contrib/intarray/Makefile @@ -12,7 +12,7 @@ OBJS = \ _intbig_gist.o EXTENSION = intarray -DATA = intarray--1.3--1.4.sql intarray--1.2--1.3.sql \ +DATA = intarray--1.4--1.5.sql intarray--1.3--1.4.sql intarray--1.2--1.3.sql \ intarray--1.2.sql intarray--1.1--1.2.sql \ intarray--1.0--1.1.sql PGFILEDESC = "intarray - functions and operators for arrays of integers" diff --git a/contrib/intarray/intarray--1.4--1.5.sql b/contrib/intarray/intarray--1.4--1.5.sql new file mode 100644 index 0000000000..d0ac11545c --- /dev/null +++ b/contrib/intarray/intarray--1.4--1.5.sql @@ -0,0 +1,10 @@ +/* contrib/intarray/intarray--1.4--1.5.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION intarray UPDATE TO '1.5'" to load this file. \quit + +-- Remove @ +DROP OPERATOR @ (_int4, _int4); + +-- Remove ~ from GIN - it was removed from gist in 1.4 +DROP OPERATOR ~ (_int4, _int4); diff --git a/contrib/intarray/intarray.control b/contrib/intarray/intarray.control index bbc837c573..c3ff753e2c 100644 --- a/contrib/intarray/intarray.control +++ b/contrib/intarray/intarray.control @@ -1,6 +1,6 @@ # intarray extension comment = 'functions, operators, and index support for 1-D arrays of integers' -default_version = '1.4' +default_version = '1.5' module_pathname = '$libdir/_int' relocatable = true trusted = true diff --git a/contrib/seg/Makefile b/contrib/seg/Makefile index f3578a8634..bb63e83506 100644 --- a/contrib/seg/Makefile +++ b/contrib/seg/Makefile @@ -7,7 +7,7 @@ OBJS = \ segparse.o EXTENSION = seg -DATA = seg--1.1.sql seg--1.1--1.2.sql seg--1.2--1.3.sql \ +DATA = seg--1.1.sql seg--1.1--1.2.sql seg--1.2--1.3.sql seg--1.3--1.4.sql \ seg--1.0--1.1.sql PGFILEDESC = "seg - line segment data type" diff --git a/contrib/seg/seg--1.3--1.4.sql b/contrib/seg/seg--1.3--1.4.sql new file mode 100644 index 0000000000..13babddba4 --- /dev/null +++ b/contrib/seg/seg--1.3--1.4.sql @@ -0,0 +1,8 @@ +/* contrib/seg/seg--1.3--1.4.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION seg UPDATE TO '1.4'" to load this file. \quit + +-- Remove @ and ~ +DROP OPERATOR @ (seg, seg); +DROP OPERATOR ~ (seg, seg); diff --git a/contrib/seg/seg.control b/contrib/seg/seg.control index 9ac3080848..e2c6a4750f 100644 --- a/contrib/seg/seg.control +++ b/contrib/seg/seg.control @@ -1,6 +1,6 @@ # seg extension comment = 'data type for representing line segments or floating-point intervals' -default_version = '1.3' +default_version = '1.4' module_pathname = '$libdir/seg' relocatable = true trusted = true diff --git a/doc/src/sgml/cube.sgml b/doc/src/sgml/cube.sgml index 3dd89bb14d..adf8dbaa91 100644 --- a/doc/src/sgml/cube.sgml +++ b/doc/src/sgml/cube.sgml @@ -222,14 +222,6 @@ </tgroup> </table> - <para> - (Before PostgreSQL 8.2, the containment operators <literal>@></literal> and <literal><@</literal> were - respectively called <literal>@</literal> and <literal>~</literal>. These names are still available, but are - deprecated and will eventually be retired. Notice that the old names - are reversed from the convention formerly followed by the core geometric - data types!) - </para> - <para> In addition to the above operators, the usual comparison operators shown in <xref linkend="functions-comparison-op-table"/> are diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml index e867fcc5ae..db5779052a 100644 --- a/doc/src/sgml/hstore.sgml +++ b/doc/src/sgml/hstore.sgml @@ -318,16 +318,6 @@ key => NULL </tgroup> </table> - <note> - <para> - Prior to PostgreSQL 8.2, the containment operators <literal>@></literal> - and <literal><@</literal> were called <literal>@</literal> and <literal>~</literal>, - respectively. These names are still available, but are deprecated and will - eventually be removed. Notice that the old names are reversed from the - convention formerly followed by the core geometric data types! - </para> - </note> - <table id="hstore-func-table"> <title><type>hstore</type> Functions</title> <tgroup cols="1"> diff --git a/doc/src/sgml/intarray.sgml b/doc/src/sgml/intarray.sgml index af44c7b214..dfe98279c0 100644 --- a/doc/src/sgml/intarray.sgml +++ b/doc/src/sgml/intarray.sgml @@ -364,14 +364,6 @@ </tgroup> </table> - <para> - (Before PostgreSQL 8.2, the containment operators <literal>@></literal> and - <literal><@</literal> were respectively called <literal>@</literal> and <literal>~</literal>. - These names are still available, but are deprecated and will eventually be - retired. Notice that the old names are reversed from the convention - formerly followed by the core geometric data types!) - </para> - <para> The operators <literal>&&</literal>, <literal>@></literal> and <literal><@</literal> are equivalent to <productname>PostgreSQL</productname>'s built-in diff --git a/doc/src/sgml/seg.sgml b/doc/src/sgml/seg.sgml index e0dfbc76cf..9be69e3609 100644 --- a/doc/src/sgml/seg.sgml +++ b/doc/src/sgml/seg.sgml @@ -352,14 +352,6 @@ test=> select '6.25 .. 6.50'::seg as "pH"; </tgroup> </table> - <para> - (Before PostgreSQL 8.2, the containment operators <literal>@></literal> and <literal><@</literal> were - respectively called <literal>@</literal> and <literal>~</literal>. These names are still available, but are - deprecated and will eventually be retired. Notice that the old names - are reversed from the convention formerly followed by the core geometric - data types!) - </para> - <para> In addition to the above operators, the usual comparison operators shown in <xref linkend="functions-comparison-op-table"/> are -- 2.17.0 --0OWHXb1mYLuhj1Ox-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* Bug in detaching a partition with a foreign key. @ 2025-01-16 13:53 Amul Sul <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Amul Sul @ 2025-01-16 13:53 UTC (permalink / raw) To: pgsql-hackers Hi, While detaching a partition with a foreign key referencing a partitioned table, I am getting the following error: ERROR: could not find ON INSERT check triggers of foreign key constraint 16636 I haven’t looked closely at what the issue might be, but it seems the logic inside DetachPartitionFinalize(), which is supposed to iterate over inherited foreign keys, is lacking. The attached trial patch fixes the issue for me, but I’m not sure if it’s the correct fix. I’ll take a closer look later. Here is the test: CREATE TABLE bar(id int PRIMARY KEY) PARTITION BY RANGE(id); CREATE TABLE bar_p0 PARTITION OF bar FOR VALUES FROM (0) TO (100); CREATE TABLE foo(id int) PARTITION BY RANGE(id); CREATE TABLE foo_p0 PARTITION OF foo FOR VALUES FROM (0) TO (100) PARTITION BY RANGE(id); CREATE TABLE foo_p0_p0 PARTITION OF foo_p0 FOR VALUES FROM (0) TO (100); ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar; ALTER TABLE foo DETACH PARTITION foo_p0; -- Regards, Amul Sul EDB: http://www.enterprisedb.com Attachments: [application/x-patch] trial.patch (1.2K, ../../CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com/2-trial.patch) download | inline diff: diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index c42a740ccef..cacdb9b475d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -19926,6 +19926,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, HeapTuple tuple, newtuple; Relation trigrel = NULL; + List *fksids; if (concurrent) { @@ -19946,6 +19947,11 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, fks = copyObject(RelationGetFKeyList(partRel)); if (fks != NIL) trigrel = table_open(TriggerRelationId, RowExclusiveLock); + + /* Collect all the constraint ids */ + foreach(cell, fks) + fksids = lappend_oid(fksids, lfirst_node(ForeignKeyCacheInfo, cell)->conoid); + foreach(cell, fks) { ForeignKeyCacheInfo *fk = lfirst(cell); @@ -19961,7 +19967,8 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, /* consider only the inherited foreign keys */ if (conform->contype != CONSTRAINT_FOREIGN || - !OidIsValid(conform->conparentid)) + !OidIsValid(conform->conparentid) || + list_member_oid(fksids, conform->conparentid)) { ReleaseSysCache(contup); continue; ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug in detaching a partition with a foreign key. @ 2025-01-17 20:44 Sami Imseih <[email protected]> parent: Amul Sul <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Sami Imseih @ 2025-01-17 20:44 UTC (permalink / raw) To: Amul Sul <[email protected]>; +Cc: pgsql-hackers This is a bug indeed. I tried your patch, but it ends up in a seg fault. I also see this was raised in another thread [0]. It can be reproduced in a slightly simplified case, using only a single level partition. """ CREATE TABLE bar(id int PRIMARY KEY) PARTITION BY RANGE(id); CREATE TABLE bar_p0 PARTITION OF bar FOR VALUES FROM (0) TO (100); CREATE TABLE foo(id int) PARTITION BY RANGE(id); CREATE TABLE foo_p0 PARTITION OF foo FOR VALUES FROM (0) TO (100); ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar; ALTER TABLE foo DETACH PARTITION foo_p0 ; """ Here is what I I found. In DetachPartitionFinalize, after the child is detached from the parent, the FK's insert and update triggers are then removed from pg_depend with TriggerSetParentTrigger /* * The constraint on this table must be marked no longer a child of * the parent's constraint, as do its check triggers. */ ConstraintSetParentConstraint(fk->conoid, InvalidOid, InvalidOid); /* * Also, look up the partition's "check" triggers corresponding to the * constraint being detached and detach them from the parent triggers. */ GetForeignKeyCheckTriggers(trigrel, fk->conoid, fk->confrelid, fk->conrelid, &insertTriggerOid, &updateTriggerOid); Assert(OidIsValid(insertTriggerOid)); TriggerSetParentTrigger(trigrel, insertTriggerOid, InvalidOid, RelationGetRelid(partRel)); Assert(OidIsValid(updateTriggerOid)); TriggerSetParentTrigger(trigrel, updateTriggerOid, InvalidOid, RelationGetRelid(partRel)); Specifically, the dependency types DEPENDENCY_PARTITION_PRI and DEPENDENCY_PARTITION_SEC are removed from pg_depend. deleteDependencyRecordsForClass(TriggerRelationId, childTrigId, TriggerRelationId, DEPENDENCY_PARTITION_PRI); deleteDependencyRecordsForClass(TriggerRelationId, childTrigId, RelationRelationId, DEPENDENCY_PARTITION_SEC); In the repro case, an FK on a partition with a reference to a partition parent table does not create a new dependency. postgres=# SELECT count(*) total, deptype FROM pg_depend WHERE deptype in ('P', 'S') group by deptype; total | deptype -------+--------- 2 | P 2 | S (2 rows) postgres=# ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar; ALTER TABLE postgres=# postgres=# SELECT count(*) total, deptype FROM pg_depend WHERE deptype in ('P', 'S') group by deptype; total | deptype -------+--------- 2 | P 2 | S (2 rows) We also see that the FK riggers created are associated with the parent constraint rather than the child constraint, i.e. tgconstraint = 17387 postgres=# ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar; ALTER TABLE postgres=# postgres=# SELECT oid, tgrelid::regclass relname, tgparentid, 'insert' trigger_type, tgconstraint FROM pg_trigger WHERE tgfoid in (1644, 1645) postgres-# and tgtype & (1 << 2) > 0 postgres-# union all postgres-# SELECT oid, tgrelid::regclass relname, tgparentid, 'update' trigger_type, tgconstraint FROM pg_trigger WHERE tgfoid in (1644, 1645) postgres-# and tgtype & (1 << 4) > 0; oid | relname | tgparentid | trigger_type | tgconstraint -------+---------+------------+--------------+-------------- 17393 | foo_p0 | 0 | insert | 17387 17394 | foo_p0 | 0 | update | 17387 (2 rows) postgres=# ALTER TABLE foo DETACH PARTITION foo_p0 ; ERROR: could not find ON INSERT check triggers of foreign key constraint 17390 postgres=# postgres=# select oid, conparentid, conrelid::regclass, confrelid::regclass from pg_constraint where oid = 17390; oid | conparentid | conrelid | confrelid -------+-------------+----------+----------- 17390 | 17387 | foo_p0 | bar_p0 (1 row) postgres=# select oid, conparentid, conrelid::regclass, confrelid::regclass from pg_constraint where oid = 17387; oid | conparentid | conrelid | confrelid -------+-------------+----------+----------- 17387 | 0 | foo_p0 | bar (1 row) This is not the case when the constraint is created on the parent table, i.e. ALTER TABLE foo ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar In this case we also see a dependency. postgres=# SELECT count(*) total, deptype FROM pg_depend WHERE deptype in ('P', 'S') group by deptype; total | deptype -------+--------- 2 | P 2 | S (2 rows) postgres=# ALTER TABLE foo ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar; ALTER TABLE postgres=# SELECT count(*) total, deptype FROM pg_depend WHERE deptype in ('P', 'S') group by deptype; total | deptype -------+--------- 3 | P 3 | S (2 rows) Also, the constraint relname in the parent constraint is that of the parent table and the child constraint is that of the child table. postgres=*# ALTER TABLE foo DETACH PARTITION foo_p0 ; ALTER TABLE postgres=*# ROLLBACK; ROLLBACK ^ postgres=# select oid, conparentid, conrelid::regclass, confrelid::regclass from pg_constraint where oid = 17455; oid | conparentid | conrelid | confrelid -------+-------------+----------+----------- 17455 | 17447 | foo_p0 | bar (1 row) postgres=# select oid, conparentid, conrelid::regclass, confrelid::regclass from pg_constraint where oid = 17447; oid | conparentid | conrelid | confrelid -------+-------------+----------+----------- 17447 | 0 | foo | bar (1 row If the relation on the parent and child constraint match, that tells us we don't have inheritance. So, I am thinking we should add another condition for checking if a foreign key is inherited by checking if the parent constraint relation is different from the child constraint relation. I am attaching an unpolished patch ( we need test coverage as well ) that implements the above. All tests pass with this patch. Regards, Sami Imseih Amazon Web Services (AWS) [0] https://www.postgresql.org/message-id/flat/CAHewXNm5rtfQZNv2uWkiHZVJeicFFa4x7p0%3Dy-x2vAM0vorgNQ%40m... Attachments: [application/octet-stream] Fix-DETACH-PARTITION-with-foreign-key-referencing.patch (1.8K, ../../CAA5RZ0vk4SJ9PiD2RyG-CKOYvOFewz6QweKcp2_EegBKns=dOA@mail.gmail.com/2-Fix-DETACH-PARTITION-with-foreign-key-referencing.patch) download | inline diff: From f3cad6887230cc20f2b4e74ed9c9f25c2b47eda1 Mon Sep 17 00:00:00 2001 From: Sami Imseih <[email protected]> Date: Fri, 17 Jan 2025 14:25:52 -0600 Subject: [PATCH v1 1/1] Fix DETACH PARTITION with foreign key referencing a parent partition --- src/backend/commands/tablecmds.c | 16 +++++++++++++++- 1 file changed, 15 insertions(+), 1 deletion(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d2420a9558..f2a14f0785 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -20009,18 +20009,32 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, { ForeignKeyCacheInfo *fk = lfirst(cell); HeapTuple contup; + HeapTuple contup_parent; Form_pg_constraint conform; + Form_pg_constraint conform_parent; Oid insertTriggerOid, updateTriggerOid; + Oid parentConRelid = InvalidOid; contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid)); if (!HeapTupleIsValid(contup)) elog(ERROR, "cache lookup failed for constraint %u", fk->conoid); conform = (Form_pg_constraint) GETSTRUCT(contup); + if (OidIsValid(conform->conparentid)) + { + contup_parent = SearchSysCache1(CONSTROID, ObjectIdGetDatum(conform->conparentid)); + if (!HeapTupleIsValid(contup_parent)) + elog(ERROR, "cache lookup failed for constraint %u", conform->conparentid); + conform_parent = (Form_pg_constraint) GETSTRUCT(contup_parent); + parentConRelid = conform_parent->conrelid; + ReleaseSysCache(contup_parent); + } + /* consider only the inherited foreign keys */ if (conform->contype != CONSTRAINT_FOREIGN || - !OidIsValid(conform->conparentid)) + !OidIsValid(conform->conparentid) || + (conform->conrelid == parentConRelid)) { ReleaseSysCache(contup); continue; -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-17 20:44 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-04-12 03:57 [PATCH] remove deprecated v8.2 containment operators Justin Pryzby <[email protected]> 2025-01-16 13:53 Bug in detaching a partition with a foreign key. Amul Sul <[email protected]> 2025-01-17 20:44 ` Re: Bug in detaching a partition with a foreign key. Sami Imseih <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox