BUG #15425: DETACH/ATTACH PARTITION bug
Started by PG Bug reporting formover 7 years ago5 messages
The following bug has been logged on the website:
Bug reference: 15425
Logged by: Michael Vitale
Email address: mikemjv@gmail.com
PostgreSQL version: 11beta4
Operating system: CentOS
Description:
After I DETACH a partition, and then try to ATTACH it again, I get errors:
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(26702, 0,
test_result_asset_id_fkey) already exists.
It looks like it is trying to add the foreign key again.
So then I try to delete that foreign key before trying to attach it again,
but now I get another error:
ERROR: cannot drop inherited constraint "test_result_asset_id_fkey" of
relation "test_result_cbsystem_0001_0050_monthly_2018_09"
But why would I get that last error since my table is detached at that point
as shown by \d+ tablename
And obviously I cannot use inherit/disinherit logic against these tables
since they were created as declarative partitions.
Maybe this wasn't tested thoroughly in the FK addition to partitioned
tables?
I find that hard to believe that I would have to cascade down and drop this
foreign key for all attached partitions before being able to add one
partition back in via ATTACH (edited)
Thinking more about it, it's as if the intention was to create the indexes
and foreign keys for new partitions being attached, not ones that were
detached for maintenance reasons, vacuum full, etc., that still have the
foreign keys and indexes defined no these detached partitions.
I deleted that one FK from the parent and then tried again to attach the
partition. I got the same error but for the next foreign key on that
table.
Finally, I deleted all the foreign keys from the parent table, and then was
allowed to ATTACH the detached partition back in.
This has to be a bug because nobody is gonna want to take the performance
hit of recreating all the foreign keys on partitioned tables whenever a
partition is detached and attached again.
Re: BUG #15425: DETACH/ATTACH PARTITION bug
On 2018-Oct-10, PG Bug reporting form wrote:
After I DETACH a partition, and then try to ATTACH it again, I get errors:
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(26702, 0,
test_result_asset_id_fkey) already exists.It looks like it is trying to add the foreign key again.
Thanks. Reproduced with
create table main (a int primary key);
create table part (a int references main) partition by range (a);
create table part1 partition of part for values from (1) to (100);
alter table part detach partition part1;
alter table part attach partition part1 for values from (1) to (100);
Looking into it.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #15425: DETACH/ATTACH PARTITION bug
On 2018-Oct-10, Alvaro Herrera wrote:
On 2018-Oct-10, PG Bug reporting form wrote:
After I DETACH a partition, and then try to ATTACH it again, I get errors:
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(26702, 0,
test_result_asset_id_fkey) already exists.It looks like it is trying to add the foreign key again.
Thanks. Reproduced with
create table main (a int primary key);
create table part (a int references main) partition by range (a);
create table part1 partition of part for values from (1) to (100);
alter table part detach partition part1;
alter table part attach partition part1 for values from (1) to (100);
There are two bugs here, actually. One is that detaching the partition
does not make the FK independent, so if you later drop the partitioned
table, the FK in the partition goes away. The second is that attaching
a partition does not first see whether a convenient FK is defined in the
partition, so we would create a duplicate one.
AFAICS the attached fixes both things. Could you please verify that it
fixes your scenario too?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
detach-fks.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 2063abb8ae..abc704d3e7 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -403,6 +403,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
TupleDesc tupdesc;
HeapTuple tuple;
AttrNumber *attmap;
+ List *partFKs;
parentRel = heap_open(parentId, NoLock); /* already got lock */
/* see ATAddForeignKeyConstraint about lock level */
@@ -425,6 +426,8 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
NULL, 1, &key);
+ partFKs = copyObject(RelationGetFKeyList(rel));
+
while ((tuple = systable_getnext(scan)) != NULL)
{
Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
@@ -435,11 +438,13 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
Oid conppeqop[INDEX_MAX_KEYS];
Oid conffeqop[INDEX_MAX_KEYS];
Constraint *fkconstraint;
+ bool attach_it;
ClonedConstraint *newc;
Oid constrOid;
ObjectAddress parentAddr,
childAddr;
int nelem;
+ ListCell *cell;
int i;
ArrayType *arr;
Datum datum;
@@ -539,6 +544,66 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
elog(ERROR, "conffeqop is not a 1-D OID array");
memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+ /*
+ * Before creating a new constraint, see whether any existing FKs are
+ * fit for the purpose. If it is, attach the parent constraint to it
+ * instead of creating a new constraint. This way we avoid the
+ * expensive verification step and don't end up with a duplicate FK.
+ */
+ attach_it = false;
+ foreach (cell, partFKs)
+ {
+ ForeignKeyCacheInfo *fk = lfirst_node(ForeignKeyCacheInfo, cell);
+
+ if (fk->confrelid == constrForm->confrelid &&
+ fk->nkeys == nelem)
+ {
+ Form_pg_constraint partConstr;
+ HeapTuple partcontup;
+
+ /* Do some quick & easy initial checks */
+ attach_it = true;
+ for (i = 0; i < nelem; i++)
+ {
+ if (fk->conkey[i] != mapped_conkey[i] ||
+ fk->confkey[i] != confkey[i] ||
+ fk->conpfeqop[i] != conpfeqop[i])
+ {
+ attach_it = false;
+ break;
+ }
+ }
+
+ /* looks good ... do some more extensive checks */
+ partcontup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(fk->conoid));
+ if (!partcontup)
+ elog(ERROR, "cache lookup failed for constraint %u",
+ fk->conoid);
+ partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
+ if (partConstr->condeferrable != constrForm->condeferrable ||
+ partConstr->condeferred != constrForm->condeferred ||
+ partConstr->confupdtype != constrForm->confupdtype ||
+ partConstr->confdeltype != constrForm->confdeltype ||
+ partConstr->confmatchtype != constrForm->confmatchtype)
+ {
+ attach_it = false;
+ ReleaseSysCache(partcontup);
+ break;
+ }
+
+ ReleaseSysCache(partcontup);
+ if (attach_it)
+ {
+ ConstraintSetParentConstraint(fk->conoid,
+ HeapTupleGetOid(tuple));
+ break;
+ }
+ }
+ }
+ if (attach_it)
+ continue;
+
constrOid =
CreateConstraintEntry(NameStr(constrForm->conname),
constrForm->connamespace,
@@ -602,6 +667,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
systable_endscan(scan);
pfree(attmap);
+ list_free_deep(partFKs);
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
@@ -1028,17 +1094,33 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
elog(ERROR, "cache lookup failed for constraint %u", childConstrId);
newtup = heap_copytuple(tuple);
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
- constrForm->conislocal = false;
- constrForm->coninhcount++;
- constrForm->conparentid = parentConstrId;
- CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ if (OidIsValid(parentConstrId))
+ {
+ constrForm->conislocal = false;
+ constrForm->coninhcount++;
+ constrForm->conparentid = parentConstrId;
+
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+
+ ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
+ ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
+
+ recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
+ }
+ else
+ {
+ constrForm->coninhcount--;
+ if (constrForm->coninhcount <= 0)
+ constrForm->conislocal = true;
+ constrForm->conparentid = InvalidOid;
+
+ deleteDependencyRecordsForClass(ConstraintRelationId, childConstrId,
+ ConstraintRelationId,
+ DEPENDENCY_INTERNAL_AUTO);
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ }
+
ReleaseSysCache(tuple);
-
- ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
- ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
-
- recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
-
heap_close(constrRel, RowExclusiveLock);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f988c16659..2045feb17e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14607,6 +14607,7 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
ObjectAddress address;
Oid defaultPartOid;
List *indexes;
+ List *fks;
ListCell *cell;
/*
@@ -14682,6 +14683,24 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
}
heap_close(classRel, RowExclusiveLock);
+ /* Detach foreign keys */
+
+ fks = copyObject(RelationGetFKeyList(partRel));
+ foreach (cell, fks)
+ {
+ ForeignKeyCacheInfo *fk = lfirst(cell);
+ HeapTuple contup;
+
+ contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
+ if (!contup)
+ elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
+
+ ConstraintSetParentConstraint(fk->conoid, InvalidOid);
+
+ ReleaseSysCache(contup);
+ }
+ list_free_deep(fks);
+
/*
* Invalidate the parent's relcache so that the partition is no longer
* included in its partition descriptor.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 39618323fc..648758de4a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4746,6 +4746,7 @@ _copyForeignKeyCacheInfo(const ForeignKeyCacheInfo *from)
{
ForeignKeyCacheInfo *newnode = makeNode(ForeignKeyCacheInfo);
+ COPY_SCALAR_FIELD(conoid);
COPY_SCALAR_FIELD(conrelid);
COPY_SCALAR_FIELD(confrelid);
COPY_SCALAR_FIELD(nkeys);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9a169f7c6c..6c3dad9ab3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3634,6 +3634,7 @@ _outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node)
WRITE_NODE_TYPE("FOREIGNKEYCACHEINFO");
+ WRITE_OID_FIELD(conoid);
WRITE_OID_FIELD(conrelid);
WRITE_OID_FIELD(confrelid);
WRITE_INT_FIELD(nkeys);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index a4fc001103..1f29956767 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4144,6 +4144,7 @@ RelationGetFKeyList(Relation relation)
continue;
info = makeNode(ForeignKeyCacheInfo);
+ info->conoid = HeapTupleGetOid(htup);
info->conrelid = constraint->conrelid;
info->confrelid = constraint->confrelid;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 6ecbdb6294..84469f5715 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -202,12 +202,13 @@ typedef struct RelationData
* The per-FK-column arrays can be fixed-size because we allow at most
* INDEX_MAX_KEYS columns in a foreign key constraint.
*
- * Currently, we only cache fields of interest to the planner, but the
- * set of fields could be expanded in future.
+ * Currently, we mostly cache fields of interest to the planner, but the set
+ * of fields has already grown the constraint OID for other uses.
*/
typedef struct ForeignKeyCacheInfo
{
NodeTag type;
+ Oid conoid; /* oid of the constraint itself */
Oid conrelid; /* relation constrained by the foreign key */
Oid confrelid; /* relation referenced by the foreign key */
int nkeys; /* number of columns in the foreign key */
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 4e5cb8901e..e252423e4c 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1648,6 +1648,41 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | | 2501
+ b | integer | | | 142857
+Foreign-key constraints:
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (c text, a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ "fk_partitioned_fk_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+DROP TABLE fk_partitioned_fk_2;
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 6fcb5dfb4e..b01d5cef12 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1226,6 +1226,25 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+DROP TABLE fk_partitioned_fk_2;
+
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
Re: BUG #15425: DETACH/ATTACH PARTITION bug
Another version. I realized that attaching a partitioned partition had
further trouble, because the recursion at each step would consider all
FKs instead of only the FKs that had been cloned. So I had to split out
the recursive step of the cloning. Now that works fine.
In order to make this work, I made two little change to struct
ForeignKeyCacheInfo: first, the constraint OID was added. Second, I
made RelationGetFKeyList() return a nonempty list for partitioned
tables, which it didn't before (because of the optimization that
presupposes no triggers means no FKs, which is not true for partitioned
tables). As far as I can see, this has no effect on how the planner
uses this function. (This stuff could be done with repeated scans of
pg_constraint, but it seems much simpler this way.)
Michael sent me his test case offlist, and I verified that it works
correctly with this patch.
Unless there are objections, I intend to get this pushed tomorrow.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
detach-fks.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 2063abb8ae..40deb29e29 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -19,6 +19,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/tupconvert.h"
+#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -37,6 +38,10 @@
#include "utils/tqual.h"
+static void clone_fk_constraints(Relation pg_constraint, Relation parentRel,
+ Relation partRel, List *clone, List **cloned);
+
+
/*
* CreateConstraintEntry
* Create a constraint table entry.
@@ -400,34 +405,72 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
Relation rel;
ScanKeyData key;
SysScanDesc scan;
- TupleDesc tupdesc;
HeapTuple tuple;
- AttrNumber *attmap;
+ List *clone = NIL;
parentRel = heap_open(parentId, NoLock); /* already got lock */
/* see ATAddForeignKeyConstraint about lock level */
rel = heap_open(relationId, AccessExclusiveLock);
-
pg_constraint = heap_open(ConstraintRelationId, RowShareLock);
+
+ /* Obtain the list of constraints to clone or attach */
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
+ F_OIDEQ, ObjectIdGetDatum(parentId));
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 1, &key);
+ while ((tuple = systable_getnext(scan)) != NULL)
+ clone = lappend_oid(clone, HeapTupleGetOid(tuple));
+ systable_endscan(scan);
+
+ /* Do the actual work, recursing to partitions as needed */
+ clone_fk_constraints(pg_constraint, parentRel, rel, clone, cloned);
+
+ /* We're done. Clean up */
+ heap_close(parentRel, NoLock);
+ heap_close(rel, NoLock); /* keep lock till commit */
+ heap_close(pg_constraint, RowShareLock);
+}
+
+/*
+ * clone_fk_constraints
+ * Recursive subroutine for CloneForeignKeyConstraints
+ *
+ * Clone the given list of FK constraints when a partition is attached.
+ *
+ * When cloning foreign key to a partition, it may happen that some FKs
+ * already exist. In that case we can skip cloning it.
+ *
+ * This function also recurses to partitions, if the new partition is
+ * partitioned; of course, only do this for FKs that were actually cloned.
+ */
+static void
+clone_fk_constraints(Relation pg_constraint, Relation parentRel,
+ Relation partRel, List *clone, List **cloned)
+{
+ TupleDesc tupdesc;
+ AttrNumber *attmap;
+ List *partFKs;
+ List *subclone = NIL;
+ ListCell *cell;
+
tupdesc = RelationGetDescr(pg_constraint);
/*
* The constraint key may differ, if the columns in the partition are
* different. This map is used to convert them.
*/
- attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
+ attmap = convert_tuples_by_name_map(RelationGetDescr(partRel),
RelationGetDescr(parentRel),
gettext_noop("could not convert row type"));
- ScanKeyInit(&key,
- Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
- F_OIDEQ, ObjectIdGetDatum(parentId));
- scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
- NULL, 1, &key);
+ partFKs = copyObject(RelationGetFKeyList(partRel));
- while ((tuple = systable_getnext(scan)) != NULL)
+ foreach(cell, clone)
{
- Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ Oid parentConstrOid = lfirst_oid(cell);
+ Form_pg_constraint constrForm;
+ HeapTuple tuple;
AttrNumber conkey[INDEX_MAX_KEYS];
AttrNumber mapped_conkey[INDEX_MAX_KEYS];
AttrNumber confkey[INDEX_MAX_KEYS];
@@ -435,22 +478,31 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
Oid conppeqop[INDEX_MAX_KEYS];
Oid conffeqop[INDEX_MAX_KEYS];
Constraint *fkconstraint;
- ClonedConstraint *newc;
+ bool attach_it;
Oid constrOid;
ObjectAddress parentAddr,
childAddr;
int nelem;
+ ListCell *cell;
int i;
ArrayType *arr;
Datum datum;
bool isnull;
+ tuple = SearchSysCache1(CONSTROID, parentConstrOid);
+ if (!tuple)
+ elog(ERROR, "cache lookup failed for constraint %u",
+ parentConstrOid);
+ constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+
/* only foreign keys */
if (constrForm->contype != CONSTRAINT_FOREIGN)
+ {
+ ReleaseSysCache(tuple);
continue;
+ }
- ObjectAddressSet(parentAddr, ConstraintRelationId,
- HeapTupleGetOid(tuple));
+ ObjectAddressSet(parentAddr, ConstraintRelationId, parentConstrOid);
datum = fastgetattr(tuple, Anum_pg_constraint_conkey,
tupdesc, &isnull);
@@ -539,6 +591,84 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
elog(ERROR, "conffeqop is not a 1-D OID array");
memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+ /*
+ * Before creating a new constraint, see whether any existing FKs are
+ * fit for the purpose. If it is, attach the parent constraint to it,
+ * and don't clone anything. This way we avoid the expensive
+ * verification step and don't end up with a duplicate FK. This also
+ * means we don't consider this constraint when recursing to
+ * partitions.
+ */
+ attach_it = false;
+ foreach (cell, partFKs)
+ {
+ ForeignKeyCacheInfo *fk = lfirst_node(ForeignKeyCacheInfo, cell);
+ Form_pg_constraint partConstr;
+ HeapTuple partcontup;
+
+ attach_it = true;
+
+ if (fk->confrelid != constrForm->confrelid || fk->nkeys != nelem)
+ {
+ attach_it = false;
+ continue;
+ }
+
+ /* Do some quick & easy initial checks */
+ for (i = 0; i < nelem; i++)
+ {
+ if (fk->conkey[i] != mapped_conkey[i] ||
+ fk->confkey[i] != confkey[i] ||
+ fk->conpfeqop[i] != conpfeqop[i])
+ {
+ attach_it = false;
+ break;
+ }
+ }
+ if (!attach_it)
+ continue;
+
+ /* looks good ... do some more extensive checks */
+ partcontup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(fk->conoid));
+ if (!partcontup)
+ elog(ERROR, "cache lookup failed for constraint %u",
+ fk->conoid);
+ partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
+ if (OidIsValid(partConstr->conparentid) ||
+ !partConstr->convalidated ||
+ partConstr->condeferrable != constrForm->condeferrable ||
+ partConstr->condeferred != constrForm->condeferred ||
+ partConstr->confupdtype != constrForm->confupdtype ||
+ partConstr->confdeltype != constrForm->confdeltype ||
+ partConstr->confmatchtype != constrForm->confmatchtype)
+ {
+ ReleaseSysCache(partcontup);
+ attach_it = false;
+ continue;
+ }
+
+ ReleaseSysCache(partcontup);
+
+ /* looks good! Attach this constraint */
+ ConstraintSetParentConstraint(fk->conoid,
+ HeapTupleGetOid(tuple));
+ CommandCounterIncrement();
+ attach_it = true;
+ break;
+ }
+
+ /*
+ * If we attached to an existing constraint, there is no need to
+ * create a new one. In fact, there's no need to recurse for this
+ * constraint to partitions, either.
+ */
+ if (attach_it)
+ {
+ ReleaseSysCache(tuple);
+ continue;
+ }
+
constrOid =
CreateConstraintEntry(NameStr(constrForm->conname),
constrForm->connamespace,
@@ -547,7 +677,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
constrForm->condeferred,
constrForm->convalidated,
HeapTupleGetOid(tuple),
- relationId,
+ RelationGetRelid(partRel),
mapped_conkey,
nelem,
nelem,
@@ -568,6 +698,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
NULL,
false,
1, false, true);
+ subclone = lappend_oid(subclone, constrOid);
ObjectAddressSet(childAddr, ConstraintRelationId, constrOid);
recordDependencyOn(&childAddr, &parentAddr, DEPENDENCY_INTERNAL_AUTO);
@@ -580,17 +711,19 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
fkconstraint->deferrable = constrForm->condeferrable;
fkconstraint->initdeferred = constrForm->condeferred;
- createForeignKeyTriggers(rel, constrForm->confrelid, fkconstraint,
+ createForeignKeyTriggers(partRel, constrForm->confrelid, fkconstraint,
constrOid, constrForm->conindid, false);
if (cloned)
{
+ ClonedConstraint *newc;
+
/*
* Feed back caller about the constraints we created, so that they
* can set up constraint verification.
*/
newc = palloc(sizeof(ClonedConstraint));
- newc->relid = relationId;
+ newc->relid = RelationGetRelid(partRel);
newc->refrelid = constrForm->confrelid;
newc->conindid = constrForm->conindid;
newc->conid = constrOid;
@@ -598,25 +731,36 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
*cloned = lappend(*cloned, newc);
}
+
+ ReleaseSysCache(tuple);
}
- systable_endscan(scan);
pfree(attmap);
+ list_free_deep(partFKs);
- if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ /*
+ * If the partition is partitioned, recurse to handle any constraints
+ * that were cloned.
+ */
+ if (partRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+ subclone != NIL)
{
- PartitionDesc partdesc = RelationGetPartitionDesc(rel);
+ PartitionDesc partdesc = RelationGetPartitionDesc(partRel);
int i;
for (i = 0; i < partdesc->nparts; i++)
- CloneForeignKeyConstraints(RelationGetRelid(rel),
- partdesc->oids[i],
- cloned);
- }
+ {
+ Relation childRel;
- heap_close(rel, NoLock); /* keep lock till commit */
- heap_close(parentRel, NoLock);
- heap_close(pg_constraint, RowShareLock);
+ childRel = heap_open(partdesc->oids[i], AccessExclusiveLock);
+ clone_fk_constraints(pg_constraint,
+ partRel,
+ childRel,
+ subclone,
+ cloned);
+ heap_close(childRel, NoLock); /* keep lock till commit */
+ }
+ }
}
/*
@@ -1028,17 +1172,33 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
elog(ERROR, "cache lookup failed for constraint %u", childConstrId);
newtup = heap_copytuple(tuple);
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
- constrForm->conislocal = false;
- constrForm->coninhcount++;
- constrForm->conparentid = parentConstrId;
- CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ if (OidIsValid(parentConstrId))
+ {
+ constrForm->conislocal = false;
+ constrForm->coninhcount++;
+ constrForm->conparentid = parentConstrId;
+
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+
+ ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
+ ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
+
+ recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
+ }
+ else
+ {
+ constrForm->coninhcount--;
+ if (constrForm->coninhcount <= 0)
+ constrForm->conislocal = true;
+ constrForm->conparentid = InvalidOid;
+
+ deleteDependencyRecordsForClass(ConstraintRelationId, childConstrId,
+ ConstraintRelationId,
+ DEPENDENCY_INTERNAL_AUTO);
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ }
+
ReleaseSysCache(tuple);
-
- ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
- ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
-
- recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
-
heap_close(constrRel, RowExclusiveLock);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f988c16659..84136fc37d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14036,6 +14036,11 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
attachrel = heap_openrv(cmd->name, AccessExclusiveLock);
/*
+ * XXX I think it'd be a good idea to grab locks on all tables referenced
+ * by FKs at this point also.
+ */
+
+ /*
* Must be owner of both parent and source table -- parent was checked by
* ATSimplePermissions call in ATPrepCmd
*/
@@ -14607,6 +14612,7 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
ObjectAddress address;
Oid defaultPartOid;
List *indexes;
+ List *fks;
ListCell *cell;
/*
@@ -14682,6 +14688,23 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
}
heap_close(classRel, RowExclusiveLock);
+ /* Detach foreign keys */
+ fks = copyObject(RelationGetFKeyList(partRel));
+ foreach (cell, fks)
+ {
+ ForeignKeyCacheInfo *fk = lfirst(cell);
+ HeapTuple contup;
+
+ contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
+ if (!contup)
+ elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
+
+ ConstraintSetParentConstraint(fk->conoid, InvalidOid);
+
+ ReleaseSysCache(contup);
+ }
+ list_free_deep(fks);
+
/*
* Invalidate the parent's relcache so that the partition is no longer
* included in its partition descriptor.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 39618323fc..648758de4a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4746,6 +4746,7 @@ _copyForeignKeyCacheInfo(const ForeignKeyCacheInfo *from)
{
ForeignKeyCacheInfo *newnode = makeNode(ForeignKeyCacheInfo);
+ COPY_SCALAR_FIELD(conoid);
COPY_SCALAR_FIELD(conrelid);
COPY_SCALAR_FIELD(confrelid);
COPY_SCALAR_FIELD(nkeys);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9a169f7c6c..6c3dad9ab3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3634,6 +3634,7 @@ _outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node)
WRITE_NODE_TYPE("FOREIGNKEYCACHEINFO");
+ WRITE_OID_FIELD(conoid);
WRITE_OID_FIELD(conrelid);
WRITE_OID_FIELD(confrelid);
WRITE_INT_FIELD(nkeys);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index a4fc001103..fd3d010b77 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4108,8 +4108,9 @@ RelationGetFKeyList(Relation relation)
if (relation->rd_fkeyvalid)
return relation->rd_fkeylist;
- /* Fast path: if it doesn't have any triggers, it can't have FKs */
- if (!relation->rd_rel->relhastriggers)
+ /* Fast path: non-partitioned tables without triggers can't have FKs */
+ if (!relation->rd_rel->relhastriggers &&
+ relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
return NIL;
/*
@@ -4144,6 +4145,7 @@ RelationGetFKeyList(Relation relation)
continue;
info = makeNode(ForeignKeyCacheInfo);
+ info->conoid = HeapTupleGetOid(htup);
info->conrelid = constraint->conrelid;
info->confrelid = constraint->confrelid;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 6ecbdb6294..84469f5715 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -202,12 +202,13 @@ typedef struct RelationData
* The per-FK-column arrays can be fixed-size because we allow at most
* INDEX_MAX_KEYS columns in a foreign key constraint.
*
- * Currently, we only cache fields of interest to the planner, but the
- * set of fields could be expanded in future.
+ * Currently, we mostly cache fields of interest to the planner, but the set
+ * of fields has already grown the constraint OID for other uses.
*/
typedef struct ForeignKeyCacheInfo
{
NodeTag type;
+ Oid conoid; /* oid of the constraint itself */
Oid conrelid; /* relation constrained by the foreign key */
Oid confrelid; /* relation referenced by the foreign key */
int nkeys; /* number of columns in the foreign key */
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 4e5cb8901e..4d86a8e1a4 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1648,6 +1648,124 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | | 2501
+ b | integer | | | 142857
+Foreign-key constraints:
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ "fk_partitioned_fk_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2 FOR VALUES FROM (100,100) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_4;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+-- should only have one constraint
+\d fk_partitioned_fk_4
+ Table "public.fk_partitioned_fk_4"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (3500, 3502)
+Partition key: RANGE (b, a)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d fk_partitioned_fk_4_1
+ Table "public.fk_partitioned_fk_4_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_4 FOR VALUES FROM (1, 1) TO (100, 100)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+-- this one has an FK with mismatched properties
+\d fk_partitioned_fk_4_2
+ Table "public.fk_partitioned_fk_4_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+CREATE TABLE fk_partitioned_fk_5 (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b),
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b))
+ PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_5_1 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_5;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+-- this one has two constraints, similar but not quite the one in the parent,
+-- so it gets a new one
+\d fk_partitioned_fk_5
+ Table "public.fk_partitioned_fk_5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (4500)
+Partition key: RANGE (a)
+Foreign-key constraints:
+ "fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+Number of partitions: 1 (Use \d+ to list them.)
+
+-- verify that it works to reattaching a child with multiple candidate
+-- constraints
+ALTER TABLE fk_partitioned_fk_5 DETACH PARTITION fk_partitioned_fk_5_1;
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+\d fk_partitioned_fk_5_1
+ Table "public.fk_partitioned_fk_5_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10)
+Foreign-key constraints:
+ "fk_partitioned_fk_5_1_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 6fcb5dfb4e..5f931abd7a 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1226,6 +1226,56 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+DROP TABLE fk_partitioned_fk_2;
+
+CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2 FOR VALUES FROM (100,100) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_4;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+-- should only have one constraint
+\d fk_partitioned_fk_4
+\d fk_partitioned_fk_4_1
+-- this one has an FK with mismatched properties
+\d fk_partitioned_fk_4_2
+
+CREATE TABLE fk_partitioned_fk_5 (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b),
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b))
+ PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_5_1 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_5;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+-- this one has two constraints, similar but not quite the one in the parent,
+-- so it gets a new one
+\d fk_partitioned_fk_5
+-- verify that it works to reattaching a child with multiple candidate
+-- constraints
+ALTER TABLE fk_partitioned_fk_5 DETACH PARTITION fk_partitioned_fk_5_1;
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+\d fk_partitioned_fk_5_1
+
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
Re: BUG #15425: DETACH/ATTACH PARTITION bug
Pushed, after some further refinement of the test case so that it'd
verify a few more corner case situations.
Thanks Michael.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services