Attached are patches to add an ALWAYS DEFERRED option to CONSTRAINTs and
CONSTRAINT TRIGGERs, meaning: SET CONSTRAINTS .. IMMEDIATE will not make
immediate any constraint/trigger that is declared as ALWAYS DEFERRED.
I.e., the opposite of NOT DEFERRED. Perhaps I should make this NOT
IMMEDIATE? Making it NOT IMMEDIATE has the benefit of not having to
change the precedence of ALWAYS to avoid a shift/reduce conflict... It
may also be more in keeping with NOT DEFERRED.
Motivation:
- I have trigger procedures that must run at the end of the transaction
(after the last statement prior to COMMIT sent by the client/user),
which I make DEFERRABLE, INITIALLY DEFERRED CONSTRAINT TRIGGERs out
of, but SET CONSTRAINTS can be used to foil my triggers. I have
written SQL code to detect that constraint triggers have fired too
soon, but I'd rather not need it.
- Symmetry. If we can have NOT DEFERRABLE constraints, why not also
NOT IMMEDIABLE? :) Naturally "immediable" is not a word, but you
get the point.
- To learn my way around PostgreSQL source code in preparation for
other contributions.
Anyways, this patch is NOT passing tests at the moment, and I'm not sure
why. I'm sure I can figure it out, but first I need to understand the
failures. E.g., I see this sort of difference:
\d testschema.test_index1
Index "testschema.test_index1"
Column | Type | Definition
--------+--------+------------
id | bigint | id
-btree, for table "testschema.test_default_tab"
+f, for table "testschema.btree", predicate (test_default_tab)
which means, I think, that I've screwed up in src/bin/psql/describe.c,
don't it's not obvious to me yet how.
Some questions for experienced PostgreSQL developers:
Q0: Is this sort of patch welcomed?
Q1: Should new columns for pg_catalog.pg_constraint go at the end, or may
they be added in the middle?
Q2: Can I add new columns to information_schema tables, or are there
standards-compliance issues with that?
Q3: Is the C-style for PG documented somewhere? (sorry if I missed this)
Q4: Any ideas what I'm doing wrong in this patch series?
Nico
--
>From 02f2765bde7e7d4fd357853c33dac55e4bdc2732 Mon Sep 17 00:00:00 2001
From: Nicolas Williams <[email protected]>
Date: Tue, 3 Oct 2017 00:33:09 -0500
Subject: [PATCH 1/4] WIP: Add ALWAYS DEFERRED option for CONSTRAINTs
and CONSTRAINT TRIGGERs.
This is important so that one can have triggers and constraints that
must run after all of the user/client's statements in a transaction
(i.e., at COMMIT time), so that the user/client may make no further
changes (triggers, of course, still can).
---
WIP | 25 +++++++++
doc/src/sgml/catalogs.sgml | 7 +++
doc/src/sgml/ref/alter_table.sgml | 4 +-
doc/src/sgml/ref/create_table.sgml | 10 ++--
doc/src/sgml/ref/create_trigger.sgml | 2 +-
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 8 +++
src/backend/catalog/information_schema.sql | 8 +++
src/backend/catalog/pg_constraint.c | 2 +
src/backend/catalog/toasting.c | 2 +-
src/backend/commands/indexcmds.c | 2 +-
src/backend/commands/tablecmds.c | 20 +++++++-
src/backend/commands/trigger.c | 25 +++++++--
src/backend/commands/typecmds.c | 3 ++
src/backend/parser/gram.y | 81 ++++++++++++++++++++++++------
src/backend/parser/parse_utilcmd.c | 1 +
src/backend/utils/adt/ruleutils.c | 2 +
src/bin/pg_dump/pg_dump.c | 23 +++++++--
src/bin/pg_dump/pg_dump.h | 2 +
src/bin/psql/describe.c | 15 ++++--
src/include/catalog/index.h | 2 +
src/include/catalog/pg_constraint.h | 4 +-
src/include/catalog/pg_constraint_fn.h | 1 +
src/include/catalog/pg_trigger.h | 4 +-
src/include/commands/trigger.h | 1 +
src/include/nodes/parsenodes.h | 6 ++-
src/include/utils/reltrigger.h | 1 +
27 files changed, 221 insertions(+), 41 deletions(-)
create mode 100644 WIP
diff --git a/WIP b/WIP
new file mode 100644
index 0000000..806df83
--- /dev/null
+++ b/WIP
@@ -0,0 +1,25 @@
+WIP notes for adding ALWAYS DEFERRED option for CONSTRAINTs and CONSTRAINT TRIGGERs
+===================================================================================
+
+ - add ALWAYS DEFERRED syntax in src/backend/parser/gram.y (DONE)
+
+ (the existing NOT DEFERRABLE == ALWAYS IMMEDIATE, so we don't add that)
+
+ - add alwaysdeferred field to several structs -- wherever initdeferred
+ is defined, basically (DONE)
+
+ - add conalwaysdeferred column to pg_constraints
+
+ - in src/backend/commands/trigger.c modify places where all_isdeferred
+ and all_isset are used (DONE)
+
+ - add AFTER_TRIGGER_ALWAYSDEFERRED for AfterTriggerSharedData's
+ ats_event (struct) and fill it in in AfterTriggerSaveEvent(), that
+ way afterTriggerCheckState() can check this
+
+ (DONE)
+
+ - add docs (DONE)
+
+ - add tests (TBD)
+
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 77c7676..b6ab8ab 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2202,6 +2202,13 @@ SCRAM-SHA-256$<replaceable><iteration count></>:<replaceable><salt><
</row>
<row>
+ <entry><structfield>conalwaysdeferred</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>Is the constraint always deferred?</entry>
+ </row>
+
+ <row>
<entry><structfield>convalidated</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dae6307..e6a1144 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -55,7 +55,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE | ALWAYS DEFERRED ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
@@ -89,7 +89,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
- [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ [ DEFERRABLE | NOT DEFERRABLE | ALWAYS DEFERRED ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 9c63d92..665adae 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -67,7 +67,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE | ALWAYS DEFERRED ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
<phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>
@@ -78,7 +78,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
-[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+[ DEFERRABLE | NOT DEFERRABLE | ALWAYS DEFERRED ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
<phrase>and <replaceable class="PARAMETER">like_option</replaceable> is:</phrase>
@@ -939,13 +939,17 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
<varlistentry>
<term><literal>DEFERRABLE</literal></term>
<term><literal>NOT DEFERRABLE</literal></term>
+ <term><literal>ALWAYS DEFERRED</literal></term>
<listitem>
<para>
- This controls whether the constraint can be deferred. A
+ This controls whether the constraint can be deferred or made immediate. A
constraint that is not deferrable will be checked immediately
after every command. Checking of constraints that are
deferrable can be postponed until the end of the transaction
(using the <xref linkend="sql-set-constraints"> command).
+ Checking of constraints that are always deferred is always
+ postponed until the end of the transaction, and this may not be
+ altered with the <xref linkend="sql-set-constraints"> command.
<literal>NOT DEFERRABLE</literal> is the default.
Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>,
<literal>EXCLUDE</>, and
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 2496250..2b1cb7c 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -29,7 +29,7 @@ PostgreSQL documentation
CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
ON <replaceable class="PARAMETER">table_name</replaceable>
[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
- [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
+ [ NOT DEFERRABLE | DEFERRABLE | ALWAYS DEFERRED ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="PARAMETER">transition_relation_name</replaceable> } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index a376b99..3c86ec2 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2087,6 +2087,7 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ false, /* Is Always Deferred */
is_validated,
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 25c5bea..f3edb6a 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -681,6 +681,7 @@ UpdateIndexRelation(Oid indexoid,
* isconstraint: index is owned by PRIMARY KEY, UNIQUE, or EXCLUSION constraint
* deferrable: constraint is DEFERRABLE
* initdeferred: constraint is INITIALLY DEFERRED
+ * alwaysdeferred: constraint is ALWAYS DEFERRED
* allow_system_table_mods: allow table to be a system catalog
* skip_build: true to skip the index_build() step for the moment; caller
* must do it later (typically via reindex_index())
@@ -710,6 +711,7 @@ index_create(Relation heapRelation,
bool isconstraint,
bool deferrable,
bool initdeferred,
+ bool alwaysdeferred,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
@@ -963,6 +965,7 @@ index_create(Relation heapRelation,
constraintType,
deferrable,
initdeferred,
+ alwaysdeferred,
false, /* already marked primary */
false, /* pg_index entry is OK */
false, /* no old dependencies */
@@ -1006,6 +1009,7 @@ index_create(Relation heapRelation,
/* Non-constraint indexes can't be deferrable */
Assert(!deferrable);
Assert(!initdeferred);
+ Assert(!alwaysdeferred);
}
/* Store dependency on collations */
@@ -1059,6 +1063,7 @@ index_create(Relation heapRelation,
Assert(!isconstraint);
Assert(!deferrable);
Assert(!initdeferred);
+ Assert(!alwaysdeferred);
}
/* Post creation hook for new index */
@@ -1151,6 +1156,7 @@ index_constraint_create(Relation heapRelation,
char constraintType,
bool deferrable,
bool initdeferred,
+ bool alwaysdeferred,
bool mark_as_primary,
bool update_pgindex,
bool remove_old_dependencies,
@@ -1199,6 +1205,7 @@ index_constraint_create(Relation heapRelation,
constraintType,
deferrable,
initdeferred,
+ alwaysdeferred,
true,
RelationGetRelid(heapRelation),
indexInfo->ii_KeyAttrNumbers,
@@ -1263,6 +1270,7 @@ index_constraint_create(Relation heapRelation,
trigger->isconstraint = true;
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
+ trigger->alwaysdeferred = alwaysdeferred;
trigger->constrrel = NULL;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 5398271..4fa230c 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -895,6 +895,10 @@ CREATE VIEW domain_constraints AS
AS yes_or_no) AS is_deferrable,
CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
AS yes_or_no) AS initially_deferred
+ /*
+ * XXX Can we add is_always_deferred here? Are there
+ * standards considerations?
+ */
FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
WHERE rs.oid = con.connamespace
AND n.oid = t.typnamespace
@@ -1779,6 +1783,10 @@ CREATE VIEW table_constraints AS
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred
+ /*
+ * XXX Can we add is_always_deferred here? Are there
+ * standards considerations?
+ */
FROM pg_namespace nc,
pg_namespace nr,
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 1336c46..81c0477 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -51,6 +51,7 @@ CreateConstraintEntry(const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isAlwaysDeferred,
bool isValidated,
Oid relId,
const int16 *constraintKey,
@@ -166,6 +167,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred);
+ values[Anum_pg_constraint_conalwaysdeferred - 1] = BoolGetDatum(isAlwaysDeferred);
values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated);
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 29756eb..d388817 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -333,7 +333,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
BTREE_AM_OID,
rel->rd_rel->reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
- true, false, false, false,
+ true, false, false, false, false,
true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 620704e..e6b7e34 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -672,7 +672,7 @@ DefineIndex(Oid relationId,
collationObjectId, classObjectId,
coloptions, reloptions, stmt->primary,
stmt->isconstraint, stmt->deferrable, stmt->initdeferred,
- allowSystemTableMods,
+ stmt->alwaysdeferred, allowSystemTableMods,
skip_build || stmt->concurrent,
stmt->concurrent, !check_rights,
stmt->if_not_exists);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 33c99b3..f48978f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6779,6 +6779,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
constraintType,
stmt->deferrable,
stmt->initdeferred,
+ stmt->alwaysdeferred,
stmt->primary,
true, /* update pg_index */
true, /* remove old dependencies */
@@ -7346,6 +7347,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
CONSTRAINT_FOREIGN,
fkconstraint->deferrable,
fkconstraint->initdeferred,
+ fkconstraint->alwaysdeferred,
fkconstraint->initially_valid,
RelationGetRelid(rel),
fkattnum,
@@ -7470,7 +7472,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
cmdcon->conname, RelationGetRelationName(rel))));
if (currcon->condeferrable != cmdcon->deferrable ||
- currcon->condeferred != cmdcon->initdeferred)
+ currcon->condeferred != cmdcon->initdeferred ||
+ currcon->conalwaysdeferred != cmdcon->alwaysdeferred)
{
HeapTuple copyTuple;
HeapTuple tgtuple;
@@ -7488,6 +7491,7 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
copy_con->condeferrable = cmdcon->deferrable;
copy_con->condeferred = cmdcon->initdeferred;
+ copy_con->conalwaysdeferred = cmdcon->alwaysdeferred;
CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(ConstraintRelationId,
@@ -7541,6 +7545,7 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
copy_tg->tgdeferrable = cmdcon->deferrable;
copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgalwaysdeferred = cmdcon->alwaysdeferred;
CatalogTupleUpdate(tgrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(TriggerRelationId,
@@ -8208,6 +8213,7 @@ validateForeignKeyConstraint(char *conname,
trig.tgconstraint = constraintOid;
trig.tgdeferrable = FALSE;
trig.tginitdeferred = FALSE;
+ trig.tgalwaysdeferred = FALSE;
/* we needn't fill in remaining fields */
/*
@@ -8297,6 +8303,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->isconstraint = true;
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
+ fk_trigger->alwaysdeferred = fkconstraint->alwaysdeferred;
fk_trigger->constrrel = NULL;
fk_trigger->args = NIL;
@@ -8345,26 +8352,31 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
case FKCONSTR_ACTION_NOACTION:
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
+ fk_trigger->alwaysdeferred = fkconstraint->alwaysdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
break;
case FKCONSTR_ACTION_RESTRICT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del");
break;
case FKCONSTR_ACTION_CASCADE:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
break;
case FKCONSTR_ACTION_SETNULL:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
break;
case FKCONSTR_ACTION_SETDEFAULT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del");
break;
default:
@@ -8400,26 +8412,31 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
case FKCONSTR_ACTION_NOACTION:
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
+ fk_trigger->alwaysdeferred = fkconstraint->alwaysdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd");
break;
case FKCONSTR_ACTION_RESTRICT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_upd");
break;
case FKCONSTR_ACTION_CASCADE:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd");
break;
case FKCONSTR_ACTION_SETNULL:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd");
break;
case FKCONSTR_ACTION_SETDEFAULT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
+ fk_trigger->alwaysdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd");
break;
default:
@@ -11162,6 +11179,7 @@ constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc)
if (acon->condeferrable != bcon->condeferrable ||
acon->condeferred != bcon->condeferred ||
+ acon->conalwaysdeferred != bcon->conalwaysdeferred ||
strcmp(decompile_conbin(a, tupleDesc),
decompile_conbin(b, tupleDesc)) != 0)
return false;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index e75a59d..8589473 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -650,6 +650,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
CONSTRAINT_TRIGGER,
stmt->deferrable,
stmt->initdeferred,
+ stmt->alwaysdeferred,
true,
RelationGetRelid(rel),
NULL, /* no conkey */
@@ -748,6 +749,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
values[Anum_pg_trigger_tgdeferrable - 1] = BoolGetDatum(stmt->deferrable);
values[Anum_pg_trigger_tginitdeferred - 1] = BoolGetDatum(stmt->initdeferred);
+ values[Anum_pg_trigger_tgalwaysdeferred - 1] = BoolGetDatum(stmt->alwaysdeferred);
if (stmt->args)
{
@@ -1245,6 +1247,7 @@ ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid)
}
fkcon->deferrable = stmt->deferrable;
fkcon->initdeferred = stmt->initdeferred;
+ fkcon->alwaysdeferred = stmt->alwaysdeferred;
fkcon->skip_validation = false;
fkcon->initially_valid = true;
@@ -1742,6 +1745,7 @@ RelationBuildTriggers(Relation relation)
build->tgconstraint = pg_trigger->tgconstraint;
build->tgdeferrable = pg_trigger->tgdeferrable;
build->tginitdeferred = pg_trigger->tginitdeferred;
+ build->tgalwaysdeferred = pg_trigger->tgalwaysdeferred;
build->tgnargs = pg_trigger->tgnargs;
/* tgattr is first var-width field, so OK to access directly */
build->tgnattr = pg_trigger->tgattr.dim1;
@@ -2050,6 +2054,8 @@ equalTriggerDescs(TriggerDesc *trigdesc1, TriggerDesc *trigdesc2)
return false;
if (trig1->tginitdeferred != trig2->tginitdeferred)
return false;
+ if (trig1->tgalwaysdeferred != trig2->tgalwaysdeferred)
+ return false;
if (trig1->tgnargs != trig2->tgnargs)
return false;
if (trig1->tgnattr != trig2->tgnattr)
@@ -3392,6 +3398,7 @@ typedef struct AfterTriggerSharedData
TriggerEvent ats_event; /* event type indicator, see trigger.h */
Oid ats_tgoid; /* the trigger's ID */
Oid ats_relid; /* the relation it's on */
+ bool ats_alwaysdeferred; /* whether this can be deferred */
CommandId ats_firing_id; /* ID for firing cycle */
struct AfterTriggersTableData *ats_table; /* transition table access */
} AfterTriggerSharedData;
@@ -3678,6 +3685,8 @@ afterTriggerCheckState(AfterTriggerShared evtshared)
*/
if ((evtshared->ats_event & AFTER_TRIGGER_DEFERRABLE) == 0)
return false;
+ if ((evtshared->ats_event & AFTER_TRIGGER_ALWAYSDEFERRED) == 0)
+ return true;
/*
* If constraint state exists, SET CONSTRAINTS might have been executed
@@ -5191,14 +5200,19 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
{
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
- if (con->condeferrable)
- conoidlist = lappend_oid(conoidlist,
- HeapTupleGetOid(tup));
- else if (stmt->deferred)
+ if (stmt->deferred && !con->condeferrable)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" is not deferrable",
constraint->relname)));
+ else if (!stmt->deferred && con->conalwaysdeferred)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" is always deferred",
+ constraint->relname)));
+ else if (con->condeferrable && !con->conalwaysdeferred)
+ conoidlist = lappend_oid(conoidlist,
+ HeapTupleGetOid(tup));
found = true;
}
@@ -5695,7 +5709,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
(event & TRIGGER_EVENT_OPMASK) |
(row_trigger ? TRIGGER_EVENT_ROW : 0) |
(trigger->tgdeferrable ? AFTER_TRIGGER_DEFERRABLE : 0) |
- (trigger->tginitdeferred ? AFTER_TRIGGER_INITDEFERRED : 0);
+ (trigger->tginitdeferred ? AFTER_TRIGGER_INITDEFERRED : 0) |
+ (trigger->tgalwaysdeferred ? AFTER_TRIGGER_ALWAYSDEFERRED : 0);
new_shared.ats_tgoid = trigger->tgoid;
new_shared.ats_relid = RelationGetRelid(rel);
new_shared.ats_firing_id = 0;
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 8e5134b..199bedf 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1014,6 +1014,7 @@ DefineDomain(CreateDomainStmt *stmt)
case CONSTR_ATTR_DEFERRABLE:
case CONSTR_ATTR_NOT_DEFERRABLE:
case CONSTR_ATTR_DEFERRED:
+ case CONSTR_ATTR_ALWAYS_DEFERRED:
case CONSTR_ATTR_IMMEDIATE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -2554,6 +2555,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
case CONSTR_ATTR_DEFERRABLE:
case CONSTR_ATTR_NOT_DEFERRABLE:
case CONSTR_ATTR_DEFERRED:
+ case CONSTR_ATTR_ALWAYS_DEFERRED:
case CONSTR_ATTR_IMMEDIATE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -3100,6 +3102,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
CONSTRAINT_CHECK, /* Constraint Type */
false, /* Is Deferrable */
false, /* Is Deferred */
+ false, /* Is Always Deferred */
!constr->skip_validation, /* Is Validated */
InvalidOid, /* not a relation constraint */
NULL,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d0de99..654f38c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -133,6 +133,7 @@ typedef struct ImportQual
#define CAS_INITIALLY_DEFERRED 0x08
#define CAS_NOT_VALID 0x10
#define CAS_NO_INHERIT 0x20
+#define CAS_ALWAYS_DEFERRED 0x40
#define parser_yyerror(msg) scanner_yyerror(msg, yyscanner)
@@ -185,7 +186,8 @@ static void SplitColQualList(List *qualList,
core_yyscan_t yyscanner);
static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
- bool *no_inherit, core_yyscan_t yyscanner);
+ bool *no_inherit, bool *alwaysdeferred,
+ core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%}
@@ -725,6 +727,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS
* so that they can follow a_expr without creating postfix-operator problems;
* for GENERATED so that it can follow b_expr;
+ * for ALWAYS for column constraints ALWAYS DEFERRED;
* and for NULL so that it can follow b_expr in ColQualList without creating
* postfix-operator problems.
*
@@ -743,7 +746,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* blame any funny behavior of UNBOUNDED on the SQL standard, though.
*/
%nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */
-%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP
+%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP ALWAYS
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -2216,7 +2219,9 @@ alter_table_cmd:
processCASbits($4, @4, "ALTER CONSTRAINT statement",
&c->deferrable,
&c->initdeferred,
- NULL, NULL, yyscanner);
+ NULL, NULL,
+ &c->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
@@ -3469,6 +3474,13 @@ ConstraintAttr:
n->location = @1;
$$ = (Node *)n;
}
+ | ALWAYS DEFERRED
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_ATTR_ALWAYS_DEFERRED;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
;
@@ -3524,7 +3536,8 @@ ConstraintElem:
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
NULL, NULL, &n->skip_validation,
- &n->is_no_inherit, yyscanner);
+ &n->is_no_inherit,
+ NULL, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
@@ -3540,7 +3553,8 @@ ConstraintElem:
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3554,7 +3568,8 @@ ConstraintElem:
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3569,7 +3584,8 @@ ConstraintElem:
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3583,7 +3599,8 @@ ConstraintElem:
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3601,7 +3618,8 @@ ConstraintElem:
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3619,6 +3637,7 @@ ConstraintElem:
processCASbits($11, @11, "FOREIGN KEY",
&n->deferrable, &n->initdeferred,
&n->skip_validation, NULL,
+ &n->alwaysdeferred,
yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -5179,6 +5198,7 @@ CreateTrigStmt:
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
+ n->alwaysdeferred = FALSE;
n->constrrel = NULL;
$$ = (Node *)n;
}
@@ -5201,7 +5221,8 @@ CreateTrigStmt:
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5357,14 +5378,22 @@ ConstraintAttributeSpec:
int newspec = $1 | $2;
/* special message for this case */
- if ((newspec & (CAS_NOT_DEFERRABLE | CAS_INITIALLY_DEFERRED)) == (CAS_NOT_DEFERRABLE | CAS_INITIALLY_DEFERRED))
+ if ((newspec & CAS_NOT_DEFERRABLE) &&
+ (newspec & (CAS_INITIALLY_DEFERRED | CAS_ALWAYS_DEFERRED)))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"),
+ errmsg("constraint declared INITIALLY or ALWAYS DEFERRED must be DEFERRABLE"),
parser_errposition(@2)));
/* generic message for other conflicts */
+ if ((newspec & CAS_ALWAYS_DEFERRED) &&
+ (newspec & (CAS_INITIALLY_IMMEDIATE)))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting constraint properties"),
+ parser_errposition(@2)));
if ((newspec & (CAS_NOT_DEFERRABLE | CAS_DEFERRABLE)) == (CAS_NOT_DEFERRABLE | CAS_DEFERRABLE) ||
- (newspec & (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED)) == (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED))
+ (newspec & (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED)) == (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED) ||
+ (newspec & CAS_ALWAYS_DEFERRED))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("conflicting constraint properties"),
@@ -5378,6 +5407,7 @@ ConstraintAttributeElem:
| DEFERRABLE { $$ = CAS_DEFERRABLE; }
| INITIALLY IMMEDIATE { $$ = CAS_INITIALLY_IMMEDIATE; }
| INITIALLY DEFERRED { $$ = CAS_INITIALLY_DEFERRED; }
+ | ALWAYS DEFERRED { $$ = CAS_ALWAYS_DEFERRED; }
| NOT VALID { $$ = CAS_NOT_VALID; }
| NO INHERIT { $$ = CAS_NO_INHERIT; }
;
@@ -5469,7 +5499,8 @@ CreateAssertStmt:
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
&n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ NULL, &n->alwaysdeferred,
+ yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -7146,6 +7177,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->isconstraint = false;
n->deferrable = false;
n->initdeferred = false;
+ n->alwaysdeferred = false;
n->transformed = false;
n->if_not_exists = false;
$$ = (Node *)n;
@@ -7172,6 +7204,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->isconstraint = false;
n->deferrable = false;
n->initdeferred = false;
+ n->alwaysdeferred = false;
n->transformed = false;
n->if_not_exists = true;
$$ = (Node *)n;
@@ -15780,17 +15813,20 @@ SplitColQualList(List *qualList,
static void
processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
- bool *no_inherit, core_yyscan_t yyscanner)
+ bool *no_inherit, bool *alwaysdeferred,
+ core_yyscan_t yyscanner)
{
/* defaults */
if (deferrable)
*deferrable = false;
if (initdeferred)
*initdeferred = false;
+ if (alwaysdeferred)
+ *alwaysdeferred = false;
if (not_valid)
*not_valid = false;
- if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
+ if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED | CAS_ALWAYS_DEFERRED))
{
if (deferrable)
*deferrable = true;
@@ -15816,6 +15852,19 @@ processCASbits(int cas_bits, int location, const char *constrType,
parser_errposition(location)));
}
+ if (cas_bits & CAS_ALWAYS_DEFERRED)
+ {
+ if (alwaysdeferred)
+ *alwaysdeferred = true;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /* translator: %s is CHECK, UNIQUE, or similar */
+ errmsg("%s constraints cannot be marked ALWAYS DEFERRED",
+ constrType),
+ parser_errposition(location)));
+ }
+
if (cas_bits & CAS_NOT_VALID)
{
if (not_valid)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ed9ed83..689b89c 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1359,6 +1359,7 @@ generateClonedIndexStmt(CreateStmtContext *cxt, Relation source_idx,
index->isconstraint = true;
index->deferrable = conrec->condeferrable;
index->initdeferred = conrec->condeferred;
+ index->alwaysdeferred = conrec->conalwaysdeferred;
/* If it's an exclusion constraint, we need the operator names */
if (idxrec->indisexclusion)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index eb01f35..d1f636d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2144,6 +2144,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
appendStringInfoString(&buf, " DEFERRABLE");
if (conForm->condeferred)
appendStringInfoString(&buf, " INITIALLY DEFERRED");
+ if (conForm->conalwaysdeferred)
+ appendStringInfoString(&buf, " ALWAYS DEFERRED");
if (!conForm->convalidated)
appendStringInfoString(&buf, " NOT VALID");
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b93dd8b..60fcc77 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6404,6 +6404,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_conname,
i_condeferrable,
i_condeferred,
+ i_conalwaysdeferred,
i_contableoid,
i_conoid,
i_condef,
@@ -6466,7 +6467,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "c.conalwaysdeferred "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6497,7 +6499,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "c.conalwaysdeferred "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6524,7 +6527,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "c.conalwaysdeferred "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6554,7 +6558,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "null AS indreloptions "
+ "null AS indreloptions, "
+ "c.conalwaysdeferred "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6586,6 +6591,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_conname = PQfnumber(res, "conname");
i_condeferrable = PQfnumber(res, "condeferrable");
i_condeferred = PQfnumber(res, "condeferred");
+ i_conalwaysdeferred = PQfnumber(res, "conalwaysdeferred");
i_contableoid = PQfnumber(res, "contableoid");
i_conoid = PQfnumber(res, "conoid");
i_condef = PQfnumber(res, "condef");
@@ -6641,6 +6647,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
constrinfo[j].condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't';
constrinfo[j].condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't';
+ constrinfo[j].conalwaysdeferred = *(PQgetvalue(res, j, i_conalwaysdeferred)) == 't';
constrinfo[j].conislocal = true;
constrinfo[j].separate = true;
@@ -6838,6 +6845,7 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
constrinfo[j].conindex = 0;
constrinfo[j].condeferrable = false;
constrinfo[j].condeferred = false;
+ constrinfo[j].conalwaysdeferred = false;
constrinfo[j].conislocal = true;
constrinfo[j].separate = true;
}
@@ -6924,6 +6932,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
constrinfo[i].conindex = 0;
constrinfo[i].condeferrable = false;
constrinfo[i].condeferred = false;
+ constrinfo[i].conalwaysdeferred = false;
constrinfo[i].conislocal = true;
constrinfo[i].separate = !validated;
@@ -8178,6 +8187,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
constrs[j].conindex = 0;
constrs[j].condeferrable = false;
constrs[j].condeferred = false;
+ constrs[j].conalwaysdeferred = false;
constrs[j].conislocal = (PQgetvalue(res, j, 4)[0] == 't');
/*
@@ -16089,6 +16099,11 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
appendPQExpBufferStr(q, " INITIALLY DEFERRED");
}
+ if (coninfo->conalwaysdeferred)
+ {
+ appendPQExpBufferStr(q, " ALWAYS DEFERRED");
+ }
+
appendPQExpBufferStr(q, ";\n");
}
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e7593e6..93b098a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -399,6 +399,7 @@ typedef struct _triggerInfo
char tgenabled;
bool tgdeferrable;
bool tginitdeferred;
+ bool tgalwaysdeferred;
char *tgdef;
} TriggerInfo;
@@ -432,6 +433,7 @@ typedef struct _constraintInfo
DumpId conindex; /* identifies associated index if any */
bool condeferrable; /* TRUE if constraint is DEFERRABLE */
bool condeferred; /* TRUE if constraint is INITIALLY DEFERRED */
+ bool conalwaysdeferred; /* TRUE if constraint is ALWAYS DEFERRED */
bool conislocal; /* TRUE if constraint has local definition */
bool separate; /* TRUE if must dump as separate item */
} ConstraintInfo;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 019aa84..07f21d0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2037,10 +2037,17 @@ describeOneTableDetails(const char *schemaname,
"WHERE conrelid = i.indrelid AND "
"conindid = i.indexrelid AND "
"contype IN ('p','u','x') AND "
- "condeferred) AS condeferred,\n");
+ "condeferred) AS condeferred,\n"
+ " (NOT i.indimmediate) AND "
+ "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
+ "WHERE conrelid = i.indrelid AND "
+ "conindid = i.indexrelid AND "
+ "contype IN ('p','u','x') AND "
+ "conalwaysdeferred) AS conalwaysdeferred,\n"
+ );
else
appendPQExpBufferStr(&buf,
- " false AS condeferrable, false AS condeferred,\n");
+ " false AS condeferrable, false AS condeferred, false AS conalwaysdeferred\n");
if (pset.sversion >= 90400)
appendPQExpBuffer(&buf, "i.indisreplident,\n");
@@ -2134,11 +2141,11 @@ describeOneTableDetails(const char *schemaname,
if (pset.sversion >= 90000)
appendPQExpBufferStr(&buf,
"pg_catalog.pg_get_constraintdef(con.oid, true), "
- "contype, condeferrable, condeferred");
+ "contype, condeferrable, condeferred, conalwaysdeferred");
else
appendPQExpBufferStr(&buf,
"null AS constraintdef, null AS contype, "
- "false AS condeferrable, false AS condeferred");
+ "false AS condeferrable, false AS condeferred, false as conalwaysdeferred");
if (pset.sversion >= 90400)
appendPQExpBufferStr(&buf, ", i.indisreplident");
else
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 1d4ec09..0e6dea4 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -58,6 +58,7 @@ extern Oid index_create(Relation heapRelation,
bool isconstraint,
bool deferrable,
bool initdeferred,
+ bool alwaysdeferred,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
@@ -71,6 +72,7 @@ extern ObjectAddress index_constraint_create(Relation heapRelation,
char constraintType,
bool deferrable,
bool initdeferred,
+ bool alwaysdeferred,
bool mark_as_primary,
bool update_pgindex,
bool remove_old_dependencies,
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index ec035d8..ef2e519 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -44,6 +44,7 @@ CATALOG(pg_constraint,2606)
char contype; /* constraint type; see codes below */
bool condeferrable; /* deferrable constraint? */
bool condeferred; /* deferred by default? */
+ bool conalwaysdeferred; /* always deferred? */
bool convalidated; /* constraint has been validated? */
/*
@@ -150,7 +151,7 @@ typedef FormData_pg_constraint *Form_pg_constraint;
* compiler constants for pg_constraint
* ----------------
*/
-#define Natts_pg_constraint 24
+#define Natts_pg_constraint 25
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
@@ -175,6 +176,7 @@ typedef FormData_pg_constraint *Form_pg_constraint;
#define Anum_pg_constraint_conexclop 22
#define Anum_pg_constraint_conbin 23
#define Anum_pg_constraint_consrc 24
+#define Anum_pg_constraint_conalwaysdeferred 25
/* ----------------
* initial contents of pg_constraint
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.h
index a4c4689..c9daf2c 100644
--- a/src/include/catalog/pg_constraint_fn.h
+++ b/src/include/catalog/pg_constraint_fn.h
@@ -32,6 +32,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
char constraintType,
bool isDeferrable,
bool isDeferred,
+ bool isAlwaysDeferred,
bool isValidated,
Oid relId,
const int16 *constraintKey,
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h
index f413caf..29b2c13 100644
--- a/src/include/catalog/pg_trigger.h
+++ b/src/include/catalog/pg_trigger.h
@@ -48,6 +48,7 @@ CATALOG(pg_trigger,2620)
Oid tgconstraint; /* associated pg_constraint entry, if any */
bool tgdeferrable; /* constraint trigger is deferrable */
bool tginitdeferred; /* constraint trigger is deferred initially */
+ bool tgalwaysdeferred; /* constraint trigger is always deferred */
int16 tgnargs; /* # of extra arguments in tgargs */
/*
@@ -75,7 +76,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
* compiler constants for pg_trigger
* ----------------
*/
-#define Natts_pg_trigger 17
+#define Natts_pg_trigger 18
#define Anum_pg_trigger_tgrelid 1
#define Anum_pg_trigger_tgname 2
#define Anum_pg_trigger_tgfoid 3
@@ -93,6 +94,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
#define Anum_pg_trigger_tgqual 15
#define Anum_pg_trigger_tgoldtable 16
#define Anum_pg_trigger_tgnewtable 17
+#define Anum_pg_trigger_tgalwaysdeferred 18
/* Bits within tgtype */
#define TRIGGER_TYPE_ROW (1 << 0)
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index adbcfa1..082affa 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -112,6 +112,7 @@ typedef struct TransitionCaptureState
#define AFTER_TRIGGER_DEFERRABLE 0x00000020
#define AFTER_TRIGGER_INITDEFERRED 0x00000040
+#define AFTER_TRIGGER_ALWAYSDEFERRED 0x00000080
#define TRIGGER_FIRED_BY_INSERT(event) \
(((event) & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_INSERT)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ef6753e..856321c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2057,7 +2057,8 @@ typedef enum ConstrType /* types of constraints */
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
CONSTR_ATTR_DEFERRED,
- CONSTR_ATTR_IMMEDIATE
+ CONSTR_ATTR_IMMEDIATE,
+ CONSTR_ATTR_ALWAYS_DEFERRED
} ConstrType;
/* Foreign key action codes */
@@ -2081,6 +2082,7 @@ typedef struct Constraint
char *conname; /* Constraint name, or NULL if unnamed */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
+ bool alwaysdeferred; /* ALWAYS DEFERRED? */
int location; /* token location, or -1 if unknown */
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
@@ -2365,6 +2367,7 @@ typedef struct CreateTrigStmt
/* The remaining fields are only used for constraint triggers */
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
+ bool alwaysdeferred; /* ALWAYS DEFERRED? */
RangeVar *constrrel; /* opposite relation, if RI trigger */
} CreateTrigStmt;
@@ -2709,6 +2712,7 @@ typedef struct IndexStmt
bool isconstraint; /* is it for a pkey/unique constraint? */
bool deferrable; /* is the constraint DEFERRABLE? */
bool initdeferred; /* is the constraint INITIALLY DEFERRED? */
+ bool alwaysdeferred; /* ALWAYS DEFERRED? */
bool transformed; /* true when transformIndexStmt is finished */
bool concurrent; /* should this be a concurrent index build? */
bool if_not_exists; /* just do nothing if index already exists? */
diff --git a/src/include/utils/reltrigger.h b/src/include/utils/reltrigger.h
index 2169b03..06e1f21 100644
--- a/src/include/utils/reltrigger.h
+++ b/src/include/utils/reltrigger.h
@@ -34,6 +34,7 @@ typedef struct Trigger
Oid tgconstraint;
bool tgdeferrable;
bool tginitdeferred;
+ bool tgalwaysdeferred;
int16 tgnargs;
int16 tgnattr;
int16 *tgattr;
--
2.7.4
>From 4085e8526155b2a3b02bc3618dab42f895d4751c Mon Sep 17 00:00:00 2001
From: Nicolas Williams <[email protected]>
Date: Tue, 3 Oct 2017 13:54:47 -0500
Subject: [PATCH 2/4] WIP: Insert conalwaysdeferred in the middle?
(pg_constraint)
---
src/include/catalog/pg_constraint.h | 40 ++++++++++++++++++-------------------
1 file changed, 20 insertions(+), 20 deletions(-)
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index ef2e519..f423901 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -157,26 +157,26 @@ typedef FormData_pg_constraint *Form_pg_constraint;
#define Anum_pg_constraint_contype 3
#define Anum_pg_constraint_condeferrable 4
#define Anum_pg_constraint_condeferred 5
-#define Anum_pg_constraint_convalidated 6
-#define Anum_pg_constraint_conrelid 7
-#define Anum_pg_constraint_contypid 8
-#define Anum_pg_constraint_conindid 9
-#define Anum_pg_constraint_confrelid 10
-#define Anum_pg_constraint_confupdtype 11
-#define Anum_pg_constraint_confdeltype 12
-#define Anum_pg_constraint_confmatchtype 13
-#define Anum_pg_constraint_conislocal 14
-#define Anum_pg_constraint_coninhcount 15
-#define Anum_pg_constraint_connoinherit 16
-#define Anum_pg_constraint_conkey 17
-#define Anum_pg_constraint_confkey 18
-#define Anum_pg_constraint_conpfeqop 19
-#define Anum_pg_constraint_conppeqop 20
-#define Anum_pg_constraint_conffeqop 21
-#define Anum_pg_constraint_conexclop 22
-#define Anum_pg_constraint_conbin 23
-#define Anum_pg_constraint_consrc 24
-#define Anum_pg_constraint_conalwaysdeferred 25
+#define Anum_pg_constraint_conalwaysdeferred 6
+#define Anum_pg_constraint_convalidated 7
+#define Anum_pg_constraint_conrelid 8
+#define Anum_pg_constraint_contypid 9
+#define Anum_pg_constraint_conindid 10
+#define Anum_pg_constraint_confrelid 11
+#define Anum_pg_constraint_confupdtype 12
+#define Anum_pg_constraint_confdeltype 13
+#define Anum_pg_constraint_confmatchtype 14
+#define Anum_pg_constraint_conislocal 15
+#define Anum_pg_constraint_coninhcount 16
+#define Anum_pg_constraint_connoinherit 17
+#define Anum_pg_constraint_conkey 18
+#define Anum_pg_constraint_confkey 19
+#define Anum_pg_constraint_conpfeqop 20
+#define Anum_pg_constraint_conppeqop 21
+#define Anum_pg_constraint_conffeqop 22
+#define Anum_pg_constraint_conexclop 23
+#define Anum_pg_constraint_conbin 24
+#define Anum_pg_constraint_consrc 25
/* ----------------
* initial contents of pg_constraint
--
2.7.4
>From 25f1cc7ed25e7f3d06db60d1c940e88f8ee2606b Mon Sep 17 00:00:00 2001
From: Nicolas Williams <[email protected]>
Date: Tue, 3 Oct 2017 13:58:55 -0500
Subject: [PATCH 3/4] WIP: Insert conalwaysdeferred in the middle? (dump)
---
src/bin/pg_dump/pg_dump.c | 20 ++++++++------------
1 file changed, 8 insertions(+), 12 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 60fcc77..b4c5c35 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6462,13 +6462,12 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"i.indkey, i.indisclustered, "
"i.indisreplident, t.relpages, "
"c.contype, c.conname, "
- "c.condeferrable, c.condeferred, "
+ "c.condeferrable, c.condeferred, c.conalwaysdeferred, "
"c.tableoid AS contableoid, "
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions, "
- "c.conalwaysdeferred "
+ "t.reloptions AS indreloptions "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6494,13 +6493,12 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"i.indkey, i.indisclustered, "
"false AS indisreplident, t.relpages, "
"c.contype, c.conname, "
- "c.condeferrable, c.condeferred, "
+ "c.condeferrable, c.condeferred, c.conalwaysdeferred, "
"c.tableoid AS contableoid, "
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions, "
- "c.conalwaysdeferred "
+ "t.reloptions AS indreloptions "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6522,13 +6520,12 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"i.indkey, i.indisclustered, "
"false AS indisreplident, t.relpages, "
"c.contype, c.conname, "
- "c.condeferrable, c.condeferred, "
+ "c.condeferrable, c.condeferred, c.conalwaysdeferred "
"c.tableoid AS contableoid, "
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions, "
- "c.conalwaysdeferred "
+ "t.reloptions AS indreloptions "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6553,13 +6550,12 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"i.indkey, i.indisclustered, "
"false AS indisreplident, t.relpages, "
"c.contype, c.conname, "
- "c.condeferrable, c.condeferred, "
+ "c.condeferrable, c.condeferred, c.conalwaysdeferred "
"c.tableoid AS contableoid, "
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "null AS indreloptions, "
- "c.conalwaysdeferred "
+ "null AS indreloptions "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
--
2.7.4
>From 12cf8c372d80e38fd11c4c30b141bd51282053b7 Mon Sep 17 00:00:00 2001
From: Nicolas Williams <[email protected]>
Date: Tue, 3 Oct 2017 12:25:05 -0500
Subject: [PATCH 4/4] Add always_deferred to information_schema??
---
src/backend/catalog/information_schema.sql | 11 ++++++++---
1 file changed, 8 insertions(+), 3 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 4fa230c..5b05843 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -894,11 +894,13 @@ CREATE VIEW domain_constraints AS
CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
AS yes_or_no) AS is_deferrable,
CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
- AS yes_or_no) AS initially_deferred
+ AS yes_or_no) AS initially_deferred,
/*
* XXX Can we add is_always_deferred here? Are there
* standards considerations?
*/
+ CAST(CASE WHEN conalwaysdeferred THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS always_deferred
FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
WHERE rs.oid = con.connamespace
AND n.oid = t.typnamespace
@@ -1782,11 +1784,13 @@ CREATE VIEW table_constraints AS
CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
- AS initially_deferred
+ AS initially_deferred,
/*
* XXX Can we add is_always_deferred here? Are there
* standards considerations?
*/
+ CAST(CASE WHEN c.conalwaysdeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
+ AS always_deferred
FROM pg_namespace nc,
pg_namespace nr,
@@ -1815,7 +1819,8 @@ CREATE VIEW table_constraints AS
CAST(r.relname AS sql_identifier) AS table_name,
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
- CAST('NO' AS yes_or_no) AS initially_deferred
+ CAST('NO' AS yes_or_no) AS initially_deferred,
+ CAST('NO' AS yes_or_no) AS always_deferred
FROM pg_namespace nr,
pg_class r,
--
2.7.4
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
