Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2060999

Browse files
committed
Add regression tests for constraint errors in partitioned tables.
While #16293 only applied to 11 (and 10 to some degree), it seems bestto add tests to all branches with partitioning support.Reported-By: Daniel WMAuthor: Andres FreundBug: #16293Discussion:https://postgr.es/m/16293-26f5777d10143a66@postgresql.orgBackpatch: 10-
1 parent44170a5 commit2060999

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_intrangepartwhere a='[1,2]'::int4range;
756756
explain (costs off)select*from pp_intrangepartwhere a='(1,2)'::int4range;
757757
droptable pp_intrangepart;
758+
759+
-- Verify that constraint errors across partition root / child are
760+
-- handled correctly (Bug #16293)
761+
CREATETABLEerrtst_parent (
762+
partidintnot null,
763+
shdataintnot null,
764+
dataintNOT NULL DEFAULT0,
765+
CONSTRAINT shdata_smallCHECK(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+
CREATETABLEerrtst_child_fastdef (
771+
partidintnot null,
772+
shdataintnot null,
773+
CONSTRAINT shdata_smallCHECK(shdata<3)
774+
);
775+
776+
-- no remapping in either direction necessary
777+
CREATETABLEerrtst_child_plaindef (
778+
partidintnot null,
779+
shdataintnot null,
780+
dataintNOT NULL DEFAULT0,
781+
CONSTRAINT shdata_smallCHECK(shdata<3),
782+
CHECK(data<10)
783+
);
784+
785+
-- remapping in both direction
786+
CREATETABLEerrtst_child_reorder (
787+
dataintNOT NULL DEFAULT0,
788+
shdataintnot null,
789+
partidintnot null,
790+
CONSTRAINT shdata_smallCHECK(shdata<3),
791+
CHECK(data<10)
792+
);
793+
794+
ALTERTABLE errtst_child_fastdef ADD COLUMN dataintNOT NULL DEFAULT0;
795+
ALTERTABLE errtst_child_fastdef ADDCONSTRAINT errtest_child_fastdef_data_checkCHECK (data<10);
796+
797+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FORVALUESFROM (0) TO (10);
798+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FORVALUESFROM (10) TO (20);
799+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_reorder FORVALUESFROM (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_parentSET data= data+1WHERE partid=0;
824+
UPDATE errtst_parentSET data= data+1WHERE partid=10;
825+
UPDATE errtst_parentSET data= data+1WHERE partid=20;
826+
ROLLBACK;
827+
828+
-- within partition update with child check constraint violation
829+
UPDATE errtst_parentSET data= data+10WHERE partid=0;
830+
UPDATE errtst_parentSET data= data+10WHERE partid=10;
831+
UPDATE errtst_parentSET data= data+10WHERE partid=20;
832+
833+
-- direct leaf partition update, without partition id violation
834+
BEGIN;
835+
UPDATE errtst_child_fastdefSET partid=1WHERE partid=0;
836+
UPDATE errtst_child_plaindefSET partid=11WHERE partid=10;
837+
UPDATE errtst_child_reorderSET partid=21WHERE partid=20;
838+
ROLLBACK;
839+
840+
-- direct leaf partition update, with partition id violation
841+
UPDATE errtst_child_fastdefSET partid= partid+10WHERE partid=0;
842+
UPDATE errtst_child_plaindefSET partid= partid+10WHERE partid=10;
843+
UPDATE errtst_child_reorderSET partid= partid+10WHERE partid=20;
844+
845+
-- partition move, without child check constraint violation
846+
UPDATE errtst_parentSET partid=10, data= data+1WHERE partid=0;
847+
UPDATE errtst_parentSET partid=20, data= data+1WHERE partid=10;
848+
UPDATE errtst_parentSET partid=0, data= data+1WHERE partid=20;
849+
850+
-- partition move, with child check constraint violation
851+
UPDATE errtst_parentSET partid=10, data= data+10WHERE partid=0;
852+
UPDATE errtst_parentSET partid=20, data= data+10WHERE partid=10;
853+
UPDATE errtst_parentSET partid=0, data= data+10WHERE partid=20;
854+
855+
-- partition move, without target partition
856+
UPDATE errtst_parentSET partid=30, data= data+10WHERE partid=20;
857+
858+
DROPTABLE errtst_parent;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp