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

Commit58995db

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 parentd35631e commit58995db

File tree

2 files changed

+226
-0
lines changed

2 files changed

+226
-0
lines changed

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

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2175,3 +2175,126 @@ reset session authorization;
21752175
revoke all on permtest_parent from regress_no_child_access;
21762176
drop role regress_no_child_access;
21772177
drop table permtest_parent;
2178+
-- Verify that constraint errors across partition root / child are
2179+
-- handled correctly (Bug #16293)
2180+
CREATE TABLE errtst_parent (
2181+
partid int not null,
2182+
shdata int not null,
2183+
data int NOT NULL DEFAULT 0,
2184+
CONSTRAINT shdata_small CHECK(shdata < 3)
2185+
) PARTITION BY RANGE (partid);
2186+
-- fast defaults lead to attribute mapping being used in one
2187+
-- direction, but not the other
2188+
CREATE TABLE errtst_child_fastdef (
2189+
partid int not null,
2190+
shdata int not null,
2191+
CONSTRAINT shdata_small CHECK(shdata < 3)
2192+
);
2193+
-- no remapping in either direction necessary
2194+
CREATE TABLE errtst_child_plaindef (
2195+
partid int not null,
2196+
shdata int not null,
2197+
data int NOT NULL DEFAULT 0,
2198+
CONSTRAINT shdata_small CHECK(shdata < 3),
2199+
CHECK(data < 10)
2200+
);
2201+
-- remapping in both direction
2202+
CREATE TABLE errtst_child_reorder (
2203+
data int NOT NULL DEFAULT 0,
2204+
shdata int not null,
2205+
partid int not null,
2206+
CONSTRAINT shdata_small CHECK(shdata < 3),
2207+
CHECK(data < 10)
2208+
);
2209+
ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
2210+
ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
2211+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
2212+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
2213+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
2214+
-- insert without child check constraint error
2215+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
2216+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
2217+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
2218+
-- insert with child check constraint error
2219+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
2220+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2221+
DETAIL: Failing row contains (0, 1, 10).
2222+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
2223+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2224+
DETAIL: Failing row contains (10, 1, 10).
2225+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
2226+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2227+
DETAIL: Failing row contains (20, 1, 10).
2228+
-- insert with child not null constraint error
2229+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
2230+
ERROR: null value in column "data" violates not-null constraint
2231+
DETAIL: Failing row contains (0, 1, null).
2232+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
2233+
ERROR: null value in column "data" violates not-null constraint
2234+
DETAIL: Failing row contains (10, 1, null).
2235+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
2236+
ERROR: null value in column "data" violates not-null constraint
2237+
DETAIL: Failing row contains (20, 1, null).
2238+
-- insert with shared check constraint error
2239+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
2240+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
2241+
DETAIL: Failing row contains (0, 5, 5).
2242+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
2243+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
2244+
DETAIL: Failing row contains (10, 5, 5).
2245+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
2246+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
2247+
DETAIL: Failing row contains (20, 5, 5).
2248+
-- within partition update without child check constraint violation
2249+
BEGIN;
2250+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
2251+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
2252+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
2253+
ROLLBACK;
2254+
-- within partition update with child check constraint violation
2255+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
2256+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2257+
DETAIL: Failing row contains (0, 1, 15).
2258+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
2259+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2260+
DETAIL: Failing row contains (10, 1, 15).
2261+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
2262+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2263+
DETAIL: Failing row contains (15, 1, 20).
2264+
-- direct leaf partition update, without partition id violation
2265+
BEGIN;
2266+
UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
2267+
UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
2268+
UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
2269+
ROLLBACK;
2270+
-- direct leaf partition update, with partition id violation
2271+
UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
2272+
ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
2273+
DETAIL: Failing row contains (10, 1, 5).
2274+
UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
2275+
ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
2276+
DETAIL: Failing row contains (20, 1, 5).
2277+
UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
2278+
ERROR: new row for relation "errtst_child_reorder" violates partition constraint
2279+
DETAIL: Failing row contains (5, 1, 30).
2280+
-- partition move, without child check constraint violation
2281+
BEGIN;
2282+
UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
2283+
UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
2284+
UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
2285+
ROLLBACK;
2286+
-- partition move, with child check constraint violation
2287+
UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
2288+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2289+
DETAIL: Failing row contains (10, 1, 15).
2290+
UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
2291+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2292+
DETAIL: Failing row contains (20, 1, 15).
2293+
UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
2294+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2295+
DETAIL: Failing row contains (0, 1, 15).
2296+
-- partition move, without target partition
2297+
UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
2298+
ERROR: no partition of relation "errtst_parent" found for row
2299+
DETAIL: Partition key of the failing row contains (partid) = (30).
2300+
DROP TABLE errtst_parent;

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

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -780,3 +780,106 @@ reset session authorization;
780780
revoke allon permtest_parentfrom regress_no_child_access;
781781
drop role regress_no_child_access;
782782
droptable permtest_parent;
783+
784+
-- Verify that constraint errors across partition root / child are
785+
-- handled correctly (Bug #16293)
786+
CREATETABLEerrtst_parent (
787+
partidintnot null,
788+
shdataintnot null,
789+
dataintNOT NULL DEFAULT0,
790+
CONSTRAINT shdata_smallCHECK(shdata<3)
791+
) PARTITION BY RANGE (partid);
792+
793+
-- fast defaults lead to attribute mapping being used in one
794+
-- direction, but not the other
795+
CREATETABLEerrtst_child_fastdef (
796+
partidintnot null,
797+
shdataintnot null,
798+
CONSTRAINT shdata_smallCHECK(shdata<3)
799+
);
800+
801+
-- no remapping in either direction necessary
802+
CREATETABLEerrtst_child_plaindef (
803+
partidintnot null,
804+
shdataintnot null,
805+
dataintNOT NULL DEFAULT0,
806+
CONSTRAINT shdata_smallCHECK(shdata<3),
807+
CHECK(data<10)
808+
);
809+
810+
-- remapping in both direction
811+
CREATETABLEerrtst_child_reorder (
812+
dataintNOT NULL DEFAULT0,
813+
shdataintnot null,
814+
partidintnot null,
815+
CONSTRAINT shdata_smallCHECK(shdata<3),
816+
CHECK(data<10)
817+
);
818+
819+
ALTERTABLE errtst_child_fastdef ADD COLUMN dataintNOT NULL DEFAULT0;
820+
ALTERTABLE errtst_child_fastdef ADDCONSTRAINT errtest_child_fastdef_data_checkCHECK (data<10);
821+
822+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FORVALUESFROM (0) TO (10);
823+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FORVALUESFROM (10) TO (20);
824+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_reorder FORVALUESFROM (20) TO (30);
825+
826+
-- insert without child check constraint error
827+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1','5');
828+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1','5');
829+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1','5');
830+
831+
-- insert with child check constraint error
832+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1','10');
833+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1','10');
834+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1','10');
835+
836+
-- insert with child not null constraint error
837+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1',NULL);
838+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1',NULL);
839+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1',NULL);
840+
841+
-- insert with shared check constraint error
842+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','5','5');
843+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','5','5');
844+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','5','5');
845+
846+
-- within partition update without child check constraint violation
847+
BEGIN;
848+
UPDATE errtst_parentSET data= data+1WHERE partid=0;
849+
UPDATE errtst_parentSET data= data+1WHERE partid=10;
850+
UPDATE errtst_parentSET data= data+1WHERE partid=20;
851+
ROLLBACK;
852+
853+
-- within partition update with child check constraint violation
854+
UPDATE errtst_parentSET data= data+10WHERE partid=0;
855+
UPDATE errtst_parentSET data= data+10WHERE partid=10;
856+
UPDATE errtst_parentSET data= data+10WHERE partid=20;
857+
858+
-- direct leaf partition update, without partition id violation
859+
BEGIN;
860+
UPDATE errtst_child_fastdefSET partid=1WHERE partid=0;
861+
UPDATE errtst_child_plaindefSET partid=11WHERE partid=10;
862+
UPDATE errtst_child_reorderSET partid=21WHERE partid=20;
863+
ROLLBACK;
864+
865+
-- direct leaf partition update, with partition id violation
866+
UPDATE errtst_child_fastdefSET partid= partid+10WHERE partid=0;
867+
UPDATE errtst_child_plaindefSET partid= partid+10WHERE partid=10;
868+
UPDATE errtst_child_reorderSET partid= partid+10WHERE partid=20;
869+
870+
-- partition move, without child check constraint violation
871+
BEGIN;
872+
UPDATE errtst_parentSET partid=10, data= data+1WHERE partid=0;
873+
UPDATE errtst_parentSET partid=20, data= data+1WHERE partid=10;
874+
UPDATE errtst_parentSET partid=0, data= data+1WHERE partid=20;
875+
ROLLBACK;
876+
877+
-- partition move, with child check constraint violation
878+
UPDATE errtst_parentSET partid=10, data= data+10WHERE partid=0;
879+
UPDATE errtst_parentSET partid=20, data= data+10WHERE partid=10;
880+
UPDATE errtst_parentSET partid=0, data= data+10WHERE partid=20;
881+
882+
-- partition move, without target partition
883+
UPDATE errtst_parentSET partid=30, data= data+10WHERE partid=20;
884+
885+
DROPTABLE errtst_parent;

0 commit comments

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