On 2025-Jul-02, Fujii Masao wrote:
> Regarding the 0003 patch:
>
> + if (($11 & CAS_NOT_ENFORCED) != 0)
> + ereport(ERROR,
> +
> errmsg("constraint triggers cannot be marked %s",
> + "NOT
> ENFORCED"),
> +
> parser_errposition(@11));
>
> Shouldn't we also raise an error when CAS_ENFORCED is given?
Great point -- ENFORCED can also throw the bogus error. However, if you
say ENFORCED, the constraint is going to be enforced, which is the same
that happens if you don't say anything, so I think we should accept the
case. So how about this?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"This is a foot just waiting to be shot" (Andrew Dunstan)
>From e4ebadb0131880c2494a31764fc0d5efe4ff9796 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvhe...@kurilemu.de>
Date: Wed, 2 Jul 2025 17:10:57 +0200
Subject: [PATCH v6] Fix bogus grammar for a CREATE CONSTRAINT TRIGGER error
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
If certain constraint characteristic clauses (NO INHERIT, NOT VALID, NOT
ENFORCED) are given to CREATE CONSTRAINT TRIGGER, the resulting error
message is
ERROR: TRIGGER constraints cannot be marked NO INHERIT
which is a bit silly, because these aren't "constraints of type
TRIGGER". Hardcode a better error message to prevent it. This is a
cosmetic fix for quite a fringe problem with no known complaints from
users, so no backpatch.
While at it, silently accept ENFORCED if given.
Author: Amul Sul <sula...@gmail.com>
Reviewed-by: jian he <jian.universal...@gmail.com>
Reviewed-by: Fujii Masao <masao.fu...@oss.nttdata.com>
Reviewed-by: Álvaro Herrera <alvhe...@kurilemu.de>
Discussion: https://postgr.es/m/caaj_b97hd-jmts7ajgu6tdbczdx_kyukxg+k-dtymoieg+g...@mail.gmail.com
Discussion: https://postgr.es/m/CACJufxHSp2puxP=q8ztugl1f+heapnzqfbzy5zngujugwjb...@mail.gmail.com
---
src/backend/parser/gram.y | 22 +++++++++++++++++++++-
src/test/regress/expected/triggers.out | 23 ++++++++++++++++++++++-
src/test/regress/sql/triggers.sql | 15 ++++++++++++++-
3 files changed, 57 insertions(+), 3 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a2e084b8f64..f404c77e504 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6041,6 +6041,26 @@ CreateTrigStmt:
EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')'
{
CreateTrigStmt *n = makeNode(CreateTrigStmt);
+ bool dummy;
+
+ if (($11 & CAS_NOT_VALID) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("constraint triggers cannot be marked %s",
+ "NOT VALID"),
+ parser_errposition(@11));
+ if (($11 & CAS_NO_INHERIT) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("constraint triggers cannot be marked %s",
+ "NO INHERIT"),
+ parser_errposition(@11));
+ if (($11 & CAS_NOT_ENFORCED) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("constraint triggers cannot be marked %s",
+ "NOT ENFORCED"),
+ parser_errposition(@11));
n->replace = $2;
if (n->replace) /* not supported, see CreateTrigger */
@@ -6060,7 +6080,7 @@ CreateTrigStmt:
n->whenClause = $15;
n->transitionRels = NIL;
processCASbits($11, @11, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
+ &n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
$$ = (Node *) n;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 2bf0e77d61e..872b9100e1a 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2280,6 +2280,27 @@ select * from parted;
drop table parted;
drop function parted_trigfunc();
--
+-- Constraint triggers
+--
+create constraint trigger crtr
+ after insert on foo not valid
+ for each row execute procedure foo ();
+ERROR: constraint triggers cannot be marked NOT VALID
+LINE 2: after insert on foo not valid
+ ^
+create constraint trigger crtr
+ after insert on foo no inherit
+ for each row execute procedure foo ();
+ERROR: constraint triggers cannot be marked NO INHERIT
+LINE 2: after insert on foo no inherit
+ ^
+create constraint trigger crtr
+ after insert on foo not enforced
+ for each row execute procedure foo ();
+ERROR: constraint triggers cannot be marked NOT ENFORCED
+LINE 2: after insert on foo not enforced
+ ^
+--
-- Constraint triggers and partitioned tables
create table parted_constr_ancestor (a int, b text)
partition by range (b);
@@ -2294,7 +2315,7 @@ create constraint trigger parted_trig after insert on parted_constr_ancestor
deferrable
for each row execute procedure trigger_notice_ab();
create constraint trigger parted_trig_two after insert on parted_constr
- deferrable initially deferred
+ deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
-- The immediate constraint is fired immediately; the WHEN clause of the
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 9ffd318385f..d674b25c83b 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1576,6 +1576,19 @@ select * from parted;
drop table parted;
drop function parted_trigfunc();
+--
+-- Constraint triggers
+--
+create constraint trigger crtr
+ after insert on foo not valid
+ for each row execute procedure foo ();
+create constraint trigger crtr
+ after insert on foo no inherit
+ for each row execute procedure foo ();
+create constraint trigger crtr
+ after insert on foo not enforced
+ for each row execute procedure foo ();
+
--
-- Constraint triggers and partitioned tables
create table parted_constr_ancestor (a int, b text)
@@ -1591,7 +1604,7 @@ create constraint trigger parted_trig after insert on parted_constr_ancestor
deferrable
for each row execute procedure trigger_notice_ab();
create constraint trigger parted_trig_two after insert on parted_constr
- deferrable initially deferred
+ deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
--
2.39.5
