CHECK Constraint Deferrable

From cf6057ebeffd026ae075ec43d573eca1164eff5b Mon Sep 17 00:00:00 2001
From: Himanshu Upadhyaya <himanshu.upadhyaya@enterprisedb.com>
Date: Thu, 7 Sep 2023 13:19:14 +0530
Subject: [PATCH v2] 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 b42711f574..b595a60b42 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,
@@ -2050,13 +2054,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.
@@ -2113,8 +2119,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 */
@@ -2142,6 +2150,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;
@@ -2235,7 +2273,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;
 
@@ -2500,7 +2538,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..6f67b9424f 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 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 b47cb5c66d..7df47017c9 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 47c556669f..36da96c8f4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -938,6 +938,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;
 		}
@@ -2930,6 +2933,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);
 				}
 			}
@@ -19543,6 +19548,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 */
 		ATAddCheckNNConstraint(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 81f27042bc..9a13aa94a7 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -515,6 +515,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
 	if (!skip_tuple)
 	{
 		List	   *recheckIndexes = NIL;
+		bool		recheckConstraints = false;
 
 		/* Compute stored generated columns */
 		if (rel->rd_att->constr &&
@@ -524,7 +525,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);
 
@@ -538,7 +539,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
@@ -583,6 +584,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
 	{
 		List	   *recheckIndexes = NIL;
 		TU_UpdateIndexes update_indexes;
+		bool		recheckConstraints = false;
 
 		/* Compute stored generated columns */
 		if (rel->rd_att->constr &&
@@ -592,7 +594,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);
 
@@ -609,7 +611,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 5005d8c0d1..f67638f3ac 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.
@@ -2898,11 +2903,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 7d2032885e..7e217c74e1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4077,7 +4077,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 55c315f0e2..112609152d 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -335,6 +335,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);
 
@@ -1417,6 +1419,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;
@@ -1446,6 +1450,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);
 
@@ -3780,7 +3786,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 7234cb3da6..88d637df93 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4561,6 +4561,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 51f7b12aa3..36aecd2be0 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -45,6 +45,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 9805bc6118..f935147718 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3898,6 +3898,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 c677e490d7..4dd36cbeba 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 revalidated.
+ */
+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 5b068477bf..2e3e1f9005 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -636,6 +636,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 a7d96e98f5..ea0281b39f 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -446,6 +446,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

From 8f812fe14e4d5d133a4f6d060cc3021a84a577f4 Mon Sep 17 00:00:00 2001
From: Himanshu Upadhyaya <himanshu.upadhyaya@enterprisedb.com>
Date: Mon, 2 Oct 2023 17:45:53 +0530
Subject: [PATCH v3] Implementation of "CHECK Constraint" to make it

Deferrable.
---
 src/backend/access/common/tupdesc.c       |   6 +-
 src/backend/catalog/heap.c                |  73 ++++++++---
 src/backend/commands/constraint.c         | 116 ++++++++++++++++++
 src/backend/commands/copyfrom.c           |  10 +-
 src/backend/commands/tablecmds.c          |  19 ++-
 src/backend/commands/trigger.c            |  37 ++++--
 src/backend/executor/execMain.c           |  41 ++++++-
 src/backend/executor/execReplication.c    |  10 +-
 src/backend/executor/nodeModifyTable.c    |  29 +++--
 src/backend/optimizer/util/plancat.c      |   9 +-
 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 | 140 ++++++++++++++++++++++
 src/test/regress/sql/constraints.sql      | 133 ++++++++++++++++++++
 src/tools/pgindent/typedefs.list          |   1 +
 21 files changed, 625 insertions(+), 65 deletions(-)

diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index ce2c7bce85..5ff488b9e7 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;
 			}
 		}
 
@@ -531,7 +533,9 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
 			if (!(strcmp(check1->ccname, check2->ccname) == 0 &&
 				  strcmp(check1->ccbin, check2->ccbin) == 0 &&
 				  check1->ccvalid == check2->ccvalid &&
-				  check1->ccnoinherit == check2->ccnoinherit))
+				  check1->ccnoinherit == check2->ccnoinherit &&
+				  check1->ccdeferrable == check2->ccdeferrable &&
+				  check1->ccdeferred == check2->ccdeferred))
 				return false;
 		}
 	}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index d03c961678..5852428946 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -52,10 +52,12 @@
 #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 "commands/trigger.h"
 #include "miscadmin.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/optimizer.h"
@@ -63,6 +65,7 @@
 #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"
@@ -101,12 +104,14 @@ static ObjectAddress AddNewRelationType(const char *typeName,
 										Oid new_array_type);
 static void RelationRemoveInheritance(Oid relid);
 static Oid	StoreRelCheck(Relation rel, const char *ccname, Node *expr,
+						  bool is_deferrable, bool initdeferred,
 						  bool is_validated, bool is_local, int inhcount,
-						  bool is_no_inherit, bool is_internal);
+						  bool is_no_inherit, bool is_internal, int numchecks);
 static void StoreConstraints(Relation rel, List *cooked_constraints,
 							 bool is_internal);
 static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
 										bool allow_merge, bool is_local,
+										bool is_deferrable, bool is_deferred,
 										bool is_initially_valid,
 										bool is_no_inherit);
 static void SetRelationNumChecks(Relation rel, int numchecks);
@@ -2049,8 +2054,9 @@ SetAttrMissing(Oid relid, char *attname, char *value)
  */
 static Oid
 StoreRelCheck(Relation rel, const char *ccname, Node *expr,
+			  bool is_deferrable, bool initdeferred,
 			  bool is_validated, bool is_local, int inhcount,
-			  bool is_no_inherit, bool is_internal)
+			  bool is_no_inherit, bool is_internal, int numchecks)
 {
 	char	   *ccbin;
 	List	   *varList;
@@ -2113,8 +2119,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 */
@@ -2141,6 +2149,38 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
 							  inhcount, /* coninhcount */
 							  is_no_inherit,	/* connoinherit */
 							  is_internal); /* internally constructed? */
+	SetRelationNumChecks(rel, numchecks);
+	CommandCounterIncrement();
+
+	/*
+	 * 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)
+	{
+		CreateTrigStmt *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);
 
@@ -2233,10 +2273,10 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
 			case CONSTR_CHECK:
 				con->conoid =
 					StoreRelCheck(rel, con->name, con->expr,
+								  con->is_deferrable, con->is_deferred,
 								  !con->skip_validation, con->is_local,
 								  con->inhcount, con->is_no_inherit,
-								  is_internal);
-				numchecks++;
+								  is_internal, ++numchecks);
 				break;
 
 			case CONSTR_NOTNULL:
@@ -2251,9 +2291,6 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
 					 (int) con->contype);
 		}
 	}
-
-	if (numchecks > 0)
-		SetRelationNumChecks(rel, numchecks);
 }
 
 /*
@@ -2451,6 +2488,8 @@ AddRelationNewConstraints(Relation rel,
 				 */
 				if (MergeWithExistingConstraint(rel, ccname, expr,
 												allow_merge, is_local,
+												cdef->deferrable,
+												cdef->initdeferred,
 												cdef->initially_valid,
 												cdef->is_no_inherit))
 					continue;
@@ -2499,10 +2538,10 @@ AddRelationNewConstraints(Relation rel,
 			 * OK, store it.
 			 */
 			constrOid =
-				StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local,
-							  is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
-
-			numchecks++;
+				StoreRelCheck(rel, ccname, expr, cdef->deferrable,
+							  cdef->initdeferred, cdef->initially_valid,
+							  is_local, is_local ? 0 : 1, cdef->is_no_inherit,
+							  is_internal, ++numchecks);
 
 			cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
 			cooked->contype = CONSTR_CHECK;
@@ -2606,8 +2645,8 @@ AddRelationNewConstraints(Relation rel,
 static bool
 MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
 							bool allow_merge, bool is_local,
-							bool is_initially_valid,
-							bool is_no_inherit)
+							bool is_deferrable, bool is_deferred,
+							bool is_initially_valid, bool is_no_inherit)
 {
 	bool		found;
 	Relation	conDesc;
@@ -2653,7 +2692,9 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
 			if (isnull)
 				elog(ERROR, "null conbin for rel %s",
 					 RelationGetRelationName(rel));
-			if (equal(expr, stringToNode(TextDatumGetCString(val))))
+			if (equal(expr, stringToNode(TextDatumGetCString(val))) &&
+				con->condeferrable == is_deferrable &&
+				con->condeferred == is_deferred)
 				found = true;
 		}
 
diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c
index 35c4451fc0..debf33be26 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 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_DEFERRED_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 70871ed819..36d2c5f80b 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, InvalidOid,
 										 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, InvalidOid,
 									 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, InvalidOid,
 									 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_DEFERRED_NO);
 
 				/*
 				 * 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, InvalidOid, cstate->transition_capture);
 
 					list_free(recheckIndexes);
 				}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 73b8dea81c..afe068531b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -353,7 +353,8 @@ static void RangeVarCallbackForTruncate(const RangeVar *relation,
 static List *MergeAttributes(List *columns, const List *supers, char relpersistence,
 							 bool is_partition, List **supconstr,
 							 List **supnotnulls);
-static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr);
+static List *MergeCheckConstraint(List *constraints, const char *name,
+								  Node *expr, bool is_deferrable, bool is_deferred);
 static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel);
 static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel);
 static void StoreCatalogInheritance(Oid relationId, List *supers,
@@ -933,6 +934,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 			cooked->name = NULL;
 			cooked->attnum = attnum;
 			cooked->expr = colDef->cooked_default;
+			cooked->is_deferrable = false;	/* By default constraint is not
+											 * deferrable */
+			cooked->is_deferred = false;	/* ditto */
 			cooked->skip_validation = false;
 			cooked->is_local = true;	/* not used for defaults */
 			cooked->inhcount = 0;	/* ditto */
@@ -2871,6 +2875,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
 			for (int i = 0; i < constr->num_check; i++)
 			{
 				char	   *name = check[i].ccname;
+				bool		is_deferrable = check[i].ccdeferrable;
+				bool		is_deferred = check[i].ccdeferred;
 				Node	   *expr;
 				bool		found_whole_row;
 
@@ -2897,7 +2903,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
 									   name,
 									   RelationGetRelationName(relation))));
 
-				constraints = MergeCheckConstraint(constraints, name, expr);
+				constraints = MergeCheckConstraint(constraints, name, expr,
+												   is_deferrable, is_deferred);
 			}
 		}
 
@@ -3251,7 +3258,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
  * the list.
  */
 static List *
-MergeCheckConstraint(List *constraints, const char *name, Node *expr)
+MergeCheckConstraint(List *constraints, const char *name, Node *expr,
+					 bool is_deferrable, bool is_deferred)
 {
 	ListCell   *lc;
 	CookedConstraint *newcon;
@@ -3291,6 +3299,8 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr)
 	newcon->contype = CONSTR_CHECK;
 	newcon->name = pstrdup(name);
 	newcon->expr = expr;
+	newcon->is_deferrable = is_deferrable;
+	newcon->is_deferred = is_deferred;
 	newcon->inhcount = 1;
 	return lappend(constraints, newcon);
 }
@@ -19491,6 +19501,9 @@ DetachAddConstraintIfNeeded(List **wqueue, Relation partRel)
 		n->raw_expr = NULL;
 		n->cooked_expr = nodeToString(make_ands_explicit(constraintExpr));
 		n->initially_valid = true;
+		n->deferrable = false;	/* By default this new constraint must be
+								 * non-deferrable */
+		n->initdeferred = false;	/* Ditto */
 		n->skip_validation = true;
 		/* It's a re-add, since it nominally already exists */
 		ATAddCheckNNConstraint(wqueue, tab, partRel, n,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 52177759ab..35915eb7f3 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, Oid 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, InvalidOid, 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)
+					 Oid 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, InvalidOid, 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, InvalidOid, 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, InvalidOid,
 							  ExecGetAllUpdatedCols(relinfo, estate),
 							  transition_capture,
 							  false);
@@ -3089,6 +3092,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 					 HeapTuple fdw_trigtuple,
 					 TupleTableSlot *newslot,
 					 List *recheckIndexes,
+					 Oid 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, InvalidOid, 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, Oid recheckConstraints,
+					  Bitmapset *modifiedCols,
 					  TransitionCaptureState *transition_capture,
 					  bool is_crosspart_update)
 {
@@ -6389,6 +6394,18 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 			}
 		}
 
+		/*
+		 * If the trigger is deferred check constraint recheck trigger, only
+		 * queue it for the constraint that was potentially violated.
+		 */
+		if (trigger->tgfoid == F_CHECK_CONSTRAINT_RECHECK)
+		{
+			if (recheckConstraints != trigger->tgconstraint)
+			{
+				continue;		/* Check constraint not violated */
+			}
+		}
+
 		/*
 		 * 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..47863d5b79 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -45,6 +45,7 @@
 #include "access/xact.h"
 #include "catalog/namespace.h"
 #include "catalog/partition.h"
+#include "catalog/pg_constraint.h"
 #include "catalog/pg_publication.h"
 #include "commands/matview.h"
 #include "commands/trigger.h"
@@ -1734,12 +1735,15 @@ ExecutePlan(EState *estate,
 
 /*
  * ExecRelCheck --- check that tuple meets constraints for result relation
+ * and populate recheckConstraints with Oid of violated deferred constraint
+ * 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,
+			 EnforceDeferredCheck checkConstraint, bool *recheckConstraints)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
 	int			ncheck = rel->rd_att->constr->num_check;
@@ -1798,7 +1802,20 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
 		 * ExecQual.
 		 */
 		if (!ExecCheck(checkconstr, econtext))
+		{
+
+			/*
+			 * If the constraint is deferrable and caller is
+			 * CHECK_DEFERRED_YES 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_DEFERRED_YES && check[i].ccdeferrable)
+			{
+				*recheckConstraints = true;
+			}
 			return check[i].ccname;
+		}
 	}
 
 	/* NULL result means no error */
@@ -1936,18 +1953,27 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
  * have been converted from the original input tuple after tuple routing.
  * 'resultRelInfo' is the final result relation, after tuple routing.
  */
-void
+Oid
 ExecConstraints(ResultRelInfo *resultRelInfo,
-				TupleTableSlot *slot, EState *estate)
+				TupleTableSlot *slot, EState *estate,
+				EnforceDeferredCheck checkConstraint)
 {
 	Relation	rel = resultRelInfo->ri_RelationDesc;
 	TupleDesc	tupdesc = RelationGetDescr(rel);
 	TupleConstr *constr = tupdesc->constr;
 	Bitmapset  *modifiedCols;
+	bool		recheckConstraints = false;
+	const char *failed;
 
 	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_DEFERRED_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_DEFERRED_EXISTING)
 	{
 		int			natts = tupdesc->natts;
 		int			attrChk;
@@ -2013,9 +2039,9 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
 
 	if (rel->rd_rel->relchecks > 0)
 	{
-		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 +2086,9 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
 					 errtableconstraint(orig_rel, failed)));
 		}
 	}
+	return (recheckConstraints ?
+			get_relation_constraint_oid(RelationGetRelid(rel), failed, false) :
+			InvalidOid);
 }
 
 /*
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 81f27042bc..989baff171 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -515,6 +515,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
 	if (!skip_tuple)
 	{
 		List	   *recheckIndexes = NIL;
+		Oid			recheckConstraints = false;
 
 		/* Compute stored generated columns */
 		if (rel->rd_att->constr &&
@@ -524,7 +525,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_DEFERRED_YES);
 		if (rel->rd_rel->relispartition)
 			ExecPartitionCheck(resultRelInfo, slot, estate, true);
 
@@ -538,7 +539,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
@@ -583,6 +584,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
 	{
 		List	   *recheckIndexes = NIL;
 		TU_UpdateIndexes update_indexes;
+		Oid			recheckConstraints = InvalidOid;
 
 		/* Compute stored generated columns */
 		if (rel->rd_att->constr &&
@@ -592,7 +594,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_DEFERRED_YES);
 		if (rel->rd_rel->relispartition)
 			ExecPartitionCheck(resultRelInfo, slot, estate, true);
 
@@ -609,7 +611,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 d21a178ad5..133337f9c3 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;
+	Oid			recheckConstraints = InvalidOid;
 
 	/*
 	 * 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_DEFERRED_YES);
 
 		/*
 		 * Also check the tuple against the partition constraint, if there is
@@ -1162,6 +1163,7 @@ ExecInsert(ModifyTableContext *context,
 							 NULL,
 							 slot,
 							 NULL,
+							 InvalidOid,
 							 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, InvalidOid,
 							 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, InvalidOid, 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, Oid *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_DEFERRED_YES);
 
 	/*
 	 * 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, Oid 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, InvalidOid, NULL, true);
 }
 
 /* ----------------------------------------------------------------
@@ -2264,6 +2267,7 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
 	Relation	resultRelationDesc = resultRelInfo->ri_RelationDesc;
 	UpdateContext updateCxt = {0};
 	TM_Result	result;
+	Oid			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};
+		Oid			recheckConstraints = false;
 
 		/*
 		 * Test condition, if any.
@@ -2898,11 +2903,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/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 243c8fb1e4..e45447a29a 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1244,15 +1244,16 @@ get_relation_constraints(PlannerInfo *root,
 			Node	   *cexpr;
 
 			/*
-			 * If this constraint hasn't been fully validated yet, we must
-			 * ignore it here.  Also ignore if NO INHERIT and we weren't told
-			 * that that's safe.
+			 * Ignore if this is deferred CHECK constraint or constraint
+			 * hasn't been fully validated yet.  Also ignore if NO INHERIT and
+			 * we weren't told that that's safe.
 			 */
+			if (constr->check[i].ccdeferrable)
+				continue;
 			if (!constr->check[i].ccvalid)
 				continue;
 			if (constr->check[i].ccnoinherit && !include_noinherit)
 				continue;
-
 			cexpr = stringToNode(constr->check[i].ccbin);
 
 			/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d2032885e..7e217c74e1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4077,7 +4077,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 cf0d432ab1..5f2e67ea93 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -335,6 +335,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);
 
@@ -1407,6 +1409,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 		{
 			char	   *ccname = constr->check[ccnum].ccname;
 			char	   *ccbin = constr->check[ccnum].ccbin;
+			bool		ccdeferrable = constr->check[ccnum].ccdeferrable;
+			bool		ccdeferred = constr->check[ccnum].ccdeferred;
 			bool		ccnoinherit = constr->check[ccnum].ccnoinherit;
 			Node	   *ccbin_node;
 			bool		found_whole_row;
@@ -1436,6 +1440,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 			n->contype = CONSTR_CHECK;
 			n->conname = pstrdup(ccname);
 			n->location = -1;
+			n->deferrable = ccdeferrable;
+			n->initdeferred = ccdeferred;
 			n->is_no_inherit = ccnoinherit;
 			n->raw_expr = NULL;
 			n->cooked_expr = nodeToString(ccbin_node);
@@ -3771,7 +3777,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 7234cb3da6..136e1e1d8a 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4559,6 +4559,8 @@ CheckConstraintFetch(Relation relation)
 			break;
 		}
 
+		check[found].ccdeferrable = conform->condeferrable;
+		check[found].ccdeferred = conform->condeferred;
 		check[found].ccvalid = conform->convalidated;
 		check[found].ccnoinherit = conform->connoinherit;
 		check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index ffd2874ee3..17af20505d 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -29,6 +29,8 @@ typedef struct ConstrCheck
 {
 	char	   *ccname;
 	char	   *ccbin;			/* nodeToString representation of expr */
+	bool		ccdeferrable;
+	bool		ccdeferred;
 	bool		ccvalid;
 	bool		ccnoinherit;	/* this is a non-inheritable constraint */
 } ConstrCheck;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 51f7b12aa3..c7560e0c78 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -40,6 +40,8 @@ typedef struct CookedConstraint
 	char	   *name;			/* name, or NULL if none */
 	AttrNumber	attnum;			/* which attr (only for NOTNULL, DEFAULT) */
 	Node	   *expr;			/* transformed default or check expr */
+	bool		is_deferrable;	/* is deferrable (only for CHECK) */
+	bool		is_deferred;	/* is deferred (only for CHECK) */
 	bool		skip_validation;	/* skip validation? (only for CHECK) */
 	bool		is_local;		/* constraint has local (non-inherited) def */
 	int			inhcount;		/* number of times constraint is inherited */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f0b7b9cbd8..60b768f8dd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3898,6 +3898,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..6798490b61 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,
+								 Oid 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,
+								 Oid 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 aeebe0e0ff..2d7ec2f245 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_DEFERRED_NO 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_DEFERRED_YES 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_DEFERRED_EXISTING and this time conflicting latest live tuple
+ * will be revalidated.
+ */
+typedef enum EnforceDeferredCheck
+{
+	CHECK_DEFERRED_NO,			/* Recheck of CHECK constraint is disabled, so
+								 * DEFERRED CHECK constraint will be
+								 * considered as non-deferrable check
+								 * constraint.  */
+	CHECK_DEFERRED_YES,			/* Recheck of CHECK constraint is enabled, so
+								 * CHECK constraint will be validated but
+								 * error will not be reported for deferred
+								 * CHECK constraint. */
+	CHECK_DEFERRED_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 */
+} EnforceDeferredCheck;
+
 /*
  * 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 Oid	ExecConstraints(ResultRelInfo *resultRelInfo,
+							TupleTableSlot *slot, EState *estate, EnforceDeferredCheck 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 5b068477bf..50c58a615a 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -636,6 +636,146 @@ 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
+
+CREATE TABLE check_deferred_1 (a int, b int);
+ALTER TABLE check_deferred_1 ADD CONSTRAINT a_check CHECK (a > 0);
+ALTER TABLE check_deferred_1 ADD CONSTRAINT b_check CHECK (b > 0) DEFERRABLE;
+-- test constraint exclusion logic
+CREATE TABLE check_deferred_ex (a int);
+CREATE TABLE check_deferred_ex_c1 () INHERITS (check_deferred_ex);
+CREATE TABLE check_deferred_ex_c2 () INHERITS (check_deferred_ex);
+ALTER TABLE check_deferred_ex_c2 ADD CONSTRAINT cc CHECK (a != 5) INITIALLY DEFERRED;
+BEGIN;
+INSERT INTO check_deferred_ex_c2 VALUES (5);
+SET LOCAL constraint_exclusion TO off;
+SELECT * FROM check_deferred_ex WHERE a = 5;
+ a 
+---
+ 5
+(1 row)
+
+SET LOCAL constraint_exclusion TO on;
+SELECT * FROM check_deferred_ex WHERE a = 5;
+ a 
+---
+ 5
+(1 row)
+
+COMMIT; --should fail
+ERROR:  new row for relation "check_deferred_ex_c2" violates check constraint "cc"
+DETAIL:  Failing row contains (5).
+-- test merge constraint logic
+CREATE TABLE p (a int, b int);
+ALTER TABLE p ADD CONSTRAINT c1 CHECK (a > 0) DEFERRABLE;
+ALTER TABLE p ADD CONSTRAINT c2 CHECK (b > 0);
+CREATE TABLE q () INHERITS (p);
+ALTER TABLE q ADD CONSTRAINT c1 CHECK (a > 0); --should fail
+ERROR:  constraint "c1" for relation "q" already exists
+ALTER TABLE q ADD CONSTRAINT c2 CHECK (b > 0) DEFERRABLE; --should fail
+ERROR:  constraint "c2" for relation "q" already exists
+-- 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;
+DROP TABLE check_deferred_1;
+DROP TABLE check_deferred_ex_c2;
+DROP TABLE check_deferred_ex_c1;
+DROP TABLE check_deferred_ex;
+DROP TABLE q;
+DROP TABLE p;
 -- 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 a7d96e98f5..f06cc97548 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -446,6 +446,139 @@ 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;
+
+CREATE TABLE check_deferred_1 (a int, b int);
+ALTER TABLE check_deferred_1 ADD CONSTRAINT a_check CHECK (a > 0);
+ALTER TABLE check_deferred_1 ADD CONSTRAINT b_check CHECK (b > 0) DEFERRABLE;
+
+-- test constraint exclusion logic
+CREATE TABLE check_deferred_ex (a int);
+CREATE TABLE check_deferred_ex_c1 () INHERITS (check_deferred_ex);
+CREATE TABLE check_deferred_ex_c2 () INHERITS (check_deferred_ex);
+ALTER TABLE check_deferred_ex_c2 ADD CONSTRAINT cc CHECK (a != 5) INITIALLY DEFERRED;
+BEGIN;
+INSERT INTO check_deferred_ex_c2 VALUES (5);
+SET LOCAL constraint_exclusion TO off;
+SELECT * FROM check_deferred_ex WHERE a = 5;
+SET LOCAL constraint_exclusion TO on;
+SELECT * FROM check_deferred_ex WHERE a = 5;
+COMMIT; --should fail
+
+-- test merge constraint logic
+CREATE TABLE p (a int, b int);
+ALTER TABLE p ADD CONSTRAINT c1 CHECK (a > 0) DEFERRABLE;
+ALTER TABLE p ADD CONSTRAINT c2 CHECK (b > 0);
+
+CREATE TABLE q () INHERITS (p);
+ALTER TABLE q ADD CONSTRAINT c1 CHECK (a > 0); --should fail
+ALTER TABLE q ADD CONSTRAINT c2 CHECK (b > 0) DEFERRABLE; --should fail
+
+-- 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;
+DROP TABLE check_deferred_1;
+DROP TABLE check_deferred_ex_c2;
+DROP TABLE check_deferred_ex_c1;
+DROP TABLE check_deferred_ex;
+DROP TABLE q;
+DROP TABLE p;
+
+
+
 -- 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/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8de90c4958..e66dc59c7f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -334,6 +334,7 @@ CCHashFN
 CEOUC_WAIT_MODE
 CFuncHashTabEntry
 CHAR
+EnforceDeferredCheck
 CHECKPOINT
 CHKVAL
 CIRCLE
-- 
2.25.1