|
||||
0001 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0002 -- 0003 -- INSERT 0004 -- https://github.com/postgres/postgres/blob/REL_12_STABLE/src/test/regress/sql/insert.sql 0005 0006 -- 0007 -- insert with DEFAULT in the target_list 0008 -- 0009 -- [SPARK-19842] Informational Referential Integrity Constraints Support in Spark 0010 -- [SPARK-29119] DEFAULT option is not supported in Spark 0011 create table inserttest (col1 int, col2 int /* NOT NULL */, col3 string /* default 'testing' */) using parquet; 0012 -- [SPARK-29119] DEFAULT option is not supported in Spark 0013 -- [SPARK-20845] Support specification of column names in INSERT INTO 0014 -- Skip a test below because the PK constraint is violated and the query fails in PostgreSQL 0015 -- insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); 0016 -- insert into inserttest (col2, col3) values (3, DEFAULT); 0017 insert into inserttest values (NULL, 3, 'testing'); 0018 -- insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); 0019 insert into inserttest values (NULL, 5, 'testing'); 0020 -- insert into inserttest values (DEFAULT, 5, 'test'); 0021 insert into inserttest values (NULL, 5, 'test'); 0022 -- insert into inserttest values (DEFAULT, 7); 0023 insert into inserttest values (NULL, 7, 'testing'); 0024 0025 select * from inserttest; 0026 0027 -- 0028 -- insert with similar expression / target_list values (all fail) 0029 -- 0030 -- [SPARK-20845] Support specification of column names in INSERT INTO 0031 -- [SPARK-29119] DEFAULT option is not supported in Spark 0032 -- insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT); 0033 -- insert into inserttest (col1, col2, col3) values (1, 2); 0034 -- insert into inserttest (col1) values (1, 2); 0035 -- insert into inserttest (col1) values (DEFAULT, DEFAULT); 0036 0037 -- select * from inserttest; 0038 0039 -- 0040 -- VALUES test 0041 -- 0042 -- [SPARK-29119] DEFAULT option is not supported in Spark 0043 -- [SPARK-29715] Support SELECT statements in VALUES of INSERT INTO 0044 -- insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT), 0045 -- ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!'); 0046 0047 -- select * from inserttest; 0048 0049 -- 0050 -- TOASTed value test 0051 -- 0052 insert into inserttest values(30, 50, repeat('x', 10000)); 0053 0054 select col1, col2, char_length(col3) from inserttest; 0055 0056 drop table inserttest; 0057 0058 -- 0059 -- check indirection (field/array assignment), cf bug #14265 0060 -- 0061 -- these tests are aware that transformInsertStmt has 3 separate code paths 0062 -- 0063 0064 -- [SPARK-29716] Support [CREATE|DROP] TYPE 0065 -- create type insert_test_type as (if1 int, if2 array<string>); 0066 0067 -- create table inserttest (f1 int, f2 int[], 0068 -- f3 insert_test_type, f4 insert_test_type[]); 0069 -- 0070 -- insert into inserttest (f2[1], f2[2]) values (1,2); 0071 -- insert into inserttest (f2[1], f2[2]) values (3,4), (5,6); 0072 -- insert into inserttest (f2[1], f2[2]) select 7,8; 0073 -- insert into inserttest (f2[1], f2[2]) values (1,default); -- not supported 0074 -- 0075 -- insert into inserttest (f3.if1, f3.if2) values (1,array['foo']); 0076 -- insert into inserttest (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); 0077 -- insert into inserttest (f3.if1, f3.if2) select 3, '{baz,quux}'; 0078 -- insert into inserttest (f3.if1, f3.if2) values (1,default); -- not supported 0079 -- 0080 -- insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); 0081 -- insert into inserttest (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 0082 -- insert into inserttest (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; 0083 -- 0084 -- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'); 0085 -- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) values ('foo', 'bar'), ('baz', 'quux'); 0086 -- insert into inserttest (f4[1].if2[1], f4[1].if2[2]) select 'bear', 'beer'; 0087 -- 0088 -- select * from inserttest; 0089 0090 -- also check reverse-listing 0091 -- create table inserttest2 (f1 bigint, f2 string); 0092 -- [SPARK-29717] Support [CREATE|DROP] RULE - define a new plan rewrite rule 0093 -- create rule irule1 as on insert to inserttest2 do also 0094 -- insert into inserttest (f3.if2[1], f3.if2[2]) 0095 -- values (new.f1,new.f2); 0096 -- create rule irule2 as on insert to inserttest2 do also 0097 -- insert into inserttest (f4[1].if1, f4[1].if2[2]) 0098 -- values (1,'fool'),(new.f1,new.f2); 0099 -- create rule irule3 as on insert to inserttest2 do also 0100 -- insert into inserttest (f4[1].if1, f4[1].if2[2]) 0101 -- select new.f1, new.f2; 0102 -- \d+ inserttest2 0103 0104 -- drop table inserttest2; 0105 -- drop table inserttest; 0106 -- [SPARK-29716] Support [CREATE|DROP] TYPE 0107 -- drop type insert_test_type; 0108 0109 -- direct partition inserts should check partition bound constraint 0110 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0111 -- create table range_parted ( 0112 -- a string, 0113 -- b int 0114 -- ) partition by range (a, (b+0)); 0115 0116 -- no partitions, so fail 0117 -- insert into range_parted values ('a', 11); 0118 0119 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0120 -- create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); 0121 -- create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); 0122 -- create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); 0123 -- create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); 0124 0125 -- fail 0126 -- insert into part1 values ('a', 11); 0127 -- insert into part1 values ('b', 1); 0128 -- ok 0129 -- insert into part1 values ('a', 1); 0130 -- fail 0131 -- insert into part4 values ('b', 21); 0132 -- insert into part4 values ('a', 10); 0133 -- ok 0134 -- insert into part4 values ('b', 10); 0135 0136 -- fail (partition key a has a NOT NULL constraint) 0137 -- insert into part1 values (null); 0138 -- fail (expression key (b+0) cannot be null either) 0139 -- insert into part1 values (1); 0140 0141 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0142 -- create table list_parted ( 0143 -- a text, 0144 -- b int 0145 -- ) partition by list (lower(a)); 0146 -- create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); 0147 -- create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); 0148 -- create table part_null partition of list_parted FOR VALUES IN (null); 0149 0150 -- fail 0151 -- insert into part_aa_bb values ('cc', 1); 0152 -- insert into part_aa_bb values ('AAa', 1); 0153 -- insert into part_aa_bb values (null); 0154 -- ok 0155 -- insert into part_cc_dd values ('cC', 1); 0156 -- insert into part_null values (null, 0); 0157 0158 -- check in case of multi-level partitioned table 0159 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0160 -- create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); 0161 -- create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); 0162 -- create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); 0163 0164 -- test default partition 0165 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0166 -- create table part_default partition of list_parted default; 0167 -- Negative test: a row, which would fit in other partition, does not fit 0168 -- default partition, even when inserted directly 0169 -- insert into part_default values ('aa', 2); 0170 -- insert into part_default values (null, 2); 0171 -- ok 0172 -- insert into part_default values ('Zz', 2); 0173 -- test if default partition works as expected for multi-level partitioned 0174 -- table as well as when default partition itself is further partitioned 0175 -- drop table part_default; 0176 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0177 -- create table part_xx_yy partition of list_parted for values in ('xx', 'yy') partition by list (a); 0178 -- create table part_xx_yy_p1 partition of part_xx_yy for values in ('xx'); 0179 -- create table part_xx_yy_defpart partition of part_xx_yy default; 0180 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0181 -- create table part_default partition of list_parted default partition by range(b); 0182 -- create table part_default_p1 partition of part_default for values from (20) to (30); 0183 -- create table part_default_p2 partition of part_default for values from (30) to (40); 0184 0185 -- fail 0186 -- insert into part_ee_ff1 values ('EE', 11); 0187 -- insert into part_default_p2 values ('gg', 43); 0188 -- fail (even the parent's, ie, part_ee_ff's partition constraint applies) 0189 -- insert into part_ee_ff1 values ('cc', 1); 0190 -- insert into part_default values ('gg', 43); 0191 -- ok 0192 -- insert into part_ee_ff1 values ('ff', 1); 0193 -- insert into part_ee_ff2 values ('ff', 11); 0194 -- insert into part_default_p1 values ('cd', 25); 0195 -- insert into part_default_p2 values ('de', 35); 0196 -- insert into list_parted values ('ab', 21); 0197 -- insert into list_parted values ('xx', 1); 0198 -- insert into list_parted values ('yy', 2); 0199 -- select tableoid::regclass, * from list_parted; 0200 0201 -- Check tuple routing for partitioned tables 0202 0203 -- fail 0204 -- insert into range_parted values ('a', 0); 0205 -- ok 0206 -- insert into range_parted values ('a', 1); 0207 -- insert into range_parted values ('a', 10); 0208 -- fail 0209 -- insert into range_parted values ('a', 20); 0210 -- ok 0211 -- insert into range_parted values ('b', 1); 0212 -- insert into range_parted values ('b', 10); 0213 -- fail (partition key (b+0) is null) 0214 -- insert into range_parted values ('a'); 0215 0216 -- Check default partition 0217 -- create table part_def partition of range_parted default; 0218 -- fail 0219 -- insert into part_def values ('b', 10); 0220 -- ok 0221 -- insert into part_def values ('c', 10); 0222 -- insert into range_parted values (null, null); 0223 -- insert into range_parted values ('a', null); 0224 -- insert into range_parted values (null, 19); 0225 -- insert into range_parted values ('b', 20); 0226 0227 -- select tableoid::regclass, * from range_parted; 0228 -- ok 0229 -- insert into list_parted values (null, 1); 0230 -- insert into list_parted (a) values ('aA'); 0231 -- fail (partition of part_ee_ff not found in both cases) 0232 -- insert into list_parted values ('EE', 0); 0233 -- insert into part_ee_ff values ('EE', 0); 0234 -- ok 0235 -- insert into list_parted values ('EE', 1); 0236 -- insert into part_ee_ff values ('EE', 10); 0237 -- select tableoid::regclass, * from list_parted; 0238 0239 -- some more tests to exercise tuple-routing with multi-level partitioning 0240 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0241 -- create table part_gg partition of list_parted for values in ('gg') partition by range (b); 0242 -- create table part_gg1 partition of part_gg for values from (minvalue) to (1); 0243 -- create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); 0244 -- create table part_gg2_1 partition of part_gg2 for values from (1) to (5); 0245 -- create table part_gg2_2 partition of part_gg2 for values from (5) to (10); 0246 0247 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0248 -- create table part_ee_ff3 partition of part_ee_ff for values from (20) to (30) partition by range (b); 0249 -- create table part_ee_ff3_1 partition of part_ee_ff3 for values from (20) to (25); 0250 -- create table part_ee_ff3_2 partition of part_ee_ff3 for values from (25) to (30); 0251 0252 -- truncate list_parted; 0253 -- insert into list_parted values ('aa'), ('cc'); 0254 -- [SPARK-27767] Built-in function: generate_series 0255 -- insert into list_parted select 'Ff', s.a from generate_series(1, 29) s(a); 0256 -- insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); 0257 -- insert into list_parted (b) values (1); 0258 -- 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; 0259 0260 -- direct partition inserts should check hash partition bound constraint 0261 0262 -- Use hand-rolled hash functions and operator classes to get predictable 0263 -- result on different matchines. The hash function for int4 simply returns 0264 -- the sum of the values passed to it and the one for text returns the length 0265 -- of the non-empty string value passed to it or 0. 0266 0267 -- create or replace function part_hashint4_noop(value int4, seed int8) 0268 -- returns int8 as $$ 0269 -- select value + seed; 0270 -- $$ language sql immutable; 0271 0272 -- create operator class part_test_int4_ops 0273 -- for type int4 0274 -- using hash as 0275 -- operator 1 =, 0276 -- function 2 part_hashint4_noop(int4, int8); 0277 0278 -- create or replace function part_hashtext_length(value text, seed int8) 0279 -- RETURNS int8 AS $$ 0280 -- select length(coalesce(value, ''))::int8 0281 -- $$ language sql immutable; 0282 0283 -- create operator class part_test_text_ops 0284 -- for type text 0285 -- using hash as 0286 -- operator 1 =, 0287 -- function 2 part_hashtext_length(text, int8); 0288 0289 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0290 -- create table hash_parted ( 0291 -- a int 0292 -- ) partition by hash (a part_test_int4_ops); 0293 -- create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); 0294 -- create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); 0295 -- create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); 0296 -- create table hpart3 partition of hash_parted for values with (modulus 4, remainder 3); 0297 0298 -- [SPARK-27767] Built-in function: generate_series 0299 -- insert into hash_parted values(generate_series(1,10)); 0300 0301 -- direct insert of values divisible by 4 - ok; 0302 -- insert into hpart0 values(12),(16); 0303 -- fail; 0304 -- insert into hpart0 values(11); 0305 -- 11 % 4 -> 3 remainder i.e. valid data for hpart3 partition 0306 -- insert into hpart3 values(11); 0307 0308 -- view data 0309 -- select tableoid::regclass as part, a, a%4 as "remainder = a % 4" 0310 -- from hash_parted order by part; 0311 0312 -- test \d+ output on a table which has both partitioned and unpartitioned 0313 -- partitions 0314 -- \d+ list_parted 0315 0316 -- cleanup 0317 -- drop table range_parted, list_parted; 0318 -- drop table hash_parted; 0319 0320 -- test that a default partition added as the first partition accepts any value 0321 -- including null 0322 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0323 -- create table list_parted (a int) partition by list (a); 0324 -- create table part_default partition of list_parted default; 0325 -- \d+ part_default 0326 -- insert into part_default values (null); 0327 -- insert into part_default values (1); 0328 -- insert into part_default values (-1); 0329 -- select tableoid::regclass, a from list_parted; 0330 -- cleanup 0331 -- drop table list_parted; 0332 0333 -- more tests for certain multi-level partitioning scenarios 0334 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0335 -- create table mlparted (a int, b int) partition by range (a, b); 0336 -- create table mlparted1 (b int not null, a int not null) partition by range ((b+0)); 0337 -- create table mlparted11 (like mlparted1); 0338 -- alter table mlparted11 drop a; 0339 -- alter table mlparted11 add a int; 0340 -- alter table mlparted11 drop a; 0341 -- alter table mlparted11 add a int not null; 0342 -- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11 0343 -- select attrelid::regclass, attname, attnum 0344 -- from pg_attribute 0345 -- where attname = 'a' 0346 -- and (attrelid = 'mlparted'::regclass 0347 -- or attrelid = 'mlparted1'::regclass 0348 -- or attrelid = 'mlparted11'::regclass) 0349 -- order by attrelid::regclass::text; 0350 0351 -- alter table mlparted1 attach partition mlparted11 for values from (2) to (5); 0352 -- alter table mlparted attach partition mlparted1 for values from (1, 2) to (1, 10); 0353 0354 -- check that "(1, 2)" is correctly routed to mlparted11. 0355 -- insert into mlparted values (1, 2); 0356 -- select tableoid::regclass, * from mlparted; 0357 0358 -- check that proper message is shown after failure to route through mlparted1 0359 -- insert into mlparted (a, b) values (1, 5); 0360 0361 -- truncate mlparted; 0362 -- alter table mlparted add constraint check_b check (b = 3); 0363 0364 -- have a BR trigger modify the row such that the check_b is violated 0365 -- create function mlparted11_trig_fn() 0366 -- returns trigger AS 0367 -- $$ 0368 -- begin 0369 -- NEW.b := 4; 0370 -- return NEW; 0371 -- end; 0372 -- $$ 0373 -- language plpgsql; 0374 -- create trigger mlparted11_trig before insert ON mlparted11 0375 -- for each row execute procedure mlparted11_trig_fn(); 0376 0377 -- check that the correct row is shown when constraint check_b fails after 0378 -- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due 0379 -- to the BR trigger mlparted11_trig_fn) 0380 -- insert into mlparted values (1, 2); 0381 -- drop trigger mlparted11_trig on mlparted11; 0382 -- drop function mlparted11_trig_fn(); 0383 0384 -- check that inserting into an internal partition successfully results in 0385 -- checking its partition constraint before inserting into the leaf partition 0386 -- selected by tuple-routing 0387 -- insert into mlparted1 (a, b) values (2, 3); 0388 0389 -- check routing error through a list partitioned table when the key is null 0390 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0391 -- create table lparted_nonullpart (a int, b char) partition by list (b); 0392 -- create table lparted_nonullpart_a partition of lparted_nonullpart for values in ('a'); 0393 -- insert into lparted_nonullpart values (1); 0394 -- drop table lparted_nonullpart; 0395 0396 -- check that RETURNING works correctly with tuple-routing 0397 -- alter table mlparted drop constraint check_b; 0398 -- create table mlparted12 partition of mlparted1 for values from (5) to (10); 0399 -- create table mlparted2 (b int not null, a int not null); 0400 -- alter table mlparted attach partition mlparted2 for values from (1, 10) to (1, 20); 0401 -- create table mlparted3 partition of mlparted for values from (1, 20) to (1, 30); 0402 -- create table mlparted4 (like mlparted); 0403 -- alter table mlparted4 drop a; 0404 -- alter table mlparted4 add a int not null; 0405 -- alter table mlparted attach partition mlparted4 for values from (1, 30) to (1, 40); 0406 -- [SPARK-27767] Built-in function: generate_series 0407 -- with ins (a, b, c) as 0408 -- (insert into mlparted (b, a) select s.a, 1 from generate_series(2, 39) s(a) returning tableoid::regclass, *) 0409 -- select a, b, min(c), max(c) from ins group by a, b order by 1; 0410 0411 -- alter table mlparted add c text; 0412 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0413 -- create table mlparted5 (c text, a int not null, b int not null) partition by list (c); 0414 -- create table mlparted5a (a int not null, c text, b int not null); 0415 -- alter table mlparted5 attach partition mlparted5a for values in ('a'); 0416 -- alter table mlparted attach partition mlparted5 for values from (1, 40) to (1, 50); 0417 -- alter table mlparted add constraint check_b check (a = 1 and b < 45); 0418 -- insert into mlparted values (1, 45, 'a'); 0419 -- create function mlparted5abrtrig_func() returns trigger as $$ begin new.c = 'b'; return new; end; $$ language plpgsql; 0420 -- create trigger mlparted5abrtrig before insert on mlparted5a for each row execute procedure mlparted5abrtrig_func(); 0421 -- insert into mlparted5 (a, b, c) values (1, 40, 'a'); 0422 -- drop table mlparted5; 0423 -- alter table mlparted drop constraint check_b; 0424 0425 -- Check multi-level default partition 0426 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0427 -- create table mlparted_def partition of mlparted default partition by range(a); 0428 -- create table mlparted_def1 partition of mlparted_def for values from (40) to (50); 0429 -- create table mlparted_def2 partition of mlparted_def for values from (50) to (60); 0430 -- insert into mlparted values (40, 100); 0431 -- insert into mlparted_def1 values (42, 100); 0432 -- insert into mlparted_def2 values (54, 50); 0433 -- fail 0434 -- insert into mlparted values (70, 100); 0435 -- insert into mlparted_def1 values (52, 50); 0436 -- insert into mlparted_def2 values (34, 50); 0437 -- ok 0438 -- create table mlparted_defd partition of mlparted_def default; 0439 -- insert into mlparted values (70, 100); 0440 0441 -- select tableoid::regclass, * from mlparted_def; 0442 0443 -- Check multi-level tuple routing with attributes dropped from the 0444 -- top-most parent. First remove the last attribute. 0445 -- alter table mlparted add d int, add e int; 0446 -- alter table mlparted drop e; 0447 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0448 -- create table mlparted5 partition of mlparted 0449 -- for values from (1, 40) to (1, 50) partition by range (c); 0450 -- create table mlparted5_ab partition of mlparted5 0451 -- for values from ('a') to ('c') partition by list (c); 0452 -- This partitioned table should remain with no partitions. 0453 -- create table mlparted5_cd partition of mlparted5 0454 -- for values from ('c') to ('e') partition by list (c); 0455 -- create table mlparted5_a partition of mlparted5_ab for values in ('a'); 0456 -- create table mlparted5_b (d int, b int, c text, a int); 0457 -- alter table mlparted5_ab attach partition mlparted5_b for values in ('b'); 0458 -- truncate mlparted; 0459 -- insert into mlparted values (1, 2, 'a', 1); 0460 -- insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 0461 -- insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 0462 -- insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 0463 -- insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 0464 -- select tableoid::regclass, * from mlparted order by a, b, c, d; 0465 -- alter table mlparted drop d; 0466 -- truncate mlparted; 0467 -- Remove the before last attribute. 0468 -- alter table mlparted add e int, add d int; 0469 -- alter table mlparted drop e; 0470 -- insert into mlparted values (1, 2, 'a', 1); 0471 -- insert into mlparted values (1, 40, 'a', 1); -- goes to mlparted5_a 0472 -- insert into mlparted values (1, 45, 'b', 1); -- goes to mlparted5_b 0473 -- insert into mlparted values (1, 45, 'c', 1); -- goes to mlparted5_cd, fails 0474 -- insert into mlparted values (1, 45, 'f', 1); -- goes to mlparted5, fails 0475 -- select tableoid::regclass, * from mlparted order by a, b, c, d; 0476 -- alter table mlparted drop d; 0477 -- drop table mlparted5; 0478 0479 -- check that message shown after failure to find a partition shows the 0480 -- appropriate key description (or none) in various situations 0481 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0482 -- create table key_desc (a int, b int) partition by list ((a+0)); 0483 -- create table key_desc_1 partition of key_desc for values in (1) partition by range (b); 0484 0485 -- create user regress_insert_other_user; 0486 -- grant select (a) on key_desc_1 to regress_insert_other_user; 0487 -- grant insert on key_desc to regress_insert_other_user; 0488 0489 -- set role regress_insert_other_user; 0490 -- no key description is shown 0491 -- insert into key_desc values (1, 1); 0492 0493 -- reset role; 0494 -- grant select (b) on key_desc_1 to regress_insert_other_user; 0495 -- set role regress_insert_other_user; 0496 -- key description (b)=(1) is now shown 0497 -- insert into key_desc values (1, 1); 0498 0499 -- key description is not shown if key contains expression 0500 -- insert into key_desc values (2, 1); 0501 -- reset role; 0502 -- revoke all on key_desc from regress_insert_other_user; 0503 -- revoke all on key_desc_1 from regress_insert_other_user; 0504 -- drop role regress_insert_other_user; 0505 -- drop table key_desc, key_desc_1; 0506 0507 -- test minvalue/maxvalue restrictions 0508 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0509 -- create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 0510 -- create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue); 0511 -- create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue); 0512 -- create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue); 0513 0514 -- check multi-column range partitioning expression enforces the same 0515 -- constraint as what tuple-routing would determine it to be 0516 -- create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue); 0517 -- create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10); 0518 -- create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, maxvalue); 0519 -- create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 0520 -- create table mcrparted4 partition of mcrparted for values from (21, minvalue, minvalue) to (30, 20, maxvalue); 0521 -- create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue, maxvalue, maxvalue); 0522 0523 -- null not allowed in range partition 0524 -- insert into mcrparted values (null, null, null); 0525 0526 -- routed to mcrparted0 0527 -- insert into mcrparted values (0, 1, 1); 0528 -- insert into mcrparted0 values (0, 1, 1); 0529 0530 -- routed to mcparted1 0531 -- insert into mcrparted values (9, 1000, 1); 0532 -- insert into mcrparted1 values (9, 1000, 1); 0533 -- insert into mcrparted values (10, 5, -1); 0534 -- insert into mcrparted1 values (10, 5, -1); 0535 -- insert into mcrparted values (2, 1, 0); 0536 -- insert into mcrparted1 values (2, 1, 0); 0537 0538 -- routed to mcparted2 0539 -- insert into mcrparted values (10, 6, 1000); 0540 -- insert into mcrparted2 values (10, 6, 1000); 0541 -- insert into mcrparted values (10, 1000, 1000); 0542 -- insert into mcrparted2 values (10, 1000, 1000); 0543 0544 -- no partition exists, nor does mcrparted3 accept it 0545 -- insert into mcrparted values (11, 1, -1); 0546 -- insert into mcrparted3 values (11, 1, -1); 0547 0548 -- routed to mcrparted5 0549 -- insert into mcrparted values (30, 21, 20); 0550 -- insert into mcrparted5 values (30, 21, 20); 0551 -- insert into mcrparted4 values (30, 21, 20); -- error 0552 0553 -- check rows 0554 -- select tableoid::regclass::text, * from mcrparted order by 1; 0555 0556 -- cleanup 0557 -- drop table mcrparted; 0558 0559 -- check that a BR constraint can't make partition contain violating rows 0560 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0561 -- create table brtrigpartcon (a int, b text) partition by list (a); 0562 -- create table brtrigpartcon1 partition of brtrigpartcon for values in (1); 0563 -- create or replace function brtrigpartcon1trigf() returns trigger as $$begin new.a := 2; return new; end$$ language plpgsql; 0564 -- create trigger brtrigpartcon1trig before insert on brtrigpartcon1 for each row execute procedure brtrigpartcon1trigf(); 0565 -- insert into brtrigpartcon values (1, 'hi there'); 0566 -- insert into brtrigpartcon1 values (1, 'hi there'); 0567 0568 -- check that the message shows the appropriate column description in a 0569 -- situation where the partitioned table is not the primary ModifyTable node 0570 -- create table inserttest3 (f1 text default 'foo', f2 text default 'bar', f3 int); 0571 -- create role regress_coldesc_role; 0572 -- grant insert on inserttest3 to regress_coldesc_role; 0573 -- grant insert on brtrigpartcon to regress_coldesc_role; 0574 -- revoke select on brtrigpartcon from regress_coldesc_role; 0575 -- set role regress_coldesc_role; 0576 -- with result as (insert into brtrigpartcon values (1, 'hi there') returning 1) 0577 -- insert into inserttest3 (f3) select * from result; 0578 -- reset role; 0579 0580 -- cleanup 0581 -- revoke all on inserttest3 from regress_coldesc_role; 0582 -- revoke all on brtrigpartcon from regress_coldesc_role; 0583 -- drop role regress_coldesc_role; 0584 -- drop table inserttest3; 0585 -- drop table brtrigpartcon; 0586 -- drop function brtrigpartcon1trigf(); 0587 0588 -- check that "do nothing" BR triggers work with tuple-routing (this checks 0589 -- that estate->es_result_relation_info is appropriately set/reset for each 0590 -- routed tuple) 0591 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0592 -- create table donothingbrtrig_test (a int, b text) partition by list (a); 0593 -- create table donothingbrtrig_test1 (b text, a int); 0594 -- create table donothingbrtrig_test2 (c text, b text, a int); 0595 -- alter table donothingbrtrig_test2 drop column c; 0596 -- create or replace function donothingbrtrig_func() returns trigger as $$begin raise notice 'b: %', new.b; return NULL; end$$ language plpgsql; 0597 -- create trigger donothingbrtrig1 before insert on donothingbrtrig_test1 for each row execute procedure donothingbrtrig_func(); 0598 -- create trigger donothingbrtrig2 before insert on donothingbrtrig_test2 for each row execute procedure donothingbrtrig_func(); 0599 -- alter table donothingbrtrig_test attach partition donothingbrtrig_test1 for values in (1); 0600 -- alter table donothingbrtrig_test attach partition donothingbrtrig_test2 for values in (2); 0601 -- insert into donothingbrtrig_test values (1, 'foo'), (2, 'bar'); 0602 -- [SPARK-29386] Copy data between a file and a table 0603 -- copy donothingbrtrig_test from stdout; 0604 -- 1 baz 0605 -- 2 qux 0606 -- \. 0607 -- select tableoid::regclass, * from donothingbrtrig_test; 0608 0609 -- cleanup 0610 -- drop table donothingbrtrig_test; 0611 -- drop function donothingbrtrig_func(); 0612 0613 -- check multi-column range partitioning with minvalue/maxvalue constraints 0614 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0615 -- create table mcrparted (a text, b int) partition by range(a, b); 0616 -- create table mcrparted1_lt_b partition of mcrparted for values from (minvalue, minvalue) to ('b', minvalue); 0617 -- create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue); 0618 -- create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue); 0619 -- create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0); 0620 -- create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10); 0621 -- create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue); 0622 -- create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); 0623 -- create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); 0624 0625 -- \d+ mcrparted 0626 -- \d+ mcrparted1_lt_b 0627 -- \d+ mcrparted2_b 0628 -- \d+ mcrparted3_c_to_common 0629 -- \d+ mcrparted4_common_lt_0 0630 -- \d+ mcrparted5_common_0_to_10 0631 -- \d+ mcrparted6_common_ge_10 0632 -- \d+ mcrparted7_gt_common_lt_d 0633 -- \d+ mcrparted8_ge_d 0634 0635 -- insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), 0636 -- ('comm', -10), ('common', -10), ('common', 0), ('common', 10), 0637 -- ('commons', 0), ('d', -10), ('e', 0); 0638 -- select tableoid::regclass, * from mcrparted order by a, b; 0639 -- drop table mcrparted; 0640 0641 -- check that wholerow vars in the RETURNING list work with partitioned tables 0642 -- [SPARK-29718] Support PARTITION BY [RANGE|LIST|HASH] and PARTITION OF in CREATE TABLE 0643 -- create table returningwrtest (a int) partition by list (a); 0644 -- create table returningwrtest1 partition of returningwrtest for values in (1); 0645 -- insert into returningwrtest values (1) returning returningwrtest; 0646 0647 -- check also that the wholerow vars in RETURNING list are converted as needed 0648 -- alter table returningwrtest add b text; 0649 -- create table returningwrtest2 (b text, c int, a int); 0650 -- alter table returningwrtest2 drop c; 0651 -- alter table returningwrtest attach partition returningwrtest2 for values in (2); 0652 -- insert into returningwrtest values (2, 'foo') returning returningwrtest; 0653 -- drop table returningwrtest;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |