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)); 168-- no partitions, so fail 169insert into range_parted values ('a', 11); 170ERROR: no partition of relation "range_parted" found for row 171DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 11). 172create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); 173create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); 174create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); 175create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); 176-- fail 177insert into part1 values ('a', 11); 178ERROR: new row for relation "part1" violates partition constraint 179DETAIL: Failing row contains (a, 11). 180insert into part1 values ('b', 1); 181ERROR: new row for relation "part1" violates partition constraint 182DETAIL: Failing row contains (b, 1). 183-- ok 184insert into part1 values ('a', 1); 185-- fail 186insert into part4 values ('b', 21); 187ERROR: new row for relation "part4" violates partition constraint 188DETAIL: Failing row contains (b, 21). 189insert into part4 values ('a', 10); 190ERROR: new row for relation "part4" violates partition constraint 191DETAIL: Failing row contains (a, 10). 192-- ok 193insert into part4 values ('b', 10); 194-- fail (partition key a has a NOT NULL constraint) 195insert into part1 values (null); 196ERROR: new row for relation "part1" violates partition constraint 197DETAIL: Failing row contains (null, null). 198-- fail (expression key (b+0) cannot be null either) 199insert into part1 values (1); 200ERROR: new row for relation "part1" violates partition constraint 201DETAIL: Failing row contains (1, null). 202create table list_parted ( 203 a text, 204 b int 205) partition by list (lower(a)); 206create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); 207create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); 208create table part_null partition of list_parted FOR VALUES IN (null); 209-- fail 210insert into part_aa_bb values ('cc', 1); 211ERROR: new row for relation "part_aa_bb" violates partition constraint 212DETAIL: Failing row contains (cc, 1). 213insert into part_aa_bb values ('AAa', 1); 214ERROR: new row for relation "part_aa_bb" violates partition constraint 215DETAIL: Failing row contains (AAa, 1). 216insert into part_aa_bb values (null); 217ERROR: new row for relation "part_aa_bb" violates partition constraint 218DETAIL: Failing row contains (null, null). 219-- ok 220insert into part_cc_dd values ('cC', 1); 221insert into part_null values (null, 0); 222-- check in case of multi-level partitioned table 223create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); 224create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); 225create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); 226-- test default partition 227create table part_default partition of list_parted default; 228-- Negative test: a row, which would fit in other partition, does not fit 229-- default partition, even when inserted directly 230insert into part_default values ('aa', 2); 231ERROR: new row for relation "part_default" violates partition constraint 232DETAIL: Failing row contains (aa, 2). 233insert into part_default values (null, 2); 234ERROR: new row for relation "part_default" violates partition constraint 235DETAIL: Failing row contains (null, 2). 236-- ok 237insert into part_default values ('Zz', 2); 238-- test if default partition works as expected for multi-level partitioned 239-- table as well as when default partition itself is further partitioned 240drop table part_default; 241create table part_xx_yy partition of list_parted for values in ('xx', 'yy') partition by list (a); 242create table part_xx_yy_p1 partition of part_xx_yy for values in ('xx'); 243create table part_xx_yy_defpart partition of part_xx_yy default; 244create table part_default partition of list_parted default partition by range(b); 245create table part_default_p1 partition of part_default for values from (20) to (30); 246create table part_default_p2 partition of part_default for values from (30) to (40); 247-- fail 248insert into part_ee_ff1 values ('EE', 11); 249ERROR: new row for relation "part_ee_ff1" violates partition constraint 250DETAIL: Failing row contains (EE, 11). 251insert into part_default_p2 values ('gg', 43); 252ERROR: new row for relation "part_default_p2" violates partition constraint 253DETAIL: Failing row contains (gg, 43). 254-- fail (even the parent's, ie, part_ee_ff's partition constraint applies) 255insert into part_ee_ff1 values ('cc', 1); 256ERROR: new row for relation "part_ee_ff1" violates partition constraint 257DETAIL: Failing row contains (cc, 1). 258insert into part_default values ('gg', 43); 259ERROR: no partition of relation "part_default" found for row 260DETAIL: Partition key of the failing row contains (b) = (43). 261-- ok 262insert into part_ee_ff1 values ('ff', 1); 263insert into part_ee_ff2 values ('ff', 11); 264insert into part_default_p1 values ('cd', 25); 265insert into part_default_p2 values ('de', 35); 266insert into list_parted values ('ab', 21); 267insert into list_parted values ('xx', 1); 268insert into list_parted values ('yy', 2); 269select tableoid::regclass, * from list_parted; 270 tableoid | a | b 271--------------------+----+---- 272 part_cc_dd | cC | 1 273 part_ee_ff1 | ff | 1 274 part_ee_ff2 | ff | 11 275 part_xx_yy_p1 | xx | 1 276 part_xx_yy_defpart | yy | 2 277 part_null | | 0 278 part_default_p1 | cd | 25 279 part_default_p1 | ab | 21 280 part_default_p2 | de | 35 281(9 rows) 282 283-- Check tuple routing for partitioned tables 284-- fail 285insert into range_parted values ('a', 0); 286ERROR: no partition of relation "range_parted" found for row 287DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 0). 288-- ok 289insert into range_parted values ('a', 1); 290insert into range_parted values ('a', 10); 291-- fail 292insert into range_parted values ('a', 20); 293ERROR: no partition of relation "range_parted" found for row 294DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, 20). 295-- ok 296insert into range_parted values ('b', 1); 297insert into range_parted values ('b', 10); 298-- fail (partition key (b+0) is null) 299insert into range_parted values ('a'); 300ERROR: no partition of relation "range_parted" found for row 301DETAIL: Partition key of the failing row contains (a, (b + 0)) = (a, null). 302-- Check default partition 303create table part_def partition of range_parted default; 304-- fail 305insert into part_def values ('b', 10); 306ERROR: new row for relation "part_def" violates partition constraint 307DETAIL: Failing row contains (b, 10). 308-- ok 309insert into part_def values ('c', 10); 310insert into range_parted values (null, null); 311insert into range_parted values ('a', null); 312insert into range_parted values (null, 19); 313insert into range_parted values ('b', 20); 314select tableoid::regclass, * from range_parted; 315 tableoid | a | b 316----------+---+---- 317 part1 | a | 1 318 part1 | a | 1 319 part2 | a | 10 320 part3 | b | 1 321 part4 | b | 10 322 part4 | b | 10 323 part_def | c | 10 324 part_def | | 325 part_def | a | 326 part_def | | 19 327 part_def | b | 20 328(11 rows) 329 330-- ok 331insert into list_parted values (null, 1); 332insert into list_parted (a) values ('aA'); 333-- fail (partition of part_ee_ff not found in both cases) 334insert into list_parted values ('EE', 0); 335ERROR: no partition of relation "part_ee_ff" found for row 336DETAIL: Partition key of the failing row contains (b) = (0). 337insert into part_ee_ff values ('EE', 0); 338ERROR: no partition of relation "part_ee_ff" found for row 339DETAIL: Partition key of the failing row contains (b) = (0). 340-- ok 341insert into list_parted values ('EE', 1); 342insert into part_ee_ff values ('EE', 10); 343select tableoid::regclass, * from list_parted; 344 tableoid | a | b 345--------------------+----+---- 346 part_aa_bb | aA | 347 part_cc_dd | cC | 1 348 part_ee_ff1 | ff | 1 349 part_ee_ff1 | EE | 1 350 part_ee_ff2 | ff | 11 351 part_ee_ff2 | EE | 10 352 part_xx_yy_p1 | xx | 1 353 part_xx_yy_defpart | yy | 2 354 part_null | | 0 355 part_null | | 1 356 part_default_p1 | cd | 25 357 part_default_p1 | ab | 21 358 part_default_p2 | de | 35 359(13 rows) 360 361-- some more tests to exercise tuple-routing with multi-level partitioning 362create table part_gg partition of list_parted for values in ('gg') partition by range (b); 363create table part_gg1 partition of part_gg for values from (minvalue) to (1); 364create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); 365create table part_gg2_1 partition of part_gg2 for values from (1) to (5); 366create table part_gg2_2 partition of part_gg2 for values from (5) to (10); 367create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b); 368create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25); 369create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30); 370truncate list_parted; 371insert into list_parted values ('aa'), ('cc'); 372insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a); 373insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); 374insert into list_parted (b) values (1); 375select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1; 376 tableoid | a | min_b | max_b 377---------------+----+-------+------- 378 part_aa_bb | aa | | 379 part_cc_dd | cc | | 380 part_ee_ff1 | Ff | 1 | 9 381 part_ee_ff2 | Ff | 10 | 19 382 part_ee_ff3_1 | Ff | 20 | 24 383 part_ee_ff3_2 | Ff | 25 | 29 384 part_gg2_1 | gg | 1 | 4 385 part_gg2_2 | gg | 5 | 9 386 part_null | | 1 | 1 387(9 rows) 388 389-- direct partition inserts should check hash partition bound constraint 390-- Use hand-rolled hash functions and operator classes to get predictable 391-- result on different machines. The hash function for int4 simply returns 392-- the sum of the values passed to it and the one for text returns the length 393-- of the non-empty string value passed to it or 0. 394create or replace function part_hashint4_noop(value int4, seed int8) 395returns int8 as $$ 396select value + seed; 397$$ language sql immutable; 398create operator class part_test_int4_ops 399for type int4 400using hash as 401operator 1 =, 402function 2 part_hashint4_noop(int4, int8); 403create or replace function part_hashtext_length(value text, seed int8) 404RETURNS int8 AS $$ 405select length(coalesce(value, ''))::int8 406$$ language sql immutable; 407create operator class part_test_text_ops 408for type text 409using hash as 410operator 1 =, 411function 2 part_hashtext_length(text, int8); 412create table hash_parted ( 413 a int 414) partition by hash (a part_test_int4_ops); 415create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); 416create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); 417create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); 418create table hpart3 partition of hash_parted for values with (modulus 4, remainder 3); 419insert into hash_parted values(generate_series(1,10)); 420-- direct insert of values divisible by 4 - ok; 421insert into hpart0 values(12),(16); 422-- fail; 423insert into hpart0 values(11); 424ERROR: new row for relation "hpart0" violates partition constraint 425DETAIL: Failing row contains (11). 426-- 11 % 4 -> 3 remainder i.e. valid data for hpart3 partition 427insert into hpart3 values(11); 428-- view data 429select tableoid::regclass as part, a, a%4 as "remainder = a % 4" 430from hash_parted order by part; 431 part | a | remainder = a % 4 432--------+----+------------------- 433 hpart0 | 4 | 0 434 hpart0 | 8 | 0 435 hpart0 | 12 | 0 436 hpart0 | 16 | 0 437 hpart1 | 1 | 1 438 hpart1 | 5 | 1 439 hpart1 | 9 | 1 440 hpart2 | 2 | 2 441 hpart2 | 6 | 2 442 hpart2 | 10 | 2 443 hpart3 | 3 | 3 444 hpart3 | 7 | 3 445 hpart3 | 11 | 3 446(13 rows) 447 448-- test \d+ output on a table which has both partitioned and unpartitioned 449-- partitions 450\d+ list_parted 451 Table "public.list_parted" 452 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 453--------+---------+-----------+----------+---------+----------+--------------+------------- 454 a | text | | | | extended | | 455 b | integer | | | | plain | | 456Partition key: LIST (lower(a)) 457Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), 458 part_cc_dd FOR VALUES IN ('cc', 'dd'), 459 part_ee_ff FOR VALUES IN ('ee', 'ff'), PARTITIONED, 460 part_gg FOR VALUES IN ('gg'), PARTITIONED, 461 part_null FOR VALUES IN (NULL), 462 part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED, 463 part_default DEFAULT, PARTITIONED 464 465-- cleanup 466drop table range_parted, list_parted; 467drop table hash_parted; 468-- test that a default partition added as the first partition accepts any value 469-- including null 470create table list_parted (a int) partition by list (a); 471create table part_default partition of list_parted default; 472\d+ part_default 473 Table "public.part_default" 474 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 475--------+---------+-----------+----------+---------+---------+--------------+------------- 476 a | integer | | | | plain | | 477Partition of: list_parted DEFAULT 478No partition constraint 479 480insert into part_default values (null); 481insert into part_default values (1); 482insert into part_default values (-1); 483select tableoid::regclass, a from list_parted; 484 tableoid | a 485--------------+---- 486 part_default | 487 part_default | 1 488 part_default | -1 489(3 rows) 490 491-- cleanup 492drop table list_parted; 493-- more tests for certain multi-level partitioning scenarios 494create table mlparted (a int, b int) partition by range (a, b); 495create table mlparted1 (b int not null, a int not null) partition by range ((b+0)); 496create table mlparted11 (like mlparted1); 497alter table mlparted11 drop a; 498alter table mlparted11 add a int; 499alter table mlparted11 drop a; 500alter table mlparted11 add a int not null; 501-- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11 502select attrelid::regclass, attname, attnum 503from pg_attribute 504where attname = 'a' 505 and (attrelid = 'mlparted'::regclass 506 or attrelid = 'mlparted1'::regclass 507 or attrelid = 'mlparted11'::regclass) 508order by attrelid::regclass::text; 509 attrelid | attname | attnum 510------------+---------+-------- 511 mlparted | a | 1 512 mlparted1 | a | 2 513 mlparted11 | a | 4 514(3 rows) 515 516alter table mlparted1 attach partition mlparted11 for values from (2) to (5); 517alter table mlparted attach partition mlparted1 for values from (1, 2) to (1, 10); 518-- check that "(1, 2)" is correctly routed to mlparted11. 519insert into mlparted values (1, 2); 520select tableoid::regclass, * from mlparted; 521 tableoid | a | b 522------------+---+--- 523 mlparted11 | 1 | 2 524(1 row) 525 526-- check that proper message is shown after failure to route through mlparted1 527insert into mlparted (a, b) values (1, 5); 528ERROR: no partition of relation "mlparted1" found for row 529DETAIL: Partition key of the failing row contains ((b + 0)) = (5). 530truncate mlparted; 531alter table mlparted add constraint check_b check (b = 3); 532-- have a BR trigger modify the row such that the check_b is violated 533create function mlparted11_trig_fn() 534returns trigger AS 535$$ 536begin 537 NEW.b := 4; 538 return NEW; 539end; 540$$ 541language plpgsql; 542create trigger mlparted11_trig before insert ON mlparted11 543 for each row execute procedure mlparted11_trig_fn(); 544-- check that the correct row is shown when constraint check_b fails after 545-- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due 546-- to the BR trigger mlparted11_trig_fn) 547insert into mlparted values (1, 2); 548ERROR: new row for relation "mlparted11" violates check constraint "check_b" 549DETAIL: Failing row contains (1, 4). 550drop trigger mlparted11_trig on mlparted11; 551drop function mlparted11_trig_fn(); 552-- check that inserting into an internal partition successfully results in 553-- checking its partition constraint before inserting into the leaf partition 554-- selected by tuple-routing 555insert into mlparted1 (a, b) values (2, 3); 556ERROR: new row for relation "mlparted1" violates partition constraint 557DETAIL: Failing row contains (3, 2). 558-- check routing error through a list partitioned table when the key is null 559create table lparted_nonullpart (a int, b char) partition by list (b); 560create table lparted_nonullpart_a partition of lparted_nonullpart for values in ('a'); 561insert into lparted_nonullpart values (1); 562ERROR: no partition of relation "lparted_nonullpart" found for row 563DETAIL: Partition key of the failing row contains (b) = (null). 564drop table lparted_nonullpart; 565-- check that RETURNING works correctly with tuple-routing 566alter table mlparted drop constraint check_b; 567create table mlparted12 partition of mlparted1 for values from (5) to (10); 568create table mlparted2 (b int not null, a int not null); 569alter table mlparted attach partition mlparted2 for values from (1, 10) to (1, 20); 570create table mlparted3 partition of mlparted for values from (1, 20) to (1, 30); 571create table mlparted4 (like mlparted); 572alter table mlparted4 drop a; 573alter table mlparted4 add a int not null; 574alter table mlparted attach partition mlparted4 for values from (1, 30) to (1, 40); 575with ins (a, b, c) as 576 (insert into mlparted (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *) 577 select a, b, min(c), max(c) from ins group by a, b order by 1; 578 a | b | min | max 579------------+---+-----+----- 580 mlparted11 | 1 | 2 | 4 581 mlparted12 | 1 | 5 | 9 582 mlparted2 | 1 | 10 | 19 583 mlparted3 | 1 | 20 | 29 584 mlparted4 | 1 | 30 | 39 585(5 rows) 586 587alter table mlparted add c text; 588create table mlparted5 (c text, a int not null, b int not null) partition by list (c); 589create table mlparted5a (a int not null, c text, b int not null); 590alter table mlparted5 attach partition mlparted5a for values in ('a'); 591alter table mlparted attach partition mlparted5 for values from (1, 40) to (1, 50); 592alter table mlparted add constraint check_b check (a = 1 and b < 45); 593insert into mlparted values (1, 45, 'a'); 594ERROR: new row for relation "mlparted5a" violates check constraint "check_b" 595DETAIL: Failing row contains (1, 45, a). 596create function mlparted5abrtrig_func() returns trigger as $$ begin new.c = 'b'; return new; end; $$ language plpgsql; 597create trigger mlparted5abrtrig before insert on mlparted5a for each row execute procedure mlparted5abrtrig_func(); 598insert into mlparted5 (a, b, c) values (1, 40, 'a'); 599ERROR: new row for relation "mlparted5a" violates partition constraint 600DETAIL: Failing row contains (b, 1, 40). 601drop table mlparted5; 602alter table mlparted drop constraint check_b; 603-- Check multi-level default partition 604create table mlparted_def partition of mlparted default partition by range(a); 605create table mlparted_def1 partition of mlparted_def for values from (40) to (50); 606create table mlparted_def2 partition of mlparted_def for values from (50) to (60); 607insert into mlparted values (40, 100); 608insert into mlparted_def1 values (42, 100); 609insert into mlparted_def2 values (54, 50); 610-- fail 611insert into mlparted values (70, 100); 612ERROR: no partition of relation "mlparted_def" found for row 613DETAIL: Partition key of the failing row contains (a) = (70). 614insert into mlparted_def1 values (52, 50); 615ERROR: new row for relation "mlparted_def1" violates partition constraint 616DETAIL: Failing row contains (52, 50, null). 617insert into mlparted_def2 values (34, 50); 618ERROR: new row for relation "mlparted_def2" violates partition constraint 619DETAIL: Failing row contains (34, 50, null). 620-- ok 621create table mlparted_defd partition of mlparted_def default; 622insert into mlparted values (70, 100); 623select tableoid::regclass, * from mlparted_def; 624 tableoid | a | b | c 625---------------+----+-----+--- 626 mlparted_def1 | 40 | 100 | 627 mlparted_def1 | 42 | 100 | 628 mlparted_def2 | 54 | 50 | 629 mlparted_defd | 70 | 100 | 630(4 rows) 631 632-- Check multi-level tuple routing with attributes dropped from the 633-- top-most parent. First remove the last attribute. 634alter table mlparted add d int, add e int; 635alter table mlparted drop e; 636create table mlparted5 partition of mlparted 637 for values from (1, 40) to (1, 50) partition by range (c); 638create table mlparted5_ab partition of mlparted5 639 for values from ('a') to ('c') partition by list (c); 640-- This partitioned table should remain with no partitions. 641create table mlparted5_cd partition of mlparted5 642 for values from ('c') to ('e') partition by list (c); 643create table mlparted5_a partition of mlparted5_ab for values in ('a'); 644create table mlparted5_b (d int, b int, c text, a int); 645alter table mlparted5_ab attach partition mlparted5_b for values in ('b'); 646truncate mlparted; 647insert into mlparted values (1, 2, 'a', 1); 648insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 649insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 650insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 651ERROR: no partition of relation "mlparted5_cd" found for row 652DETAIL: Partition key of the failing row contains (c) = (c). 653insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 654ERROR: no partition of relation "mlparted5" found for row 655DETAIL: Partition key of the failing row contains (c) = (f). 656select tableoid::regclass, * from mlparted order by a, b, c, d; 657 tableoid | a | b | c | d 658-------------+---+----+---+--- 659 mlparted11 | 1 | 2 | a | 1 660 mlparted5_a | 1 | 40 | a | 1 661 mlparted5_b | 1 | 45 | b | 1 662(3 rows) 663 664alter table mlparted drop d; 665truncate mlparted; 666-- Remove the before last attribute. 667alter table mlparted add e int, add d int; 668alter table mlparted drop e; 669insert into mlparted values (1, 2, 'a', 1); 670insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 671insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 672insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 673ERROR: no partition of relation "mlparted5_cd" found for row 674DETAIL: Partition key of the failing row contains (c) = (c). 675insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 676ERROR: no partition of relation "mlparted5" found for row 677DETAIL: Partition key of the failing row contains (c) = (f). 678select tableoid::regclass, * from mlparted order by a, b, c, d; 679 tableoid | a | b | c | d 680-------------+---+----+---+--- 681 mlparted11 | 1 | 2 | a | 1 682 mlparted5_a | 1 | 40 | a | 1 683 mlparted5_b | 1 | 45 | b | 1 684(3 rows) 685 686alter table mlparted drop d; 687drop table mlparted5; 688-- check that message shown after failure to find a partition shows the 689-- appropriate key description (or none) in various situations 690create table key_desc (a int, b int) partition by list ((a+0)); 691create table key_desc_1 partition of key_desc for values in (1) partition by range (b); 692create user regress_insert_other_user; 693grant select (a) on key_desc_1 to regress_insert_other_user; 694grant insert on key_desc to regress_insert_other_user; 695set role regress_insert_other_user; 696-- no key description is shown 697insert into key_desc values (1, 1); 698ERROR: no partition of relation "key_desc_1" found for row 699reset role; 700grant select (b) on key_desc_1 to regress_insert_other_user; 701set role regress_insert_other_user; 702-- key description (b)=(1) is now shown 703insert into key_desc values (1, 1); 704ERROR: no partition of relation "key_desc_1" found for row 705DETAIL: Partition key of the failing row contains (b) = (1). 706-- key description is not shown if key contains expression 707insert into key_desc values (2, 1); 708ERROR: no partition of relation "key_desc" found for row 709reset role; 710revoke all on key_desc from regress_insert_other_user; 711revoke all on key_desc_1 from regress_insert_other_user; 712drop role regress_insert_other_user; 713drop table key_desc, key_desc_1; 714-- test minvalue/maxvalue restrictions 715create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 716create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue); 717ERROR: every bound following MINVALUE must also be MINVALUE 718LINE 1: ...partition of mcrparted for values from (minvalue, 0, 0) to (... 719 ^ 720create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue); 721ERROR: every bound following MAXVALUE must also be MAXVALUE 722LINE 1: ...r values from (10, 6, minvalue) to (10, maxvalue, minvalue); 723 ^ 724create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue); 725ERROR: every bound following MINVALUE must also be MINVALUE 726LINE 1: ...ition of mcrparted for values from (21, minvalue, 0) to (30,... 727 ^ 728-- check multi-column range partitioning expression enforces the same 729-- constraint as what tuple-routing would determine it to be 730create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue); 731create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10); 732create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, maxvalue); 733create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 734create table mcrparted4 partition of mcrparted for values from (21, minvalue, minvalue) to (30, 20, maxvalue); 735create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, maxvalue, maxvalue); 736-- null not allowed in range partition 737insert into mcrparted values (null, null, null); 738ERROR: no partition of relation "mcrparted" found for row 739DETAIL: Partition key of the failing row contains (a, abs(b), c) = (null, null, null). 740-- routed to mcrparted0 741insert into mcrparted values (0, 1, 1); 742insert into mcrparted0 values (0, 1, 1); 743-- routed to mcparted1 744insert into mcrparted values (9, 1000, 1); 745insert into mcrparted1 values (9, 1000, 1); 746insert into mcrparted values (10, 5, -1); 747insert into mcrparted1 values (10, 5, -1); 748insert into mcrparted values (2, 1, 0); 749insert into mcrparted1 values (2, 1, 0); 750-- routed to mcparted2 751insert into mcrparted values (10, 6, 1000); 752insert into mcrparted2 values (10, 6, 1000); 753insert into mcrparted values (10, 1000, 1000); 754insert into mcrparted2 values (10, 1000, 1000); 755-- no partition exists, nor does mcrparted3 accept it 756insert into mcrparted values (11, 1, -1); 757ERROR: no partition of relation "mcrparted" found for row 758DETAIL: Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1). 759insert into mcrparted3 values (11, 1, -1); 760ERROR: new row for relation "mcrparted3" violates partition constraint 761DETAIL: Failing row contains (11, 1, -1). 762-- routed to mcrparted5 763insert into mcrparted values (30, 21, 20); 764insert into mcrparted5 values (30, 21, 20); 765insert into mcrparted4 values (30, 21, 20); -- error 766ERROR: new row for relation "mcrparted4" violates partition constraint 767DETAIL: Failing row contains (30, 21, 20). 768-- check rows 769select tableoid::regclass::text, * from mcrparted order by 1; 770 tableoid | a | b | c 771------------+----+------+------ 772 mcrparted0 | 0 | 1 | 1 773 mcrparted0 | 0 | 1 | 1 774 mcrparted1 | 9 | 1000 | 1 775 mcrparted1 | 9 | 1000 | 1 776 mcrparted1 | 10 | 5 | -1 777 mcrparted1 | 10 | 5 | -1 778 mcrparted1 | 2 | 1 | 0 779 mcrparted1 | 2 | 1 | 0 780 mcrparted2 | 10 | 6 | 1000 781 mcrparted2 | 10 | 6 | 1000 782 mcrparted2 | 10 | 1000 | 1000 783 mcrparted2 | 10 | 1000 | 1000 784 mcrparted5 | 30 | 21 | 20 785 mcrparted5 | 30 | 21 | 20 786(14 rows) 787 788-- cleanup 789drop table mcrparted; 790-- check that a BR constraint can't make partition contain violating rows 791create table brtrigpartcon (a int, b text) partition by list (a); 792create table brtrigpartcon1 partition of brtrigpartcon for values in (1); 793create or replace function brtrigpartcon1trigf() returns trigger as $$begin new.a := 2; return new; end$$ language plpgsql; 794create trigger brtrigpartcon1trig before insert on brtrigpartcon1 for each row execute procedure brtrigpartcon1trigf(); 795insert into brtrigpartcon values (1, 'hi there'); 796ERROR: new row for relation "brtrigpartcon1" violates partition constraint 797DETAIL: Failing row contains (2, hi there). 798insert into brtrigpartcon1 values (1, 'hi there'); 799ERROR: new row for relation "brtrigpartcon1" violates partition constraint 800DETAIL: Failing row contains (2, hi there). 801-- check that the message shows the appropriate column description in a 802-- situation where the partitioned table is not the primary ModifyTable node 803create table inserttest3 (f1 text default 'foo', f2 text default 'bar', f3 int); 804create role regress_coldesc_role; 805grant insert on inserttest3 to regress_coldesc_role; 806grant insert on brtrigpartcon to regress_coldesc_role; 807revoke select on brtrigpartcon from regress_coldesc_role; 808set role regress_coldesc_role; 809with result as (insert into brtrigpartcon values (1, 'hi there') returning 1) 810 insert into inserttest3 (f3) select * from result; 811ERROR: new row for relation "brtrigpartcon1" violates partition constraint 812DETAIL: Failing row contains (a, b) = (2, hi there). 813reset role; 814-- cleanup 815revoke all on inserttest3 from regress_coldesc_role; 816revoke all on brtrigpartcon from regress_coldesc_role; 817drop role regress_coldesc_role; 818drop table inserttest3; 819drop table brtrigpartcon; 820drop function brtrigpartcon1trigf(); 821-- check that "do nothing" BR triggers work with tuple-routing (this checks 822-- that estate->es_result_relation_info is appropriately set/reset for each 823-- routed tuple) 824create table donothingbrtrig_test (a int, b text) partition by list (a); 825create table donothingbrtrig_test1 (b text, a int); 826create table donothingbrtrig_test2 (c text, b text, a int); 827alter table donothingbrtrig_test2 drop column c; 828create or replace function donothingbrtrig_func() returns trigger as $$begin raise notice 'b: %', new.b; return NULL; end$$ language plpgsql; 829create trigger donothingbrtrig1 before insert on donothingbrtrig_test1 for each row execute procedure donothingbrtrig_func(); 830create trigger donothingbrtrig2 before insert on donothingbrtrig_test2 for each row execute procedure donothingbrtrig_func(); 831alter table donothingbrtrig_test attach partition donothingbrtrig_test1 for values in (1); 832alter table donothingbrtrig_test attach partition donothingbrtrig_test2 for values in (2); 833insert into donothingbrtrig_test values (1, 'foo'), (2, 'bar'); 834NOTICE: b: foo 835NOTICE: b: bar 836copy donothingbrtrig_test from stdout; 837NOTICE: b: baz 838NOTICE: b: qux 839select tableoid::regclass, * from donothingbrtrig_test; 840 tableoid | a | b 841----------+---+--- 842(0 rows) 843 844-- cleanup 845drop table donothingbrtrig_test; 846drop function donothingbrtrig_func(); 847-- check multi-column range partitioning with minvalue/maxvalue constraints 848create table mcrparted (a text, b int) partition by range(a, b); 849create table mcrparted1_lt_b partition of mcrparted for values from (minvalue, minvalue) to ('b', minvalue); 850create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue); 851create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue); 852create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0); 853create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10); 854create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue); 855create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); 856create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); 857\d+ mcrparted 858 Table "public.mcrparted" 859 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 860--------+---------+-----------+----------+---------+----------+--------------+------------- 861 a | text | | | | extended | | 862 b | integer | | | | plain | | 863Partition key: RANGE (a, b) 864Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE), 865 mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE), 866 mcrparted3_c_to_common FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE), 867 mcrparted4_common_lt_0 FOR VALUES FROM ('common', MINVALUE) TO ('common', 0), 868 mcrparted5_common_0_to_10 FOR VALUES FROM ('common', 0) TO ('common', 10), 869 mcrparted6_common_ge_10 FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE), 870 mcrparted7_gt_common_lt_d FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE), 871 mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) 872 873\d+ mcrparted1_lt_b 874 Table "public.mcrparted1_lt_b" 875 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 876--------+---------+-----------+----------+---------+----------+--------------+------------- 877 a | text | | | | extended | | 878 b | integer | | | | plain | | 879Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE) 880Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text)) 881 882\d+ mcrparted2_b 883 Table "public.mcrparted2_b" 884 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 885--------+---------+-----------+----------+---------+----------+--------------+------------- 886 a | text | | | | extended | | 887 b | integer | | | | plain | | 888Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE) 889Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text)) 890 891\d+ mcrparted3_c_to_common 892 Table "public.mcrparted3_c_to_common" 893 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 894--------+---------+-----------+----------+---------+----------+--------------+------------- 895 a | text | | | | extended | | 896 b | integer | | | | plain | | 897Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE) 898Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text)) 899 900\d+ mcrparted4_common_lt_0 901 Table "public.mcrparted4_common_lt_0" 902 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 903--------+---------+-----------+----------+---------+----------+--------------+------------- 904 a | text | | | | extended | | 905 b | integer | | | | plain | | 906Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0) 907Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0)) 908 909\d+ mcrparted5_common_0_to_10 910 Table "public.mcrparted5_common_0_to_10" 911 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 912--------+---------+-----------+----------+---------+----------+--------------+------------- 913 a | text | | | | extended | | 914 b | integer | | | | plain | | 915Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10) 916Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10)) 917 918\d+ mcrparted6_common_ge_10 919 Table "public.mcrparted6_common_ge_10" 920 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 921--------+---------+-----------+----------+---------+----------+--------------+------------- 922 a | text | | | | extended | | 923 b | integer | | | | plain | | 924Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE) 925Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10)) 926 927\d+ mcrparted7_gt_common_lt_d 928 Table "public.mcrparted7_gt_common_lt_d" 929 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 930--------+---------+-----------+----------+---------+----------+--------------+------------- 931 a | text | | | | extended | | 932 b | integer | | | | plain | | 933Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE) 934Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text)) 935 936\d+ mcrparted8_ge_d 937 Table "public.mcrparted8_ge_d" 938 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 939--------+---------+-----------+----------+---------+----------+--------------+------------- 940 a | text | | | | extended | | 941 b | integer | | | | plain | | 942Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) 943Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text)) 944 945insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), 946 ('comm', -10), ('common', -10), ('common', 0), ('common', 10), 947 ('commons', 0), ('d', -10), ('e', 0); 948select tableoid::regclass, * from mcrparted order by a, b; 949 tableoid | a | b 950---------------------------+---------+----- 951 mcrparted1_lt_b | aaa | 0 952 mcrparted2_b | b | 0 953 mcrparted2_b | bz | 10 954 mcrparted3_c_to_common | c | -10 955 mcrparted3_c_to_common | comm | -10 956 mcrparted4_common_lt_0 | common | -10 957 mcrparted5_common_0_to_10 | common | 0 958 mcrparted6_common_ge_10 | common | 10 959 mcrparted7_gt_common_lt_d | commons | 0 960 mcrparted8_ge_d | d | -10 961 mcrparted8_ge_d | e | 0 962(11 rows) 963 964drop table mcrparted; 965-- check that wholerow vars in the RETURNING list work with partitioned tables 966create table returningwrtest (a int) partition by list (a); 967create table returningwrtest1 partition of returningwrtest for values in (1); 968insert into returningwrtest values (1) returning returningwrtest; 969 returningwrtest 970----------------- 971 (1) 972(1 row) 973 974-- check also that the wholerow vars in RETURNING list are converted as needed 975alter table returningwrtest add b text; 976create table returningwrtest2 (b text, c int, a int); 977alter table returningwrtest2 drop c; 978alter table returningwrtest attach partition returningwrtest2 for values in (2); 979insert into returningwrtest values (2, 'foo') returning returningwrtest; 980 returningwrtest 981----------------- 982 (2,foo) 983(1 row) 984 985drop table returningwrtest; 986