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/* Test multiple inheritance of column defaults */ 147 148CREATE TABLE firstparent (tomorrow date default now()::date + 1); 149CREATE TABLE secondparent (tomorrow date default now() :: date + 1); 150CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok 151CREATE TABLE thirdparent (tomorrow date default now()::date - 1); 152CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok 153CREATE TABLE otherchild (tomorrow date default now()) 154 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default 155 156DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; 157 158-- Test changing the type of inherited columns 159insert into d values('test','one','two','three'); 160alter table a alter column aa type integer using bit_length(aa); 161select * from d; 162 163-- The above verified that we can change the type of a multiply-inherited 164-- column; but we should reject that if any definition was inherited from 165-- an unrelated parent. 166create temp table parent1(f1 int, f2 int); 167create temp table parent2(f1 int, f3 bigint); 168create temp table childtab(f4 int) inherits(parent1, parent2); 169alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 170alter table parent1 alter column f2 type bigint; -- ok 171 172-- check that oid column is handled properly during alter table inherit 173create table oid_parent (a int) with oids; 174 175create table oid_child () inherits (oid_parent); 176select attinhcount, attislocal from pg_attribute 177 where attrelid = 'oid_child'::regclass and attname = 'oid'; 178drop table oid_child; 179 180create table oid_child (a int) without oids; 181alter table oid_child inherit oid_parent; -- fail 182alter table oid_child set with oids; 183select attinhcount, attislocal from pg_attribute 184 where attrelid = 'oid_child'::regclass and attname = 'oid'; 185alter table oid_child inherit oid_parent; 186select attinhcount, attislocal from pg_attribute 187 where attrelid = 'oid_child'::regclass and attname = 'oid'; 188alter table oid_child set without oids; -- fail 189alter table oid_parent set without oids; 190select attinhcount, attislocal from pg_attribute 191 where attrelid = 'oid_child'::regclass and attname = 'oid'; 192alter table oid_child set without oids; 193select attinhcount, attislocal from pg_attribute 194 where attrelid = 'oid_child'::regclass and attname = 'oid'; 195 196drop table oid_parent cascade; 197 198-- Test non-inheritable parent constraints 199create table p1(ff1 int); 200alter table p1 add constraint p1chk check (ff1 > 0) no inherit; 201alter table p1 add constraint p2chk check (ff1 > 10); 202-- connoinherit should be true for NO INHERIT constraint 203select 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; 204 205-- Test that child does not inherit NO INHERIT constraints 206create table c1 () inherits (p1); 207\d p1 208\d c1 209 210-- Test that child does not override inheritable constraints of the parent 211create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails 212 213drop table p1 cascade; 214 215-- Tests for casting between the rowtypes of parent and child 216-- tables. See the pgsql-hackers thread beginning Dec. 4/04 217create table base (i integer); 218create table derived () inherits (base); 219insert into derived (i) values (0); 220select derived::base from derived; 221drop table derived; 222drop table base; 223 224create table p1(ff1 int); 225create table p2(f1 text); 226create function p2text(p2) returns text as 'select $1.f1' language sql; 227create table c1(f3 int) inherits(p1,p2); 228insert into c1 values(123456789, 'hi', 42); 229select p2text(c1.*) from c1; 230drop function p2text(p2); 231drop table c1; 232drop table p2; 233drop table p1; 234 235CREATE TABLE ac (aa TEXT); 236alter table ac add constraint ac_check check (aa is not null); 237CREATE TABLE bc (bb TEXT) INHERITS (ac); 238select 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; 239 240insert into ac (aa) values (NULL); 241insert into bc (aa) values (NULL); 242 243alter table bc drop constraint ac_check; -- fail, disallowed 244alter table ac drop constraint ac_check; 245select 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; 246 247-- try the unnamed-constraint case 248alter table ac add check (aa is not null); 249select 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; 250 251insert into ac (aa) values (NULL); 252insert into bc (aa) values (NULL); 253 254alter table bc drop constraint ac_aa_check; -- fail, disallowed 255alter table ac drop constraint ac_aa_check; 256select 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; 257 258alter table ac add constraint ac_check check (aa is not null); 259alter table bc no inherit ac; 260select 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; 261alter table bc drop constraint ac_check; 262select 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; 263alter table ac drop constraint ac_check; 264select 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; 265 266drop table bc; 267drop table ac; 268 269create table ac (a int constraint check_a check (a <> 0)); 270create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); 271select 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; 272 273drop table bc; 274drop table ac; 275 276create table ac (a int constraint check_a check (a <> 0)); 277create table bc (b int constraint check_b check (b <> 0)); 278create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); 279select 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; 280 281alter table cc no inherit bc; 282select 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; 283 284drop table cc; 285drop table bc; 286drop table ac; 287 288create table p1(f1 int); 289create table p2(f2 int); 290create table c1(f3 int) inherits(p1,p2); 291insert into c1 values(1,-1,2); 292alter table p2 add constraint cc check (f2>0); -- fail 293alter table p2 add check (f2>0); -- check it without a name, too 294delete from c1; 295insert into c1 values(1,1,2); 296alter table p2 add check (f2>0); 297insert into c1 values(1,-1,2); -- fail 298create table c2(f3 int) inherits(p1,p2); 299\d c2 300create table c3 (f4 int) inherits(c1,c2); 301\d c3 302drop table p1 cascade; 303drop table p2 cascade; 304 305create table pp1 (f1 int); 306create table cc1 (f2 text, f3 int) inherits (pp1); 307alter table pp1 add column a1 int check (a1 > 0); 308\d cc1 309create table cc2(f4 float) inherits(pp1,cc1); 310\d cc2 311alter table pp1 add column a2 int check (a2 > 0); 312\d cc2 313drop table pp1 cascade; 314 315-- Test for renaming in simple multiple inheritance 316CREATE TABLE inht1 (a int, b int); 317CREATE TABLE inhs1 (b int, c int); 318CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); 319 320ALTER TABLE inht1 RENAME a TO aa; 321ALTER TABLE inht1 RENAME b TO bb; -- to be failed 322ALTER TABLE inhts RENAME aa TO aaa; -- to be failed 323ALTER TABLE inhts RENAME d TO dd; 324\d+ inhts 325 326DROP TABLE inhts; 327 328-- Test for renaming in diamond inheritance 329CREATE TABLE inht2 (x int) INHERITS (inht1); 330CREATE TABLE inht3 (y int) INHERITS (inht1); 331CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); 332 333ALTER TABLE inht1 RENAME aa TO aaa; 334\d+ inht4 335 336CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); 337ALTER TABLE inht1 RENAME aaa TO aaaa; 338ALTER TABLE inht1 RENAME b TO bb; -- to be failed 339\d+ inhts 340 341WITH RECURSIVE r AS ( 342 SELECT 'inht1'::regclass AS inhrelid 343UNION ALL 344 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent 345) 346SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected 347 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits 348 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e 349 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal 350 ORDER BY a.attrelid::regclass::name, a.attnum; 351 352DROP TABLE inht1, inhs1 CASCADE; 353 354 355-- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints 356CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); 357CREATE TABLE test_constraints_inh () INHERITS (test_constraints); 358\d+ test_constraints 359ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; 360\d+ test_constraints 361\d+ test_constraints_inh 362DROP TABLE test_constraints_inh; 363DROP TABLE test_constraints; 364 365CREATE TABLE test_ex_constraints ( 366 c circle, 367 EXCLUDE USING gist (c WITH &&) 368); 369CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); 370\d+ test_ex_constraints 371ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; 372\d+ test_ex_constraints 373\d+ test_ex_constraints_inh 374DROP TABLE test_ex_constraints_inh; 375DROP TABLE test_ex_constraints; 376 377-- Test non-inheritable foreign key constraints 378CREATE TABLE test_primary_constraints(id int PRIMARY KEY); 379CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); 380CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); 381\d+ test_primary_constraints 382\d+ test_foreign_constraints 383ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; 384\d+ test_foreign_constraints 385\d+ test_foreign_constraints_inh 386DROP TABLE test_foreign_constraints_inh; 387DROP TABLE test_foreign_constraints; 388DROP TABLE test_primary_constraints; 389 390-- Test that parent and child CHECK constraints can be created in either order 391create table p1(f1 int); 392create table p1_c1() inherits(p1); 393 394alter table p1 add constraint inh_check_constraint1 check (f1 > 0); 395alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); 396 397alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); 398alter table p1 add constraint inh_check_constraint2 check (f1 < 10); 399 400select conrelid::regclass::text as relname, conname, conislocal, coninhcount 401from pg_constraint where conname like 'inh\_check\_constraint%' 402order by 1, 2; 403 404drop table p1 cascade; 405 406-- Test that a valid child can have not-valid parent, but not vice versa 407create table invalid_check_con(f1 int); 408create table invalid_check_con_child() inherits(invalid_check_con); 409 410alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; 411alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail 412alter table invalid_check_con_child drop constraint inh_check_constraint; 413 414insert into invalid_check_con values(0); 415 416alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); 417alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; 418 419insert into invalid_check_con values(0); -- fail 420insert into invalid_check_con_child values(0); -- fail 421 422select conrelid::regclass::text as relname, conname, 423 convalidated, conislocal, coninhcount, connoinherit 424from pg_constraint where conname like 'inh\_check\_constraint%' 425order by 1, 2; 426 427-- We don't drop the invalid_check_con* tables, to test dump/reload with 428 429-- 430-- Test parameterized append plans for inheritance trees 431-- 432 433create temp table patest0 (id, x) as 434 select x, x from generate_series(0,1000) x; 435create temp table patest1() inherits (patest0); 436insert into patest1 437 select x, x from generate_series(0,1000) x; 438create temp table patest2() inherits (patest0); 439insert into patest2 440 select x, x from generate_series(0,1000) x; 441create index patest0i on patest0(id); 442create index patest1i on patest1(id); 443create index patest2i on patest2(id); 444analyze patest0; 445analyze patest1; 446analyze patest2; 447 448explain (costs off) 449select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 450select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 451 452drop index patest2i; 453 454explain (costs off) 455select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 456select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 457 458drop table patest0 cascade; 459 460-- 461-- Test merge-append plans for inheritance trees 462-- 463 464create table matest0 (id serial primary key, name text); 465create table matest1 (id integer primary key) inherits (matest0); 466create table matest2 (id integer primary key) inherits (matest0); 467create table matest3 (id integer primary key) inherits (matest0); 468 469create index matest0i on matest0 ((1-id)); 470create index matest1i on matest1 ((1-id)); 471-- create index matest2i on matest2 ((1-id)); -- intentionally missing 472create index matest3i on matest3 ((1-id)); 473 474insert into matest1 (name) values ('Test 1'); 475insert into matest1 (name) values ('Test 2'); 476insert into matest2 (name) values ('Test 3'); 477insert into matest2 (name) values ('Test 4'); 478insert into matest3 (name) values ('Test 5'); 479insert into matest3 (name) values ('Test 6'); 480 481set enable_indexscan = off; -- force use of seqscan/sort, so no merge 482explain (verbose, costs off) select * from matest0 order by 1-id; 483select * from matest0 order by 1-id; 484explain (verbose, costs off) select min(1-id) from matest0; 485select min(1-id) from matest0; 486reset enable_indexscan; 487 488set enable_seqscan = off; -- plan with fewest seqscans should be merge 489explain (verbose, costs off) select * from matest0 order by 1-id; 490select * from matest0 order by 1-id; 491explain (verbose, costs off) select min(1-id) from matest0; 492select min(1-id) from matest0; 493reset enable_seqscan; 494 495drop table matest0 cascade; 496 497-- 498-- Check that use of an index with an extraneous column doesn't produce 499-- a plan with extraneous sorting 500-- 501 502create table matest0 (a int, b int, c int, d int); 503create table matest1 () inherits(matest0); 504create index matest0i on matest0 (b, c); 505create index matest1i on matest1 (b, c); 506 507set enable_nestloop = off; -- we want a plan with two MergeAppends 508 509explain (costs off) 510select t1.* from matest0 t1, matest0 t2 511where t1.b = t2.b and t2.c = t2.d 512order by t1.b limit 10; 513 514reset enable_nestloop; 515 516drop table matest0 cascade; 517 518-- 519-- Test merge-append for UNION ALL append relations 520-- 521 522set enable_seqscan = off; 523set enable_indexscan = on; 524set enable_bitmapscan = off; 525 526-- Check handling of duplicated, constant, or volatile targetlist items 527explain (costs off) 528SELECT thousand, tenthous FROM tenk1 529UNION ALL 530SELECT thousand, thousand FROM tenk1 531ORDER BY thousand, tenthous; 532 533explain (costs off) 534SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 535UNION ALL 536SELECT 42, 42, hundred FROM tenk1 537ORDER BY thousand, tenthous; 538 539explain (costs off) 540SELECT thousand, tenthous FROM tenk1 541UNION ALL 542SELECT thousand, random()::integer FROM tenk1 543ORDER BY thousand, tenthous; 544 545-- Check min/max aggregate optimization 546explain (costs off) 547SELECT min(x) FROM 548 (SELECT unique1 AS x FROM tenk1 a 549 UNION ALL 550 SELECT unique2 AS x FROM tenk1 b) s; 551 552explain (costs off) 553SELECT min(y) FROM 554 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a 555 UNION ALL 556 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; 557 558-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted 559explain (costs off) 560SELECT x, y FROM 561 (SELECT thousand AS x, tenthous AS y FROM tenk1 a 562 UNION ALL 563 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s 564ORDER BY x, y; 565 566-- exercise rescan code path via a repeatedly-evaluated subquery 567explain (costs off) 568SELECT 569 ARRAY(SELECT f.i FROM ( 570 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 571 UNION ALL 572 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 573 ) f(i) 574 ORDER BY f.i LIMIT 10) 575FROM generate_series(1, 3) g(i); 576 577SELECT 578 ARRAY(SELECT f.i FROM ( 579 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 580 UNION ALL 581 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 582 ) f(i) 583 ORDER BY f.i LIMIT 10) 584FROM generate_series(1, 3) g(i); 585 586reset enable_seqscan; 587reset enable_indexscan; 588reset enable_bitmapscan; 589 590-- 591-- Check handling of a constant-null CHECK constraint 592-- 593create table cnullparent (f1 int); 594create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent); 595insert into cnullchild values(1); 596insert into cnullchild values(2); 597insert into cnullchild values(null); 598select * from cnullparent; 599select * from cnullparent where f1 = 2; 600drop table cnullparent cascade; 601