summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/alter_table.out101
-rw-r--r--src/test/regress/expected/domain.out11
-rw-r--r--src/test/regress/sql/alter_table.sql46
-rw-r--r--src/test/regress/sql/domain.sql10
4 files changed, 168 insertions, 0 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 9ab84f983ed..b78b1510efb 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -196,14 +196,115 @@ DELETE FROM tmp3 where a=5;
-- Try (and succeed) and repeat to show it works on already valid constraint
ALTER TABLE tmp3 validate constraint tmpconstr;
ALTER TABLE tmp3 validate constraint tmpconstr;
+-- Try a non-verified CHECK constraint
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
+ERROR: check constraint "b_greater_than_ten" is violated by some row
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
+ERROR: check constraint "b_greater_than_ten" is violated by some row
+DELETE FROM tmp3 WHERE NOT b > 10;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+-- Test inherited NOT VALID CHECK constraints
+select * from tmp3;
+ a | b
+---+----
+ 1 | 20
+(1 row)
+
+CREATE TABLE tmp6 () INHERITS (tmp3);
+CREATE TABLE tmp7 () INHERITS (tmp3);
+INSERT INTO tmp6 VALUES (6, 30), (7, 16);
+ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
+ERROR: check constraint "b_le_20" is violated by some row
+DELETE FROM tmp6 WHERE b > 20;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
+-- An already validated constraint must not be revalidated
+CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
+INSERT INTO tmp7 VALUES (8, 18);
+ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
+NOTICE: boo: 18
+ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
+NOTICE: merging constraint "identity" with inherited definition
+ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
+NOTICE: boo: 16
+NOTICE: boo: 20
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
-- tmp4 is a,b
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
ERROR: there is no unique constraint matching given keys for referenced table "tmp4"
+DROP TABLE tmp7;
+DROP TABLE tmp6;
DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
+-- exclusion until validated
+set constraint_exclusion TO 'partition';
+create table nv_parent (d date);
+create table nv_child_2010 () inherits (nv_parent);
+create table nv_child_2011 () inherits (nv_parent);
+alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
+alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
+explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Result
+ -> Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2010 nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2011 nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+(8 rows)
+
+create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
+explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Result
+ -> Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2010 nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2011 nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+(8 rows)
+
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Result
+ -> Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2010 nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2011 nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2009 nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+(10 rows)
+
+-- after validation, the constraint should be used
+alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Result
+ -> Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2010 nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2009 nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+(8 rows)
+
-- Foreign key adding test with mixed types
-- Note: these tables are TEMP to avoid name conflicts when this test
-- is run in parallel with foreign_key.sql.
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 34bc31ab380..521fe01fa17 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -352,6 +352,17 @@ alter domain con drop constraint t;
insert into domcontest values (-5); --fails
ERROR: value for domain con violates check constraint "con_check"
insert into domcontest values (42);
+-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
+create domain things AS INT;
+CREATE TABLE thethings (stuff things);
+INSERT INTO thethings (stuff) VALUES (55);
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11);
+ERROR: column "stuff" of table "thethings" contains values that violate the new constraint
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+ERROR: column "stuff" of table "thethings" contains values that violate the new constraint
+UPDATE thethings SET stuff = 10;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
-- Confirm ALTER DOMAIN with RULES.
create table domtab (col1 integer);
create domain dom as integer;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index b5d76ea68e3..bb2c27718a0 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -236,12 +236,41 @@ DELETE FROM tmp3 where a=5;
ALTER TABLE tmp3 validate constraint tmpconstr;
ALTER TABLE tmp3 validate constraint tmpconstr;
+-- Try a non-verified CHECK constraint
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
+DELETE FROM tmp3 WHERE NOT b > 10;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+
+-- Test inherited NOT VALID CHECK constraints
+select * from tmp3;
+CREATE TABLE tmp6 () INHERITS (tmp3);
+CREATE TABLE tmp7 () INHERITS (tmp3);
+
+INSERT INTO tmp6 VALUES (6, 30), (7, 16);
+ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
+DELETE FROM tmp6 WHERE b > 20;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
+
+-- An already validated constraint must not be revalidated
+CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
+INSERT INTO tmp7 VALUES (8, 18);
+ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
+ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
-- tmp4 is a,b
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
+DROP TABLE tmp7;
+
+DROP TABLE tmp6;
+
DROP TABLE tmp5;
DROP TABLE tmp4;
@@ -250,6 +279,23 @@ DROP TABLE tmp3;
DROP TABLE tmp2;
+-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
+-- exclusion until validated
+set constraint_exclusion TO 'partition';
+create table nv_parent (d date);
+create table nv_child_2010 () inherits (nv_parent);
+create table nv_child_2011 () inherits (nv_parent);
+alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
+alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
+explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
+create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
+explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+-- after validation, the constraint should be used
+alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+
+
-- Foreign key adding test with mixed types
-- Note: these tables are TEMP to avoid name conflicts when this test
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 1cc447b8a24..449b4234a5c 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -259,6 +259,16 @@ alter domain con drop constraint t;
insert into domcontest values (-5); --fails
insert into domcontest values (42);
+-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
+create domain things AS INT;
+CREATE TABLE thethings (stuff things);
+INSERT INTO thethings (stuff) VALUES (55);
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11);
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+UPDATE thethings SET stuff = 10;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+
-- Confirm ALTER DOMAIN with RULES.
create table domtab (col1 integer);
create domain dom as integer;
generated by cgit v1.2.3 (git 2.39.1) at 2025-08-05 10:19:52 +0000