src/test/regress/sql/insert.sql

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