čt 20. 3. 2025 v 22:30 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:
> Hi
>
> út 18. 3. 2025 v 21:33 odesílatel Álvaro Herrera <alvhe...@alvh.no-ip.org>
> napsal:
>
>> On 2025-Mar-18, Pavel Stehule wrote:
>>
>> > Maybe I found a bug
>> >
>> > (2025-03-18 19:28:06) postgres=# create table foo(a int constraint gzero
>> > check(a > 10) NOT ENFORCED);
>> > CREATE TABLE
>> > (2025-03-18 19:29:37) postgres=# insert into foo values(0);
>> > INSERT 0 1
>> > (2025-03-18 19:29:49) postgres=# insert into foo values(6);
>> > INSERT 0 1
>> > (2025-03-18 19:29:55) postgres=# alter table foo alter constraint gzero
>> > enforced;
>> > ERROR: FOREIGN KEY constraints cannot be marked ENFORCED
>> > LINE 1: alter table foo alter constraint gzero enforced;
>> >
>> > I know so altering enforcing constraint is not supported yet, but the
>> error
>> > message is surely wrong
>>
>> Yep, this is a bug all right -- I reported this and related problems a
>> few days ago [1]. There's a proposal in that thread for how to fix this
>> (see Amul's email [2] and my followup), but I haven't had time to fully
>> implement it. If you want to give it a couple of hours to complete it,
>> that'd be great. I have a couple of patches that I need to handle
>> before coming back to that.
>>
>> [1] https://postgr.es/m/202503041822.aobpqke3igvb@alvherre.pgsql
>> [2]
>> https://postgr.es/m/caaj_b97hd-jmts7ajgu6tdbczdx_kyukxg+k-dtymoieg+g...@mail.gmail.com
>
>
> I am looking this issue, and I am not sure if proposed way is the best
>
> cannot we change processCASbits just like ?
>
>
> if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
> {
> if (deferrable)
> *deferrable = true;
> else if (constrType)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> /* translator: %s is CHECK, UNIQUE, or similar */
> constrType ?
> errmsg("%s constraints cannot be marked DEFERRABLE",
> constrType) :
> errmsg("constraint cannot be marked DEFERRABLE"),
> parser_errposition(location)));
> }
> ...
>
> Probably can be better to not try to read from catalog in this moment, and
> then we can accept so we don't know constraint type
>
something like attached patch
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>
>
>
>
>
>
>>
>> --
>> Álvaro Herrera PostgreSQL Developer —
>> https://www.EnterpriseDB.com/
>>
>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 271ae26cbaf..7b0e66a8940 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2663,7 +2663,7 @@ alter_table_cmd:
n->def = (Node *) c;
c->conname = $3;
c->alterDeferrability = true;
- processCASbits($4, @4, "FOREIGN KEY",
+ processCASbits($4, @4, NULL,
&c->deferrable,
&c->initdeferred,
NULL, NULL, NULL, yyscanner);
@@ -19531,9 +19531,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked DEFERRABLE",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked DEFERRABLE"),
parser_errposition(location)));
}
@@ -19544,9 +19546,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked DEFERRABLE",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked DEFERRABLE"),
parser_errposition(location)));
}
@@ -19557,9 +19561,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked NOT VALID",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked NOT VALID"),
parser_errposition(location)));
}
@@ -19570,9 +19576,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked NO INHERIT",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked NO INHERIT"),
parser_errposition(location)));
}
@@ -19583,9 +19591,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked NOT ENFORCED",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked NOT ENFORCED"),
parser_errposition(location)));
/*
@@ -19605,9 +19615,11 @@ processCASbits(int cas_bits, int location, const char *constrType,
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is CHECK, UNIQUE, or similar */
+ constrType ?
+ /* translator: %s is CHECK, UNIQUE, TRIGGER, or similar */
errmsg("%s constraints cannot be marked ENFORCED",
- constrType),
+ constrType) :
+ errmsg("constraint cannot be marked ENFORCED"),
parser_errposition(location)));
}
}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..226a1b4c9e6 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -745,11 +745,11 @@ ERROR: misplaced NOT ENFORCED clause
LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
^
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked ENFORCED
+ERROR: constraint cannot be marked ENFORCED
LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
^
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked NOT ENFORCED
+ERROR: constraint cannot be marked NOT ENFORCED
LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC...
^
DROP TABLE unique_tbl;
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 6a3374d5152..776f1059b2d 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1284,11 +1284,11 @@ ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
^
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT;
-ERROR: FOREIGN KEY constraints cannot be marked NO INHERIT
+ERROR: constraint cannot be marked NO INHERIT
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT...
^
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
-ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
+ERROR: constraint cannot be marked NOT VALID
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
^
-- test order of firing of FK triggers when several RI-induced changes need to
