Skip to content

Commit 2060999

Browse files
anarazelanarazel
committed
Add regression tests for constraint errors in partitioned tables.
While #16293 only applied to 11 (and 10 to some degree), it seems best to add tests to all branches with partitioning support. Reported-By: Daniel WM Author: Andres Freund Bug: #16293 Discussion: https://postgr.es/m/16293-26f5777d10143a66@postgresql.org Backpatch: 10-
1 parent 44170a5 commit 2060999

File tree

2 files changed

+228
-0
lines changed

2 files changed

+228
-0
lines changed

‎src/test/regress/expected/inherit.out

Lines changed: 127 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2149,3 +2149,130 @@ explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
21492149
(2 rows)
21502150

21512151
drop table pp_intrangepart;
2152+
-- Verify that constraint errors across partition root / child are
2153+
-- handled correctly (Bug #16293)
2154+
CREATE TABLE errtst_parent (
2155+
partid int not null,
2156+
shdata int not null,
2157+
data int NOT NULL DEFAULT 0,
2158+
CONSTRAINT shdata_small CHECK(shdata < 3)
2159+
) PARTITION BY RANGE (partid);
2160+
-- fast defaults lead to attribute mapping being used in one
2161+
-- direction, but not the other
2162+
CREATE TABLE errtst_child_fastdef (
2163+
partid int not null,
2164+
shdata int not null,
2165+
CONSTRAINT shdata_small CHECK(shdata < 3)
2166+
);
2167+
-- no remapping in either direction necessary
2168+
CREATE TABLE errtst_child_plaindef (
2169+
partid int not null,
2170+
shdata int not null,
2171+
data int NOT NULL DEFAULT 0,
2172+
CONSTRAINT shdata_small CHECK(shdata < 3),
2173+
CHECK(data < 10)
2174+
);
2175+
-- remapping in both direction
2176+
CREATE TABLE errtst_child_reorder (
2177+
data int NOT NULL DEFAULT 0,
2178+
shdata int not null,
2179+
partid int not null,
2180+
CONSTRAINT shdata_small CHECK(shdata < 3),
2181+
CHECK(data < 10)
2182+
);
2183+
ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
2184+
ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
2185+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
2186+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
2187+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
2188+
-- insert without child check constraint error
2189+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
2190+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
2191+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
2192+
-- insert with child check constraint error
2193+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
2194+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2195+
DETAIL: Failing row contains (0, 1, 10).
2196+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
2197+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2198+
DETAIL: Failing row contains (10, 1, 10).
2199+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
2200+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2201+
DETAIL: Failing row contains (20, 1, 10).
2202+
-- insert with child not null constraint error
2203+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
2204+
ERROR: null value in column "data" violates not-null constraint
2205+
DETAIL: Failing row contains (0, 1, null).
2206+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
2207+
ERROR: null value in column "data" violates not-null constraint
2208+
DETAIL: Failing row contains (10, 1, null).
2209+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
2210+
ERROR: null value in column "data" violates not-null constraint
2211+
DETAIL: Failing row contains (20, 1, null).
2212+
-- insert with shared check constraint error
2213+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
2214+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
2215+
DETAIL: Failing row contains (0, 5, 5).
2216+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
2217+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
2218+
DETAIL: Failing row contains (10, 5, 5).
2219+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
2220+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
2221+
DETAIL: Failing row contains (20, 5, 5).
2222+
-- within partition update without child check constraint violation
2223+
BEGIN;
2224+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
2225+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
2226+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
2227+
ROLLBACK;
2228+
-- within partition update with child check constraint violation
2229+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
2230+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2231+
DETAIL: Failing row contains (0, 1, 15).
2232+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
2233+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2234+
DETAIL: Failing row contains (10, 1, 15).
2235+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
2236+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2237+
DETAIL: Failing row contains (15, 1, 20).
2238+
-- direct leaf partition update, without partition id violation
2239+
BEGIN;
2240+
UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
2241+
UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
2242+
UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
2243+
ROLLBACK;
2244+
-- direct leaf partition update, with partition id violation
2245+
UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
2246+
ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
2247+
DETAIL: Failing row contains (10, 1, 5).
2248+
UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
2249+
ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
2250+
DETAIL: Failing row contains (20, 1, 5).
2251+
UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
2252+
ERROR: new row for relation "errtst_child_reorder" violates partition constraint
2253+
DETAIL: Failing row contains (5, 1, 30).
2254+
-- partition move, without child check constraint violation
2255+
UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
2256+
ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
2257+
DETAIL: Failing row contains (10, 1, 6).
2258+
UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
2259+
ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
2260+
DETAIL: Failing row contains (20, 1, 6).
2261+
UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
2262+
ERROR: new row for relation "errtst_child_reorder" violates partition constraint
2263+
DETAIL: Failing row contains (6, 1, 0).
2264+
-- partition move, with child check constraint violation
2265+
UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
2266+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2267+
DETAIL: Failing row contains (10, 1, 15).
2268+
UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
2269+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2270+
DETAIL: Failing row contains (20, 1, 15).
2271+
UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
2272+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2273+
DETAIL: Failing row contains (15, 1, 0).
2274+
-- partition move, without target partition
2275+
UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
2276+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2277+
DETAIL: Failing row contains (15, 1, 30).
2278+
DROP TABLE errtst_parent;

‎src/test/regress/sql/inherit.sql

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -755,3 +755,104 @@ create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2
755755
explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
756756
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
757757
drop table pp_intrangepart;
758+
759+
-- Verify that constraint errors across partition root / child are
760+
-- handled correctly (Bug #16293)
761+
CREATE TABLE errtst_parent (
762+
partid int not null,
763+
shdata int not null,
764+
data int NOT NULL DEFAULT 0,
765+
CONSTRAINT shdata_small CHECK(shdata < 3)
766+
) PARTITION BY RANGE (partid);
767+
768+
-- fast defaults lead to attribute mapping being used in one
769+
-- direction, but not the other
770+
CREATE TABLE errtst_child_fastdef (
771+
partid int not null,
772+
shdata int not null,
773+
CONSTRAINT shdata_small CHECK(shdata < 3)
774+
);
775+
776+
-- no remapping in either direction necessary
777+
CREATE TABLE errtst_child_plaindef (
778+
partid int not null,
779+
shdata int not null,
780+
data int NOT NULL DEFAULT 0,
781+
CONSTRAINT shdata_small CHECK(shdata < 3),
782+
CHECK(data < 10)
783+
);
784+
785+
-- remapping in both direction
786+
CREATE TABLE errtst_child_reorder (
787+
data int NOT NULL DEFAULT 0,
788+
shdata int not null,
789+
partid int not null,
790+
CONSTRAINT shdata_small CHECK(shdata < 3),
791+
CHECK(data < 10)
792+
);
793+
794+
ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
795+
ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
796+
797+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
798+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
799+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
800+
801+
-- insert without child check constraint error
802+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
803+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
804+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
805+
806+
-- insert with child check constraint error
807+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
808+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
809+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
810+
811+
-- insert with child not null constraint error
812+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
813+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
814+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
815+
816+
-- insert with shared check constraint error
817+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
818+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
819+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
820+
821+
-- within partition update without child check constraint violation
822+
BEGIN;
823+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
824+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
825+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
826+
ROLLBACK;
827+
828+
-- within partition update with child check constraint violation
829+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
830+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
831+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
832+
833+
-- direct leaf partition update, without partition id violation
834+
BEGIN;
835+
UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
836+
UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
837+
UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
838+
ROLLBACK;
839+
840+
-- direct leaf partition update, with partition id violation
841+
UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
842+
UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
843+
UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
844+
845+
-- partition move, without child check constraint violation
846+
UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
847+
UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
848+
UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
849+
850+
-- partition move, with child check constraint violation
851+
UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
852+
UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
853+
UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
854+
855+
-- partition move, without target partition
856+
UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
857+
858+
DROP TABLE errtst_parent;

0 commit comments

Comments
 (0)