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; 175 176-- Check UPDATE with multi-level partitioned inherited target 177create table mlparted_tab (a int, b char, c text) partition by list (a); 178create table mlparted_tab_part1 partition of mlparted_tab for values in (1); 179create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b); 180create table mlparted_tab_part3 partition of mlparted_tab for values in (3); 181create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a'); 182create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b'); 183insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a'); 184 185update mlparted_tab mlp set c = 'xxx' 186from 187 (select a from some_tab union all select a+1 from some_tab) ss (a) 188where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3; 189select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2; 190 191drop table mlparted_tab; 192drop table some_tab cascade; 193 194/* Test multiple inheritance of column defaults */ 195 196CREATE TABLE firstparent (tomorrow date default now()::date + 1); 197CREATE TABLE secondparent (tomorrow date default now() :: date + 1); 198CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok 199CREATE TABLE thirdparent (tomorrow date default now()::date - 1); 200CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok 201CREATE TABLE otherchild (tomorrow date default now()) 202 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default 203 204DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; 205 206-- Test changing the type of inherited columns 207insert into d values('test','one','two','three'); 208alter table a alter column aa type integer using bit_length(aa); 209select * from d; 210 211-- The above verified that we can change the type of a multiply-inherited 212-- column; but we should reject that if any definition was inherited from 213-- an unrelated parent. 214create temp table parent1(f1 int, f2 int); 215create temp table parent2(f1 int, f3 bigint); 216create temp table childtab(f4 int) inherits(parent1, parent2); 217alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 218alter table parent1 alter column f2 type bigint; -- ok 219 220-- Test non-inheritable parent constraints 221create table p1(ff1 int); 222alter table p1 add constraint p1chk check (ff1 > 0) no inherit; 223alter table p1 add constraint p2chk check (ff1 > 10); 224-- connoinherit should be true for NO INHERIT constraint 225select 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; 226 227-- Test that child does not inherit NO INHERIT constraints 228create table c1 () inherits (p1); 229\d p1 230\d c1 231 232-- Test that child does not override inheritable constraints of the parent 233create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails 234 235drop table p1 cascade; 236 237-- Tests for casting between the rowtypes of parent and child 238-- tables. See the pgsql-hackers thread beginning Dec. 4/04 239create table base (i integer); 240create table derived () inherits (base); 241create table more_derived (like derived, b int) inherits (derived); 242insert into derived (i) values (0); 243select derived::base from derived; 244select NULL::derived::base; 245-- remove redundant conversions. 246explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived; 247explain (verbose on, costs off) select (1, 2)::more_derived::derived::base; 248drop table more_derived; 249drop table derived; 250drop table base; 251 252create table p1(ff1 int); 253create table p2(f1 text); 254create function p2text(p2) returns text as 'select $1.f1' language sql; 255create table c1(f3 int) inherits(p1,p2); 256insert into c1 values(123456789, 'hi', 42); 257select p2text(c1.*) from c1; 258drop function p2text(p2); 259drop table c1; 260drop table p2; 261drop table p1; 262 263CREATE TABLE ac (aa TEXT); 264alter table ac add constraint ac_check check (aa is not null); 265CREATE TABLE bc (bb TEXT) INHERITS (ac); 266select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 267 268insert into ac (aa) values (NULL); 269insert into bc (aa) values (NULL); 270 271alter table bc drop constraint ac_check; -- fail, disallowed 272alter table ac drop constraint ac_check; 273select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 274 275-- try the unnamed-constraint case 276alter table ac add check (aa is not null); 277select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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 279insert into ac (aa) values (NULL); 280insert into bc (aa) values (NULL); 281 282alter table bc drop constraint ac_aa_check; -- fail, disallowed 283alter table ac drop constraint ac_aa_check; 284select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 285 286alter table ac add constraint ac_check check (aa is not null); 287alter table bc no inherit ac; 288select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 289alter table bc drop constraint ac_check; 290select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 291alter table ac drop constraint ac_check; 292select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 293 294drop table bc; 295drop table ac; 296 297create table ac (a int constraint check_a check (a <> 0)); 298create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); 299select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 300 301drop table bc; 302drop table ac; 303 304create table ac (a int constraint check_a check (a <> 0)); 305create table bc (b int constraint check_b check (b <> 0)); 306create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); 307select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 308 309alter table cc no inherit bc; 310select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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; 311 312drop table cc; 313drop table bc; 314drop table ac; 315 316create table p1(f1 int); 317create table p2(f2 int); 318create table c1(f3 int) inherits(p1,p2); 319insert into c1 values(1,-1,2); 320alter table p2 add constraint cc check (f2>0); -- fail 321alter table p2 add check (f2>0); -- check it without a name, too 322delete from c1; 323insert into c1 values(1,1,2); 324alter table p2 add check (f2>0); 325insert into c1 values(1,-1,2); -- fail 326create table c2(f3 int) inherits(p1,p2); 327\d c2 328create table c3 (f4 int) inherits(c1,c2); 329\d c3 330drop table p1 cascade; 331drop table p2 cascade; 332 333create table pp1 (f1 int); 334create table cc1 (f2 text, f3 int) inherits (pp1); 335alter table pp1 add column a1 int check (a1 > 0); 336\d cc1 337create table cc2(f4 float) inherits(pp1,cc1); 338\d cc2 339alter table pp1 add column a2 int check (a2 > 0); 340\d cc2 341drop table pp1 cascade; 342 343-- Test for renaming in simple multiple inheritance 344CREATE TABLE inht1 (a int, b int); 345CREATE TABLE inhs1 (b int, c int); 346CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); 347 348ALTER TABLE inht1 RENAME a TO aa; 349ALTER TABLE inht1 RENAME b TO bb; -- to be failed 350ALTER TABLE inhts RENAME aa TO aaa; -- to be failed 351ALTER TABLE inhts RENAME d TO dd; 352\d+ inhts 353 354DROP TABLE inhts; 355 356-- Test for renaming in diamond inheritance 357CREATE TABLE inht2 (x int) INHERITS (inht1); 358CREATE TABLE inht3 (y int) INHERITS (inht1); 359CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); 360 361ALTER TABLE inht1 RENAME aa TO aaa; 362\d+ inht4 363 364CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); 365ALTER TABLE inht1 RENAME aaa TO aaaa; 366ALTER TABLE inht1 RENAME b TO bb; -- to be failed 367\d+ inhts 368 369WITH RECURSIVE r AS ( 370 SELECT 'inht1'::regclass AS inhrelid 371UNION ALL 372 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent 373) 374SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected 375 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits 376 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e 377 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal 378 ORDER BY a.attrelid::regclass::name, a.attnum; 379 380DROP TABLE inht1, inhs1 CASCADE; 381 382 383-- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints 384CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); 385CREATE TABLE test_constraints_inh () INHERITS (test_constraints); 386\d+ test_constraints 387ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; 388\d+ test_constraints 389\d+ test_constraints_inh 390DROP TABLE test_constraints_inh; 391DROP TABLE test_constraints; 392 393CREATE TABLE test_ex_constraints ( 394 c circle, 395 EXCLUDE USING gist (c WITH &&) 396); 397CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); 398\d+ test_ex_constraints 399ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; 400\d+ test_ex_constraints 401\d+ test_ex_constraints_inh 402DROP TABLE test_ex_constraints_inh; 403DROP TABLE test_ex_constraints; 404 405-- Test non-inheritable foreign key constraints 406CREATE TABLE test_primary_constraints(id int PRIMARY KEY); 407CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); 408CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); 409\d+ test_primary_constraints 410\d+ test_foreign_constraints 411ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; 412\d+ test_foreign_constraints 413\d+ test_foreign_constraints_inh 414DROP TABLE test_foreign_constraints_inh; 415DROP TABLE test_foreign_constraints; 416DROP TABLE test_primary_constraints; 417 418-- Test foreign key behavior 419create table inh_fk_1 (a int primary key); 420insert into inh_fk_1 values (1), (2), (3); 421create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete cascade); 422insert into inh_fk_2 values (11, 1), (22, 2), (33, 3); 423create table inh_fk_2_child () inherits (inh_fk_2); 424insert into inh_fk_2_child values (111, 1), (222, 2); 425delete from inh_fk_1 where a = 1; 426select * from inh_fk_1 order by 1; 427select * from inh_fk_2 order by 1, 2; 428drop table inh_fk_1, inh_fk_2, inh_fk_2_child; 429 430-- Test that parent and child CHECK constraints can be created in either order 431create table p1(f1 int); 432create table p1_c1() inherits(p1); 433 434alter table p1 add constraint inh_check_constraint1 check (f1 > 0); 435alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); 436 437alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); 438alter table p1 add constraint inh_check_constraint2 check (f1 < 10); 439 440select conrelid::regclass::text as relname, conname, conislocal, coninhcount 441from pg_constraint where conname like 'inh\_check\_constraint%' 442order by 1, 2; 443 444drop table p1 cascade; 445 446-- Test that a valid child can have not-valid parent, but not vice versa 447create table invalid_check_con(f1 int); 448create table invalid_check_con_child() inherits(invalid_check_con); 449 450alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; 451alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail 452alter table invalid_check_con_child drop constraint inh_check_constraint; 453 454insert into invalid_check_con values(0); 455 456alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); 457alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; 458 459insert into invalid_check_con values(0); -- fail 460insert into invalid_check_con_child values(0); -- fail 461 462select conrelid::regclass::text as relname, conname, 463 convalidated, conislocal, coninhcount, connoinherit 464from pg_constraint where conname like 'inh\_check\_constraint%' 465order by 1, 2; 466 467-- We don't drop the invalid_check_con* tables, to test dump/reload with 468 469-- 470-- Test parameterized append plans for inheritance trees 471-- 472 473create temp table patest0 (id, x) as 474 select x, x from generate_series(0,1000) x; 475create temp table patest1() inherits (patest0); 476insert into patest1 477 select x, x from generate_series(0,1000) x; 478create temp table patest2() inherits (patest0); 479insert into patest2 480 select x, x from generate_series(0,1000) x; 481create index patest0i on patest0(id); 482create index patest1i on patest1(id); 483create index patest2i on patest2(id); 484analyze patest0; 485analyze patest1; 486analyze patest2; 487 488explain (costs off) 489select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 490select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 491 492drop index patest2i; 493 494explain (costs off) 495select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 496select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 497 498drop table patest0 cascade; 499 500-- 501-- Test merge-append plans for inheritance trees 502-- 503 504create table matest0 (id serial primary key, name text); 505create table matest1 (id integer primary key) inherits (matest0); 506create table matest2 (id integer primary key) inherits (matest0); 507create table matest3 (id integer primary key) inherits (matest0); 508 509create index matest0i on matest0 ((1-id)); 510create index matest1i on matest1 ((1-id)); 511-- create index matest2i on matest2 ((1-id)); -- intentionally missing 512create index matest3i on matest3 ((1-id)); 513 514insert into matest1 (name) values ('Test 1'); 515insert into matest1 (name) values ('Test 2'); 516insert into matest2 (name) values ('Test 3'); 517insert into matest2 (name) values ('Test 4'); 518insert into matest3 (name) values ('Test 5'); 519insert into matest3 (name) values ('Test 6'); 520 521set enable_indexscan = off; -- force use of seqscan/sort, so no merge 522explain (verbose, costs off) select * from matest0 order by 1-id; 523select * from matest0 order by 1-id; 524explain (verbose, costs off) select min(1-id) from matest0; 525select min(1-id) from matest0; 526reset enable_indexscan; 527 528set enable_seqscan = off; -- plan with fewest seqscans should be merge 529set enable_parallel_append = off; -- Don't let parallel-append interfere 530explain (verbose, costs off) select * from matest0 order by 1-id; 531select * from matest0 order by 1-id; 532explain (verbose, costs off) select min(1-id) from matest0; 533select min(1-id) from matest0; 534reset enable_seqscan; 535reset enable_parallel_append; 536 537drop table matest0 cascade; 538 539-- 540-- Check that use of an index with an extraneous column doesn't produce 541-- a plan with extraneous sorting 542-- 543 544create table matest0 (a int, b int, c int, d int); 545create table matest1 () inherits(matest0); 546create index matest0i on matest0 (b, c); 547create index matest1i on matest1 (b, c); 548 549set enable_nestloop = off; -- we want a plan with two MergeAppends 550 551explain (costs off) 552select t1.* from matest0 t1, matest0 t2 553where t1.b = t2.b and t2.c = t2.d 554order by t1.b limit 10; 555 556reset enable_nestloop; 557 558drop table matest0 cascade; 559 560-- 561-- Test merge-append for UNION ALL append relations 562-- 563 564set enable_seqscan = off; 565set enable_indexscan = on; 566set enable_bitmapscan = off; 567 568-- Check handling of duplicated, constant, or volatile targetlist items 569explain (costs off) 570SELECT thousand, tenthous FROM tenk1 571UNION ALL 572SELECT thousand, thousand FROM tenk1 573ORDER BY thousand, tenthous; 574 575explain (costs off) 576SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 577UNION ALL 578SELECT 42, 42, hundred FROM tenk1 579ORDER BY thousand, tenthous; 580 581explain (costs off) 582SELECT thousand, tenthous FROM tenk1 583UNION ALL 584SELECT thousand, random()::integer FROM tenk1 585ORDER BY thousand, tenthous; 586 587-- Check min/max aggregate optimization 588explain (costs off) 589SELECT min(x) FROM 590 (SELECT unique1 AS x FROM tenk1 a 591 UNION ALL 592 SELECT unique2 AS x FROM tenk1 b) s; 593 594explain (costs off) 595SELECT min(y) FROM 596 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a 597 UNION ALL 598 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; 599 600-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted 601explain (costs off) 602SELECT x, y FROM 603 (SELECT thousand AS x, tenthous AS y FROM tenk1 a 604 UNION ALL 605 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s 606ORDER BY x, y; 607 608-- exercise rescan code path via a repeatedly-evaluated subquery 609explain (costs off) 610SELECT 611 ARRAY(SELECT f.i FROM ( 612 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 613 UNION ALL 614 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 615 ) f(i) 616 ORDER BY f.i LIMIT 10) 617FROM generate_series(1, 3) g(i); 618 619SELECT 620 ARRAY(SELECT f.i FROM ( 621 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 622 UNION ALL 623 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 624 ) f(i) 625 ORDER BY f.i LIMIT 10) 626FROM generate_series(1, 3) g(i); 627 628reset enable_seqscan; 629reset enable_indexscan; 630reset enable_bitmapscan; 631 632-- 633-- Check handling of a constant-null CHECK constraint 634-- 635create table cnullparent (f1 int); 636create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent); 637insert into cnullchild values(1); 638insert into cnullchild values(2); 639insert into cnullchild values(null); 640select * from cnullparent; 641select * from cnullparent where f1 = 2; 642drop table cnullparent cascade; 643 644-- 645-- Check use of temporary tables with inheritance trees 646-- 647create table inh_perm_parent (a1 int); 648create temp table inh_temp_parent (a1 int); 649create temp table inh_temp_child () inherits (inh_perm_parent); -- ok 650create table inh_perm_child () inherits (inh_temp_parent); -- error 651create temp table inh_temp_child_2 () inherits (inh_temp_parent); -- ok 652insert into inh_perm_parent values (1); 653insert into inh_temp_parent values (2); 654insert into inh_temp_child values (3); 655insert into inh_temp_child_2 values (4); 656select tableoid::regclass, a1 from inh_perm_parent; 657select tableoid::regclass, a1 from inh_temp_parent; 658drop table inh_perm_parent cascade; 659drop table inh_temp_parent cascade; 660 661-- 662-- Check that constraint exclusion works correctly with partitions using 663-- implicit constraints generated from the partition bound information. 664-- 665create table list_parted ( 666 a varchar 667) partition by list (a); 668create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); 669create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); 670create table part_null_xy partition of list_parted for values in (null, 'xy'); 671 672explain (costs off) select * from list_parted; 673explain (costs off) select * from list_parted where a is null; 674explain (costs off) select * from list_parted where a is not null; 675explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); 676explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); 677explain (costs off) select * from list_parted where a = 'ab'; 678 679create table range_list_parted ( 680 a int, 681 b char(2) 682) partition by range (a); 683create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); 684create table part_1_10_ab partition of part_1_10 for values in ('ab'); 685create table part_1_10_cd partition of part_1_10 for values in ('cd'); 686create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); 687create table part_10_20_ab partition of part_10_20 for values in ('ab'); 688create table part_10_20_cd partition of part_10_20 for values in ('cd'); 689create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); 690create table part_21_30_ab partition of part_21_30 for values in ('ab'); 691create table part_21_30_cd partition of part_21_30 for values in ('cd'); 692create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b); 693create table part_40_inf_ab partition of part_40_inf for values in ('ab'); 694create table part_40_inf_cd partition of part_40_inf for values in ('cd'); 695create table part_40_inf_null partition of part_40_inf for values in (null); 696 697explain (costs off) select * from range_list_parted; 698explain (costs off) select * from range_list_parted where a = 5; 699explain (costs off) select * from range_list_parted where b = 'ab'; 700explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); 701 702/* Should select no rows because range partition key cannot be null */ 703explain (costs off) select * from range_list_parted where a is null; 704 705/* Should only select rows from the null-accepting partition */ 706explain (costs off) select * from range_list_parted where b is null; 707explain (costs off) select * from range_list_parted where a is not null and a < 67; 708explain (costs off) select * from range_list_parted where a >= 30; 709 710drop table list_parted; 711drop table range_list_parted; 712 713-- check that constraint exclusion is able to cope with the partition 714-- constraint emitted for multi-column range partitioned tables 715create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 716create table mcrparted_def partition of mcrparted default; 717create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); 718create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); 719create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); 720create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 721create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); 722create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); 723explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def 724explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def 725explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def 726explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions 727explain (costs off) select * from mcrparted where a > -1; -- scans all partitions 728explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 729explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def 730 731-- check that partitioned table Appends cope with being referenced in 732-- subplans 733create table parted_minmax (a int, b varchar(16)) partition by range (a); 734create table parted_minmax1 partition of parted_minmax for values from (1) to (10); 735create index parted_minmax1i on parted_minmax1 (a, b); 736insert into parted_minmax values (1,'12345'); 737explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; 738select min(a), max(a) from parted_minmax where b = '12345'; 739drop table parted_minmax; 740 741-- Test code that uses Append nodes in place of MergeAppend when the 742-- partition ordering matches the desired ordering. 743 744create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c); 745 746-- MergeAppend must be used when a default partition exists 747explain (costs off) select * from mcrparted order by a, abs(b), c; 748 749drop table mcrparted_def; 750 751-- Append is used for a RANGE partitioned table with no default 752-- and no subpartitions 753explain (costs off) select * from mcrparted order by a, abs(b), c; 754 755-- Append is used with subpaths in reverse order with backwards index scans 756explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc; 757 758-- check that Append plan is used containing a MergeAppend for sub-partitions 759-- that are unordered. 760drop table mcrparted5; 761create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue) partition by list (a); 762create table mcrparted5a partition of mcrparted5 for values in(20); 763create table mcrparted5_def partition of mcrparted5 default; 764 765explain (costs off) select * from mcrparted order by a, abs(b), c; 766 767drop table mcrparted5_def; 768 769-- check that an Append plan is used and the sub-partitions are flattened 770-- into the main Append when the sub-partition is unordered but contains 771-- just a single sub-partition. 772explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c; 773 774-- check that Append is used when the sub-partitioned tables are pruned 775-- during planning. 776explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c; 777 778create table mclparted (a int) partition by list(a); 779create table mclparted1 partition of mclparted for values in(1); 780create table mclparted2 partition of mclparted for values in(2); 781create index on mclparted (a); 782 783-- Ensure an Append is used for a list partition with an order by. 784explain (costs off) select * from mclparted order by a; 785 786-- Ensure a MergeAppend is used when a partition exists with interleaved 787-- datums in the partition bound. 788create table mclparted3_5 partition of mclparted for values in(3,5); 789create table mclparted4 partition of mclparted for values in(4); 790 791explain (costs off) select * from mclparted order by a; 792 793drop table mclparted; 794 795-- Ensure subplans which don't have a path with the correct pathkeys get 796-- sorted correctly. 797drop index mcrparted_a_abs_c_idx; 798create index on mcrparted1 (a, abs(b), c); 799create index on mcrparted2 (a, abs(b), c); 800create index on mcrparted3 (a, abs(b), c); 801create index on mcrparted4 (a, abs(b), c); 802 803explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1; 804 805set enable_bitmapscan = 0; 806-- Ensure Append node can be used when the partition is ordered by some 807-- pathkeys which were deemed redundant. 808explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c; 809reset enable_bitmapscan; 810 811drop table mcrparted; 812 813-- Ensure LIST partitions allow an Append to be used instead of a MergeAppend 814create table bool_lp (b bool) partition by list(b); 815create table bool_lp_true partition of bool_lp for values in(true); 816create table bool_lp_false partition of bool_lp for values in(false); 817create index on bool_lp (b); 818 819explain (costs off) select * from bool_lp order by b; 820 821drop table bool_lp; 822 823-- Ensure const bool quals can be properly detected as redundant 824create table bool_rp (b bool, a int) partition by range(b,a); 825create table bool_rp_false_1k partition of bool_rp for values from (false,0) to (false,1000); 826create table bool_rp_true_1k partition of bool_rp for values from (true,0) to (true,1000); 827create table bool_rp_false_2k partition of bool_rp for values from (false,1000) to (false,2000); 828create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000); 829create index on bool_rp (b,a); 830explain (costs off) select * from bool_rp where b = true order by b,a; 831explain (costs off) select * from bool_rp where b = false order by b,a; 832explain (costs off) select * from bool_rp where b = true order by a; 833explain (costs off) select * from bool_rp where b = false order by a; 834 835drop table bool_rp; 836 837-- Ensure an Append scan is chosen when the partition order is a subset of 838-- the required order. 839create table range_parted (a int, b int, c int) partition by range(a, b); 840create table range_parted1 partition of range_parted for values from (0,0) to (10,10); 841create table range_parted2 partition of range_parted for values from (10,10) to (20,20); 842create index on range_parted (a,b,c); 843 844explain (costs off) select * from range_parted order by a,b,c; 845explain (costs off) select * from range_parted order by a desc,b desc,c desc; 846 847drop table range_parted; 848 849-- Check that we allow access to a child table's statistics when the user 850-- has permissions only for the parent table. 851create table permtest_parent (a int, b text, c text) partition by list (a); 852create table permtest_child (b text, c text, a int) partition by list (b); 853create table permtest_grandchild (c text, b text, a int); 854alter table permtest_child attach partition permtest_grandchild for values in ('a'); 855alter table permtest_parent attach partition permtest_child for values in (1); 856create index on permtest_parent (left(c, 3)); 857insert into permtest_parent 858 select 1, 'a', left(md5(i::text), 5) from generate_series(0, 100) i; 859analyze permtest_parent; 860create role regress_no_child_access; 861revoke all on permtest_grandchild from regress_no_child_access; 862grant select on permtest_parent to regress_no_child_access; 863set session authorization regress_no_child_access; 864-- without stats access, these queries would produce hash join plans: 865explain (costs off) 866 select * from permtest_parent p1 inner join permtest_parent p2 867 on p1.a = p2.a and p1.c ~ 'a1$'; 868explain (costs off) 869 select * from permtest_parent p1 inner join permtest_parent p2 870 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$'; 871reset session authorization; 872revoke all on permtest_parent from regress_no_child_access; 873grant select(a,c) on permtest_parent to regress_no_child_access; 874set session authorization regress_no_child_access; 875explain (costs off) 876 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2 877 on p1.a = p2.a and p1.c ~ 'a1$'; 878-- we will not have access to the expression index's stats here: 879explain (costs off) 880 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2 881 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$'; 882reset session authorization; 883revoke all on permtest_parent from regress_no_child_access; 884drop role regress_no_child_access; 885drop table permtest_parent; 886 887-- Verify that constraint errors across partition root / child are 888-- handled correctly (Bug #16293) 889CREATE TABLE errtst_parent ( 890 partid int not null, 891 shdata int not null, 892 data int NOT NULL DEFAULT 0, 893 CONSTRAINT shdata_small CHECK(shdata < 3) 894) PARTITION BY RANGE (partid); 895 896-- fast defaults lead to attribute mapping being used in one 897-- direction, but not the other 898CREATE TABLE errtst_child_fastdef ( 899 partid int not null, 900 shdata int not null, 901 CONSTRAINT shdata_small CHECK(shdata < 3) 902); 903 904-- no remapping in either direction necessary 905CREATE TABLE errtst_child_plaindef ( 906 partid int not null, 907 shdata int not null, 908 data int NOT NULL DEFAULT 0, 909 CONSTRAINT shdata_small CHECK(shdata < 3), 910 CHECK(data < 10) 911); 912 913-- remapping in both direction 914CREATE TABLE errtst_child_reorder ( 915 data int NOT NULL DEFAULT 0, 916 shdata int not null, 917 partid int not null, 918 CONSTRAINT shdata_small CHECK(shdata < 3), 919 CHECK(data < 10) 920); 921 922ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0; 923ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10); 924 925ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10); 926ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20); 927ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30); 928 929-- insert without child check constraint error 930INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5'); 931INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5'); 932INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5'); 933 934-- insert with child check constraint error 935INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10'); 936INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10'); 937INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10'); 938 939-- insert with child not null constraint error 940INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL); 941INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL); 942INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL); 943 944-- insert with shared check constraint error 945INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5'); 946INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5'); 947INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5'); 948 949-- within partition update without child check constraint violation 950BEGIN; 951UPDATE errtst_parent SET data = data + 1 WHERE partid = 0; 952UPDATE errtst_parent SET data = data + 1 WHERE partid = 10; 953UPDATE errtst_parent SET data = data + 1 WHERE partid = 20; 954ROLLBACK; 955 956-- within partition update with child check constraint violation 957UPDATE errtst_parent SET data = data + 10 WHERE partid = 0; 958UPDATE errtst_parent SET data = data + 10 WHERE partid = 10; 959UPDATE errtst_parent SET data = data + 10 WHERE partid = 20; 960 961-- direct leaf partition update, without partition id violation 962BEGIN; 963UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0; 964UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10; 965UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20; 966ROLLBACK; 967 968-- direct leaf partition update, with partition id violation 969UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0; 970UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10; 971UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20; 972 973-- partition move, without child check constraint violation 974BEGIN; 975UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0; 976UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10; 977UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20; 978ROLLBACK; 979 980-- partition move, with child check constraint violation 981UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0; 982UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10; 983UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20; 984 985-- partition move, without target partition 986UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20; 987 988DROP TABLE errtst_parent; 989