Hi all.
Sorry about the long delay.
On Tue, Jul 10, 2018 at 10:17 AM Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:
> On Wed, Mar 7, 2018 at 11:49 PM, Matheus de Oliveira
> <matioli.math...@gmail.com> wrote:
> >
> >
> > Em 3 de mar de 2018 19:32, "Peter Eisentraut"
> > <peter.eisentr...@2ndquadrant.com> escreveu:
> >
> > On 2/20/18 10:10, Matheus de Oliveira wrote:
> >> Besides that, there is a another change in this patch on current ALTER
> >> CONSTRAINT about deferrability options. Previously, if the user did
> >> ALTER CONSTRAINT without specifying an option on deferrable or
> >> initdeferred, it was implied the default options, so this:
> >>
> >> ALTER TABLE tbl
> >> ALTER CONSTRAINT con_name;
> >>
> >> Was equivalent to:
> >>
> >> ALTER TABLE tbl
> >> ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;
> >
> > Oh, that seems wrong. Probably, it shouldn't even accept that syntax
> > with an empty options list, let alone reset options that are not
> > mentioned.
> >
> >
> > Yeah, it felt really weird when I noticed it. And I just noticed while
> > reading the source.
> >
> > Can
> >
> > you prepare a separate patch for this issue?
> >
> >
> > I can do that, no problem. It'll take awhile though, I'm on a trip and
> will
> > be home around March 20th.
>
> Matheus,
> When do you think you can provide the patch for bug fix?
>
>
Attached the patch for the bug fix, all files with naming
`postgresql-fix-alter-constraint-${version}.patch`, with `${version}` since
9.4, where ALTER CONSTRAINT was introduced.
Not sure if you want to apply to master/12 as well (since the other patch
applies that as well), but I've attached it anyway, so you can decide.
> Also, the patch you originally posted doesn't apply cleanly. Can you
> please post a rebased version?
>
>
Attached the rebased version that applies to current master,
`postgresql-alter-constraint.v3.patch`.
> The patch contains 70 odd lines of test SQL and 3600 odd lines of
> output. The total patch is 4200 odd lines. I don't think that it will
> be acceptable to add that huge an output to the regression test. You
> will need to provide a patch with much smaller output addition and may
> be a smaller test as well.
>
>
You are correct. I have made a test that tries all combinations of ALTER
CONSTRAINT ON UPDATE/DELETE ACTION, but it caused a really huge output. I
have changed that to a simple DO block, and still trying all possibilities
but now I just verify if the ALTER matches the same definition on
pg_trigger of a constraint that was created with the target action already,
seems simpler and work for any change.
Please, let me know if you all think any change should be made on this
patch.
Best regards,
--
Matheus de Oliveira
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 424a426..958f7d3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6681,6 +6681,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index db492a7..2e0d68b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2592,6 +2592,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 5c03e9f..78fa6fa 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2843,6 +2843,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e27d37c..a6bd48b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -173,7 +173,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2084,8 +2085,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3200,7 +3201,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3216,8 +3217,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3230,8 +3231,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3245,8 +3246,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3259,8 +3260,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3277,8 +3278,8 @@ ConstraintElem:
n->indexspace = $7;
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3294,7 +3295,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -4741,8 +4743,9 @@ CreateTrigStmt:
n->whenClause = $14;
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -4992,8 +4995,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -14759,7 +14763,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -14770,6 +14775,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4a19842..2fbce55 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1822,6 +1822,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 6f459df..51b0521 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -654,6 +654,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 358081b..c6844ec 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -473,6 +473,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 567c09a..0f7b964 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6358,6 +6358,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 823ca3e..794d84d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2396,6 +2396,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9c16a1f..1e7d9b6 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2659,6 +2659,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 030285c..6b6c10c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -161,7 +161,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2053,8 +2054,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3123,7 +3124,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3139,8 +3140,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3153,8 +3154,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3168,8 +3169,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3182,8 +3183,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3200,8 +3201,8 @@ ConstraintElem:
n->indexspace = $7;
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3217,7 +3218,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -4464,8 +4466,9 @@ CreateTrigStmt:
n->whenClause = $14;
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -4717,8 +4720,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -13872,7 +13876,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -13883,6 +13888,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3146aa5..f8c1b00 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1626,6 +1626,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index b9f9cf5..7f8f9aa 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -652,6 +652,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 7e29b48..d8d5af1 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -472,6 +472,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1f7c732..2a308c6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6711,6 +6711,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c2b1ccf..2a530b1 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2627,6 +2627,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index f8d43db..8c39746 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3157,6 +3157,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a0ef488..0e46adc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -174,7 +174,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2097,8 +2098,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3218,7 +3219,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3234,8 +3235,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3248,8 +3249,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3263,8 +3264,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3277,8 +3278,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3295,8 +3296,8 @@ ConstraintElem:
n->indexspace = $7;
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3312,7 +3313,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -4791,8 +4793,9 @@ CreateTrigStmt:
n->whenClause = $14;
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5042,8 +5045,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -14907,7 +14911,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -14918,6 +14923,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1481fff..0e232fe 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1830,6 +1830,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index abce3c5..a6e8a5e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -663,6 +663,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 168d95e..4731037 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -476,6 +476,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eb2d33d..87c9342 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7818,6 +7818,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3961832..d14eaa9 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2899,6 +2899,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 1d78b53..2cc8c3c 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3508,6 +3508,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 90dfac2..71669b9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -184,7 +184,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2279,8 +2280,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3675,7 +3676,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3692,8 +3693,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3707,8 +3708,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
@@ -3723,8 +3724,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $8;
processCASbits($9, @9, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3738,8 +3739,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3757,8 +3758,8 @@ ConstraintElem:
n->indexspace = $8;
n->where_clause = $9;
processCASbits($10, @10, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3774,7 +3775,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -5381,8 +5383,9 @@ CreateTrigStmt:
n->transitionRels = NIL;
n->isconstraint = true;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5649,8 +5652,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = true;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -16197,7 +16201,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -16208,6 +16213,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index fb9a5c4..ac43800 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2097,6 +2097,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 37440f6..a91a1df 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -720,6 +720,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 22cf4ef..7ab9aec 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -526,6 +526,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f4745f3..5bf3494 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7496,6 +7496,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8c8384c..66c817f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2832,6 +2832,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index d8cf660..4127c43 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3428,6 +3428,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 025e82b..11411f0 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -184,7 +184,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2214,8 +2215,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3526,7 +3527,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3542,8 +3543,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $6;
processCASbits($7, @7, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3556,8 +3557,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace
@@ -3571,8 +3572,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3585,8 +3586,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3603,8 +3604,8 @@ ConstraintElem:
n->indexspace = $7;
n->where_clause = $8;
processCASbits($9, @9, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3620,7 +3621,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -5203,8 +5205,9 @@ CreateTrigStmt:
n->transitionRels = NIL;
n->isconstraint = TRUE;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5471,8 +5474,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = TRUE;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -15799,7 +15803,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -15810,6 +15815,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index aa0b9b0..fd515e9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2086,6 +2086,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 86127ce..53ceb93 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -663,6 +663,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 519d984..13fea5f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -476,6 +476,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 03c0b73..204aac9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7841,6 +7841,26 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Check which deferrable attributes changed. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
currcon->condeferred != cmdcon->initdeferred)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7c8220c..14166c5 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2900,6 +2900,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 69fd5b2..a28258a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3529,6 +3529,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bd2223..9b4a05f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -184,7 +184,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -2279,8 +2280,8 @@ alter_table_cmd:
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3675,7 +3676,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3692,8 +3693,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3707,8 +3708,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
@@ -3723,8 +3724,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $8;
processCASbits($9, @9, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3738,8 +3739,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3757,8 +3758,8 @@ ConstraintElem:
n->indexspace = $8;
n->where_clause = $9;
processCASbits($10, @10, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3774,7 +3775,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -5381,8 +5383,9 @@ CreateTrigStmt:
n->transitionRels = NIL;
n->isconstraint = true;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5654,8 +5657,9 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = true;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -16208,7 +16212,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -16219,6 +16224,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07ab1a3..155d0e1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2097,6 +2097,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index dccc9b2..62a6ff8 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -720,6 +720,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index b904978..16e3717 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -526,6 +526,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index ec6b4c3..4c65c1f 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -55,7 +55,9 @@ 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>
+ [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
+ [ DEFERRABLE | NOT DEFERRABLE ] [ 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 ]
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 03c0b73..1db2936 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7841,8 +7841,43 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Verify for FKCONSTR_ACTION_UNKNOWN, if found, replace by current
+ * action. We could handle FKCONSTR_ACTION_UNKNOWN bellow, but since
+ * we already have to handle the case of changing to the same action,
+ * seems simpler to simple replace FKCONSTR_ACTION_UNKNOWN by the
+ * current action here.
+ */
+ if (cmdcon->fk_del_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_del_action = currcon->confdeltype;
+
+ if (cmdcon->fk_upd_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_upd_action = currcon->confupdtype;
+
+ /*
+ * Do the same for deferrable attributes. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
- currcon->condeferred != cmdcon->initdeferred)
+ currcon->condeferred != cmdcon->initdeferred ||
+ currcon->confdeltype != cmdcon->fk_del_action ||
+ currcon->confupdtype != cmdcon->fk_upd_action)
{
HeapTuple copyTuple;
HeapTuple tgtuple;
@@ -7860,6 +7895,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
copy_con->condeferrable = cmdcon->deferrable;
copy_con->condeferred = cmdcon->initdeferred;
+ copy_con->confdeltype = cmdcon->fk_del_action;
+ copy_con->confupdtype = cmdcon->fk_upd_action;
CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(ConstraintRelationId,
@@ -7896,23 +7933,106 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
otherrelids = list_append_unique_oid(otherrelids,
tgform->tgrelid);
- /*
- * Update deferrability of RI_FKey_noaction_del,
- * RI_FKey_noaction_upd, RI_FKey_check_ins and RI_FKey_check_upd
- * triggers, but not others; see createForeignKeyTriggers and
- * CreateFKCheckTrigger.
- */
- if (tgform->tgfoid != F_RI_FKEY_NOACTION_DEL &&
- tgform->tgfoid != F_RI_FKEY_NOACTION_UPD &&
- tgform->tgfoid != F_RI_FKEY_CHECK_INS &&
- tgform->tgfoid != F_RI_FKEY_CHECK_UPD)
- continue;
-
copyTuple = heap_copytuple(tgtuple);
copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
+ /*
+ * Set deferrability here, but note that it may be overridden bellow
+ * if the pg_trigger entry is on the referencing table and depending
+ * on the action used for ON UPDATE/DELETE. But for check triggers
+ * (in the referenced table) it is kept as is (since ON
+ * UPDATE/DELETE actions makes no difference for the check
+ * triggers).
+ */
copy_tg->tgdeferrable = cmdcon->deferrable;
copy_tg->tginitdeferred = cmdcon->initdeferred;
+
+ /*
+ * Set ON DELETE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_DEL ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_DEL ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_DEL)
+ {
+ switch (cmdcon->fk_del_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_DEL;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_DEL;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_DEL;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_DEL;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_DEL;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_del_action);
+ break;
+ }
+ }
+
+ /*
+ * Set ON UPDATE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_UPD ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_UPD ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_UPD)
+ {
+ switch (cmdcon->fk_upd_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_UPD;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_UPD;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_UPD;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_UPD;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_UPD;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_upd_action);
+ break;
+ }
+ }
+
CatalogTupleUpdate(tgrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(TriggerRelationId,
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7c8220c..14166c5 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2900,6 +2900,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 69fd5b2..a28258a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3529,6 +3529,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bd2223..c718d89 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -184,7 +184,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 *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -537,7 +538,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> TableLikeOptionList TableLikeOption
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
-%type <ival> key_actions key_delete key_match key_update key_action
+%type <ival> key_actions opt_key_actions
+%type <ival> key_delete key_match key_update key_action
%type <ival> ConstraintAttributeSpec ConstraintAttributeElem
%type <str> ExistingIndex
@@ -2270,7 +2272,7 @@ alter_table_cmd:
$$ = (Node *)n;
}
/* ALTER TABLE <name> ALTER CONSTRAINT ... */
- | ALTER CONSTRAINT name ConstraintAttributeSpec
+ | ALTER CONSTRAINT name opt_key_actions ConstraintAttributeSpec
{
AlterTableCmd *n = makeNode(AlterTableCmd);
Constraint *c = makeNode(Constraint);
@@ -2278,9 +2280,11 @@ alter_table_cmd:
n->def = (Node *) c;
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
- processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ c->fk_upd_action = (char) ($4 >> 8);
+ c->fk_del_action = (char) ($4 & 0xFF);
+ processCASbits($5, @4, "ALTER CONSTRAINT statement",
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3675,7 +3679,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3692,8 +3696,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3707,8 +3711,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
@@ -3723,8 +3727,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $8;
processCASbits($9, @9, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3738,8 +3742,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3757,8 +3761,8 @@ ConstraintElem:
n->indexspace = $8;
n->where_clause = $9;
processCASbits($10, @10, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3774,7 +3778,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -3854,7 +3859,7 @@ ExclusionWhereClause:
* We combine the update and delete actions into one value temporarily
* for simplicity of parsing, and then break them down again in the
* calling production. update is in the left 8 bits, delete in the right.
- * Note that NOACTION is the default.
+ * Note that NOACTION is the default. See also opt_key_actions.
*/
key_actions:
key_update
@@ -3869,6 +3874,23 @@ key_actions:
{ $$ = (FKCONSTR_ACTION_NOACTION << 8) | (FKCONSTR_ACTION_NOACTION & 0xFF); }
;
+/*
+ * Basically the same as key_actions, but using FKCONSTR_ACTION_UNKNOWN
+ * as the default one instead of NOACTION.
+ */
+opt_key_actions:
+ key_update
+ { $$ = ($1 << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ | key_delete
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | ($1 & 0xFF); }
+ | key_update key_delete
+ { $$ = ($1 << 8) | ($2 & 0xFF); }
+ | key_delete key_update
+ { $$ = ($2 << 8) | ($1 & 0xFF); }
+ | /*EMPTY*/
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ ;
+
key_update: ON UPDATE key_action { $$ = $3; }
;
@@ -5381,8 +5403,8 @@ CreateTrigStmt:
n->transitionRels = NIL;
n->isconstraint = true;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -5654,8 +5676,8 @@ CreateAssertStmt:
n->args = list_make1($6);
n->isconstraint = true;
processCASbits($8, @8, "ASSERTION",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -16208,7 +16230,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -16219,6 +16242,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07ab1a3..ddf42c9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2080,6 +2080,7 @@ typedef enum ConstrType /* types of constraints */
#define FKCONSTR_ACTION_CASCADE 'c'
#define FKCONSTR_ACTION_SETNULL 'n'
#define FKCONSTR_ACTION_SETDEFAULT 'd'
+#define FKCONSTR_ACTION_UNKNOWN 'u' /* unknown is used only for ALTER CONSTRAINT */
/* Foreign key matchtype codes */
#define FKCONSTR_MATCH_FULL 'f'
@@ -2097,6 +2098,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index fc3bbe4..076b633 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1651,3 +1651,127 @@ INSERT INTO fk_notpartitioned_pk VALUES (1600, 601), (1600, 1601);
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
FOR VALUES IN (1600);
-- leave these tables around intentionally
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest; -- created with source and altered to target, test
+DO
+$test_alter_con$
+DECLARE
+ v_result json;
+ method text;
+ from_action text;
+ to_action text;
+BEGIN
+ FOR method, from_action, to_action IN
+ WITH act(action) AS (
+ SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+ )
+ SELECT
+ m.method, a1.action, a2.action
+ FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+ LOOP
+ EXECUTE format(
+ $sql$
+ -- Alter from ON %1$s %2$s to ON %1$s %3$s
+ CREATE TABLE createtest.foo(id integer primary key);
+ CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+ CREATE TABLE altertest.foo(id integer primary key);
+ INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+ CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+ ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s;
+
+ $sql$, method, from_action, to_action);
+
+ SELECT json_agg(t)
+ INTO v_result
+ FROM (
+ -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+ EXCEPT
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+ ) t;
+
+ DROP TABLE createtest.bar;
+ DROP TABLE createtest.foo;
+ DROP TABLE altertest.bar;
+ DROP TABLE altertest.foo;
+
+ IF (v_result IS NULL) THEN
+ RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+ ELSE
+ RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+ END IF;
+ END LOOP;
+END;
+$test_alter_con$
+;
+INFO: ON UPDATE from NO ACTION to NO ACTION: OK.
+INFO: ON UPDATE from RESTRICT to NO ACTION: OK.
+INFO: ON UPDATE from CASCADE to NO ACTION: OK.
+INFO: ON UPDATE from SET DEFAULT to NO ACTION: OK.
+INFO: ON UPDATE from SET NULL to NO ACTION: OK.
+INFO: ON DELETE from NO ACTION to NO ACTION: OK.
+INFO: ON DELETE from RESTRICT to NO ACTION: OK.
+INFO: ON DELETE from CASCADE to NO ACTION: OK.
+INFO: ON DELETE from SET DEFAULT to NO ACTION: OK.
+INFO: ON DELETE from SET NULL to NO ACTION: OK.
+INFO: ON UPDATE from NO ACTION to RESTRICT: OK.
+INFO: ON UPDATE from RESTRICT to RESTRICT: OK.
+INFO: ON UPDATE from CASCADE to RESTRICT: OK.
+INFO: ON UPDATE from SET DEFAULT to RESTRICT: OK.
+INFO: ON UPDATE from SET NULL to RESTRICT: OK.
+INFO: ON DELETE from NO ACTION to RESTRICT: OK.
+INFO: ON DELETE from RESTRICT to RESTRICT: OK.
+INFO: ON DELETE from CASCADE to RESTRICT: OK.
+INFO: ON DELETE from SET DEFAULT to RESTRICT: OK.
+INFO: ON DELETE from SET NULL to RESTRICT: OK.
+INFO: ON UPDATE from NO ACTION to CASCADE: OK.
+INFO: ON UPDATE from RESTRICT to CASCADE: OK.
+INFO: ON UPDATE from CASCADE to CASCADE: OK.
+INFO: ON UPDATE from SET DEFAULT to CASCADE: OK.
+INFO: ON UPDATE from SET NULL to CASCADE: OK.
+INFO: ON DELETE from NO ACTION to CASCADE: OK.
+INFO: ON DELETE from RESTRICT to CASCADE: OK.
+INFO: ON DELETE from CASCADE to CASCADE: OK.
+INFO: ON DELETE from SET DEFAULT to CASCADE: OK.
+INFO: ON DELETE from SET NULL to CASCADE: OK.
+INFO: ON UPDATE from NO ACTION to SET DEFAULT: OK.
+INFO: ON UPDATE from RESTRICT to SET DEFAULT: OK.
+INFO: ON UPDATE from CASCADE to SET DEFAULT: OK.
+INFO: ON UPDATE from SET DEFAULT to SET DEFAULT: OK.
+INFO: ON UPDATE from SET NULL to SET DEFAULT: OK.
+INFO: ON DELETE from NO ACTION to SET DEFAULT: OK.
+INFO: ON DELETE from RESTRICT to SET DEFAULT: OK.
+INFO: ON DELETE from CASCADE to SET DEFAULT: OK.
+INFO: ON DELETE from SET DEFAULT to SET DEFAULT: OK.
+INFO: ON DELETE from SET NULL to SET DEFAULT: OK.
+INFO: ON UPDATE from NO ACTION to SET NULL: OK.
+INFO: ON UPDATE from RESTRICT to SET NULL: OK.
+INFO: ON UPDATE from CASCADE to SET NULL: OK.
+INFO: ON UPDATE from SET DEFAULT to SET NULL: OK.
+INFO: ON UPDATE from SET NULL to SET NULL: OK.
+INFO: ON DELETE from NO ACTION to SET NULL: OK.
+INFO: ON DELETE from RESTRICT to SET NULL: OK.
+INFO: ON DELETE from CASCADE to SET NULL: OK.
+INFO: ON DELETE from SET DEFAULT to SET NULL: OK.
+INFO: ON DELETE from SET NULL to SET NULL: OK.
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index d2cecdf..2331b2f 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1230,3 +1230,80 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
FOR VALUES IN (1600);
-- leave these tables around intentionally
+
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest; -- created with source and altered to target, test
+
+DO
+$test_alter_con$
+DECLARE
+ v_result json;
+ method text;
+ from_action text;
+ to_action text;
+BEGIN
+ FOR method, from_action, to_action IN
+ WITH act(action) AS (
+ SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+ )
+ SELECT
+ m.method, a1.action, a2.action
+ FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+ LOOP
+ EXECUTE format(
+ $sql$
+ -- Alter from ON %1$s %2$s to ON %1$s %3$s
+ CREATE TABLE createtest.foo(id integer primary key);
+ CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+ CREATE TABLE altertest.foo(id integer primary key);
+ INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+ CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+ ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s;
+
+ $sql$, method, from_action, to_action);
+
+ SELECT json_agg(t)
+ INTO v_result
+ FROM (
+ -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+ EXCEPT
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+ ) t;
+
+ DROP TABLE createtest.bar;
+ DROP TABLE createtest.foo;
+ DROP TABLE altertest.bar;
+ DROP TABLE altertest.foo;
+
+ IF (v_result IS NULL) THEN
+ RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+ ELSE
+ RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+ END IF;
+ END LOOP;
+END;
+$test_alter_con$
+;
+
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
