[make check-world passes. Tests and docs included. Should be ready for code review.]
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. Motivation: - Security. One may have triggers they need to always be deferred and they cannot give direct PG access because of SET CONSTRAINTS .. IMMEDIATE. I have such triggers 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. I have written SQL code to detect that constraint triggers have fired too soon, but I'd rather not need it as it does slow things down (it uses DDL event triggers and per-table triggers). Making it easier to write secure code DEFERRED CONSTRAINT TRIGGERs seems like a good idea to me. - Symmetry. Not using NOT DEFERRABLE is not the inverse of NOT DEFERRABLE. There is no inverse at this time. If we can have NOT DEFERRABLE constraints, why not also the inverse, a constraint that cannot be made IMMEDIATE with SET CONSTRAINTs? I've *not* cleaned up C style issues in surrounding -- I'm not sure if that's desired. Not cleaning up makes it easier to see what I changed. Some questions for experienced PostgreSQL developers: Q0: Is this sort of patch welcomed? Q1: Should new columns for pg_catalog tables go at the end, or may they be added in the middle? FYI, I'm adding them in the middle, so they are next to related columns. Q2: Can I add new columns to information_schema tables, or are there standards-compliance issues with that? This is done in the second patch, and it can be dropped safely. Q3: Perhaps I should make this NOT IMMEDIATE rather than ALWAYS DEFERRED? 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. Thoughts? Nico --
>From 1d04483511f99cd3417df571ecc0498e928ace35 Mon Sep 17 00:00:00 2001 From: Nicolas Williams <n...@cryptonector.com> Date: Tue, 3 Oct 2017 00:33:09 -0500 Subject: [PATCH 1/2] 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). --- doc/src/sgml/catalogs.sgml | 17 ++++- doc/src/sgml/ref/alter_table.sgml | 4 +- doc/src/sgml/ref/create_table.sgml | 10 ++- doc/src/sgml/ref/create_trigger.sgml | 2 +- doc/src/sgml/trigger.sgml | 1 + src/backend/bootstrap/bootparse.y | 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 | 28 +++++++-- src/backend/commands/typecmds.c | 3 + src/backend/nodes/copyfuncs.c | 3 + src/backend/nodes/outfuncs.c | 4 ++ src/backend/parser/gram.y | 99 ++++++++++++++++++++++-------- src/backend/parser/parse_utilcmd.c | 46 +++++++++++++- src/backend/utils/adt/ruleutils.c | 4 ++ src/bin/pg_dump/pg_dump.c | 31 ++++++++-- src/bin/pg_dump/pg_dump.h | 2 + src/bin/psql/describe.c | 34 +++++++--- src/include/catalog/index.h | 2 + src/include/catalog/pg_constraint.h | 42 +++++++------ src/include/catalog/pg_constraint_fn.h | 1 + src/include/catalog/pg_trigger.h | 16 ++--- src/include/commands/trigger.h | 1 + src/include/nodes/parsenodes.h | 6 +- src/include/utils/reltrigger.h | 1 + src/test/regress/input/constraints.source | 51 +++++++++++++++ src/test/regress/output/constraints.source | 54 +++++++++++++++- 32 files changed, 416 insertions(+), 91 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9af77c1..2c3ed23 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> @@ -6948,6 +6955,13 @@ SCRAM-SHA-256$<replaceable><iteration count></>:<replaceable><salt>< </row> <row> + <entry><structfield>tgalwaysdeferred</structfield></entry> + <entry><type>bool</type></entry> + <entry></entry> + <entry>True if constraint trigger is always deferred</entry> + </row> + + <row> <entry><structfield>tgnargs</structfield></entry> <entry><type>int2</type></entry> <entry></entry> @@ -7009,7 +7023,8 @@ SCRAM-SHA-256$<replaceable><iteration count></>:<replaceable><salt>< <para> When <structfield>tgconstraint</> is nonzero, <structfield>tgconstrrelid</>, <structfield>tgconstrindid</>, - <structfield>tgdeferrable</>, and <structfield>tginitdeferred</> are + <structfield>tgdeferrable</>, <structfield>tginitdeferred</>, and + <structfield>tgalwaysdeferred</> are largely redundant with the referenced <structname>pg_constraint</> entry. However, it is possible for a non-deferrable trigger to be associated with a deferrable constraint: foreign key constraints can have some diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 0fb385e..e81d1fa 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 1477288..38c88b8 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> @@ -961,13 +961,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/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index f5f74af..342964e 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -646,6 +646,7 @@ typedef struct Trigger Oid tgconstraint; bool tgdeferrable; bool tginitdeferred; + bool tgalwaysdeferred; int16 tgnargs; int16 tgnattr; int16 *tgattr; diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 2e1fef0..c08ac60 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -310,6 +310,7 @@ Boot_DeclareIndexStmt: stmt->isconstraint = false; stmt->deferrable = false; stmt->initdeferred = false; + stmt->alwaysdeferred = false; stmt->transformed = false; stmt->concurrent = false; stmt->if_not_exists = false; @@ -354,6 +355,7 @@ Boot_DeclareUniqueIndexStmt: stmt->isconstraint = false; stmt->deferrable = false; stmt->initdeferred = false; + stmt->alwaysdeferred = false; stmt->transformed = false; stmt->concurrent = false; stmt->if_not_exists = false; diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 05e7081..a54c524 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2113,6 +2113,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 c7b2f03..65e995d 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -684,6 +684,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()) @@ -713,6 +714,7 @@ index_create(Relation heapRelation, bool isconstraint, bool deferrable, bool initdeferred, + bool alwaysdeferred, bool allow_system_table_mods, bool skip_build, bool concurrent, @@ -966,6 +968,7 @@ index_create(Relation heapRelation, constraintType, deferrable, initdeferred, + alwaysdeferred, false, /* already marked primary */ false, /* pg_index entry is OK */ false, /* no old dependencies */ @@ -1009,6 +1012,7 @@ index_create(Relation heapRelation, /* Non-constraint indexes can't be deferrable */ Assert(!deferrable); Assert(!initdeferred); + Assert(!alwaysdeferred); } /* Store dependency on collations */ @@ -1062,6 +1066,7 @@ index_create(Relation heapRelation, Assert(!isconstraint); Assert(!deferrable); Assert(!initdeferred); + Assert(!alwaysdeferred); } /* Post creation hook for new index */ @@ -1154,6 +1159,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, @@ -1202,6 +1208,7 @@ index_constraint_create(Relation heapRelation, constraintType, deferrable, initdeferred, + alwaysdeferred, true, RelationGetRelid(heapRelation), indexInfo->ii_KeyAttrNumbers, @@ -1266,6 +1273,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 236f6be..4f0d193 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 6f517bb..f90ea32 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 b61aaac..791c1f8 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 563bcda..8177684 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6874,6 +6874,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel, constraintType, stmt->deferrable, stmt->initdeferred, + stmt->alwaysdeferred, stmt->primary, true, /* update pg_index */ true, /* remove old dependencies */ @@ -7443,6 +7444,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, CONSTRAINT_FOREIGN, fkconstraint->deferrable, fkconstraint->initdeferred, + fkconstraint->alwaysdeferred, fkconstraint->initially_valid, RelationGetRelid(rel), fkattnum, @@ -7567,7 +7569,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; @@ -7585,6 +7588,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, @@ -7638,6 +7642,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, @@ -8305,6 +8310,7 @@ validateForeignKeyConstraint(char *conname, trig.tgconstraint = constraintOid; trig.tgdeferrable = FALSE; trig.tginitdeferred = FALSE; + trig.tgalwaysdeferred = FALSE; /* we needn't fill in remaining fields */ /* @@ -8394,6 +8400,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; @@ -8442,26 +8449,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: @@ -8497,26 +8509,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: @@ -11261,6 +11278,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..ee41280 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) @@ -2143,7 +2149,8 @@ ExecCallTriggerFunc(TriggerData *trigdata, TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) && TRIGGER_FIRED_AFTER(trigdata->tg_event) && !(trigdata->tg_event & AFTER_TRIGGER_DEFERRABLE) && - !(trigdata->tg_event & AFTER_TRIGGER_INITDEFERRED)) || + !(trigdata->tg_event & AFTER_TRIGGER_INITDEFERRED) && + !(trigdata->tg_event & AFTER_TRIGGER_ALEWAYSDEFERRED)) || (trigdata->tg_oldtable == NULL && trigdata->tg_newtable == NULL)); finfo += tgindx; @@ -3392,6 +3399,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 +3686,8 @@ afterTriggerCheckState(AfterTriggerShared evtshared) */ if ((evtshared->ats_event & AFTER_TRIGGER_DEFERRABLE) == 0) return false; + if ((evtshared->ats_event & AFTER_TRIGGER_ALWAYSDEFERRED)) + return true; /* * If constraint state exists, SET CONSTRAINTS might have been executed @@ -5191,14 +5201,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 +5710,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 c1b87e0..85b73ba 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1016,6 +1016,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), @@ -2603,6 +2604,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), @@ -3150,6 +3152,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/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index c1a83ca..bbd1705 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2831,6 +2831,7 @@ _copyConstraint(const Constraint *from) COPY_STRING_FIELD(conname); COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); + COPY_SCALAR_FIELD(alwaysdeferred); COPY_LOCATION_FIELD(location); COPY_SCALAR_FIELD(is_no_inherit); COPY_NODE_FIELD(raw_expr); @@ -3379,6 +3380,7 @@ _copyIndexStmt(const IndexStmt *from) COPY_SCALAR_FIELD(isconstraint); COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); + COPY_SCALAR_FIELD(alwaysdeferred); COPY_SCALAR_FIELD(transformed); COPY_SCALAR_FIELD(concurrent); COPY_SCALAR_FIELD(if_not_exists); @@ -4148,6 +4150,7 @@ _copyCreateTrigStmt(const CreateTrigStmt *from) COPY_NODE_FIELD(transitionRels); COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); + COPY_SCALAR_FIELD(alwaysdeferred); COPY_NODE_FIELD(constrrel); return newnode; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 2532edc..3b2f8df 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -3508,6 +3508,10 @@ _outConstraint(StringInfo str, const Constraint *node) appendStringInfoString(str, "ATTR_NOT_DEFERRABLE"); break; + case CONSTR_ATTR_ALWAYS_DEFERRED: + appendStringInfoString(str, "ATTR_ALWAYS_DEFERRED"); + break; + case CONSTR_ATTR_DEFERRED: appendStringInfoString(str, "ATTR_DEFERRED"); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4c83a63..dd5fe68 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) @@ -184,8 +185,8 @@ static void SplitColQualList(List *qualList, List **constraintList, CollateClause **collClause, 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 *deferrable, bool *initdeferred, bool *alwaysdeferred, + bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner); static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %} @@ -727,6 +728,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. * @@ -745,7 +747,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 '*' '/' '%' @@ -2234,7 +2236,9 @@ alter_table_cmd: processCASbits($4, @4, "ALTER CONSTRAINT statement", &c->deferrable, &c->initdeferred, - NULL, NULL, yyscanner); + &c->alwaysdeferred, + NULL, NULL, + yyscanner); $$ = (Node *)n; } /* ALTER TABLE <name> VALIDATE CONSTRAINT ... */ @@ -3500,6 +3504,13 @@ ConstraintAttr: n->location = @1; $$ = (Node *)n; } + | ALWAYS DEFERRED + { + Constraint *n = makeNode(Constraint); + n->contype = CONSTR_ATTR_ALWAYS_DEFERRED; + n->location = @1; + $$ = (Node *)n; + } ; @@ -3554,8 +3565,10 @@ ConstraintElem: n->raw_expr = $3; n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", - NULL, NULL, &n->skip_validation, - &n->is_no_inherit, yyscanner); + NULL, NULL, NULL, + &n->skip_validation, + &n->is_no_inherit, + yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *)n; } @@ -3570,8 +3583,9 @@ ConstraintElem: n->indexname = NULL; n->indexspace = $6; processCASbits($7, @7, "UNIQUE", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); $$ = (Node *)n; } | UNIQUE ExistingIndex ConstraintAttributeSpec @@ -3584,8 +3598,9 @@ ConstraintElem: n->indexname = $2; n->indexspace = NULL; processCASbits($3, @3, "UNIQUE", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); $$ = (Node *)n; } | PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace @@ -3599,8 +3614,9 @@ ConstraintElem: n->indexname = NULL; n->indexspace = $7; processCASbits($8, @8, "PRIMARY KEY", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); $$ = (Node *)n; } | PRIMARY KEY ExistingIndex ConstraintAttributeSpec @@ -3613,8 +3629,9 @@ ConstraintElem: n->indexname = $3; n->indexspace = NULL; processCASbits($4, @4, "PRIMARY KEY", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); $$ = (Node *)n; } | EXCLUDE access_method_clause '(' ExclusionConstraintList ')' @@ -3631,8 +3648,9 @@ ConstraintElem: n->indexspace = $7; n->where_clause = $8; processCASbits($9, @9, "EXCLUDE", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name @@ -3649,6 +3667,7 @@ ConstraintElem: n->fk_del_action = (char) ($10 & 0xFF); processCASbits($11, @11, "FOREIGN KEY", &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, &n->skip_validation, NULL, yyscanner); n->initially_valid = !n->skip_validation; @@ -5210,6 +5229,7 @@ CreateTrigStmt: n->isconstraint = FALSE; n->deferrable = FALSE; n->initdeferred = FALSE; + n->alwaysdeferred = FALSE; n->constrrel = NULL; $$ = (Node *)n; } @@ -5231,8 +5251,9 @@ CreateTrigStmt: n->transitionRels = NIL; n->isconstraint = TRUE; processCASbits($10, @10, "TRIGGER", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); n->constrrel = $9; $$ = (Node *)n; } @@ -5388,17 +5409,24 @@ 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"), 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 1"), + 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)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("conflicting constraint properties"), + errmsg("conflicting constraint properties 2"), parser_errposition(@2))); $$ = newspec; } @@ -5409,6 +5437,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; } ; @@ -5499,8 +5528,9 @@ CreateAssertStmt: n->args = list_make1($6); n->isconstraint = TRUE; processCASbits($8, @8, "ASSERTION", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + &n->deferrable, &n->initdeferred, + &n->alwaysdeferred, NULL, NULL, + yyscanner); ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -7177,6 +7207,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; @@ -7203,6 +7234,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; @@ -15791,18 +15823,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 *deferrable, bool *initdeferred, bool *alwaysdeferred, + bool *not_valid, bool *no_inherit, 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; @@ -15815,7 +15849,7 @@ processCASbits(int cas_bits, int location, const char *constrType, parser_errposition(location))); } - if (cas_bits & CAS_INITIALLY_DEFERRED) + if (cas_bits & (CAS_INITIALLY_DEFERRED | CAS_ALWAYS_DEFERRED)) { if (initdeferred) *initdeferred = true; @@ -15828,6 +15862,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 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 27e568f..54d1c3f 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -750,6 +750,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) case CONSTR_ATTR_DEFERRABLE: case CONSTR_ATTR_NOT_DEFERRABLE: + case CONSTR_ATTR_ALWAYS_DEFERRED: case CONSTR_ATTR_DEFERRED: case CONSTR_ATTR_IMMEDIATE: /* transformConstraintAttrs took care of these */ @@ -878,6 +879,7 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) case CONSTR_DEFAULT: case CONSTR_ATTR_DEFERRABLE: case CONSTR_ATTR_NOT_DEFERRABLE: + case CONSTR_ATTR_ALWAYS_DEFERRED: case CONSTR_ATTR_DEFERRED: case CONSTR_ATTR_IMMEDIATE: elog(ERROR, "invalid context for constraint type %d", @@ -1359,6 +1361,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) @@ -1715,7 +1718,8 @@ transformIndexConstraints(CreateStmtContext *cxt) equal(index->excludeOpNames, priorindex->excludeOpNames) && strcmp(index->accessMethod, priorindex->accessMethod) == 0 && index->deferrable == priorindex->deferrable && - index->initdeferred == priorindex->initdeferred) + index->initdeferred == priorindex->initdeferred && + index->alwaysdeferred == priorindex->alwaysdeferred) { priorindex->unique |= index->unique; @@ -1770,6 +1774,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index->isconstraint = true; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; + index->alwaysdeferred = constraint->alwaysdeferred; if (constraint->conname != NULL) index->idxname = pstrdup(constraint->conname); @@ -2983,6 +2988,9 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) { Constraint *lastprimarycon = NULL; bool saw_deferrability = false; + bool saw_deferrable = false; + bool saw_notdeferrable = false; + bool saw_alwaysdeferred = false; bool saw_initially = false; ListCell *clist; @@ -3008,12 +3016,13 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced DEFERRABLE clause"), parser_errposition(cxt->pstate, con->location))); - if (saw_deferrability) + if (saw_deferrable || saw_notdeferrable) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"), parser_errposition(cxt->pstate, con->location))); saw_deferrability = true; + saw_deferrable = true; lastprimarycon->deferrable = true; break; @@ -3023,13 +3032,20 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT DEFERRABLE clause"), parser_errposition(cxt->pstate, con->location))); - if (saw_deferrability) + if (saw_deferrable || saw_notdeferrable) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"), parser_errposition(cxt->pstate, con->location))); + if (saw_alwaysdeferred) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple ALWAYS DEFERRED/NOT DEFERRABLE clauses not allowed"), + parser_errposition(cxt->pstate, con->location))); saw_deferrability = true; + saw_notdeferrable = true; lastprimarycon->deferrable = false; + lastprimarycon->alwaysdeferred = false; if (saw_initially && lastprimarycon->initdeferred) ereport(ERROR, @@ -3038,6 +3054,30 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) parser_errposition(cxt->pstate, con->location))); break; + case CONSTR_ATTR_ALWAYS_DEFERRED: + if (!SUPPORTS_ATTRS(lastprimarycon)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("misplaced ALWAYS DEFERRED clause"), + parser_errposition(cxt->pstate, con->location))); + if (saw_alwaysdeferred || saw_notdeferrable) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple ALWAYS DEFERRED/NOT DEFERRABLE clauses not allowed"), + parser_errposition(cxt->pstate, con->location))); + saw_initially = true; + saw_deferrability = true; + saw_alwaysdeferred = true; + lastprimarycon->deferrable = true; + lastprimarycon->alwaysdeferred = true; + if (saw_initially && + !lastprimarycon->initdeferred) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("constraint declared INITIALLY IMMEDIATE must not be ALWAYS DEFERRED"), + parser_errposition(cxt->pstate, con->location))); + break; + case CONSTR_ATTR_DEFERRED: if (!SUPPORTS_ATTRS(lastprimarycon)) ereport(ERROR, diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 84759b6..61fd899 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -936,6 +936,8 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) appendStringInfoString(&buf, "DEFERRED "); else appendStringInfoString(&buf, "IMMEDIATE "); + if (trigrec->tgalwaysdeferred) + appendStringInfoString(&buf, "ALWAYS DEFERRED"); } value = fastgetattr(ht_trig, Anum_pg_trigger_tgoldtable, @@ -2144,6 +2146,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 e34c83a..a095a0a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6509,6 +6509,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname, i_condeferrable, i_condeferred, + i_conalwaysdeferred, i_contableoid, i_conoid, i_condef, @@ -6566,7 +6567,7 @@ 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, " @@ -6597,7 +6598,7 @@ 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, " @@ -6624,7 +6625,7 @@ 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, " @@ -6654,7 +6655,7 @@ 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, " @@ -6691,6 +6692,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"); @@ -6746,6 +6748,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; @@ -6943,6 +6946,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; } @@ -7029,6 +7033,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; @@ -7193,6 +7198,7 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables) i_tgenabled, i_tgdeferrable, i_tginitdeferred, + i_tgalwaysdeferred, i_tgdef; int ntups; @@ -7291,6 +7297,7 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables) i_tgenabled = PQfnumber(res, "tgenabled"); i_tgdeferrable = PQfnumber(res, "tgdeferrable"); i_tginitdeferred = PQfnumber(res, "tginitdeferred"); + i_tgalwaysdeferred = PQfnumber(res, "tgalwaysdeferred"); i_tgdef = PQfnumber(res, "tgdef"); tginfo = (TriggerInfo *) pg_malloc(ntups * sizeof(TriggerInfo)); @@ -7320,6 +7327,7 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables) tginfo[j].tgisconstraint = false; tginfo[j].tgdeferrable = false; tginfo[j].tginitdeferred = false; + tginfo[j].tgalwaysdeferred = false; tginfo[j].tgconstrname = NULL; tginfo[j].tgconstrrelid = InvalidOid; tginfo[j].tgconstrrelname = NULL; @@ -7335,6 +7343,8 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables) tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't'; tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't'; tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't'; + if (i_tgalwaysdeferred != -1) + tginfo[j].tgalwaysdeferred = *(PQgetvalue(res, j, i_tgalwaysdeferred)) == 't'; if (tginfo[j].tgisconstraint) { @@ -8283,6 +8293,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'); /* @@ -16204,6 +16215,11 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) appendPQExpBufferStr(q, " INITIALLY DEFERRED"); } + if (coninfo->conalwaysdeferred) + { + appendPQExpBufferStr(q, " ALWAYS DEFERRED"); + } + appendPQExpBufferStr(q, ";\n"); } @@ -16866,9 +16882,12 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo) appendPQExpBufferStr(query, "NOT "); appendPQExpBufferStr(query, "DEFERRABLE INITIALLY "); if (tginfo->tginitdeferred) - appendPQExpBufferStr(query, "DEFERRED\n"); + appendPQExpBufferStr(query, "DEFERRED"); else - appendPQExpBufferStr(query, "IMMEDIATE\n"); + appendPQExpBufferStr(query, "IMMEDIATE"); + if (tginfo->tgalwaysdeferred) + appendPQExpBufferStr(query, " ALWAYS DEFERRED"); + appendPQExpBufferStr(query, "\n"); } if (TRIGGER_FOR_ROW(tginfo->tgtype)) 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 0688571..c387d19 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2043,10 +2043,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"); @@ -2076,10 +2083,11 @@ describeOneTableDetails(const char *schemaname, char *indisvalid = PQgetvalue(result, 0, 3); char *deferrable = PQgetvalue(result, 0, 4); char *deferred = PQgetvalue(result, 0, 5); - char *indisreplident = PQgetvalue(result, 0, 6); - char *indamname = PQgetvalue(result, 0, 7); - char *indtable = PQgetvalue(result, 0, 8); - char *indpred = PQgetvalue(result, 0, 9); + char *alwaysdeferred = PQgetvalue(result, 0, 6); + char *indisreplident = PQgetvalue(result, 0, 7); + char *indamname = PQgetvalue(result, 0, 8); + char *indtable = PQgetvalue(result, 0, 9); + char *indpred = PQgetvalue(result, 0, 10); if (strcmp(indisprimary, "t") == 0) printfPQExpBuffer(&tmpbuf, _("primary key, ")); @@ -2108,6 +2116,9 @@ describeOneTableDetails(const char *schemaname, if (strcmp(deferred, "t") == 0) appendPQExpBufferStr(&tmpbuf, _(", initially deferred")); + if (strcmp(alwaysdeferred, "t") == 0) + appendPQExpBufferStr(&tmpbuf, _(", always deferred")); + if (strcmp(indisreplident, "t") == 0) appendPQExpBuffer(&tmpbuf, _(", replica identity")); @@ -2140,11 +2151,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 @@ -2210,6 +2221,9 @@ describeOneTableDetails(const char *schemaname, if (strcmp(PQgetvalue(result, i, 9), "t") == 0) appendPQExpBufferStr(&buf, " INITIALLY DEFERRED"); + + if (strcmp(PQgetvalue(result, i, 10), "t") == 0) + appendPQExpBufferStr(&buf, " ALWAYS DEFERRED"); } /* Add these for all cases */ @@ -2219,7 +2233,7 @@ describeOneTableDetails(const char *schemaname, if (strcmp(PQgetvalue(result, i, 4), "t") != 0) appendPQExpBufferStr(&buf, " INVALID"); - if (strcmp(PQgetvalue(result, i, 10), "t") == 0) + if (strcmp(PQgetvalue(result, i, 11), "t") == 0) appendPQExpBuffer(&buf, " REPLICA IDENTITY"); printTableAddFooter(&cont, buf.data); @@ -2227,7 +2241,7 @@ describeOneTableDetails(const char *schemaname, /* Print tablespace of the index on the same line */ if (pset.sversion >= 80000) add_tablespace_footer(&cont, RELKIND_INDEX, - atooid(PQgetvalue(result, i, 11)), + atooid(PQgetvalue(result, i, 12)), false); } } 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..f423901 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,31 +151,32 @@ 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 #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 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 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..acb9d17 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 @@ -87,12 +88,13 @@ typedef FormData_pg_trigger *Form_pg_trigger; #define Anum_pg_trigger_tgconstraint 9 #define Anum_pg_trigger_tgdeferrable 10 #define Anum_pg_trigger_tginitdeferred 11 -#define Anum_pg_trigger_tgnargs 12 -#define Anum_pg_trigger_tgattr 13 -#define Anum_pg_trigger_tgargs 14 -#define Anum_pg_trigger_tgqual 15 -#define Anum_pg_trigger_tgoldtable 16 -#define Anum_pg_trigger_tgnewtable 17 +#define Anum_pg_trigger_tgalwaysdeferred 12 +#define Anum_pg_trigger_tgnargs 13 +#define Anum_pg_trigger_tgattr 14 +#define Anum_pg_trigger_tgargs 15 +#define Anum_pg_trigger_tgqual 16 +#define Anum_pg_trigger_tgoldtable 17 +#define Anum_pg_trigger_tgnewtable 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 50eec73..47486a3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2060,7 +2060,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 */ @@ -2084,6 +2085,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): */ @@ -2368,6 +2370,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; @@ -2712,6 +2715,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; diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source index dbab8f1..0885a39 100644 --- a/src/test/regress/input/constraints.source +++ b/src/test/regress/input/constraints.source @@ -421,8 +421,59 @@ COMMIT; SELECT * FROM unique_tbl; +-- test ALWAYS DEFERRED +ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; +ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key + UNIQUE (i) ALWAYS DEFERRED; + +BEGIN; +-- should fail at commit time +UPDATE unique_tbl SET i = 2 WHERE i = 1; +COMMIT; -- should fail + +BEGIN; +-- should fail at commit time +SET CONSTRAINTS ALL IMMEDIATE; +UPDATE unique_tbl SET i = 2 WHERE i = 1; +COMMIT; -- should fail + +BEGIN; +SET CONSTRAINTS unique_tbl_i_key IMMEDIATE; -- should fail +ROLLBACK; + DROP TABLE unique_tbl; +CREATE FUNCTION deferred_trigger_test() RETURNS TRIGGER AS $$ + BEGIN + RAISE EXCEPTION 'deferred_trigger_test() ran'; + END +$$ language plpgsql; + +CREATE TABLE deferred_trigger_test_tbl (a TEXT); + +CREATE CONSTRAINT TRIGGER deferred_trigger_test_constraint +AFTER INSERT OR UPDATE OR DELETE +ON deferred_trigger_test_tbl +ALWAYS DEFERRED +FOR EACH ROW +EXECUTE PROCEDURE deferred_trigger_test(); + +BEGIN; +INSERT INTO deferred_trigger_test_tbl (a) SELECT 'foo'; +COMMIT; -- should fail + +BEGIN; +SET CONSTRAINTS ALL IMMEDIATE; +INSERT INTO deferred_trigger_test_tbl (a) SELECT 'foo'; +COMMIT; -- should fail + +BEGIN; +SET CONSTRAINTS deferred_trigger_test_constraint IMMEDIATE; -- should fail +ROLLBACK; + +DROP TABLE deferred_trigger_test_tbl CASCADE; +DROP FUNCTION deferred_trigger_test(); + -- -- EXCLUDE constraints -- diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index bb75165..d55803b 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -344,7 +344,7 @@ SELECT * FROM INSERT_TBL; CREATE TABLE COPY_TBL (x INT, y TEXT, z INT, CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 )); -COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data'; +COPY COPY_TBL FROM '/home/nico/ws/postgres/src/test/regress/data/constro.data'; SELECT '' AS two, * FROM COPY_TBL; two | x | y | z -----+---+---------------+--- @@ -352,7 +352,7 @@ SELECT '' AS two, * FROM COPY_TBL; | 6 | OK | 4 (2 rows) -COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data'; +COPY COPY_TBL FROM '/home/nico/ws/postgres/src/test/regress/data/constrf.data'; ERROR: new row for relation "copy_tbl" violates check constraint "copy_con" DETAIL: Failing row contains (7, check failed, 6). CONTEXT: COPY copy_tbl, line 2: "7 check failed 6" @@ -592,7 +592,57 @@ SELECT * FROM unique_tbl; 3 | threex (5 rows) +-- test ALWAYS DEFERRED +ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; +ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key + UNIQUE (i) ALWAYS DEFERRED; +BEGIN; +-- should fail at commit time +UPDATE unique_tbl SET i = 2 WHERE i = 1; +COMMIT; -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +DETAIL: Key (i)=(2) already exists. +BEGIN; +-- should fail at commit time +SET CONSTRAINTS ALL IMMEDIATE; +UPDATE unique_tbl SET i = 2 WHERE i = 1; +COMMIT; -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +DETAIL: Key (i)=(2) already exists. +BEGIN; +SET CONSTRAINTS unique_tbl_i_key IMMEDIATE; -- should fail +ERROR: constraint "unique_tbl_i_key" is always deferred +ROLLBACK; DROP TABLE unique_tbl; +CREATE FUNCTION deferred_trigger_test() RETURNS TRIGGER AS $$ + BEGIN + RAISE EXCEPTION 'deferred_trigger_test() ran'; + END +$$ language plpgsql; +CREATE TABLE deferred_trigger_test_tbl (a TEXT); +CREATE CONSTRAINT TRIGGER deferred_trigger_test_constraint +AFTER INSERT OR UPDATE OR DELETE +ON deferred_trigger_test_tbl +ALWAYS DEFERRED +FOR EACH ROW +EXECUTE PROCEDURE deferred_trigger_test(); +BEGIN; +INSERT INTO deferred_trigger_test_tbl (a) SELECT 'foo'; +COMMIT; -- should fail +ERROR: deferred_trigger_test() ran +CONTEXT: PL/pgSQL function deferred_trigger_test() line 3 at RAISE +BEGIN; +SET CONSTRAINTS ALL IMMEDIATE; +INSERT INTO deferred_trigger_test_tbl (a) SELECT 'foo'; +COMMIT; -- should fail +ERROR: deferred_trigger_test() ran +CONTEXT: PL/pgSQL function deferred_trigger_test() line 3 at RAISE +BEGIN; +SET CONSTRAINTS deferred_trigger_test_constraint IMMEDIATE; -- should fail +ERROR: constraint "deferred_trigger_test_constraint" is always deferred +ROLLBACK; +DROP TABLE deferred_trigger_test_tbl CASCADE; +DROP FUNCTION deferred_trigger_test(); -- -- EXCLUDE constraints -- -- 2.7.4
>From 1c87e6d66ed5e30ee3dc51ecc1d76334586a155f Mon Sep 17 00:00:00 2001 From: Nicolas Williams <n...@cryptonector.com> Date: Tue, 3 Oct 2017 12:25:05 -0500 Subject: [PATCH 2/2] 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 4f0d193..db113df 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers