Hi all
Attached is a patch to implement ALTER TABLE ... ALTER CONSTRAINT ...
SET DEFERRABLE on UNIQUE or PRIMARY KEY constraints.
Currently only FOREIGN KEY constraints are supported. Others are rejected with:
constraint \"%s\" of relation \"%s\" is not a foreign key constraint
The patch also adds some regression tests for DEFERRABLE constraints.
The ALTER doesn't take effect in the session it's run in, which makes
me suspect I need to do additional cache invalidations - maybe the
index backing the constraint? Anyway, posted here as-is because I'm
out of time for now and it might be useful for someone who's looking
for info on this.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From c0a041f0ca5d884842820538b56d82472a701c3c Mon Sep 17 00:00:00 2001
From: Craig Ringer <cr...@2ndquadrant.com>
Date: Fri, 26 Jun 2015 11:59:30 +0800
Subject: [PATCH] Allow ALTER TABLE...ALTER CONSTRAINT on PK and UNIQUE
We presently support DEFERRABLE constraints on PRIMARY KEY and UNIQUE
constraints, but do not permit ALTER TABLE to modify their deferrable
state.
Fix that and add some regression test coverage.
---
src/backend/commands/tablecmds.c | 72 +++++++++++++++++++-----------------
src/test/regress/sql/alter_table.sql | 37 ++++++++++++++++++
2 files changed, 76 insertions(+), 33 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d394713..5875987 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6647,22 +6647,20 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
- if (currcon->contype != CONSTRAINT_FOREIGN)
+ if (currcon->contype != CONSTRAINT_FOREIGN &&
+ currcon->contype != CONSTRAINT_PRIMARY &&
+ currcon->contype != CONSTRAINT_UNIQUE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
+ errmsg("constraint \"%s\" of relation \"%s\" must be FOREIGN KEY, PRIMARY KEY or UNIQUE",
cmdcon->conname, RelationGetRelationName(rel))));
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
HeapTuple copyTuple;
- HeapTuple tgtuple;
Form_pg_constraint copy_con;
List *otherrelids = NIL;
- ScanKeyData tgkey;
- SysScanDesc tgscan;
- Relation tgrel;
ListCell *lc;
/*
@@ -6682,44 +6680,52 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
/*
* Now we need to update the multiple entries in pg_trigger that
- * implement the constraint.
+ * implement the constraint if it's a foreign key constraint.
*/
- tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+ if (currcon->contype == CONSTRAINT_FOREIGN)
+ {
+ HeapTuple tgtuple;
+ ScanKeyData tgkey;
+ SysScanDesc tgscan;
+ Relation tgrel;
- ScanKeyInit(&tgkey,
- Anum_pg_trigger_tgconstraint,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(HeapTupleGetOid(contuple)));
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
- tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
- NULL, 1, &tgkey);
+ ScanKeyInit(&tgkey,
+ Anum_pg_trigger_tgconstraint,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(HeapTupleGetOid(contuple)));
- while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
- {
- Form_pg_trigger copy_tg;
+ tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
+ NULL, 1, &tgkey);
- copyTuple = heap_copytuple(tgtuple);
- copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
+ while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
+ {
+ Form_pg_trigger copy_tg;
- /* Remember OIDs of other relation(s) involved in FK constraint */
- if (copy_tg->tgrelid != RelationGetRelid(rel))
- otherrelids = list_append_unique_oid(otherrelids,
- copy_tg->tgrelid);
+ copyTuple = heap_copytuple(tgtuple);
+ copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
- copy_tg->tgdeferrable = cmdcon->deferrable;
- copy_tg->tginitdeferred = cmdcon->initdeferred;
- simple_heap_update(tgrel, ©Tuple->t_self, copyTuple);
- CatalogUpdateIndexes(tgrel, copyTuple);
+ /* Remember OIDs of other relation(s) involved in FK constraint */
+ if (copy_tg->tgrelid != RelationGetRelid(rel))
+ otherrelids = list_append_unique_oid(otherrelids,
+ copy_tg->tgrelid);
- InvokeObjectPostAlterHook(TriggerRelationId,
- HeapTupleGetOid(tgtuple), 0);
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ simple_heap_update(tgrel, ©Tuple->t_self, copyTuple);
+ CatalogUpdateIndexes(tgrel, copyTuple);
- heap_freetuple(copyTuple);
- }
+ InvokeObjectPostAlterHook(TriggerRelationId,
+ HeapTupleGetOid(tgtuple), 0);
+
+ heap_freetuple(copyTuple);
+ }
- systable_endscan(tgscan);
+ systable_endscan(tgscan);
- heap_close(tgrel, RowExclusiveLock);
+ heap_close(tgrel, RowExclusiveLock);
+ }
/*
* Invalidate relcache so that others see the new attributes. We must
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 29c1875..ec31597 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1688,3 +1688,40 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
DROP TABLE logged3;
DROP TABLE logged2;
DROP TABLE logged1;
+-- Make sure that ALTERing a table to make a PK or UNIQUE constraint deferrable works
+CREATE TABLE pkuk(
+ pk INTEGER PRIMARY KEY,
+ uk INTEGER,
+ CONSTRAINT pkuk_unique_uk UNIQUE(uk)
+);
+INSERT INTO pkuk(pk, uk) VALUES (1,1), (2,2);
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE INITIALLY DEFERRED;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL IMMEDIATE;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE INITIALLY IMMEDIATE;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE INITIALLY IMMEDIATE;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
--
2.1.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
