1-- 2-- Test inheritance features 3-- 4CREATE TABLE a (aa TEXT); 5CREATE TABLE b (bb TEXT) INHERITS (a); 6CREATE TABLE c (cc TEXT) INHERITS (a); 7CREATE TABLE d (dd TEXT) INHERITS (b,c,a); 8 9INSERT INTO a(aa) VALUES('aaa'); 10INSERT INTO a(aa) VALUES('aaaa'); 11INSERT INTO a(aa) VALUES('aaaaa'); 12INSERT INTO a(aa) VALUES('aaaaaa'); 13INSERT INTO a(aa) VALUES('aaaaaaa'); 14INSERT INTO a(aa) VALUES('aaaaaaaa'); 15 16INSERT INTO b(aa) VALUES('bbb'); 17INSERT INTO b(aa) VALUES('bbbb'); 18INSERT INTO b(aa) VALUES('bbbbb'); 19INSERT INTO b(aa) VALUES('bbbbbb'); 20INSERT INTO b(aa) VALUES('bbbbbbb'); 21INSERT INTO b(aa) VALUES('bbbbbbbb'); 22 23INSERT INTO c(aa) VALUES('ccc'); 24INSERT INTO c(aa) VALUES('cccc'); 25INSERT INTO c(aa) VALUES('ccccc'); 26INSERT INTO c(aa) VALUES('cccccc'); 27INSERT INTO c(aa) VALUES('ccccccc'); 28INSERT INTO c(aa) VALUES('cccccccc'); 29 30INSERT INTO d(aa) VALUES('ddd'); 31INSERT INTO d(aa) VALUES('dddd'); 32INSERT INTO d(aa) VALUES('ddddd'); 33INSERT INTO d(aa) VALUES('dddddd'); 34INSERT INTO d(aa) VALUES('ddddddd'); 35INSERT INTO d(aa) VALUES('dddddddd'); 36 37SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 38SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 39SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 40SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 41SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 42SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 43SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 44SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 45 46UPDATE a SET aa='zzzz' WHERE aa='aaaa'; 47UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; 48UPDATE b SET aa='zzz' WHERE aa='aaa'; 49UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; 50UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; 51 52SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 53SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 54SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 55SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 56SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 57SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 58SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 59SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 60 61UPDATE b SET aa='new'; 62 63SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 64SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 65SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 66SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 67SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 68SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 69SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 70SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 71 72UPDATE a SET aa='new'; 73 74DELETE FROM ONLY c WHERE aa='new'; 75 76SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 77SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 78SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 79SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 80SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 81SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 82SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 83SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 84 85DELETE FROM a; 86 87SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 88SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 89SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 90SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 91SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 92SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 93SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 94SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 95 96-- Confirm PRIMARY KEY adds NOT NULL constraint to child table 97CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); 98INSERT INTO z VALUES (NULL, 'text'); -- should fail 99 100-- Check inherited UPDATE with all children excluded 101create table some_tab (a int, b int); 102create table some_tab_child () inherits (some_tab); 103insert into some_tab_child values(1,2); 104 105explain (verbose, costs off) 106update some_tab set a = a + 1 where false; 107update some_tab set a = a + 1 where false; 108explain (verbose, costs off) 109update some_tab set a = a + 1 where false returning b, a; 110update some_tab set a = a + 1 where false returning b, a; 111table some_tab; 112 113drop table some_tab cascade; 114 115-- Check UPDATE with inherited target and an inherited source table 116create temp table foo(f1 int, f2 int); 117create temp table foo2(f3 int) inherits (foo); 118create temp table bar(f1 int, f2 int); 119create temp table bar2(f3 int) inherits (bar); 120 121insert into foo values(1,1); 122insert into foo values(3,3); 123insert into foo2 values(2,2,2); 124insert into foo2 values(3,3,3); 125insert into bar values(1,1); 126insert into bar values(2,2); 127insert into bar values(3,3); 128insert into bar values(4,4); 129insert into bar2 values(1,1,1); 130insert into bar2 values(2,2,2); 131insert into bar2 values(3,3,3); 132insert into bar2 values(4,4,4); 133 134update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 135 136select tableoid::regclass::text as relname, bar.* from bar order by 1,2; 137 138-- Check UPDATE with inherited target and an appendrel subquery 139update bar set f2 = f2 + 100 140from 141 ( select f1 from foo union all select f1+3 from foo ) ss 142where bar.f1 = ss.f1; 143 144select tableoid::regclass::text as relname, bar.* from bar order by 1,2; 145 146-- Check UPDATE with *partitioned* inherited target and an appendrel subquery 147create table some_tab (a int); 148insert into some_tab values (0); 149create table some_tab_child () inherits (some_tab); 150insert into some_tab_child values (1); 151create table parted_tab (a int, b char) partition by list (a); 152create table parted_tab_part1 partition of parted_tab for values in (1); 153create table parted_tab_part2 partition of parted_tab for values in (2); 154create table parted_tab_part3 partition of parted_tab for values in (3); 155insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); 156 157update parted_tab set b = 'b' 158from 159 (select a from some_tab union all select a+1 from some_tab) ss (a) 160where parted_tab.a = ss.a; 161select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; 162 163truncate parted_tab; 164insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); 165update parted_tab set b = 'b' 166from 167 (select 0 from parted_tab union all select 1 from parted_tab) ss (a) 168where parted_tab.a = ss.a; 169select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; 170 171-- modifies partition key, but no rows will actually be updated 172explain update parted_tab set a = 2 where false; 173 174drop table parted_tab; 175drop table some_tab cascade; 176 177/* Test multiple inheritance of column defaults */ 178 179CREATE TABLE firstparent (tomorrow date default now()::date + 1); 180CREATE TABLE secondparent (tomorrow date default now() :: date + 1); 181CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok 182CREATE TABLE thirdparent (tomorrow date default now()::date - 1); 183CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok 184CREATE TABLE otherchild (tomorrow date default now()) 185 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default 186 187DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; 188 189-- Test changing the type of inherited columns 190insert into d values('test','one','two','three'); 191alter table a alter column aa type integer using bit_length(aa); 192select * from d; 193 194-- The above verified that we can change the type of a multiply-inherited 195-- column; but we should reject that if any definition was inherited from 196-- an unrelated parent. 197create temp table parent1(f1 int, f2 int); 198create temp table parent2(f1 int, f3 bigint); 199create temp table childtab(f4 int) inherits(parent1, parent2); 200alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 201alter table parent1 alter column f2 type bigint; -- ok 202 203-- check that oid column is handled properly during alter table inherit 204create table oid_parent (a int) with oids; 205 206create table oid_child () inherits (oid_parent); 207select attinhcount, attislocal from pg_attribute 208 where attrelid = 'oid_child'::regclass and attname = 'oid'; 209drop table oid_child; 210 211create table oid_child (a int) without oids; 212alter table oid_child inherit oid_parent; -- fail 213alter table oid_child set with oids; 214select attinhcount, attislocal from pg_attribute 215 where attrelid = 'oid_child'::regclass and attname = 'oid'; 216alter table oid_child inherit oid_parent; 217select attinhcount, attislocal from pg_attribute 218 where attrelid = 'oid_child'::regclass and attname = 'oid'; 219alter table oid_child set without oids; -- fail 220alter table oid_parent set without oids; 221select attinhcount, attislocal from pg_attribute 222 where attrelid = 'oid_child'::regclass and attname = 'oid'; 223alter table oid_child set without oids; 224select attinhcount, attislocal from pg_attribute 225 where attrelid = 'oid_child'::regclass and attname = 'oid'; 226 227drop table oid_parent cascade; 228 229-- Test non-inheritable parent constraints 230create table p1(ff1 int); 231alter table p1 add constraint p1chk check (ff1 > 0) no inherit; 232alter table p1 add constraint p2chk check (ff1 > 10); 233-- connoinherit should be true for NO INHERIT constraint 234select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2; 235 236-- Test that child does not inherit NO INHERIT constraints 237create table c1 () inherits (p1); 238\d p1 239\d c1 240 241-- Test that child does not override inheritable constraints of the parent 242create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails 243 244drop table p1 cascade; 245 246-- Tests for casting between the rowtypes of parent and child 247-- tables. See the pgsql-hackers thread beginning Dec. 4/04 248create table base (i integer); 249create table derived () inherits (base); 250insert into derived (i) values (0); 251select derived::base from derived; 252select NULL::derived::base; 253drop table derived; 254drop table base; 255 256create table p1(ff1 int); 257create table p2(f1 text); 258create function p2text(p2) returns text as 'select $1.f1' language sql; 259create table c1(f3 int) inherits(p1,p2); 260insert into c1 values(123456789, 'hi', 42); 261select p2text(c1.*) from c1; 262drop function p2text(p2); 263drop table c1; 264drop table p2; 265drop table p1; 266 267CREATE TABLE ac (aa TEXT); 268alter table ac add constraint ac_check check (aa is not null); 269CREATE TABLE bc (bb TEXT) INHERITS (ac); 270select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 271 272insert into ac (aa) values (NULL); 273insert into bc (aa) values (NULL); 274 275alter table bc drop constraint ac_check; -- fail, disallowed 276alter table ac drop constraint ac_check; 277select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 278 279-- try the unnamed-constraint case 280alter table ac add check (aa is not null); 281select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 282 283insert into ac (aa) values (NULL); 284insert into bc (aa) values (NULL); 285 286alter table bc drop constraint ac_aa_check; -- fail, disallowed 287alter table ac drop constraint ac_aa_check; 288select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 289 290alter table ac add constraint ac_check check (aa is not null); 291alter table bc no inherit ac; 292select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 293alter table bc drop constraint ac_check; 294select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 295alter table ac drop constraint ac_check; 296select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 297 298drop table bc; 299drop table ac; 300 301create table ac (a int constraint check_a check (a <> 0)); 302create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); 303select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 304 305drop table bc; 306drop table ac; 307 308create table ac (a int constraint check_a check (a <> 0)); 309create table bc (b int constraint check_b check (b <> 0)); 310create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); 311select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; 312 313alter table cc no inherit bc; 314select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; 315 316drop table cc; 317drop table bc; 318drop table ac; 319 320create table p1(f1 int); 321create table p2(f2 int); 322create table c1(f3 int) inherits(p1,p2); 323insert into c1 values(1,-1,2); 324alter table p2 add constraint cc check (f2>0); -- fail 325alter table p2 add check (f2>0); -- check it without a name, too 326delete from c1; 327insert into c1 values(1,1,2); 328alter table p2 add check (f2>0); 329insert into c1 values(1,-1,2); -- fail 330create table c2(f3 int) inherits(p1,p2); 331\d c2 332create table c3 (f4 int) inherits(c1,c2); 333\d c3 334drop table p1 cascade; 335drop table p2 cascade; 336 337create table pp1 (f1 int); 338create table cc1 (f2 text, f3 int) inherits (pp1); 339alter table pp1 add column a1 int check (a1 > 0); 340\d cc1 341create table cc2(f4 float) inherits(pp1,cc1); 342\d cc2 343alter table pp1 add column a2 int check (a2 > 0); 344\d cc2 345drop table pp1 cascade; 346 347-- Test for renaming in simple multiple inheritance 348CREATE TABLE inht1 (a int, b int); 349CREATE TABLE inhs1 (b int, c int); 350CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); 351 352ALTER TABLE inht1 RENAME a TO aa; 353ALTER TABLE inht1 RENAME b TO bb; -- to be failed 354ALTER TABLE inhts RENAME aa TO aaa; -- to be failed 355ALTER TABLE inhts RENAME d TO dd; 356\d+ inhts 357 358DROP TABLE inhts; 359 360-- Test for renaming in diamond inheritance 361CREATE TABLE inht2 (x int) INHERITS (inht1); 362CREATE TABLE inht3 (y int) INHERITS (inht1); 363CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); 364 365ALTER TABLE inht1 RENAME aa TO aaa; 366\d+ inht4 367 368CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); 369ALTER TABLE inht1 RENAME aaa TO aaaa; 370ALTER TABLE inht1 RENAME b TO bb; -- to be failed 371\d+ inhts 372 373WITH RECURSIVE r AS ( 374 SELECT 'inht1'::regclass AS inhrelid 375UNION ALL 376 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent 377) 378SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected 379 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits 380 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e 381 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal 382 ORDER BY a.attrelid::regclass::name, a.attnum; 383 384DROP TABLE inht1, inhs1 CASCADE; 385 386 387-- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints 388CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); 389CREATE TABLE test_constraints_inh () INHERITS (test_constraints); 390\d+ test_constraints 391ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; 392\d+ test_constraints 393\d+ test_constraints_inh 394DROP TABLE test_constraints_inh; 395DROP TABLE test_constraints; 396 397CREATE TABLE test_ex_constraints ( 398 c circle, 399 EXCLUDE USING gist (c WITH &&) 400); 401CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); 402\d+ test_ex_constraints 403ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; 404\d+ test_ex_constraints 405\d+ test_ex_constraints_inh 406DROP TABLE test_ex_constraints_inh; 407DROP TABLE test_ex_constraints; 408 409-- Test non-inheritable foreign key constraints 410CREATE TABLE test_primary_constraints(id int PRIMARY KEY); 411CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); 412CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); 413\d+ test_primary_constraints 414\d+ test_foreign_constraints 415ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; 416\d+ test_foreign_constraints 417\d+ test_foreign_constraints_inh 418DROP TABLE test_foreign_constraints_inh; 419DROP TABLE test_foreign_constraints; 420DROP TABLE test_primary_constraints; 421 422-- Test that parent and child CHECK constraints can be created in either order 423create table p1(f1 int); 424create table p1_c1() inherits(p1); 425 426alter table p1 add constraint inh_check_constraint1 check (f1 > 0); 427alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); 428 429alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); 430alter table p1 add constraint inh_check_constraint2 check (f1 < 10); 431 432select conrelid::regclass::text as relname, conname, conislocal, coninhcount 433from pg_constraint where conname like 'inh\_check\_constraint%' 434order by 1, 2; 435 436drop table p1 cascade; 437 438-- Test that a valid child can have not-valid parent, but not vice versa 439create table invalid_check_con(f1 int); 440create table invalid_check_con_child() inherits(invalid_check_con); 441 442alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; 443alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail 444alter table invalid_check_con_child drop constraint inh_check_constraint; 445 446insert into invalid_check_con values(0); 447 448alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); 449alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; 450 451insert into invalid_check_con values(0); -- fail 452insert into invalid_check_con_child values(0); -- fail 453 454select conrelid::regclass::text as relname, conname, 455 convalidated, conislocal, coninhcount, connoinherit 456from pg_constraint where conname like 'inh\_check\_constraint%' 457order by 1, 2; 458 459-- We don't drop the invalid_check_con* tables, to test dump/reload with 460 461-- 462-- Test parameterized append plans for inheritance trees 463-- 464 465create temp table patest0 (id, x) as 466 select x, x from generate_series(0,1000) x; 467create temp table patest1() inherits (patest0); 468insert into patest1 469 select x, x from generate_series(0,1000) x; 470create temp table patest2() inherits (patest0); 471insert into patest2 472 select x, x from generate_series(0,1000) x; 473create index patest0i on patest0(id); 474create index patest1i on patest1(id); 475create index patest2i on patest2(id); 476analyze patest0; 477analyze patest1; 478analyze patest2; 479 480explain (costs off) 481select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 482select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 483 484drop index patest2i; 485 486explain (costs off) 487select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 488select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 489 490drop table patest0 cascade; 491 492-- 493-- Test merge-append plans for inheritance trees 494-- 495 496create table matest0 (id serial primary key, name text); 497create table matest1 (id integer primary key) inherits (matest0); 498create table matest2 (id integer primary key) inherits (matest0); 499create table matest3 (id integer primary key) inherits (matest0); 500 501create index matest0i on matest0 ((1-id)); 502create index matest1i on matest1 ((1-id)); 503-- create index matest2i on matest2 ((1-id)); -- intentionally missing 504create index matest3i on matest3 ((1-id)); 505 506insert into matest1 (name) values ('Test 1'); 507insert into matest1 (name) values ('Test 2'); 508insert into matest2 (name) values ('Test 3'); 509insert into matest2 (name) values ('Test 4'); 510insert into matest3 (name) values ('Test 5'); 511insert into matest3 (name) values ('Test 6'); 512 513set enable_indexscan = off; -- force use of seqscan/sort, so no merge 514explain (verbose, costs off) select * from matest0 order by 1-id; 515select * from matest0 order by 1-id; 516explain (verbose, costs off) select min(1-id) from matest0; 517select min(1-id) from matest0; 518reset enable_indexscan; 519 520set enable_seqscan = off; -- plan with fewest seqscans should be merge 521explain (verbose, costs off) select * from matest0 order by 1-id; 522select * from matest0 order by 1-id; 523explain (verbose, costs off) select min(1-id) from matest0; 524select min(1-id) from matest0; 525reset enable_seqscan; 526 527drop table matest0 cascade; 528 529-- 530-- Check that use of an index with an extraneous column doesn't produce 531-- a plan with extraneous sorting 532-- 533 534create table matest0 (a int, b int, c int, d int); 535create table matest1 () inherits(matest0); 536create index matest0i on matest0 (b, c); 537create index matest1i on matest1 (b, c); 538 539set enable_nestloop = off; -- we want a plan with two MergeAppends 540 541explain (costs off) 542select t1.* from matest0 t1, matest0 t2 543where t1.b = t2.b and t2.c = t2.d 544order by t1.b limit 10; 545 546reset enable_nestloop; 547 548drop table matest0 cascade; 549 550-- 551-- Test merge-append for UNION ALL append relations 552-- 553 554set enable_seqscan = off; 555set enable_indexscan = on; 556set enable_bitmapscan = off; 557 558-- Check handling of duplicated, constant, or volatile targetlist items 559explain (costs off) 560SELECT thousand, tenthous FROM tenk1 561UNION ALL 562SELECT thousand, thousand FROM tenk1 563ORDER BY thousand, tenthous; 564 565explain (costs off) 566SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 567UNION ALL 568SELECT 42, 42, hundred FROM tenk1 569ORDER BY thousand, tenthous; 570 571explain (costs off) 572SELECT thousand, tenthous FROM tenk1 573UNION ALL 574SELECT thousand, random()::integer FROM tenk1 575ORDER BY thousand, tenthous; 576 577-- Check min/max aggregate optimization 578explain (costs off) 579SELECT min(x) FROM 580 (SELECT unique1 AS x FROM tenk1 a 581 UNION ALL 582 SELECT unique2 AS x FROM tenk1 b) s; 583 584explain (costs off) 585SELECT min(y) FROM 586 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a 587 UNION ALL 588 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; 589 590-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted 591explain (costs off) 592SELECT x, y FROM 593 (SELECT thousand AS x, tenthous AS y FROM tenk1 a 594 UNION ALL 595 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s 596ORDER BY x, y; 597 598-- exercise rescan code path via a repeatedly-evaluated subquery 599explain (costs off) 600SELECT 601 ARRAY(SELECT f.i FROM ( 602 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 603 UNION ALL 604 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 605 ) f(i) 606 ORDER BY f.i LIMIT 10) 607FROM generate_series(1, 3) g(i); 608 609SELECT 610 ARRAY(SELECT f.i FROM ( 611 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 612 UNION ALL 613 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 614 ) f(i) 615 ORDER BY f.i LIMIT 10) 616FROM generate_series(1, 3) g(i); 617 618reset enable_seqscan; 619reset enable_indexscan; 620reset enable_bitmapscan; 621 622-- 623-- Check handling of a constant-null CHECK constraint 624-- 625create table cnullparent (f1 int); 626create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent); 627insert into cnullchild values(1); 628insert into cnullchild values(2); 629insert into cnullchild values(null); 630select * from cnullparent; 631select * from cnullparent where f1 = 2; 632drop table cnullparent cascade; 633 634-- 635-- Check that constraint exclusion works correctly with partitions using 636-- implicit constraints generated from the partition bound information. 637-- 638create table list_parted ( 639 a varchar 640) partition by list (a); 641create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); 642create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); 643create table part_null_xy partition of list_parted for values in (null, 'xy'); 644 645explain (costs off) select * from list_parted; 646explain (costs off) select * from list_parted where a is null; 647explain (costs off) select * from list_parted where a is not null; 648explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); 649explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); 650explain (costs off) select * from list_parted where a = 'ab'; 651 652create table range_list_parted ( 653 a int, 654 b char(2) 655) partition by range (a); 656create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); 657create table part_1_10_ab partition of part_1_10 for values in ('ab'); 658create table part_1_10_cd partition of part_1_10 for values in ('cd'); 659create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); 660create table part_10_20_ab partition of part_10_20 for values in ('ab'); 661create table part_10_20_cd partition of part_10_20 for values in ('cd'); 662create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); 663create table part_21_30_ab partition of part_21_30 for values in ('ab'); 664create table part_21_30_cd partition of part_21_30 for values in ('cd'); 665create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b); 666create table part_40_inf_ab partition of part_40_inf for values in ('ab'); 667create table part_40_inf_cd partition of part_40_inf for values in ('cd'); 668create table part_40_inf_null partition of part_40_inf for values in (null); 669 670explain (costs off) select * from range_list_parted; 671explain (costs off) select * from range_list_parted where a = 5; 672explain (costs off) select * from range_list_parted where b = 'ab'; 673explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); 674 675/* Should select no rows because range partition key cannot be null */ 676explain (costs off) select * from range_list_parted where a is null; 677 678/* Should only select rows from the null-accepting partition */ 679explain (costs off) select * from range_list_parted where b is null; 680explain (costs off) select * from range_list_parted where a is not null and a < 67; 681explain (costs off) select * from range_list_parted where a >= 30; 682 683drop table list_parted; 684drop table range_list_parted; 685 686-- check that constraint exclusion is able to cope with the partition 687-- constraint emitted for multi-column range partitioned tables 688create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 689create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); 690create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); 691create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); 692create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 693create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); 694create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); 695explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0 696explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1 697explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2 698explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions 699explain (costs off) select * from mcrparted where a > -1; -- scans all partitions 700explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 701explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5 702drop table mcrparted; 703 704-- check that partitioned table Appends cope with being referenced in 705-- subplans 706create table parted_minmax (a int, b varchar(16)) partition by range (a); 707create table parted_minmax1 partition of parted_minmax for values from (1) to (10); 708create index parted_minmax1i on parted_minmax1 (a, b); 709insert into parted_minmax values (1,'12345'); 710explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; 711select min(a), max(a) from parted_minmax where b = '12345'; 712drop table parted_minmax; 713 714 715-- 716-- check that pruning works properly when the partition key is of a 717-- pseudotype 718-- 719 720-- array type list partition key 721create table pp_arrpart (a int[]) partition by list (a); 722create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); 723create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); 724explain (costs off) select * from pp_arrpart where a = '{1}'; 725explain (costs off) select * from pp_arrpart where a = '{1, 2}'; 726explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); 727explain (costs off) update pp_arrpart set a = a where a = '{1}'; 728explain (costs off) delete from pp_arrpart where a = '{1}'; 729drop table pp_arrpart; 730 731-- enum type list partition key 732create type pp_colors as enum ('green', 'blue', 'black'); 733create table pp_enumpart (a pp_colors) partition by list (a); 734create table pp_enumpart_green partition of pp_enumpart for values in ('green'); 735create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); 736explain (costs off) select * from pp_enumpart where a = 'blue'; 737explain (costs off) select * from pp_enumpart where a = 'black'; 738drop table pp_enumpart; 739drop type pp_colors; 740 741-- record type as partition key 742create type pp_rectype as (a int, b int); 743create table pp_recpart (a pp_rectype) partition by list (a); 744create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); 745create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); 746explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; 747explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; 748drop table pp_recpart; 749drop type pp_rectype; 750 751-- range type partition key 752create table pp_intrangepart (a int4range) partition by list (a); 753create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); 754create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); 755explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; 756explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; 757drop table pp_intrangepart; 758 759-- Verify that constraint errors across partition root / child are 760-- handled correctly (Bug #16293) 761CREATE TABLE errtst_parent ( 762 partid int not null, 763 shdata int not null, 764 data int NOT NULL DEFAULT 0, 765 CONSTRAINT shdata_small CHECK(shdata < 3) 766) PARTITION BY RANGE (partid); 767 768-- fast defaults lead to attribute mapping being used in one 769-- direction, but not the other 770CREATE TABLE errtst_child_fastdef ( 771 partid int not null, 772 shdata int not null, 773 CONSTRAINT shdata_small CHECK(shdata < 3) 774); 775 776-- no remapping in either direction necessary 777CREATE TABLE errtst_child_plaindef ( 778 partid int not null, 779 shdata int not null, 780 data int NOT NULL DEFAULT 0, 781 CONSTRAINT shdata_small CHECK(shdata < 3), 782 CHECK(data < 10) 783); 784 785-- remapping in both direction 786CREATE TABLE errtst_child_reorder ( 787 data int NOT NULL DEFAULT 0, 788 shdata int not null, 789 partid int not null, 790 CONSTRAINT shdata_small CHECK(shdata < 3), 791 CHECK(data < 10) 792); 793 794ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0; 795ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10); 796 797ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10); 798ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20); 799ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30); 800 801-- insert without child check constraint error 802INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5'); 803INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5'); 804INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5'); 805 806-- insert with child check constraint error 807INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10'); 808INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10'); 809INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10'); 810 811-- insert with child not null constraint error 812INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL); 813INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL); 814INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL); 815 816-- insert with shared check constraint error 817INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5'); 818INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5'); 819INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5'); 820 821-- within partition update without child check constraint violation 822BEGIN; 823UPDATE errtst_parent SET data = data + 1 WHERE partid = 0; 824UPDATE errtst_parent SET data = data + 1 WHERE partid = 10; 825UPDATE errtst_parent SET data = data + 1 WHERE partid = 20; 826ROLLBACK; 827 828-- within partition update with child check constraint violation 829UPDATE errtst_parent SET data = data + 10 WHERE partid = 0; 830UPDATE errtst_parent SET data = data + 10 WHERE partid = 10; 831UPDATE errtst_parent SET data = data + 10 WHERE partid = 20; 832 833-- direct leaf partition update, without partition id violation 834BEGIN; 835UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0; 836UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10; 837UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20; 838ROLLBACK; 839 840-- direct leaf partition update, with partition id violation 841UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0; 842UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10; 843UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20; 844 845-- partition move, without child check constraint violation 846UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0; 847UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10; 848UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20; 849 850-- partition move, with child check constraint violation 851UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0; 852UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10; 853UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20; 854 855-- partition move, without target partition 856UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20; 857 858DROP TABLE errtst_parent; 859