1-- 2-- insert with DEFAULT in the target_list 3-- 4create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing'); 5insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); 6ERROR: null value in column "col2" violates not-null constraint 7DETAIL: Failing row contains (null, null, testing). 8insert into inserttest (col2, col3) values (3, DEFAULT); 9insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); 10insert into inserttest values (DEFAULT, 5, 'test'); 11insert into inserttest values (DEFAULT, 7); 12select * from inserttest; 13 col1 | col2 | col3 14------+------+--------- 15 | 3 | testing 16 | 5 | testing 17 | 5 | test 18 | 7 | testing 19(4 rows) 20 21-- 22-- insert with similar expression / target_list values (all fail) 23-- 24insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT); 25ERROR: INSERT has more target columns than expressions 26LINE 1: insert into inserttest (col1, col2, col3) values (DEFAULT, D... 27 ^ 28insert into inserttest (col1, col2, col3) values (1, 2); 29ERROR: INSERT has more target columns than expressions 30LINE 1: insert into inserttest (col1, col2, col3) values (1, 2); 31 ^ 32insert into inserttest (col1) values (1, 2); 33ERROR: INSERT has more expressions than target columns 34LINE 1: insert into inserttest (col1) values (1, 2); 35 ^ 36insert into inserttest (col1) values (DEFAULT, DEFAULT); 37ERROR: INSERT has more expressions than target columns 38LINE 1: insert into inserttest (col1) values (DEFAULT, DEFAULT); 39 ^ 40select * from inserttest; 41 col1 | col2 | col3 42------+------+--------- 43 | 3 | testing 44 | 5 | testing 45 | 5 | test 46 | 7 | testing 47(4 rows) 48 49-- 50-- VALUES test 51-- 52insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT), 53 ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!'); 54select * from inserttest; 55 col1 | col2 | col3 56------+------+----------------- 57 | 3 | testing 58 | 5 | testing 59 | 5 | test 60 | 7 | testing 61 10 | 20 | 40 62 -1 | 2 | testing 63 2 | 3 | values are fun! 64(7 rows) 65 66-- 67-- TOASTed value test 68-- 69insert into inserttest values(30, 50, repeat('x', 10000)); 70select col1, col2, char_length(col3) from inserttest; 71 col1 | col2 | char_length 72------+------+------------- 73 | 3 | 7 74 | 5 | 7 75 | 5 | 4 76 | 7 | 7 77 10 | 20 | 2 78 -1 | 2 | 7 79 2 | 3 | 15 80 30 | 50 | 10000 81(8 rows) 82 83drop table inserttest; 84-- 85-- check indirection (field/array assignment), cf bug #14265 86-- 87-- these tests are aware that transformInsertStmt has 3 separate code paths 88-- 89create type insert_test_type as (if1 int, if2 text[]); 90create table inserttest (f1 int, f2 int[], 91 f3 insert_test_type, f4 insert_test_type[]); 92insert into inserttest (f2[1], f2[2]) values (1,2); 93insert into inserttest (f2[1], f2[2]) values (3,4), (5,6); 94insert into inserttest (f2[1], f2[2]) select 7,8; 95insert into inserttest (f2[1], f2[2]) values (1,default); -- not supported 96ERROR: cannot set an array element to DEFAULT 97LINE 1: insert into inserttest (f2[1], f2[2]) values (1,default); 98 ^ 99insert into inserttest (f3.if1, f3.if2) values (1,array['foo']); 100insert into inserttest (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); 101insert into inserttest (f3.if1, f3.if2) select 3, '{baz,quux}'; 102insert into inserttest (f3.if1, f3.if2) values (1,default); -- not supported 103ERROR: cannot set a subfield to DEFAULT 104LINE 1: insert into inserttest (f3.if1, f3.if2) values (1,default); 105 ^ 106insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); 107insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 108insert into inserttest (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; 109insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'); 110insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 111insert into inserttest (f4[1].if2[1], f4[1].if2[2]) select 'bear', 'beer'; 112select * from inserttest; 113 f1 | f2 | f3 | f4 114----+-------+------------------+------------------------ 115 | {1,2} | | 116 | {3,4} | | 117 | {5,6} | | 118 | {7,8} | | 119 | | (1,{foo}) | 120 | | (1,{foo}) | 121 | | (2,{bar}) | 122 | | (3,"{baz,quux}") | 123 | | (,"{foo,bar}") | 124 | | (,"{foo,bar}") | 125 | | (,"{baz,quux}") | 126 | | (,"{bear,beer}") | 127 | | | {"(,\"{foo,bar}\")"} 128 | | | {"(,\"{foo,bar}\")"} 129 | | | {"(,\"{baz,quux}\")"} 130 | | | {"(,\"{bear,beer}\")"} 131(16 rows) 132 133-- also check reverse-listing 134create table inserttest2 (f1 bigint, f2 text); 135create rule irule1 as on insert to inserttest2 do also 136 insert into inserttest (f3.if2[1], f3.if2[2]) 137 values (new.f1,new.f2); 138create rule irule2 as on insert to inserttest2 do also 139 insert into inserttest (f4[1].if1, f4[1].if2[2]) 140 values (1,'fool'),(new.f1,new.f2); 141create rule irule3 as on insert to inserttest2 do also 142 insert into inserttest (f4[1].if1, f4[1].if2[2]) 143 select new.f1, new.f2; 144\d+ inserttest2 145 Table "public.inserttest2" 146 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 147--------+--------+-----------+----------+---------+----------+--------------+------------- 148 f1 | bigint | | | | plain | | 149 f2 | text | | | | extended | | 150Rules: 151 irule1 AS 152 ON INSERT TO inserttest2 DO INSERT INTO inserttest (f3.if2[1], f3.if2[2]) 153 VALUES (new.f1, new.f2) 154 irule2 AS 155 ON INSERT TO inserttest2 DO INSERT INTO inserttest (f4[1].if1, f4[1].if2[2]) VALUES (1,'fool'::text), (new.f1,new.f2) 156 irule3 AS 157 ON INSERT TO inserttest2 DO INSERT INTO inserttest (f4[1].if1, f4[1].if2[2]) SELECT new.f1, 158 new.f2 159 160drop table inserttest2; 161drop table inserttest; 162drop type insert_test_type; 163-- direct partition inserts should check partition bound constraint 164create table range_parted ( 165 a text, 166 b int 167) partition by range (a, (b+0)); 168create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); 169create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); 170create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); 171create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); 172-- fail 173insert into part1 values ('a', 11); 174ERROR: new row for relation "part1" violates partition constraint 175DETAIL: Failing row contains (a, 11). 176insert into part1 values ('b', 1); 177ERROR: new row for relation "part1" violates partition constraint 178DETAIL: Failing row contains (b, 1). 179-- ok 180insert into part1 values ('a', 1); 181-- fail 182insert into part4 values ('b', 21); 183ERROR: new row for relation "part4" violates partition constraint 184DETAIL: Failing row contains (b, 21). 185insert into part4 values ('a', 10); 186ERROR: new row for relation "part4" violates partition constraint 187DETAIL: Failing row contains (a, 10). 188-- ok 189insert into part4 values ('b', 10); 190-- fail (partition key a has a NOT NULL constraint) 191insert into part1 values (null); 192ERROR: new row for relation "part1" violates partition constraint 193DETAIL: Failing row contains (null, null). 194-- fail (expression key (b+0) cannot be null either) 195insert into part1 values (1); 196ERROR: new row for relation "part1" violates partition constraint 197DETAIL: Failing row contains (1, null). 198create table list_parted ( 199 a text, 200 b int 201) partition by list (lower(a)); 202create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); 203create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); 204create table part_null partition of list_parted FOR VALUES IN (null); 205-- fail 206insert into part_aa_bb values ('cc', 1); 207ERROR: new row for relation "part_aa_bb" violates partition constraint 208DETAIL: Failing row contains (cc, 1). 209insert into part_aa_bb values ('AAa', 1); 210ERROR: new row for relation "part_aa_bb" violates partition constraint 211DETAIL: Failing row contains (AAa, 1). 212insert into part_aa_bb values (null); 213ERROR: new row for relation "part_aa_bb" violates partition constraint 214DETAIL: Failing row contains (null, null). 215-- ok 216insert into part_cc_dd values ('cC', 1); 217insert into part_null values (null, 0); 218-- check in case of multi-level partitioned table 219create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); 220create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); 221create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); 222-- fail 223insert into part_ee_ff1 values ('EE', 11); 224ERROR: new row for relation "part_ee_ff1" violates partition constraint 225DETAIL: Failing row contains (EE, 11). 226-- fail (even the parent's, ie, part_ee_ff's partition constraint applies) 227insert into part_ee_ff1 values ('cc', 1); 228ERROR: new row for relation "part_ee_ff1" violates partition constraint 229DETAIL: Failing row contains (cc, 1). 230-- ok 231insert into part_ee_ff1 values ('ff', 1); 232insert into part_ee_ff2 values ('ff', 11); 233-- Check tuple routing for partitioned tables 234-- fail 235insert into range_parted values ('a', 0); 236ERROR: no partition of relation "range_parted" found for row 237DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 0). 238-- ok 239insert into range_parted values ('a', 1); 240insert into range_parted values ('a', 10); 241-- fail 242insert into range_parted values ('a', 20); 243ERROR: no partition of relation "range_parted" found for row 244DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 20). 245-- ok 246insert into range_parted values ('b', 1); 247insert into range_parted values ('b', 10); 248-- fail (partition key (b+0) is null) 249insert into range_parted values ('a'); 250ERROR: no partition of relation "range_parted" found for row 251DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, null). 252select tableoid::regclass, * from range_parted; 253 tableoid | a | b 254----------+---+---- 255 part1 | a | 1 256 part1 | a | 1 257 part2 | a | 10 258 part3 | b | 1 259 part4 | b | 10 260 part4 | b | 10 261(6 rows) 262 263-- ok 264insert into list_parted values (null, 1); 265insert into list_parted (a) values ('aA'); 266-- fail (partition of part_ee_ff not found in both cases) 267insert into list_parted values ('EE', 0); 268ERROR: no partition of relation "part_ee_ff" found for row 269DETAIL: Partition key of the failing row contains (b) = (0). 270insert into part_ee_ff values ('EE', 0); 271ERROR: no partition of relation "part_ee_ff" found for row 272DETAIL: Partition key of the failing row contains (b) = (0). 273-- ok 274insert into list_parted values ('EE', 1); 275insert into part_ee_ff values ('EE', 10); 276select tableoid::regclass, * from list_parted; 277 tableoid | a | b 278-------------+----+---- 279 part_aa_bb | aA | 280 part_cc_dd | cC | 1 281 part_null | | 0 282 part_null | | 1 283 part_ee_ff1 | ff | 1 284 part_ee_ff1 | EE | 1 285 part_ee_ff2 | ff | 11 286 part_ee_ff2 | EE | 10 287(8 rows) 288 289-- some more tests to exercise tuple-routing with multi-level partitioning 290create table part_gg partition of list_parted for values in ('gg') partition by range (b); 291create table part_gg1 partition of part_gg for values from (minvalue) to (1); 292create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); 293create table part_gg2_1 partition of part_gg2 for values from (1) to (5); 294create table part_gg2_2 partition of part_gg2 for values from (5) to (10); 295create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b); 296create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25); 297create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30); 298truncate list_parted; 299insert into list_parted values ('aa'), ('cc'); 300insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a); 301insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); 302insert into list_parted (b) values (1); 303select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1; 304 tableoid | a | min_b | max_b 305---------------+----+-------+------- 306 part_aa_bb | aa | | 307 part_cc_dd | cc | | 308 part_ee_ff1 | Ff | 1 | 9 309 part_ee_ff2 | Ff | 10 | 19 310 part_ee_ff3_1 | Ff | 20 | 24 311 part_ee_ff3_2 | Ff | 25 | 29 312 part_gg2_1 | gg | 1 | 4 313 part_gg2_2 | gg | 5 | 9 314 part_null | | 1 | 1 315(9 rows) 316 317-- cleanup 318drop table range_parted, list_parted; 319-- more tests for certain multi-level partitioning scenarios 320create table mlparted (a int, b int) partition by range (a, b); 321create table mlparted1 (b int not null, a int not null) partition by range ((b+0)); 322create table mlparted11 (like mlparted1); 323alter table mlparted11 drop a; 324alter table mlparted11 add a int; 325alter table mlparted11 drop a; 326alter table mlparted11 add a int not null; 327-- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11 328select attrelid::regclass, attname, attnum 329from pg_attribute 330where attname = 'a' 331 and (attrelid = 'mlparted'::regclass 332 or attrelid = 'mlparted1'::regclass 333 or attrelid = 'mlparted11'::regclass) 334order by attrelid::regclass::text; 335 attrelid | attname | attnum 336------------+---------+-------- 337 mlparted | a | 1 338 mlparted1 | a | 2 339 mlparted11 | a | 4 340(3 rows) 341 342alter table mlparted1 attach partition mlparted11 for values from (2) to (5); 343alter table mlparted attach partition mlparted1 for values from (1, 2) to (1, 10); 344-- check that "(1, 2)" is correctly routed to mlparted11. 345insert into mlparted values (1, 2); 346select tableoid::regclass, * from mlparted; 347 tableoid | a | b 348------------+---+--- 349 mlparted11 | 1 | 2 350(1 row) 351 352-- check that proper message is shown after failure to route through mlparted1 353insert into mlparted (a, b) values (1, 5); 354ERROR: no partition of relation "mlparted1" found for row 355DETAIL: Partition key of the failing row contains ((b + 0)) = (5). 356truncate mlparted; 357alter table mlparted add constraint check_b check (b = 3); 358-- have a BR trigger modify the row such that the check_b is violated 359create function mlparted11_trig_fn() 360returns trigger AS 361$$ 362begin 363 NEW.b := 4; 364 return NEW; 365end; 366$$ 367language plpgsql; 368create trigger mlparted11_trig before insert ON mlparted11 369 for each row execute procedure mlparted11_trig_fn(); 370-- check that the correct row is shown when constraint check_b fails after 371-- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due 372-- to the BR trigger mlparted11_trig_fn) 373insert into mlparted values (1, 2); 374ERROR: new row for relation "mlparted11" violates check constraint "check_b" 375DETAIL: Failing row contains (1, 4). 376drop trigger mlparted11_trig on mlparted11; 377drop function mlparted11_trig_fn(); 378-- check that inserting into an internal partition successfully results in 379-- checking its partition constraint before inserting into the leaf partition 380-- selected by tuple-routing 381insert into mlparted1 (a, b) values (2, 3); 382ERROR: new row for relation "mlparted1" violates partition constraint 383DETAIL: Failing row contains (3, 2). 384-- check routing error through a list partitioned table when the key is null 385create table lparted_nonullpart (a int, b char) partition by list (b); 386create table lparted_nonullpart_a partition of lparted_nonullpart for values in ('a'); 387insert into lparted_nonullpart values (1); 388ERROR: no partition of relation "lparted_nonullpart" found for row 389DETAIL: Partition key of the failing row contains (b) = (null). 390drop table lparted_nonullpart; 391-- check that RETURNING works correctly with tuple-routing 392alter table mlparted drop constraint check_b; 393create table mlparted12 partition of mlparted1 for values from (5) to (10); 394create table mlparted2 (b int not null, a int not null); 395alter table mlparted attach partition mlparted2 for values from (1, 10) to (1, 20); 396create table mlparted3 partition of mlparted for values from (1, 20) to (1, 30); 397create table mlparted4 (like mlparted); 398alter table mlparted4 drop a; 399alter table mlparted4 add a int not null; 400alter table mlparted attach partition mlparted4 for values from (1, 30) to (1, 40); 401with ins (a, b, c) as 402 (insert into mlparted (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *) 403 select a, b, min(c), max(c) from ins group by a, b order by 1; 404 a | b | min | max 405------------+---+-----+----- 406 mlparted11 | 1 | 2 | 4 407 mlparted12 | 1 | 5 | 9 408 mlparted2 | 1 | 10 | 19 409 mlparted3 | 1 | 20 | 29 410 mlparted4 | 1 | 30 | 39 411(5 rows) 412 413alter table mlparted add c text; 414create table mlparted5 (c text, a int not null, b int not null) partition by list (c); 415create table mlparted5a (a int not null, c text, b int not null); 416alter table mlparted5 attach partition mlparted5a for values in ('a'); 417alter table mlparted attach partition mlparted5 for values from (1, 40) to (1, 50); 418alter table mlparted add constraint check_b check (a = 1 and b < 45); 419insert into mlparted values (1, 45, 'a'); 420ERROR: new row for relation "mlparted5a" violates check constraint "check_b" 421DETAIL: Failing row contains (1, 45, a). 422create function mlparted5abrtrig_func() returns trigger as $$ begin new.c = 'b'; return new; end; $$ language plpgsql; 423create trigger mlparted5abrtrig before insert on mlparted5a for each row execute procedure mlparted5abrtrig_func(); 424insert into mlparted5 (a, b, c) values (1, 40, 'a'); 425ERROR: new row for relation "mlparted5a" violates partition constraint 426DETAIL: Failing row contains (b, 1, 40). 427drop table mlparted5; 428-- check that message shown after failure to find a partition shows the 429-- appropriate key description (or none) in various situations 430create table key_desc (a int, b int) partition by list ((a+0)); 431create table key_desc_1 partition of key_desc for values in (1) partition by range (b); 432create user regress_insert_other_user; 433grant select (a) on key_desc_1 to regress_insert_other_user; 434grant insert on key_desc to regress_insert_other_user; 435set role regress_insert_other_user; 436-- no key description is shown 437insert into key_desc values (1, 1); 438ERROR: no partition of relation "key_desc_1" found for row 439reset role; 440grant select (b) on key_desc_1 to regress_insert_other_user; 441set role regress_insert_other_user; 442-- key description (b)=(1) is now shown 443insert into key_desc values (1, 1); 444ERROR: no partition of relation "key_desc_1" found for row 445DETAIL: Partition key of the failing row contains (b) = (1). 446-- key description is not shown if key contains expression 447insert into key_desc values (2, 1); 448ERROR: no partition of relation "key_desc" found for row 449reset role; 450revoke all on key_desc from regress_insert_other_user; 451revoke all on key_desc_1 from regress_insert_other_user; 452drop role regress_insert_other_user; 453drop table key_desc, key_desc_1; 454-- test minvalue/maxvalue restrictions 455create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 456create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue); 457ERROR: every bound following MINVALUE must also be MINVALUE 458LINE 1: ...partition of mcrparted for values from (minvalue, 0, 0) to (... 459 ^ 460create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue); 461ERROR: every bound following MAXVALUE must also be MAXVALUE 462LINE 1: ...r values from (10, 6, minvalue) to (10, maxvalue, minvalue); 463 ^ 464create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue); 465ERROR: every bound following MINVALUE must also be MINVALUE 466LINE 1: ...ition of mcrparted for values from (21, minvalue, 0) to (30,... 467 ^ 468-- check multi-column range partitioning expression enforces the same 469-- constraint as what tuple-routing would determine it to be 470create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue); 471create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10); 472create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, maxvalue); 473create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 474create table mcrparted4 partition of mcrparted for values from (21, minvalue, minvalue) to (30, 20, maxvalue); 475create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, maxvalue, maxvalue); 476-- routed to mcrparted0 477insert into mcrparted values (0, 1, 1); 478insert into mcrparted0 values (0, 1, 1); 479-- routed to mcparted1 480insert into mcrparted values (9, 1000, 1); 481insert into mcrparted1 values (9, 1000, 1); 482insert into mcrparted values (10, 5, -1); 483insert into mcrparted1 values (10, 5, -1); 484insert into mcrparted values (2, 1, 0); 485insert into mcrparted1 values (2, 1, 0); 486-- routed to mcparted2 487insert into mcrparted values (10, 6, 1000); 488insert into mcrparted2 values (10, 6, 1000); 489insert into mcrparted values (10, 1000, 1000); 490insert into mcrparted2 values (10, 1000, 1000); 491-- no partition exists, nor does mcrparted3 accept it 492insert into mcrparted values (11, 1, -1); 493ERROR: no partition of relation "mcrparted" found for row 494DETAIL: Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1). 495insert into mcrparted3 values (11, 1, -1); 496ERROR: new row for relation "mcrparted3" violates partition constraint 497DETAIL: Failing row contains (11, 1, -1). 498-- routed to mcrparted5 499insert into mcrparted values (30, 21, 20); 500insert into mcrparted5 values (30, 21, 20); 501insert into mcrparted4 values (30, 21, 20); -- error 502ERROR: new row for relation "mcrparted4" violates partition constraint 503DETAIL: Failing row contains (30, 21, 20). 504-- check rows 505select tableoid::regclass::text, * from mcrparted order by 1; 506 tableoid | a | b | c 507------------+----+------+------ 508 mcrparted0 | 0 | 1 | 1 509 mcrparted0 | 0 | 1 | 1 510 mcrparted1 | 9 | 1000 | 1 511 mcrparted1 | 9 | 1000 | 1 512 mcrparted1 | 10 | 5 | -1 513 mcrparted1 | 10 | 5 | -1 514 mcrparted1 | 2 | 1 | 0 515 mcrparted1 | 2 | 1 | 0 516 mcrparted2 | 10 | 6 | 1000 517 mcrparted2 | 10 | 6 | 1000 518 mcrparted2 | 10 | 1000 | 1000 519 mcrparted2 | 10 | 1000 | 1000 520 mcrparted5 | 30 | 21 | 20 521 mcrparted5 | 30 | 21 | 20 522(14 rows) 523 524-- cleanup 525drop table mcrparted; 526-- check that a BR constraint can't make partition contain violating rows 527create table brtrigpartcon (a int, b text) partition by list (a); 528create table brtrigpartcon1 partition of brtrigpartcon for values in (1); 529create or replace function brtrigpartcon1trigf() returns trigger as $$begin new.a := 2; return new; end$$ language plpgsql; 530create trigger brtrigpartcon1trig before insert on brtrigpartcon1 for each row execute procedure brtrigpartcon1trigf(); 531insert into brtrigpartcon values (1, 'hi there'); 532ERROR: new row for relation "brtrigpartcon1" violates partition constraint 533DETAIL: Failing row contains (2, hi there). 534insert into brtrigpartcon1 values (1, 'hi there'); 535ERROR: new row for relation "brtrigpartcon1" violates partition constraint 536DETAIL: Failing row contains (2, hi there). 537-- check that the message shows the appropriate column description in a 538-- situation where the partitioned table is not the primary ModifyTable node 539create table inserttest3 (f1 text default 'foo', f2 text default 'bar', f3 int); 540create role regress_coldesc_role; 541grant insert on inserttest3 to regress_coldesc_role; 542grant insert on brtrigpartcon to regress_coldesc_role; 543revoke select on brtrigpartcon from regress_coldesc_role; 544set role regress_coldesc_role; 545with result as (insert into brtrigpartcon values (1, 'hi there') returning 1) 546 insert into inserttest3 (f3) select * from result; 547ERROR: new row for relation "brtrigpartcon1" violates partition constraint 548DETAIL: Failing row contains (a, b) = (2, hi there). 549reset role; 550-- cleanup 551revoke all on inserttest3 from regress_coldesc_role; 552revoke all on brtrigpartcon from regress_coldesc_role; 553drop role regress_coldesc_role; 554drop table inserttest3; 555drop table brtrigpartcon; 556drop function brtrigpartcon1trigf(); 557-- check that "do nothing" BR triggers work with tuple-routing (this checks 558-- that estate->es_result_relation_info is appropriately set/reset for each 559-- routed tuple) 560create table donothingbrtrig_test (a int, b text) partition by list (a); 561create table donothingbrtrig_test1 (b text, a int); 562create table donothingbrtrig_test2 (c text, b text, a int); 563alter table donothingbrtrig_test2 drop column c; 564create or replace function donothingbrtrig_func() returns trigger as $$begin raise notice 'b: %', new.b; return NULL; end$$ language plpgsql; 565create trigger donothingbrtrig1 before insert on donothingbrtrig_test1 for each row execute procedure donothingbrtrig_func(); 566create trigger donothingbrtrig2 before insert on donothingbrtrig_test2 for each row execute procedure donothingbrtrig_func(); 567alter table donothingbrtrig_test attach partition donothingbrtrig_test1 for values in (1); 568alter table donothingbrtrig_test attach partition donothingbrtrig_test2 for values in (2); 569insert into donothingbrtrig_test values (1, 'foo'), (2, 'bar'); 570NOTICE: b: foo 571NOTICE: b: bar 572copy donothingbrtrig_test from stdout; 573NOTICE: b: baz 574NOTICE: b: qux 575select tableoid::regclass, * from donothingbrtrig_test; 576 tableoid | a | b 577----------+---+--- 578(0 rows) 579 580-- cleanup 581drop table donothingbrtrig_test; 582drop function donothingbrtrig_func(); 583-- check multi-column range partitioning with minvalue/maxvalue constraints 584create table mcrparted (a text, b int) partition by range(a, b); 585create table mcrparted1_lt_b partition of mcrparted for values from (minvalue, minvalue) to ('b', minvalue); 586create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue); 587create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue); 588create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0); 589create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10); 590create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue); 591create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); 592create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); 593\d+ mcrparted 594 Table "public.mcrparted" 595 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 596--------+---------+-----------+----------+---------+----------+--------------+------------- 597 a | text | | | | extended | | 598 b | integer | | | | plain | | 599Partition key: RANGE (a, b) 600Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE), 601 mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE), 602 mcrparted3_c_to_common FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE), 603 mcrparted4_common_lt_0 FOR VALUES FROM ('common', MINVALUE) TO ('common', 0), 604 mcrparted5_common_0_to_10 FOR VALUES FROM ('common', 0) TO ('common', 10), 605 mcrparted6_common_ge_10 FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE), 606 mcrparted7_gt_common_lt_d FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE), 607 mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) 608 609\d+ mcrparted1_lt_b 610 Table "public.mcrparted1_lt_b" 611 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 612--------+---------+-----------+----------+---------+----------+--------------+------------- 613 a | text | | | | extended | | 614 b | integer | | | | plain | | 615Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE) 616Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text)) 617 618\d+ mcrparted2_b 619 Table "public.mcrparted2_b" 620 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 621--------+---------+-----------+----------+---------+----------+--------------+------------- 622 a | text | | | | extended | | 623 b | integer | | | | plain | | 624Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE) 625Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text)) 626 627\d+ mcrparted3_c_to_common 628 Table "public.mcrparted3_c_to_common" 629 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 630--------+---------+-----------+----------+---------+----------+--------------+------------- 631 a | text | | | | extended | | 632 b | integer | | | | plain | | 633Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE) 634Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text)) 635 636\d+ mcrparted4_common_lt_0 637 Table "public.mcrparted4_common_lt_0" 638 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 639--------+---------+-----------+----------+---------+----------+--------------+------------- 640 a | text | | | | extended | | 641 b | integer | | | | plain | | 642Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0) 643Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0)) 644 645\d+ mcrparted5_common_0_to_10 646 Table "public.mcrparted5_common_0_to_10" 647 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 648--------+---------+-----------+----------+---------+----------+--------------+------------- 649 a | text | | | | extended | | 650 b | integer | | | | plain | | 651Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10) 652Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10)) 653 654\d+ mcrparted6_common_ge_10 655 Table "public.mcrparted6_common_ge_10" 656 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 657--------+---------+-----------+----------+---------+----------+--------------+------------- 658 a | text | | | | extended | | 659 b | integer | | | | plain | | 660Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE) 661Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10)) 662 663\d+ mcrparted7_gt_common_lt_d 664 Table "public.mcrparted7_gt_common_lt_d" 665 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 666--------+---------+-----------+----------+---------+----------+--------------+------------- 667 a | text | | | | extended | | 668 b | integer | | | | plain | | 669Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE) 670Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text)) 671 672\d+ mcrparted8_ge_d 673 Table "public.mcrparted8_ge_d" 674 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 675--------+---------+-----------+----------+---------+----------+--------------+------------- 676 a | text | | | | extended | | 677 b | integer | | | | plain | | 678Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) 679Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text)) 680 681insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), 682 ('comm', -10), ('common', -10), ('common', 0), ('common', 10), 683 ('commons', 0), ('d', -10), ('e', 0); 684select tableoid::regclass, * from mcrparted order by a, b; 685 tableoid | a | b 686---------------------------+---------+----- 687 mcrparted1_lt_b | aaa | 0 688 mcrparted2_b | b | 0 689 mcrparted2_b | bz | 10 690 mcrparted3_c_to_common | c | -10 691 mcrparted3_c_to_common | comm | -10 692 mcrparted4_common_lt_0 | common | -10 693 mcrparted5_common_0_to_10 | common | 0 694 mcrparted6_common_ge_10 | common | 10 695 mcrparted7_gt_common_lt_d | commons | 0 696 mcrparted8_ge_d | d | -10 697 mcrparted8_ge_d | e | 0 698(11 rows) 699 700drop table mcrparted; 701-- check that wholerow vars in the RETURNING list work with partitioned tables 702create table returningwrtest (a int) partition by list (a); 703create table returningwrtest1 partition of returningwrtest for values in (1); 704insert into returningwrtest values (1) returning returningwrtest; 705 returningwrtest 706----------------- 707 (1) 708(1 row) 709 710-- check also that the wholerow vars in RETURNING list are converted as needed 711alter table returningwrtest add b text; 712create table returningwrtest2 (b text, c int, a int); 713alter table returningwrtest2 drop c; 714alter table returningwrtest attach partition returningwrtest2 for values in (2); 715insert into returningwrtest values (2, 'foo') returning returningwrtest; 716 returningwrtest 717----------------- 718 (2,foo) 719(1 row) 720 721drop table returningwrtest; 722