From 48475d1240f6b8e8e85d801443148eb1740daa70 Mon Sep 17 00:00:00 2001 From: jian he 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