Hi,
Currently, there is no support for CHECK constraint DEFERRABLE in a create
table statement.
SQL standard specifies that CHECK constraint can be defined as DEFERRABLE.
The attached patch is having implementation for CHECK constraint Deferrable
as below:
‘postgres[579453]=#’CREATE TABLE t1 (i int CHECK(i<>0) DEFERRABLE, t text);
CREATE TABLE
‘postgres[579453]=#’\d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
t | text | | |
Check constraints:
"t1_i_check" CHECK (i <> 0) DEFERRABLE
Now we can have a deferrable CHECK constraint, and we can defer the
constraint validation:
‘postgres[579453]=#’BEGIN;
BEGIN
‘postgres[579453]=#*’SET CONSTRAINTS t1_i_check DEFERRED;
SET CONSTRAINTS
‘postgres[579453]=#*’INSERT INTO t1 VALUES (0, 'one'); -- should succeed
INSERT 0 1
‘postgres[579453]=#*’UPDATE t1 SET i = 1 WHERE t = 'one';
UPDATE 1
‘postgres[579453]=#*’COMMIT; -- should succeed
COMMIT
Attaching the initial patch, I will improve it with documentation in my
next version of the patch.
thoughts?
--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com
From 1eb72b14a3a6914e893854508a071ae835d23245 Mon Sep 17 00:00:00 2001
From: Himanshu Upadhyaya <[email protected]>
Date: Wed, 5 Jul 2023 14:54:37 +0530
Subject: [PATCH v1] Implementation of "CHECK Constraint" to make it
Deferrable.
---
src/backend/access/common/tupdesc.c | 2 +
src/backend/catalog/heap.c | 50 ++++++++--
src/backend/commands/constraint.c | 116 ++++++++++++++++++++++
src/backend/commands/copyfrom.c | 10 +-
src/backend/commands/tablecmds.c | 8 ++
src/backend/commands/trigger.c | 43 ++++++--
src/backend/executor/execMain.c | 33 +++++-
src/backend/executor/execReplication.c | 10 +-
src/backend/executor/nodeModifyTable.c | 29 +++---
src/backend/parser/gram.y | 2 +-
src/backend/parser/parse_utilcmd.c | 9 +-
src/backend/utils/cache/relcache.c | 2 +
src/include/access/tupdesc.h | 2 +
src/include/catalog/heap.h | 2 +
src/include/catalog/pg_proc.dat | 5 +
src/include/commands/trigger.h | 2 +
src/include/executor/executor.h | 42 +++++++-
src/test/regress/expected/constraints.out | 98 ++++++++++++++++++
src/test/regress/sql/constraints.sql | 99 ++++++++++++++++++
19 files changed, 518 insertions(+), 46 deletions(-)
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 7c5c390503..098cb27932 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -204,6 +204,8 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin);
cpy->check[i].ccvalid = constr->check[i].ccvalid;
cpy->check[i].ccnoinherit = constr->check[i].ccnoinherit;
+ cpy->check[i].ccdeferrable = constr->check[i].ccdeferrable;
+ cpy->check[i].ccdeferred = constr->check[i].ccdeferred;
}
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 2a0d82aedd..8a1e8e266f 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -52,17 +52,20 @@
#include "catalog/pg_statistic.h"
#include "catalog/pg_subscription_rel.h"
#include "catalog/pg_tablespace.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "catalog/storage.h"
#include "commands/tablecmds.h"
#include "commands/typecmds.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "parser/parser.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
#include "pgstat.h"
@@ -102,7 +105,8 @@ static ObjectAddress AddNewRelationType(const char *typeName,
static void RelationRemoveInheritance(Oid relid);
static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal);
+ bool is_no_inherit, bool is_internal,
+ bool is_deferrable, bool initdeferred);
static void StoreConstraints(Relation rel, List *cooked_constraints,
bool is_internal);
static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
@@ -2063,13 +2067,15 @@ SetAttrMissing(Oid relid, char *attname, char *value)
static Oid
StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal)
+ bool is_no_inherit, bool is_internal,
+ bool is_deferrable, bool initdeferred)
{
char *ccbin;
List *varList;
int keycount;
int16 *attNos;
Oid constrOid;
+ CreateTrigStmt *trigger;
/*
* Flatten expression to string form for storage.
@@ -2126,8 +2132,10 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
CreateConstraintEntry(ccname, /* Constraint Name */
RelationGetNamespace(rel), /* namespace */
CONSTRAINT_CHECK, /* Constraint Type */
- false, /* Is Deferrable */
- false, /* Is Deferred */
+ is_deferrable, /* Is Check Constraint
+ * deferrable */
+ initdeferred, /* Is Check Constraint initially
+ * deferred */
is_validated,
InvalidOid, /* no parent constraint */
RelationGetRelid(rel), /* relation */
@@ -2155,6 +2163,36 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
is_no_inherit, /* connoinherit */
is_internal); /* internally constructed? */
+ /*
+ * If the constraint is deferrable, create the deferred trigger to
+ * re-validate the check constraint.(The trigger will be given an internal
+ * dependency on the constraint by CreateTrigger, so there's no need to do
+ * anything more here.)
+ */
+ if (is_deferrable)
+ {
+ trigger = makeNode(CreateTrigStmt);
+ trigger->replace = false;
+ trigger->isconstraint = true;
+ trigger->trigname = "Check_ConstraintTrigger";
+ trigger->relation = NULL;
+ trigger->funcname = SystemFuncName("check_constraint_recheck");
+ trigger->args = NIL;
+ trigger->row = true;
+ trigger->timing = TRIGGER_TYPE_AFTER;
+ trigger->events = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE;
+ trigger->columns = NIL;
+ trigger->whenClause = NULL;
+ trigger->transitionRels = NIL;
+ trigger->deferrable = true;
+ trigger->initdeferred = initdeferred;
+ trigger->constrrel = NULL;
+
+ (void) CreateTrigger(trigger, NULL, RelationGetRelid(rel),
+ InvalidOid, constrOid, InvalidOid, InvalidOid,
+ InvalidOid, NULL, true, false);
+ }
+
pfree(ccbin);
return constrOid;
@@ -2201,7 +2239,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
StoreRelCheck(rel, con->name, con->expr,
!con->skip_validation, con->is_local,
con->inhcount, con->is_no_inherit,
- is_internal);
+ is_internal, con->is_deferrable, con->is_deferred);
numchecks++;
break;
default:
@@ -2453,7 +2491,7 @@ AddRelationNewConstraints(Relation rel,
*/
constrOid =
StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local,
- is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
+ is_local ? 0 : 1, cdef->is_no_inherit, is_internal, cdef->deferrable, cdef->initdeferred);
numchecks++;
diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c
index 35c4451fc0..0dc474d447 100644
--- a/src/backend/commands/constraint.c
+++ b/src/backend/commands/constraint.c
@@ -203,3 +203,119 @@ unique_key_recheck(PG_FUNCTION_ARGS)
return PointerGetDatum(NULL);
}
+
+/*
+ * check_constraint_recheck- trigger function to do a deferred check for CHECK constraint.
+ *
+ * This is invoked as an AFTER ROW trigger for both INSERT and UPDATE,
+ * for any rows recorded as potential violation of Deferred check
+ * constraint.
+ *
+ * This may be an end-of-statement check or a commit-time check.
+ */
+Datum
+check_constraint_recheck(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ const char *funcname = "check_constraint_recheck";
+ ItemPointerData checktid;
+ Relation rel;
+ EState *estate;
+ TupleTableSlot *slot;
+ ResultRelInfo *rInfo = NULL;
+ TableScanDesc scan;
+ ExprContext *econtext;
+
+ /*
+ * Make sure this is being called as an AFTER ROW trigger. Note:
+ * translatable error strings are shared with ri_triggers.c, so resist the
+ * temptation to fold the function name into them.
+ */
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("function \"%s\" was not called by trigger manager",
+ funcname)));
+
+ if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
+ !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("function \"%s\" must be fired AFTER ROW",
+ funcname)));
+
+ /*
+ * Get the new data that was inserted/updated.
+ */
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ checktid = trigdata->tg_trigslot->tts_tid;
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ checktid = trigdata->tg_newslot->tts_tid;
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("function \"%s\" must be fired for INSERT or UPDATE",
+ funcname)));
+ ItemPointerSetInvalid(&checktid); /* keep compiler quiet */
+ }
+
+ slot = table_slot_create(trigdata->tg_relation, NULL);
+ scan = table_beginscan_tid(trigdata->tg_relation, SnapshotSelf);
+
+ /*
+ * Now look for latest tuple in that chain because it is possible that
+ * same tuple is updated(or even inserted and then updated/deleted)
+ * multiple times in a transaction.
+ */
+ heap_get_latest_tid(scan, &checktid);
+
+ /*
+ * Check if latest tuple is visible to current transaction.
+ * heap_get_latest_tid(as called above) provides the latest tuple as per
+ * current Snapshot and if tuple is not visible (if
+ * table_tuple_fetch_row_version returns false), it means tuple is
+ * inserted/updated and then deleted in the same transaction. We are sure
+ * that initially tuple was inserted or or updated in this transaction
+ * because this constraint trigger's function was called as an UPDATE or
+ * INSERT event of after row trigger.
+ */
+ if (!table_tuple_fetch_row_version(trigdata->tg_relation,
+ &checktid,
+ SnapshotSelf,
+ slot))
+ {
+ table_endscan(scan);
+ ExecDropSingleTupleTableSlot(slot);
+ return PointerGetDatum(NULL);
+ }
+
+ /* Make a local estate and Exprcontext */
+ estate = CreateExecutorState();
+ econtext = GetPerTupleExprContext(estate);
+ econtext->ecxt_scantuple = slot;
+
+ /*
+ * Open the relation, acquiring a AccessShareLock.
+ */
+ rel = table_open(trigdata->tg_relation->rd_id, AccessShareLock);
+ rInfo = ExecGetTriggerResultRel(estate, RelationGetRelid(rel),
+ NULL);
+ ExecConstraints(rInfo, slot, estate, CHECK_RECHECK_EXISTING);
+
+ /*
+ * If that worked, then this potential failure of check constraint is now
+ * resolved, and we are done.
+ */
+ if (estate != NULL)
+ {
+ ExecCloseResultRelations(estate);
+ ExecResetTupleTable(estate->es_tupleTable, false);
+ FreeExecutorState(estate);
+ }
+
+ table_endscan(scan);
+ ExecDropSingleTupleTableSlot(slot);
+ table_close(rel, AccessShareLock);
+ return PointerGetDatum(NULL);
+}
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 80bca79cd0..975b5fdc4d 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -375,7 +375,7 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
slot->tts_tableOid = relid;
ExecARInsertTriggers(estate, resultRelInfo,
- slot, NIL,
+ slot, NIL, false,
cstate->transition_capture);
}
}
@@ -437,7 +437,7 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
buffer->slots[i], estate, false,
false, NULL, NIL, false);
ExecARInsertTriggers(estate, resultRelInfo,
- slots[i], recheckIndexes,
+ slots[i], recheckIndexes, false,
cstate->transition_capture);
list_free(recheckIndexes);
}
@@ -452,7 +452,7 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
{
cstate->cur_lineno = buffer->linenos[i];
ExecARInsertTriggers(estate, resultRelInfo,
- slots[i], NIL,
+ slots[i], NIL, false,
cstate->transition_capture);
}
@@ -1169,7 +1169,7 @@ CopyFrom(CopyFromState cstate)
*/
if (resultRelInfo->ri_FdwRoutine == NULL &&
resultRelInfo->ri_RelationDesc->rd_att->constr)
- ExecConstraints(resultRelInfo, myslot, estate);
+ ExecConstraints(resultRelInfo, myslot, estate, CHECK_RECHECK_DISABLED);
/*
* Also check the tuple against the partition constraint, if
@@ -1254,7 +1254,7 @@ CopyFrom(CopyFromState cstate)
/* AFTER ROW INSERT Triggers */
ExecARInsertTriggers(estate, resultRelInfo, myslot,
- recheckIndexes, cstate->transition_capture);
+ recheckIndexes, false, cstate->transition_capture);
list_free(recheckIndexes);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fce5e6f220..099b2ee1f6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -924,6 +924,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
cooked->is_local = true; /* not used for defaults */
cooked->inhcount = 0; /* ditto */
cooked->is_no_inherit = false;
+ cooked->is_deferrable = false; /* By default constraint is not
+ * deferrable */
+ cooked->is_deferred = false; /* ditto */
cookedDefaults = lappend(cookedDefaults, cooked);
attr->atthasdef = true;
}
@@ -2841,6 +2844,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
cooked->is_local = false;
cooked->inhcount = 1;
cooked->is_no_inherit = false;
+ cooked->is_deferrable = check[i].ccdeferrable;
+ cooked->is_deferred = check[i].ccdeferred;
constraints = lappend(constraints, cooked);
}
}
@@ -18810,6 +18815,9 @@ DetachAddConstraintIfNeeded(List **wqueue, Relation partRel)
n->cooked_expr = nodeToString(make_ands_explicit(constraintExpr));
n->initially_valid = true;
n->skip_validation = true;
+ n->deferrable = false; /* By default this new constraint must be
+ * non-deferrable */
+ n->initdeferred = false; /* Ditto */
/* It's a re-add, since it nominally already exists */
ATAddCheckConstraint(wqueue, tab, partRel, n,
true, false, true, ShareUpdateExclusiveLock);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 52177759ab..9f582fcfd7 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -103,7 +103,8 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
ResultRelInfo *dst_partinfo,
int event, bool row_trigger,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
- List *recheckIndexes, Bitmapset *modifiedCols,
+ List *recheckIndexes, bool recheckConstraints,
+ Bitmapset *modifiedCols,
TransitionCaptureState *transition_capture,
bool is_crosspart_update);
static void AfterTriggerEnlargeQueryState(void);
@@ -2456,7 +2457,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo,
if (trigdesc && trigdesc->trig_insert_after_statement)
AfterTriggerSaveEvent(estate, relinfo, NULL, NULL,
TRIGGER_EVENT_INSERT,
- false, NULL, NULL, NIL, NULL, transition_capture,
+ false, NULL, NULL, NIL, false, NULL, transition_capture,
false);
}
@@ -2539,7 +2540,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
void
ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
TupleTableSlot *slot, List *recheckIndexes,
- TransitionCaptureState *transition_capture)
+ bool recheckConstraints, TransitionCaptureState *transition_capture)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2548,7 +2549,9 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
AfterTriggerSaveEvent(estate, relinfo, NULL, NULL,
TRIGGER_EVENT_INSERT,
true, NULL, slot,
- recheckIndexes, NULL,
+ recheckIndexes,
+ recheckConstraints,
+ NULL,
transition_capture,
false);
}
@@ -2674,7 +2677,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
if (trigdesc && trigdesc->trig_delete_after_statement)
AfterTriggerSaveEvent(estate, relinfo, NULL, NULL,
TRIGGER_EVENT_DELETE,
- false, NULL, NULL, NIL, NULL, transition_capture,
+ false, NULL, NULL, NIL, false, NULL, transition_capture,
false);
}
@@ -2807,7 +2810,7 @@ ExecARDeleteTriggers(EState *estate,
AfterTriggerSaveEvent(estate, relinfo, NULL, NULL,
TRIGGER_EVENT_DELETE,
- true, slot, NULL, NIL, NULL,
+ true, slot, NULL, NIL, false, NULL,
transition_capture,
is_crosspart_update);
}
@@ -2930,7 +2933,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
if (trigdesc && trigdesc->trig_update_after_statement)
AfterTriggerSaveEvent(estate, relinfo, NULL, NULL,
TRIGGER_EVENT_UPDATE,
- false, NULL, NULL, NIL,
+ false, NULL, NULL, NIL, false,
ExecGetAllUpdatedCols(relinfo, estate),
transition_capture,
false);
@@ -3089,6 +3092,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
List *recheckIndexes,
+ bool recheckConstraints,
TransitionCaptureState *transition_capture,
bool is_crosspart_update)
{
@@ -3133,7 +3137,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
src_partinfo, dst_partinfo,
TRIGGER_EVENT_UPDATE,
true,
- oldslot, newslot, recheckIndexes,
+ oldslot, newslot, recheckIndexes, recheckConstraints,
ExecGetAllUpdatedCols(relinfo, estate),
transition_capture,
is_crosspart_update);
@@ -3262,7 +3266,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
AfterTriggerSaveEvent(estate, relinfo,
NULL, NULL,
TRIGGER_EVENT_TRUNCATE,
- false, NULL, NULL, NIL, NULL, NULL,
+ false, NULL, NULL, NIL, false, NULL, NULL,
false);
}
@@ -6051,7 +6055,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
ResultRelInfo *dst_partinfo,
int event, bool row_trigger,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
- List *recheckIndexes, Bitmapset *modifiedCols,
+ List *recheckIndexes, bool recheckConstraints,
+ Bitmapset *modifiedCols,
TransitionCaptureState *transition_capture,
bool is_crosspart_update)
{
@@ -6064,6 +6069,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
int tgtype_level;
int i;
Tuplestorestate *fdw_tuplestore = NULL;
+ bool isChkConRechkQueued = false;
/*
* Check state. We use a normal test not Assert because it is possible to
@@ -6389,6 +6395,23 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
}
}
+ /*
+ * If the trigger is deferred check constraint recheck trigger, only
+ * queue it if any of the check constraint was potentially violated
+ * and no check constraint trigger is yet added(queued).
+ */
+ if (trigger->tgfoid == F_CHECK_CONSTRAINT_RECHECK)
+ {
+ if (!recheckConstraints || isChkConRechkQueued)
+ {
+ continue;
+ }
+ else
+ {
+ isChkConRechkQueued = true;
+ }
+ }
+
/*
* If the trigger is a deferred unique constraint check trigger, only
* queue it if the unique constraint was potentially violated, which
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 4c5a7bbf62..6d6dd713c1 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1734,12 +1734,14 @@ ExecutePlan(EState *estate,
/*
* ExecRelCheck --- check that tuple meets constraints for result relation
+ * and reset recheckConstraints to true in case of any constraint violation and
+ * if that constraint is deferrable.
*
* Returns NULL if OK, else name of failed check constraint
*/
static const char *
ExecRelCheck(ResultRelInfo *resultRelInfo,
- TupleTableSlot *slot, EState *estate)
+ TupleTableSlot *slot, EState *estate, checkConstraintRecheck checkConstraint, bool *recheckConstraints)
{
Relation rel = resultRelInfo->ri_RelationDesc;
int ncheck = rel->rd_att->constr->num_check;
@@ -1798,7 +1800,20 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
* ExecQual.
*/
if (!ExecCheck(checkconstr, econtext))
+ {
+
+ /*
+ * If the constraint is deferrable and caller is
+ * CHECK_RECHECK_ENABLED then constraints must be revalidated at
+ * the time of enforcing the constraint, that is at commit time
+ * and via after Row trigger.
+ */
+ if (checkConstraint == CHECK_RECHECK_ENABLED && check[i].ccdeferrable)
+ {
+ *recheckConstraints = true;
+ }
return check[i].ccname;
+ }
}
/* NULL result means no error */
@@ -1936,18 +1951,25 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
* have been converted from the original input tuple after tuple routing.
* 'resultRelInfo' is the final result relation, after tuple routing.
*/
-void
+bool
ExecConstraints(ResultRelInfo *resultRelInfo,
- TupleTableSlot *slot, EState *estate)
+ TupleTableSlot *slot, EState *estate, checkConstraintRecheck checkConstraint)
{
Relation rel = resultRelInfo->ri_RelationDesc;
TupleDesc tupdesc = RelationGetDescr(rel);
TupleConstr *constr = tupdesc->constr;
Bitmapset *modifiedCols;
+ bool recheckConstraints = false;
Assert(constr); /* we should not be called otherwise */
- if (constr->has_not_null)
+ /*
+ * NOT NULL constraint is not supported as deferrable so don't need to
+ * recheck( CHECK_RECHECK_EXISTING means it is getting called by trigger
+ * function check_constraint_recheck for re-checking the potential
+ * constraint violation of "CHECK" constraint on one/more columns).
+ */
+ if (constr->has_not_null && checkConstraint != CHECK_RECHECK_EXISTING)
{
int natts = tupdesc->natts;
int attrChk;
@@ -2015,7 +2037,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
const char *failed;
- if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) != NULL)
+ if ((failed = ExecRelCheck(resultRelInfo, slot, estate, checkConstraint, &recheckConstraints)) != NULL && !recheckConstraints)
{
char *val_desc;
Relation orig_rel = rel;
@@ -2060,6 +2082,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
errtableconstraint(orig_rel, failed)));
}
}
+ return recheckConstraints;
}
/*
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 9dd7168461..c29b6a5200 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -477,6 +477,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
if (!skip_tuple)
{
List *recheckIndexes = NIL;
+ bool recheckConstraints = false;
/* Compute stored generated columns */
if (rel->rd_att->constr &&
@@ -486,7 +487,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
- ExecConstraints(resultRelInfo, slot, estate);
+ recheckConstraints = ExecConstraints(resultRelInfo, slot, estate, CHECK_RECHECK_ENABLED);
if (rel->rd_rel->relispartition)
ExecPartitionCheck(resultRelInfo, slot, estate, true);
@@ -500,7 +501,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
/* AFTER ROW INSERT Triggers */
ExecARInsertTriggers(estate, resultRelInfo, slot,
- recheckIndexes, NULL);
+ recheckIndexes, recheckConstraints, NULL);
/*
* XXX we should in theory pass a TransitionCaptureState object to the
@@ -545,6 +546,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
{
List *recheckIndexes = NIL;
TU_UpdateIndexes update_indexes;
+ bool recheckConstraints = false;
/* Compute stored generated columns */
if (rel->rd_att->constr &&
@@ -554,7 +556,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
- ExecConstraints(resultRelInfo, slot, estate);
+ recheckConstraints = ExecConstraints(resultRelInfo, slot, estate, CHECK_RECHECK_ENABLED);
if (rel->rd_rel->relispartition)
ExecPartitionCheck(resultRelInfo, slot, estate, true);
@@ -571,7 +573,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
ExecARUpdateTriggers(estate, resultRelInfo,
NULL, NULL,
tid, NULL, slot,
- recheckIndexes, NULL, false);
+ recheckIndexes, recheckConstraints, NULL, false);
list_free(recheckIndexes);
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 2a5fec8d01..022221f92c 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -767,6 +767,7 @@ ExecInsert(ModifyTableContext *context,
OnConflictAction onconflict = node->onConflictAction;
PartitionTupleRouting *proute = mtstate->mt_partition_tuple_routing;
MemoryContext oldContext;
+ bool recheckConstraints;
/*
* If the input result relation is a partitioned table, find the leaf
@@ -995,7 +996,7 @@ ExecInsert(ModifyTableContext *context,
* Check the constraints of the tuple.
*/
if (resultRelationDesc->rd_att->constr)
- ExecConstraints(resultRelInfo, slot, estate);
+ recheckConstraints = ExecConstraints(resultRelInfo, slot, estate, CHECK_RECHECK_ENABLED);
/*
* Also check the tuple against the partition constraint, if there is
@@ -1162,6 +1163,7 @@ ExecInsert(ModifyTableContext *context,
NULL,
slot,
NULL,
+ false,
mtstate->mt_transition_capture,
false);
@@ -1173,7 +1175,7 @@ ExecInsert(ModifyTableContext *context,
}
/* AFTER ROW INSERT Triggers */
- ExecARInsertTriggers(estate, resultRelInfo, slot, recheckIndexes,
+ ExecARInsertTriggers(estate, resultRelInfo, slot, recheckIndexes, recheckConstraints,
ar_insert_trig_tcs);
list_free(recheckIndexes);
@@ -1247,7 +1249,7 @@ ExecBatchInsert(ModifyTableState *mtstate,
slot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
/* AFTER ROW INSERT Triggers */
- ExecARInsertTriggers(estate, resultRelInfo, slot, NIL,
+ ExecARInsertTriggers(estate, resultRelInfo, slot, NIL, false,
mtstate->mt_transition_capture);
/*
@@ -1380,7 +1382,7 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ExecARUpdateTriggers(estate, resultRelInfo,
NULL, NULL,
tupleid, oldtuple,
- NULL, NULL, mtstate->mt_transition_capture,
+ NULL, NULL, false, mtstate->mt_transition_capture,
false);
/*
@@ -1967,7 +1969,7 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo,
static TM_Result
ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
- bool canSetTag, UpdateContext *updateCxt)
+ bool canSetTag, UpdateContext *updateCxt, bool *recheckConstraints)
{
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
@@ -2087,7 +2089,7 @@ lreplace:
* have it validate all remaining checks.
*/
if (resultRelationDesc->rd_att->constr)
- ExecConstraints(resultRelInfo, slot, estate);
+ *recheckConstraints = ExecConstraints(resultRelInfo, slot, estate, CHECK_RECHECK_ENABLED);
/*
* replace the heap tuple
@@ -2120,7 +2122,7 @@ lreplace:
static void
ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
ResultRelInfo *resultRelInfo, ItemPointer tupleid,
- HeapTuple oldtuple, TupleTableSlot *slot)
+ HeapTuple oldtuple, TupleTableSlot *slot, bool recheckConstraints)
{
ModifyTableState *mtstate = context->mtstate;
List *recheckIndexes = NIL;
@@ -2138,6 +2140,7 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
NULL, NULL,
tupleid, oldtuple, slot,
recheckIndexes,
+ recheckConstraints,
mtstate->operation == CMD_INSERT ?
mtstate->mt_oc_transition_capture :
mtstate->mt_transition_capture,
@@ -2225,7 +2228,7 @@ ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context,
/* Perform the root table's triggers. */
ExecARUpdateTriggers(context->estate,
rootRelInfo, sourcePartInfo, destPartInfo,
- tupleid, NULL, newslot, NIL, NULL, true);
+ tupleid, NULL, newslot, NIL, false, NULL, true);
}
/* ----------------------------------------------------------------
@@ -2264,6 +2267,7 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
UpdateContext updateCxt = {0};
TM_Result result;
+ bool recheckConstraints = false;
/*
* abort the operation if not running transactions
@@ -2320,7 +2324,7 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
*/
redo_act:
result = ExecUpdateAct(context, resultRelInfo, tupleid, oldtuple, slot,
- canSetTag, &updateCxt);
+ canSetTag, &updateCxt, &recheckConstraints);
/*
* If ExecUpdateAct reports that a cross-partition update was done,
@@ -2476,7 +2480,7 @@ redo_act:
(estate->es_processed)++;
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo, tupleid, oldtuple,
- slot);
+ slot, recheckConstraints);
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
@@ -2845,6 +2849,7 @@ lmerge_matched:
CmdType commandType = relaction->mas_action->commandType;
TM_Result result;
UpdateContext updateCxt = {0};
+ bool recheckConstraints = false;
/*
* Test condition, if any.
@@ -2897,11 +2902,11 @@ lmerge_matched:
break; /* concurrent update/delete */
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
- newslot, false, &updateCxt);
+ newslot, false, &updateCxt, &recheckConstraints);
if (result == TM_Ok && updateCxt.updated)
{
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,
- tupleid, NULL, newslot);
+ tupleid, NULL, newslot, recheckConstraints);
mtstate->mt_merge_updated += 1;
}
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 39ab7eac0d..f03c35bc0f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4076,7 +4076,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ &n->deferrable, &n->initdeferred, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d67580fc77..f78bb63520 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -333,6 +333,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
*
* For regular tables all constraints can be marked valid immediately,
* because the table is new therefore empty. Not so for foreign tables.
+ * Also, Create After Row trigger(for Insert and Update) for Deferrable
+ * check constraint.
*/
transformCheckConstraints(&cxt, !cxt.isforeign);
@@ -1319,6 +1321,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
char *ccname = constr->check[ccnum].ccname;
char *ccbin = constr->check[ccnum].ccbin;
bool ccnoinherit = constr->check[ccnum].ccnoinherit;
+ bool ccdeferrable = constr->check[ccnum].ccdeferrable;
+ bool ccdeferred = constr->check[ccnum].ccdeferred;
Node *ccbin_node;
bool found_whole_row;
Constraint *n;
@@ -1348,6 +1352,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
n->conname = pstrdup(ccname);
n->location = -1;
n->is_no_inherit = ccnoinherit;
+ n->deferrable = ccdeferrable;
+ n->initdeferred = ccdeferred;
n->raw_expr = NULL;
n->cooked_expr = nodeToString(ccbin_node);
@@ -3649,7 +3655,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
((node)->contype == CONSTR_PRIMARY || \
(node)->contype == CONSTR_UNIQUE || \
(node)->contype == CONSTR_EXCLUSION || \
- (node)->contype == CONSTR_FOREIGN))
+ (node)->contype == CONSTR_FOREIGN || \
+ (node)->contype == CONSTR_CHECK))
foreach(clist, constraintList)
{
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 8a08463c2b..cc997adeb4 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4560,6 +4560,8 @@ CheckConstraintFetch(Relation relation)
check[found].ccvalid = conform->convalidated;
check[found].ccnoinherit = conform->connoinherit;
+ check[found].ccdeferrable = conform->condeferrable;
+ check[found].ccdeferred = conform->condeferred;
check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
NameStr(conform->conname));
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index b4286cf922..87c18f6299 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -31,6 +31,8 @@ typedef struct ConstrCheck
char *ccbin; /* nodeToString representation of expr */
bool ccvalid;
bool ccnoinherit; /* this is a non-inheritable constraint */
+ bool ccdeferrable;
+ bool ccdeferred;
} ConstrCheck;
/* This structure contains constraints of a tuple */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index d01ab504b6..1bfc3fe99a 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -44,6 +44,8 @@ typedef struct CookedConstraint
int inhcount; /* number of times constraint is inherited */
bool is_no_inherit; /* constraint has local def and cannot be
* inherited */
+ bool is_deferrable; /* is deferrable (only for CHECK) */
+ bool is_deferred; /* is deferred (only for CHECK) */
} CookedConstraint;
extern Relation heap_create(const char *relname,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..8f893eb864 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3886,6 +3886,11 @@
proname => 'unique_key_recheck', provolatile => 'v', prorettype => 'trigger',
proargtypes => '', prosrc => 'unique_key_recheck' },
+# Deferrable unique constraint trigger
+{ oid => '1382', descr => 'deferred CHECK constraint check',
+ proname => 'check_constraint_recheck', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'check_constraint_recheck' },
+
# Generic referential integrity constraint triggers
{ oid => '1644', descr => 'referential integrity FOREIGN KEY ... REFERENCES',
proname => 'RI_FKey_check_ins', provolatile => 'v', prorettype => 'trigger',
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 430e3ca7dd..d13908939e 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -197,6 +197,7 @@ extern void ExecARInsertTriggers(EState *estate,
ResultRelInfo *relinfo,
TupleTableSlot *slot,
List *recheckIndexes,
+ bool recheckConstraints,
TransitionCaptureState *transition_capture);
extern bool ExecIRInsertTriggers(EState *estate,
ResultRelInfo *relinfo,
@@ -244,6 +245,7 @@ extern void ExecARUpdateTriggers(EState *estate,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
List *recheckIndexes,
+ bool recheckConstraints,
TransitionCaptureState *transition_capture,
bool is_crosspart_update);
extern bool ExecIRUpdateTriggers(EState *estate,
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ac02247947..b30376399b 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -194,6 +194,44 @@ ExecGetJunkAttribute(TupleTableSlot *slot, AttrNumber attno, bool *isNull)
}
#endif
+/*
+ * Enumeration specifying the type for re-check of CHECK constraint to perform in
+ * ExecConstraints().
+ *
+ * CHECK_RECHECK_DISABLED is the traditional Postgres immediate check, should
+ * throw an error if there is any check constraint violation. This is useful
+ * for command like CopyFrom.
+ *
+ * For deferrable CHECK constraints, CHECK_RECHECK_ENABLED is passed to
+ * to ExecConstraints for insert or update queries. ExecConstraints() should
+ * validate if the CHECK constraint is violated but should not throw an error,
+ * block, or prevent the insertion. We'll recheck later when it is time for the
+ * constraint to be enforced. The ExecConstraints() must return false if the tuple is
+ * not violating any check constraint, true if it is possibly a violation and we need
+ * to recheck the CHECK constraint. In the "false" case
+ * it is safe to omit the later recheck.
+ *
+ * When it is time to recheck the deferred constraint(via AR trigger), a
+ * call is made with CHECK_RECHECK_EXISTING and this time conflicting latest live tuple
+ * will be revalidate.
+ */
+typedef enum checkConstraintRecheck
+{
+ CHECK_RECHECK_DISABLED, /* Recheck of CHECK constraint is disabled, so
+ * DEFERRED CHECK constraint will be
+ * considered as non-deferrable check
+ * constraint. */
+ CHECK_RECHECK_ENABLED, /* Recheck of CHECK constraint is enabled, so
+ * CHECK constraint will be validated but
+ * error will not be reported for deferred
+ * CHECK constraint. */
+ CHECK_RECHECK_EXISTING /* Recheck of existing violated CHECK
+ * constraint, indicates that this is a
+ * deferred recheck of a row that was reported
+ * as a potential violation of CHECK
+ * CONSTRAINT */
+} checkConstraintRecheck;
+
/*
* prototypes from functions in execMain.c
*/
@@ -219,8 +257,8 @@ extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid,
ResultRelInfo *rootRelInfo);
extern List *ExecGetAncestorResultRels(EState *estate, ResultRelInfo *resultRelInfo);
-extern void ExecConstraints(ResultRelInfo *resultRelInfo,
- TupleTableSlot *slot, EState *estate);
+extern bool ExecConstraints(ResultRelInfo *resultRelInfo,
+ TupleTableSlot *slot, EState *estate, checkConstraintRecheck checkConstraint);
extern bool ExecPartitionCheck(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate, bool emitError);
extern void ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e6f6602d95..9d34067b6e 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -603,6 +603,104 @@ COMMIT;
ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
DETAIL: Key (i)=(1) already exists.
DROP TABLE parted_uniq_tbl;
+-- deferrable CHECK constraint
+CREATE TABLE check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t text); -- initially Immediate
+INSERT INTO check_constr_tbl VALUES (1, 'one');
+-- default is immediate so this should fail right away
+INSERT INTO check_constr_tbl VALUES (0, 'zero');
+ERROR: new row for relation "check_constr_tbl" violates check constraint "check_constr_tbl_i_check"
+DETAIL: Failing row contains (0, zero).
+-- should fail here too
+BEGIN;
+INSERT INTO check_constr_tbl VALUES (0, 'zero');
+ERROR: new row for relation "check_constr_tbl" violates check constraint "check_constr_tbl_i_check"
+DETAIL: Failing row contains (0, zero).
+COMMIT;
+-- explicitly defer the constraint
+BEGIN;
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'one');-- should succeed
+COMMIT; -- should fail
+ERROR: new row for relation "check_constr_tbl" violates check constraint "check_constr_tbl_i_check"
+DETAIL: Failing row contains (0, one).
+BEGIN;
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should succeed
+UPDATE check_constr_tbl SET i = 1 WHERE t = 'one';
+COMMIT; -- should succeed
+-- INSERT Followed by UPDATE, UPDATE
+BEGIN;
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (3, 'three'); -- should succeed
+UPDATE check_constr_tbl SET i = 0 WHERE t = 'three' and i = 3; -- should succeed
+UPDATE check_constr_tbl SET i = 3 WHERE t = 'three' and i = 0; -- should succeed
+COMMIT; -- should succeed
+-- INSERT Followed by DELETE
+BEGIN;
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'zero'); -- should succeed
+DELETE FROM check_constr_tbl where i = 0; -- should succeed
+COMMIT; -- should succeed
+-- try adding an initially deferred constraint
+ALTER TABLE check_constr_tbl DROP CONSTRAINT check_constr_tbl_i_check;
+ALTER TABLE check_constr_tbl ADD CONSTRAINT check_constr_tbl_i_check
+ CHECK (i<>0) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should succeed
+COMMIT; -- should fail
+ERROR: new row for relation "check_constr_tbl" violates check constraint "check_constr_tbl_i_check"
+DETAIL: Failing row contains (0, one).
+BEGIN;
+SET CONSTRAINTS ALL IMMEDIATE;
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should fail
+ERROR: new row for relation "check_constr_tbl" violates check constraint "check_constr_tbl_i_check"
+DETAIL: Failing row contains (0, one).
+COMMIT;
+-- test deferrable CHECK constraint with a partition table
+CREATE TABLE parted_check_constr_tbl (i int check(i<>0) DEFERRABLE) partition by range (i);
+CREATE TABLE parted_check_constr_tbl_1 PARTITION OF parted_check_constr_tbl FOR VALUES FROM (0) TO (10);
+CREATE TABLE parted_check_constr_tbl_2 PARTITION OF parted_check_constr_tbl FOR VALUES FROM (20) TO (30);
+SELECT conname, conrelid::regclass FROM pg_constraint
+ WHERE conname LIKE 'parted_check%' ORDER BY conname;
+ conname | conrelid
+---------------------------------+---------------------------
+ parted_check_constr_tbl_i_check | parted_check_constr_tbl
+ parted_check_constr_tbl_i_check | parted_check_constr_tbl_1
+ parted_check_constr_tbl_i_check | parted_check_constr_tbl_2
+(3 rows)
+
+BEGIN;
+INSERT INTO parted_check_constr_tbl VALUES (1);
+SAVEPOINT f;
+UPDATE parted_check_constr_tbl set i = 0 where i = 1; -- check constraint violation
+ERROR: new row for relation "parted_check_constr_tbl_1" violates check constraint "parted_check_constr_tbl_i_check"
+DETAIL: Failing row contains (0).
+ROLLBACK TO f;
+SET CONSTRAINTS parted_check_constr_tbl_i_check DEFERRED;
+UPDATE parted_check_constr_tbl set i = 0 where i = 1; -- now succeed
+COMMIT; -- should fail
+ERROR: new row for relation "parted_check_constr_tbl_1" violates check constraint "parted_check_constr_tbl_i_check"
+DETAIL: Failing row contains (0).
+-- test table inheritance, must inhert column i DEFERRABLE check constraint
+CREATE TABLE parent_check_deferred ( i int CHECK(i<>0) DEFERRABLE INITIALLY DEFERRED);
+CREATE TABLE child_check_deferred ( j int) INHERITS (parent_check_deferred);
+\d+ child_check_deferred;
+ Table "public.child_check_deferred"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i | integer | | | | plain | |
+ j | integer | | | | plain | |
+Check constraints:
+ "parent_check_deferred_i_check" CHECK (i <> 0) DEFERRABLE INITIALLY DEFERRED
+Inherits: parent_check_deferred
+
+-- clean up
+DROP TABLE child_check_deferred;
+DROP TABLE parent_check_deferred;
+DROP TABLE parted_check_constr_tbl_1;
+DROP TABLE parted_check_constr_tbl_2;
+DROP TABLE parted_check_constr_tbl;
+DROP TABLE check_constr_tbl;
-- test naming a constraint in a partition when a conflict exists
CREATE TABLE parted_fk_naming (
id bigint NOT NULL default 1,
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 5ffcd4ffc7..5952ad53f6 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -430,6 +430,105 @@ INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
COMMIT;
DROP TABLE parted_uniq_tbl;
+
+-- deferrable CHECK constraint
+CREATE TABLE check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t text); -- initially Immediate
+
+INSERT INTO check_constr_tbl VALUES (1, 'one');
+
+-- default is immediate so this should fail right away
+INSERT INTO check_constr_tbl VALUES (0, 'zero');
+
+-- should fail here too
+BEGIN;
+
+INSERT INTO check_constr_tbl VALUES (0, 'zero');
+
+COMMIT;
+
+-- explicitly defer the constraint
+BEGIN;
+
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'one');-- should succeed
+
+COMMIT; -- should fail
+
+BEGIN;
+
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should succeed
+UPDATE check_constr_tbl SET i = 1 WHERE t = 'one';
+
+COMMIT; -- should succeed
+
+-- INSERT Followed by UPDATE, UPDATE
+BEGIN;
+
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (3, 'three'); -- should succeed
+UPDATE check_constr_tbl SET i = 0 WHERE t = 'three' and i = 3; -- should succeed
+UPDATE check_constr_tbl SET i = 3 WHERE t = 'three' and i = 0; -- should succeed
+
+COMMIT; -- should succeed
+
+-- INSERT Followed by DELETE
+BEGIN;
+
+SET CONSTRAINTS check_constr_tbl_i_check DEFERRED;
+INSERT INTO check_constr_tbl VALUES (0, 'zero'); -- should succeed
+DELETE FROM check_constr_tbl where i = 0; -- should succeed
+
+COMMIT; -- should succeed
+
+-- try adding an initially deferred constraint
+ALTER TABLE check_constr_tbl DROP CONSTRAINT check_constr_tbl_i_check;
+ALTER TABLE check_constr_tbl ADD CONSTRAINT check_constr_tbl_i_check
+ CHECK (i<>0) DEFERRABLE INITIALLY DEFERRED;
+
+BEGIN;
+
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should succeed
+
+COMMIT; -- should fail
+
+BEGIN;
+
+SET CONSTRAINTS ALL IMMEDIATE;
+
+INSERT INTO check_constr_tbl VALUES (0, 'one'); -- should fail
+
+COMMIT;
+
+
+-- test deferrable CHECK constraint with a partition table
+CREATE TABLE parted_check_constr_tbl (i int check(i<>0) DEFERRABLE) partition by range (i);
+CREATE TABLE parted_check_constr_tbl_1 PARTITION OF parted_check_constr_tbl FOR VALUES FROM (0) TO (10);
+CREATE TABLE parted_check_constr_tbl_2 PARTITION OF parted_check_constr_tbl FOR VALUES FROM (20) TO (30);
+SELECT conname, conrelid::regclass FROM pg_constraint
+ WHERE conname LIKE 'parted_check%' ORDER BY conname;
+BEGIN;
+INSERT INTO parted_check_constr_tbl VALUES (1);
+SAVEPOINT f;
+UPDATE parted_check_constr_tbl set i = 0 where i = 1; -- check constraint violation
+ROLLBACK TO f;
+SET CONSTRAINTS parted_check_constr_tbl_i_check DEFERRED;
+UPDATE parted_check_constr_tbl set i = 0 where i = 1; -- now succeed
+COMMIT; -- should fail
+
+-- test table inheritance, must inhert column i DEFERRABLE check constraint
+CREATE TABLE parent_check_deferred ( i int CHECK(i<>0) DEFERRABLE INITIALLY DEFERRED);
+CREATE TABLE child_check_deferred ( j int) INHERITS (parent_check_deferred);
+\d+ child_check_deferred;
+
+-- clean up
+DROP TABLE child_check_deferred;
+DROP TABLE parent_check_deferred;
+DROP TABLE parted_check_constr_tbl_1;
+DROP TABLE parted_check_constr_tbl_2;
+DROP TABLE parted_check_constr_tbl;
+DROP TABLE check_constr_tbl;
+
-- test naming a constraint in a partition when a conflict exists
CREATE TABLE parted_fk_naming (
id bigint NOT NULL default 1,
--
2.25.1
