v4-0001-add-regress-tests-for-not-enforced-enforced-fo.no-cfbot
application/octet-stream
Filename: v4-0001-add-regress-tests-for-not-enforced-enforced-fo.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: NOT ENFORCED constraint feature
From 48475d1240f6b8e8e85d801443148eb1740daa70 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 18 Nov 2024 20:34:16 +0800
Subject: [PATCH v4 1/1] add regress tests for not enforced/enforced for check
constraint
---
src/test/regress/expected/constraints.out | 95 +++++++++++++++++++
.../regress/expected/create_table_like.out | 15 +++
src/test/regress/sql/constraints.sql | 30 ++++++
src/test/regress/sql/create_table_like.sql | 7 ++
4 files changed, 147 insertions(+)
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1fe2a76fa3..66261f789f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -744,6 +744,101 @@ CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
ERROR: misplaced NOT ENFORCED clause
LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
^
+-- none of the following should be accepted
+create table t(a int not null enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: create table t(a int not null enforced);
+ ^
+create table t(a int not null enforced enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: create table t(a int not null enforced enforced);
+ ^
+create table t(a int not null not enforced);
+ERROR: misplaced NOT ENFORCED clause
+LINE 1: create table t(a int not null not enforced);
+ ^
+create table t(a int unique enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: create table t(a int unique enforced);
+ ^
+create table t(a int unique not enforced);
+ERROR: misplaced NOT ENFORCED clause
+LINE 1: create table t(a int unique not enforced);
+ ^
+create table t(a int primary key enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: create table t(a int primary key enforced);
+ ^
+create table t(a int primary key not enforced);
+ERROR: misplaced NOT ENFORCED clause
+LINE 1: create table t(a int primary key not enforced);
+ ^
+create table t(a int generated by default as identity enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: ...te table t(a int generated by default as identity enforced);
+ ^
+create table t(a int generated ALWAYS as (2) stored enforced);
+ERROR: misplaced ENFORCED clause
+LINE 1: ...eate table t(a int generated ALWAYS as (2) stored enforced);
+ ^
+-- none of the above should be accepted
+create table t(a int primary key);
+create table t1(a int);
+-- none of the following should be accepted
+alter table t add constraint constr0 not null a not enforced;
+ERROR: NOT NULL constraints cannot be marked NOT ENFORCED
+LINE 1: alter table t add constraint constr0 not null a not enforce...
+ ^
+alter table t add constraint constr0 not null a enforced;
+ERROR: NOT NULL constraints cannot be marked ENFORCED
+LINE 1: alter table t add constraint constr0 not null a enforced;
+ ^
+alter table t add constraint constr0 unique (a) not enforced;
+ERROR: UNIQUE constraints cannot be marked NOT ENFORCED
+LINE 1: alter table t add constraint constr0 unique (a) not enforced...
+ ^
+alter table t add constraint constr0 unique (a) enforced;
+ERROR: UNIQUE constraints cannot be marked ENFORCED
+LINE 1: alter table t add constraint constr0 unique (a) enforced;
+ ^
+alter table t add constraint constr0 primary key (a) enforced;
+ERROR: PRIMARY KEY constraints cannot be marked ENFORCED
+LINE 1: ...ter table t add constraint constr0 primary key (a) enforced;
+ ^
+alter table t add constraint constr0 primary key (a) not enforced;
+ERROR: PRIMARY KEY constraints cannot be marked NOT ENFORCED
+LINE 1: ...er table t add constraint constr0 primary key (a) not enforc...
+ ^
+alter table t add constraint constr0 primary key (a) not enforced;
+ERROR: PRIMARY KEY constraints cannot be marked NOT ENFORCED
+LINE 1: ...er table t add constraint constr0 primary key (a) not enforc...
+ ^
+alter table t add constraint constr0 exclude using btree (a with = ) not enforced;
+ERROR: EXCLUDE constraints cannot be marked NOT ENFORCED
+LINE 1: ...onstraint constr0 exclude using btree (a with = ) not enforc...
+ ^
+alter table t add constraint constr0 exclude using btree (a with = ) enforced;
+ERROR: EXCLUDE constraints cannot be marked ENFORCED
+LINE 1: ...constraint constr0 exclude using btree (a with = ) enforced;
+ ^
+alter table t add constraint constr0 check (a > 1) not enforced enforced;
+ERROR: conflicting constraint properties
+LINE 1: ... add constraint constr0 check (a > 1) not enforced enforced;
+ ^
+alter table t1 add constraint constr0 foreign key(a) references t(a) not enforced;
+ERROR: FOREIGN KEY constraints cannot be marked NOT ENFORCED
+LINE 1: ...constraint constr0 foreign key(a) references t(a) not enforc...
+ ^
+alter table t1 add constraint constr0 foreign key(a) references t(a) enforced;
+ERROR: FOREIGN KEY constraints cannot be marked ENFORCED
+LINE 1: ... constraint constr0 foreign key(a) references t(a) enforced;
+ ^
+alter table t1 add constraint constr0 foreign key(a) references t(a) not enforced enforced;
+ERROR: conflicting constraint properties
+LINE 1: ...onstr0 foreign key(a) references t(a) not enforced enforced;
+ ^
+-- none of the above should be accepted
+drop table t1, t;
-- XXX: error message is misleading here
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
ERROR: constraint "unique_tbl_i_key" of relation "unique_tbl" is not a foreign key constraint
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..117afae727 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -540,6 +540,21 @@ Not-null constraints:
"noinh_con_copy_b_not_null" NOT NULL "b"
"noinh_con_copy_c_not_null" NOT NULL "c" NO INHERIT
+-- LIKE must respect NO INHERIT property of constraints
+CREATE TABLE check_not_enforced_copy(
+ a int constraint n1 CHECK (a > 0) NO INHERIT not enforced,
+ constraint n2 check (a > 0) enforced);
+CREATE TABLE check_not_enforced_copy1 (LIKE check_not_enforced_copy INCLUDING CONSTRAINTS);
+\d+ check_not_enforced_copy1
+ Table "public.check_not_enforced_copy1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Check constraints:
+ "n1" CHECK (a > 0) NO INHERIT NOT ENFORCED
+ "n2" CHECK (a > 0)
+
+drop table check_not_enforced_copy, check_not_enforced_copy1;
-- fail, as partitioned tables don't allow NO INHERIT constraints
CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
PARTITION BY LIST (a);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 32c8b433bf..ee3e5eb106 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -534,6 +534,36 @@ SELECT * FROM unique_tbl;
-- enforcibility cannot be specified or set for unique constrain
CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED);
CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
+-- none of the following should be accepted
+create table t(a int not null enforced);
+create table t(a int not null enforced enforced);
+create table t(a int not null not enforced);
+create table t(a int unique enforced);
+create table t(a int unique not enforced);
+create table t(a int primary key enforced);
+create table t(a int primary key not enforced);
+create table t(a int generated by default as identity enforced);
+create table t(a int generated ALWAYS as (2) stored enforced);
+-- none of the above should be accepted
+create table t(a int primary key);
+create table t1(a int);
+-- none of the following should be accepted
+alter table t add constraint constr0 not null a not enforced;
+alter table t add constraint constr0 not null a enforced;
+alter table t add constraint constr0 unique (a) not enforced;
+alter table t add constraint constr0 unique (a) enforced;
+alter table t add constraint constr0 primary key (a) enforced;
+alter table t add constraint constr0 primary key (a) not enforced;
+alter table t add constraint constr0 primary key (a) not enforced;
+alter table t add constraint constr0 exclude using btree (a with = ) not enforced;
+alter table t add constraint constr0 exclude using btree (a with = ) enforced;
+alter table t add constraint constr0 check (a > 1) not enforced enforced;
+
+alter table t1 add constraint constr0 foreign key(a) references t(a) not enforced;
+alter table t1 add constraint constr0 foreign key(a) references t(a) enforced;
+alter table t1 add constraint constr0 foreign key(a) references t(a) not enforced enforced;
+-- none of the above should be accepted
+drop table t1, t;
-- XXX: error message is misleading here
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..460ba73574 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -200,6 +200,13 @@ CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT, b int not null,
CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS);
\d+ noinh_con_copy1
+-- LIKE must respect NO INHERIT property of constraints
+CREATE TABLE check_not_enforced_copy(
+ a int constraint n1 CHECK (a > 0) NO INHERIT not enforced,
+ constraint n2 check (a > 0) enforced);
+CREATE TABLE check_not_enforced_copy1 (LIKE check_not_enforced_copy INCLUDING CONSTRAINTS);
+\d+ check_not_enforced_copy1
+drop table check_not_enforced_copy, check_not_enforced_copy1;
-- fail, as partitioned tables don't allow NO INHERIT constraints
CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
PARTITION BY LIST (a);
--
2.34.1