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

Commitf801ceb

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 parent0df94be commitf801ceb

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
@@ -2409,3 +2409,126 @@ reset session authorization;
24092409
revoke all on permtest_parent from regress_no_child_access;
24102410
drop role regress_no_child_access;
24112411
drop table permtest_parent;
2412+
-- Verify that constraint errors across partition root / child are
2413+
-- handled correctly (Bug #16293)
2414+
CREATE TABLE errtst_parent (
2415+
partid int not null,
2416+
shdata int not null,
2417+
data int NOT NULL DEFAULT 0,
2418+
CONSTRAINT shdata_small CHECK(shdata < 3)
2419+
) PARTITION BY RANGE (partid);
2420+
-- fast defaults lead to attribute mapping being used in one
2421+
-- direction, but not the other
2422+
CREATE TABLE errtst_child_fastdef (
2423+
partid int not null,
2424+
shdata int not null,
2425+
CONSTRAINT shdata_small CHECK(shdata < 3)
2426+
);
2427+
-- no remapping in either direction necessary
2428+
CREATE TABLE errtst_child_plaindef (
2429+
partid int not null,
2430+
shdata int not null,
2431+
data int NOT NULL DEFAULT 0,
2432+
CONSTRAINT shdata_small CHECK(shdata < 3),
2433+
CHECK(data < 10)
2434+
);
2435+
-- remapping in both direction
2436+
CREATE TABLE errtst_child_reorder (
2437+
data int NOT NULL DEFAULT 0,
2438+
shdata int not null,
2439+
partid int not null,
2440+
CONSTRAINT shdata_small CHECK(shdata < 3),
2441+
CHECK(data < 10)
2442+
);
2443+
ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
2444+
ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
2445+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
2446+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
2447+
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
2448+
-- insert without child check constraint error
2449+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
2450+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
2451+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
2452+
-- insert with child check constraint error
2453+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
2454+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2455+
DETAIL: Failing row contains (0, 1, 10).
2456+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
2457+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2458+
DETAIL: Failing row contains (10, 1, 10).
2459+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
2460+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2461+
DETAIL: Failing row contains (20, 1, 10).
2462+
-- insert with child not null constraint error
2463+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
2464+
ERROR: null value in column "data" of relation "errtst_child_fastdef" violates not-null constraint
2465+
DETAIL: Failing row contains (0, 1, null).
2466+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
2467+
ERROR: null value in column "data" of relation "errtst_child_plaindef" violates not-null constraint
2468+
DETAIL: Failing row contains (10, 1, null).
2469+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
2470+
ERROR: null value in column "data" of relation "errtst_child_reorder" violates not-null constraint
2471+
DETAIL: Failing row contains (20, 1, null).
2472+
-- insert with shared check constraint error
2473+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
2474+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
2475+
DETAIL: Failing row contains (0, 5, 5).
2476+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
2477+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
2478+
DETAIL: Failing row contains (10, 5, 5).
2479+
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
2480+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
2481+
DETAIL: Failing row contains (20, 5, 5).
2482+
-- within partition update without child check constraint violation
2483+
BEGIN;
2484+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
2485+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
2486+
UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
2487+
ROLLBACK;
2488+
-- within partition update with child check constraint violation
2489+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
2490+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2491+
DETAIL: Failing row contains (0, 1, 15).
2492+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
2493+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2494+
DETAIL: Failing row contains (10, 1, 15).
2495+
UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
2496+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2497+
DETAIL: Failing row contains (15, 1, 20).
2498+
-- direct leaf partition update, without partition id violation
2499+
BEGIN;
2500+
UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
2501+
UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
2502+
UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
2503+
ROLLBACK;
2504+
-- direct leaf partition update, with partition id violation
2505+
UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
2506+
ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
2507+
DETAIL: Failing row contains (10, 1, 5).
2508+
UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
2509+
ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
2510+
DETAIL: Failing row contains (20, 1, 5).
2511+
UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
2512+
ERROR: new row for relation "errtst_child_reorder" violates partition constraint
2513+
DETAIL: Failing row contains (5, 1, 30).
2514+
-- partition move, without child check constraint violation
2515+
BEGIN;
2516+
UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
2517+
UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
2518+
UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
2519+
ROLLBACK;
2520+
-- partition move, with child check constraint violation
2521+
UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
2522+
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2523+
DETAIL: Failing row contains (10, 1, 15).
2524+
UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
2525+
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2526+
DETAIL: Failing row contains (20, 1, 15).
2527+
UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
2528+
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2529+
DETAIL: Failing row contains (0, 1, 15).
2530+
-- partition move, without target partition
2531+
UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
2532+
ERROR: no partition of relation "errtst_parent" found for row
2533+
DETAIL: Partition key of the failing row contains (partid) = (30).
2534+
DROP TABLE errtst_parent;

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

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -883,3 +883,106 @@ reset session authorization;
883883
revoke allon permtest_parentfrom regress_no_child_access;
884884
drop role regress_no_child_access;
885885
droptable permtest_parent;
886+
887+
-- Verify that constraint errors across partition root / child are
888+
-- handled correctly (Bug #16293)
889+
CREATETABLEerrtst_parent (
890+
partidintnot null,
891+
shdataintnot null,
892+
dataintNOT NULL DEFAULT0,
893+
CONSTRAINT shdata_smallCHECK(shdata<3)
894+
) PARTITION BY RANGE (partid);
895+
896+
-- fast defaults lead to attribute mapping being used in one
897+
-- direction, but not the other
898+
CREATETABLEerrtst_child_fastdef (
899+
partidintnot null,
900+
shdataintnot null,
901+
CONSTRAINT shdata_smallCHECK(shdata<3)
902+
);
903+
904+
-- no remapping in either direction necessary
905+
CREATETABLEerrtst_child_plaindef (
906+
partidintnot null,
907+
shdataintnot null,
908+
dataintNOT NULL DEFAULT0,
909+
CONSTRAINT shdata_smallCHECK(shdata<3),
910+
CHECK(data<10)
911+
);
912+
913+
-- remapping in both direction
914+
CREATETABLEerrtst_child_reorder (
915+
dataintNOT NULL DEFAULT0,
916+
shdataintnot null,
917+
partidintnot null,
918+
CONSTRAINT shdata_smallCHECK(shdata<3),
919+
CHECK(data<10)
920+
);
921+
922+
ALTERTABLE errtst_child_fastdef ADD COLUMN dataintNOT NULL DEFAULT0;
923+
ALTERTABLE errtst_child_fastdef ADDCONSTRAINT errtest_child_fastdef_data_checkCHECK (data<10);
924+
925+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FORVALUESFROM (0) TO (10);
926+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FORVALUESFROM (10) TO (20);
927+
ALTERTABLE errtst_parent ATTACH PARTITION errtst_child_reorder FORVALUESFROM (20) TO (30);
928+
929+
-- insert without child check constraint error
930+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1','5');
931+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1','5');
932+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1','5');
933+
934+
-- insert with child check constraint error
935+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1','10');
936+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1','10');
937+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1','10');
938+
939+
-- insert with child not null constraint error
940+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','1',NULL);
941+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','1',NULL);
942+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','1',NULL);
943+
944+
-- insert with shared check constraint error
945+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('0','5','5');
946+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('10','5','5');
947+
INSERT INTO errtst_parent(partid, shdata, data)VALUES ('20','5','5');
948+
949+
-- within partition update without child check constraint violation
950+
BEGIN;
951+
UPDATE errtst_parentSET data= data+1WHERE partid=0;
952+
UPDATE errtst_parentSET data= data+1WHERE partid=10;
953+
UPDATE errtst_parentSET data= data+1WHERE partid=20;
954+
ROLLBACK;
955+
956+
-- within partition update with child check constraint violation
957+
UPDATE errtst_parentSET data= data+10WHERE partid=0;
958+
UPDATE errtst_parentSET data= data+10WHERE partid=10;
959+
UPDATE errtst_parentSET data= data+10WHERE partid=20;
960+
961+
-- direct leaf partition update, without partition id violation
962+
BEGIN;
963+
UPDATE errtst_child_fastdefSET partid=1WHERE partid=0;
964+
UPDATE errtst_child_plaindefSET partid=11WHERE partid=10;
965+
UPDATE errtst_child_reorderSET partid=21WHERE partid=20;
966+
ROLLBACK;
967+
968+
-- direct leaf partition update, with partition id violation
969+
UPDATE errtst_child_fastdefSET partid= partid+10WHERE partid=0;
970+
UPDATE errtst_child_plaindefSET partid= partid+10WHERE partid=10;
971+
UPDATE errtst_child_reorderSET partid= partid+10WHERE partid=20;
972+
973+
-- partition move, without child check constraint violation
974+
BEGIN;
975+
UPDATE errtst_parentSET partid=10, data= data+1WHERE partid=0;
976+
UPDATE errtst_parentSET partid=20, data= data+1WHERE partid=10;
977+
UPDATE errtst_parentSET partid=0, data= data+1WHERE partid=20;
978+
ROLLBACK;
979+
980+
-- partition move, with child check constraint violation
981+
UPDATE errtst_parentSET partid=10, data= data+10WHERE partid=0;
982+
UPDATE errtst_parentSET partid=20, data= data+10WHERE partid=10;
983+
UPDATE errtst_parentSET partid=0, data= data+10WHERE partid=20;
984+
985+
-- partition move, without target partition
986+
UPDATE errtst_parentSET partid=30, data= data+10WHERE partid=20;
987+
988+
DROPTABLE errtst_parent;

0 commit comments

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