-- -- Test inheritance features -- CREATE TABLE a (aa TEXT); CREATE TABLE b (bb TEXT) INHERITS (a); CREATE TABLE c (cc TEXT) INHERITS (a); CREATE TABLE d (dd TEXT) INHERITS (b,c,a); NOTICE: merging multiple inherited definitions of column "aa" NOTICE: merging multiple inherited definitions of column "aa" INSERT INTO a(aa) VALUES('aaa'); INSERT INTO a(aa) VALUES('aaaa'); INSERT INTO a(aa) VALUES('aaaaa'); INSERT INTO a(aa) VALUES('aaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaaa'); INSERT INTO b(aa) VALUES('bbb'); INSERT INTO b(aa) VALUES('bbbb'); INSERT INTO b(aa) VALUES('bbbbb'); INSERT INTO b(aa) VALUES('bbbbbb'); INSERT INTO b(aa) VALUES('bbbbbbb'); INSERT INTO b(aa) VALUES('bbbbbbbb'); INSERT INTO c(aa) VALUES('ccc'); INSERT INTO c(aa) VALUES('cccc'); INSERT INTO c(aa) VALUES('ccccc'); INSERT INTO c(aa) VALUES('cccccc'); INSERT INTO c(aa) VALUES('ccccccc'); INSERT INTO c(aa) VALUES('cccccccc'); INSERT INTO d(aa) VALUES('ddd'); INSERT INTO d(aa) VALUES('dddd'); INSERT INTO d(aa) VALUES('ddddd'); INSERT INTO d(aa) VALUES('dddddd'); INSERT INTO d(aa) VALUES('ddddddd'); INSERT INTO d(aa) VALUES('dddddddd'); SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---------- a | aaa a | aaaa a | aaaaa a | aaaaaa a | aaaaaaa a | aaaaaaaa b | bbb b | bbbb b | bbbbb b | bbbbbb b | bbbbbbb b | bbbbbbbb c | ccc c | cccc c | ccccc c | cccccc c | ccccccc c | cccccccc d | ddd d | dddd d | ddddd d | dddddd d | ddddddd d | dddddddd (24 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----------+---- b | bbb | b | bbbb | b | bbbbb | b | bbbbbb | b | bbbbbbb | b | bbbbbbbb | d | ddd | d | dddd | d | ddddd | d | dddddd | d | ddddddd | d | dddddddd | (12 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | d | ddd | d | dddd | d | ddddd | d | dddddd | d | ddddddd | d | dddddddd | (12 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----------+----+----+---- d | ddd | | | d | dddd | | | d | ddddd | | | d | dddddd | | | d | ddddddd | | | d | dddddddd | | | (6 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---------- a | aaa a | aaaa a | aaaaa a | aaaaaa a | aaaaaaa a | aaaaaaaa (6 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----------+---- b | bbb | b | bbbb | b | bbbbb | b | bbbbbb | b | bbbbbbb | b | bbbbbbbb | (6 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | (6 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----------+----+----+---- d | ddd | | | d | dddd | | | d | ddddd | | | d | dddddd | | | d | ddddddd | | | d | dddddddd | | | (6 rows) UPDATE a SET aa='zzzz' WHERE aa='aaaa'; UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; UPDATE b SET aa='zzz' WHERE aa='aaa'; UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---------- a | zzzz a | zzzzz a | zzzzzz a | zzzzzz a | zzzzzz a | zzzzzz b | bbb b | bbbb b | bbbbb b | bbbbbb b | bbbbbbb b | bbbbbbbb c | ccc c | cccc c | ccccc c | cccccc c | ccccccc c | cccccccc d | ddd d | dddd d | ddddd d | dddddd d | ddddddd d | dddddddd (24 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----------+---- b | bbb | b | bbbb | b | bbbbb | b | bbbbbb | b | bbbbbbb | b | bbbbbbbb | d | ddd | d | dddd | d | ddddd | d | dddddd | d | ddddddd | d | dddddddd | (12 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | d | ddd | d | dddd | d | ddddd | d | dddddd | d | ddddddd | d | dddddddd | (12 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----------+----+----+---- d | ddd | | | d | dddd | | | d | ddddd | | | d | dddddd | | | d | ddddddd | | | d | dddddddd | | | (6 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+-------- a | zzzz a | zzzzz a | zzzzzz a | zzzzzz a | zzzzzz a | zzzzzz (6 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----------+---- b | bbb | b | bbbb | b | bbbbb | b | bbbbbb | b | bbbbbbb | b | bbbbbbbb | (6 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | (6 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----------+----+----+---- d | ddd | | | d | dddd | | | d | ddddd | | | d | dddddd | | | d | ddddddd | | | d | dddddddd | | | (6 rows) UPDATE b SET aa='new'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---------- a | zzzz a | zzzzz a | zzzzzz a | zzzzzz a | zzzzzz a | zzzzzz b | new b | new b | new b | new b | new b | new c | ccc c | cccc c | ccccc c | cccccc c | ccccccc c | cccccccc d | new d | new d | new d | new d | new d | new (24 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+-----+---- b | new | b | new | b | new | b | new | b | new | b | new | d | new | d | new | d | new | d | new | d | new | d | new | (12 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | d | new | d | new | d | new | d | new | d | new | d | new | (12 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+-----+----+----+---- d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | (6 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+-------- a | zzzz a | zzzzz a | zzzzzz a | zzzzzz a | zzzzzz a | zzzzzz (6 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+-----+---- b | new | b | new | b | new | b | new | b | new | b | new | (6 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----------+---- c | ccc | c | cccc | c | ccccc | c | cccccc | c | ccccccc | c | cccccccc | (6 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+-----+----+----+---- d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | (6 rows) UPDATE a SET aa='new'; DELETE FROM ONLY c WHERE aa='new'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+----- a | new a | new a | new a | new a | new a | new b | new b | new b | new b | new b | new b | new d | new d | new d | new d | new d | new d | new (18 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+-----+---- b | new | b | new | b | new | b | new | b | new | b | new | d | new | d | new | d | new | d | new | d | new | d | new | (12 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+-----+---- d | new | d | new | d | new | d | new | d | new | d | new | (6 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+-----+----+----+---- d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | (6 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+----- a | new a | new a | new a | new a | new a | new (6 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+-----+---- b | new | b | new | b | new | b | new | b | new | b | new | (6 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----+---- (0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+-----+----+----+---- d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | d | new | | | (6 rows) DELETE FROM a; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---- (0 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----+---- (0 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----+---- (0 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----+----+----+---- (0 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; relname | aa ---------+---- (0 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; relname | aa | bb ---------+----+---- (0 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; relname | aa | cc ---------+----+---- (0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; relname | aa | bb | cc | dd ---------+----+----+----+---- (0 rows) -- Confirm PRIMARY KEY adds NOT NULL constraint to child table CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); INSERT INTO z VALUES (NULL, 'text'); -- should fail ERROR: null value in column "aa" of relation "z" violates not-null constraint DETAIL: Failing row contains (null, text). -- Check inherited UPDATE with first child excluded create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit); create table some_tab_child () inherits(some_tab); insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i; create index on some_tab_child(f1, f2); -- while at it, also check that statement-level triggers fire create function some_tab_stmt_trig_func() returns trigger as $$begin raise notice 'updating some_tab'; return NULL; end;$$ language plpgsql; create trigger some_tab_stmt_trig before update on some_tab execute function some_tab_stmt_trig_func(); explain (costs off) update some_tab set f3 = 11 where f1 = 12 and f2 = 13; QUERY PLAN ------------------------------------------------------------------------------------ Update on some_tab Update on some_tab_child some_tab_1 -> Result -> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1 Index Cond: ((f1 = 12) AND (f2 = 13)) (5 rows) update some_tab set f3 = 11 where f1 = 12 and f2 = 13; NOTICE: updating some_tab drop table some_tab cascade; NOTICE: drop cascades to table some_tab_child drop function some_tab_stmt_trig_func(); -- Check inherited UPDATE with all children excluded create table some_tab (a int, b int); create table some_tab_child () inherits (some_tab); insert into some_tab_child values(1,2); explain (verbose, costs off) update some_tab set a = a + 1 where false; QUERY PLAN -------------------------------------------------------- Update on public.some_tab -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid One-Time Filter: false (4 rows) update some_tab set a = a + 1 where false; explain (verbose, costs off) update some_tab set a = a + 1 where false returning b, a; QUERY PLAN -------------------------------------------------------- Update on public.some_tab Output: some_tab.b, some_tab.a -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid One-Time Filter: false (5 rows) update some_tab set a = a + 1 where false returning b, a; b | a ---+--- (0 rows) table some_tab; a | b ---+--- 1 | 2 (1 row) drop table some_tab cascade; NOTICE: drop cascades to table some_tab_child -- Check UPDATE with inherited target and an inherited source table create temp table foo(f1 int, f2 int); create temp table foo2(f3 int) inherits (foo); create temp table bar(f1 int, f2 int); create temp table bar2(f3 int) inherits (bar); insert into foo values(1,1); insert into foo values(3,3); insert into foo2 values(2,2,2); insert into foo2 values(3,3,3); insert into bar values(1,1); insert into bar values(2,2); insert into bar values(3,3); insert into bar values(4,4); insert into bar2 values(1,1,1); insert into bar2 values(2,2,2); insert into bar2 values(3,3,3); insert into bar2 values(4,4,4); update bar set f2 = f2 + 100 where f1 in (select f1 from foo); select tableoid::regclass::text as relname, bar.* from bar order by 1,2; relname | f1 | f2 ---------+----+----- bar | 1 | 101 bar | 2 | 102 bar | 3 | 103 bar | 4 | 4 bar2 | 1 | 101 bar2 | 2 | 102 bar2 | 3 | 103 bar2 | 4 | 4 (8 rows) -- Check UPDATE with inherited target and an appendrel subquery update bar set f2 = f2 + 100 from ( select f1 from foo union all select f1+3 from foo ) ss where bar.f1 = ss.f1; select tableoid::regclass::text as relname, bar.* from bar order by 1,2; relname | f1 | f2 ---------+----+----- bar | 1 | 201 bar | 2 | 202 bar | 3 | 203 bar | 4 | 104 bar2 | 1 | 201 bar2 | 2 | 202 bar2 | 3 | 203 bar2 | 4 | 104 (8 rows) -- Check UPDATE with *partitioned* inherited target and an appendrel subquery create table some_tab (a int); insert into some_tab values (0); create table some_tab_child () inherits (some_tab); insert into some_tab_child values (1); create table parted_tab (a int, b char) partition by list (a); create table parted_tab_part1 partition of parted_tab for values in (1); create table parted_tab_part2 partition of parted_tab for values in (2); create table parted_tab_part3 partition of parted_tab for values in (3); insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); update parted_tab set b = 'b' from (select a from some_tab union all select a+1 from some_tab) ss (a) where parted_tab.a = ss.a; select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; relname | a | b ------------------+---+--- parted_tab_part1 | 1 | b parted_tab_part2 | 2 | b parted_tab_part3 | 3 | a (3 rows) truncate parted_tab; insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); update parted_tab set b = 'b' from (select 0 from parted_tab union all select 1 from parted_tab) ss (a) where parted_tab.a = ss.a; select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; relname | a | b ------------------+---+--- parted_tab_part1 | 1 | b parted_tab_part2 | 2 | a parted_tab_part3 | 3 | a (3 rows) -- modifies partition key, but no rows will actually be updated explain update parted_tab set a = 2 where false; QUERY PLAN -------------------------------------------------------- Update on parted_tab (cost=0.00..0.00 rows=0 width=0) -> Result (cost=0.00..0.00 rows=0 width=10) One-Time Filter: false (3 rows) drop table parted_tab; -- Check UPDATE with multi-level partitioned inherited target create table mlparted_tab (a int, b char, c text) partition by list (a); create table mlparted_tab_part1 partition of mlparted_tab for values in (1); create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b); create table mlparted_tab_part3 partition of mlparted_tab for values in (3); create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a'); create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b'); insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a'); update mlparted_tab mlp set c = 'xxx' from (select a from some_tab union all select a+1 from some_tab) ss (a) where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3; select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2; relname | a | b | c ---------------------+---+---+----- mlparted_tab_part1 | 1 | a | mlparted_tab_part2a | 2 | a | mlparted_tab_part2b | 2 | b | xxx mlparted_tab_part3 | 3 | a | xxx (4 rows) drop table mlparted_tab; drop table some_tab cascade; NOTICE: drop cascades to table some_tab_child /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); CREATE TABLE secondparent (tomorrow date default now() :: date + 1); CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok NOTICE: merging multiple inherited definitions of column "tomorrow" CREATE TABLE thirdparent (tomorrow date default now()::date - 1); CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok NOTICE: merging multiple inherited definitions of column "tomorrow" ERROR: column "tomorrow" inherits conflicting default values HINT: To resolve the conflict, specify a default explicitly. CREATE TABLE otherchild (tomorrow date default now()) INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default NOTICE: merging multiple inherited definitions of column "tomorrow" NOTICE: merging column "tomorrow" with inherited definition DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; -- Test changing the type of inherited columns insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); select * from d; aa | bb | cc | dd ----+-----+-----+------- 32 | one | two | three (1 row) -- The above verified that we can change the type of a multiply-inherited -- column; but we should reject that if any definition was inherited from -- an unrelated parent. create temp table parent1(f1 int, f2 int); create temp table parent2(f1 int, f3 bigint); create temp table childtab(f4 int) inherits(parent1, parent2); NOTICE: merging multiple inherited definitions of column "f1" alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 ERROR: cannot alter inherited column "f1" of relation "childtab" alter table parent1 alter column f2 type bigint; -- ok -- Test non-inheritable parent constraints create table p1(ff1 int); alter table p1 add constraint p1chk check (ff1 > 0) no inherit; alter table p1 add constraint p2chk check (ff1 > 10); -- connoinherit should be true for NO INHERIT constraint select 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; relname | conname | contype | conislocal | coninhcount | connoinherit ---------+---------+---------+------------+-------------+-------------- p1 | p1chk | c | t | 0 | t p1 | p2chk | c | t | 0 | f (2 rows) -- Test that child does not inherit NO INHERIT constraints create table c1 () inherits (p1); \d p1 Table "public.p1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- ff1 | integer | | | Check constraints: "p1chk" CHECK (ff1 > 0) NO INHERIT "p2chk" CHECK (ff1 > 10) Number of child tables: 1 (Use \d+ to list them.) \d c1 Table "public.c1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- ff1 | integer | | | Check constraints: "p2chk" CHECK (ff1 > 10) Inherits: p1 -- Test that child does not override inheritable constraints of the parent create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2" drop table p1 cascade; NOTICE: drop cascades to table c1 -- Tests for casting between the rowtypes of parent and child -- tables. See the pgsql-hackers thread beginning Dec. 4/04 create table base (i integer); create table derived () inherits (base); create table more_derived (like derived, b int) inherits (derived); NOTICE: merging column "i" with inherited definition insert into derived (i) values (0); select derived::base from derived; derived --------- (0) (1 row) select NULL::derived::base; base ------ (1 row) -- remove redundant conversions. explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived; QUERY PLAN ------------------------------------------- Seq Scan on public.more_derived Output: (ROW(i, b)::more_derived)::base (2 rows) explain (verbose on, costs off) select (1, 2)::more_derived::derived::base; QUERY PLAN ----------------------- Result Output: '(1)'::base (2 rows) drop table more_derived; drop table derived; drop table base; create table p1(ff1 int); create table p2(f1 text); create function p2text(p2) returns text as 'select $1.f1' language sql; create table c1(f3 int) inherits(p1,p2); insert into c1 values(123456789, 'hi', 42); select p2text(c1.*) from c1; p2text -------- hi (1 row) drop function p2text(p2); drop table c1; drop table p2; drop table p1; CREATE TABLE ac (aa TEXT); alter table ac add constraint ac_check check (aa is not null); CREATE TABLE bc (bb TEXT) INHERITS (ac); select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+----------+---------+------------+-------------+------------------ ac | ac_check | c | t | 0 | (aa IS NOT NULL) bc | ac_check | c | f | 1 | (aa IS NOT NULL) (2 rows) insert into ac (aa) values (NULL); ERROR: new row for relation "ac" violates check constraint "ac_check" DETAIL: Failing row contains (null). insert into bc (aa) values (NULL); ERROR: new row for relation "bc" violates check constraint "ac_check" DETAIL: Failing row contains (null, null). alter table bc drop constraint ac_check; -- fail, disallowed ERROR: cannot drop inherited constraint "ac_check" of relation "bc" alter table ac drop constraint ac_check; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+-------- (0 rows) -- try the unnamed-constraint case alter table ac add check (aa is not null); select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+-------------+---------+------------+-------------+------------------ ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL) (2 rows) insert into ac (aa) values (NULL); ERROR: new row for relation "ac" violates check constraint "ac_aa_check" DETAIL: Failing row contains (null). insert into bc (aa) values (NULL); ERROR: new row for relation "bc" violates check constraint "ac_aa_check" DETAIL: Failing row contains (null, null). alter table bc drop constraint ac_aa_check; -- fail, disallowed ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc" alter table ac drop constraint ac_aa_check; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+-------- (0 rows) alter table ac add constraint ac_check check (aa is not null); alter table bc no inherit ac; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+----------+---------+------------+-------------+------------------ ac | ac_check | c | t | 0 | (aa IS NOT NULL) bc | ac_check | c | t | 0 | (aa IS NOT NULL) (2 rows) alter table bc drop constraint ac_check; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+----------+---------+------------+-------------+------------------ ac | ac_check | c | t | 0 | (aa IS NOT NULL) (1 row) alter table ac drop constraint ac_check; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+-------- (0 rows) drop table bc; drop table ac; create table ac (a int constraint check_a check (a <> 0)); create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); NOTICE: merging column "a" with inherited definition NOTICE: merging constraint "check_a" with inherited definition select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+---------- ac | check_a | c | t | 0 | (a <> 0) bc | check_a | c | t | 1 | (a <> 0) bc | check_b | c | t | 0 | (b <> 0) (3 rows) drop table bc; drop table ac; create table ac (a int constraint check_a check (a <> 0)); create table bc (b int constraint check_b check (b <> 0)); create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+---------- ac | check_a | c | t | 0 | (a <> 0) bc | check_b | c | t | 0 | (b <> 0) cc | check_a | c | f | 1 | (a <> 0) cc | check_b | c | f | 1 | (b <> 0) cc | check_c | c | t | 0 | (c <> 0) (5 rows) alter table cc no inherit bc; select 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; relname | conname | contype | conislocal | coninhcount | consrc ---------+---------+---------+------------+-------------+---------- ac | check_a | c | t | 0 | (a <> 0) bc | check_b | c | t | 0 | (b <> 0) cc | check_a | c | f | 1 | (a <> 0) cc | check_b | c | t | 0 | (b <> 0) cc | check_c | c | t | 0 | (c <> 0) (5 rows) drop table cc; drop table bc; drop table ac; create table p1(f1 int); create table p2(f2 int); create table c1(f3 int) inherits(p1,p2); insert into c1 values(1,-1,2); alter table p2 add constraint cc check (f2>0); -- fail ERROR: check constraint "cc" of relation "c1" is violated by some row alter table p2 add check (f2>0); -- check it without a name, too ERROR: check constraint "p2_f2_check" of relation "c1" is violated by some row delete from c1; insert into c1 values(1,1,2); alter table p2 add check (f2>0); insert into c1 values(1,-1,2); -- fail ERROR: new row for relation "c1" violates check constraint "p2_f2_check" DETAIL: Failing row contains (1, -1, 2). create table c2(f3 int) inherits(p1,p2); \d c2 Table "public.c2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | f2 | integer | | | f3 | integer | | | Check constraints: "p2_f2_check" CHECK (f2 > 0) Inherits: p1, p2 create table c3 (f4 int) inherits(c1,c2); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "f2" NOTICE: merging multiple inherited definitions of column "f3" \d c3 Table "public.c3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | f2 | integer | | | f3 | integer | | | f4 | integer | | | Check constraints: "p2_f2_check" CHECK (f2 > 0) Inherits: c1, c2 drop table p1 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table c1 drop cascades to table c2 drop cascades to table c3 drop table p2 cascade; create table pp1 (f1 int); create table cc1 (f2 text, f3 int) inherits (pp1); alter table pp1 add column a1 int check (a1 > 0); \d cc1 Table "public.cc1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | f2 | text | | | f3 | integer | | | a1 | integer | | | Check constraints: "pp1_a1_check" CHECK (a1 > 0) Inherits: pp1 create table cc2(f4 float) inherits(pp1,cc1); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "a1" \d cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- f1 | integer | | | a1 | integer | | | f2 | text | | | f3 | integer | | | f4 | double precision | | | Check constraints: "pp1_a1_check" CHECK (a1 > 0) Inherits: pp1, cc1 alter table pp1 add column a2 int check (a2 > 0); NOTICE: merging definition of column "a2" for child "cc2" NOTICE: merging constraint "pp1_a2_check" with inherited definition \d cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- f1 | integer | | | a1 | integer | | | f2 | text | | | f3 | integer | | | f4 | double precision | | | a2 | integer | | | Check constraints: "pp1_a1_check" CHECK (a1 > 0) "pp1_a2_check" CHECK (a2 > 0) Inherits: pp1, cc1 drop table pp1 cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table cc1 drop cascades to table cc2 -- Test for renaming in simple multiple inheritance CREATE TABLE inht1 (a int, b int); CREATE TABLE inhs1 (b int, c int); CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME a TO aa; ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" ALTER TABLE inhts RENAME aa TO aaa; -- to be failed ERROR: cannot rename inherited column "aa" ALTER TABLE inhts RENAME d TO dd; \d+ inhts Table "public.inhts" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- aa | integer | | | | plain | | b | integer | | | | plain | | c | integer | | | | plain | | dd | integer | | | | plain | | Inherits: inht1, inhs1 DROP TABLE inhts; -- Test for adding a column to a parent table with complex inheritance CREATE TABLE inhta (); CREATE TABLE inhtb () INHERITS (inhta); CREATE TABLE inhtc () INHERITS (inhtb); CREATE TABLE inhtd () INHERITS (inhta, inhtb, inhtc); ALTER TABLE inhta ADD COLUMN i int; NOTICE: merging definition of column "i" for child "inhtd" NOTICE: merging definition of column "i" for child "inhtd" \d+ inhta Table "public.inhta" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- i | integer | | | | plain | | Child tables: inhtb, inhtd DROP TABLE inhta, inhtb, inhtc, inhtd; -- Test for renaming in diamond inheritance CREATE TABLE inht2 (x int) INHERITS (inht1); CREATE TABLE inht3 (y int) INHERITS (inht1); CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); NOTICE: merging multiple inherited definitions of column "aa" NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aa TO aaa; \d+ inht4 Table "public.inht4" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- aaa | integer | | | | plain | | b | integer | | | | plain | | x | integer | | | | plain | | y | integer | | | | plain | | z | integer | | | | plain | | Inherits: inht2, inht3 CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aaa TO aaaa; ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" \d+ inhts Table "public.inhts" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- aaaa | integer | | | | plain | | b | integer | | | | plain | | x | integer | | | | plain | | c | integer | | | | plain | | d | integer | | | | plain | | Inherits: inht2, inhs1 WITH RECURSIVE r AS ( SELECT 'inht1'::regclass AS inhrelid UNION ALL SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent ) SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal ORDER BY a.attrelid::regclass::name, a.attnum; attrelid | attname | attinhcount | expected ----------+---------+-------------+---------- inht2 | aaaa | 1 | 1 inht2 | b | 1 | 1 inht3 | aaaa | 1 | 1 inht3 | b | 1 | 1 inht4 | aaaa | 2 | 2 inht4 | b | 2 | 2 inht4 | x | 1 | 2 inht4 | y | 1 | 2 inhts | aaaa | 1 | 1 inhts | b | 2 | 1 inhts | x | 1 | 1 inhts | c | 1 | 1 (12 rows) DROP TABLE inht1, inhs1 CASCADE; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table inht2 drop cascades to table inhts drop cascades to table inht3 drop cascades to table inht4 -- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); CREATE TABLE test_constraints_inh () INHERITS (test_constraints); \d+ test_constraints Table "public.test_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------------------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | val1 | character varying | | | | extended | | val2 | integer | | | | plain | | Indexes: "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2) Child tables: test_constraints_inh ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints Table "public.test_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------------------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | val1 | character varying | | | | extended | | val2 | integer | | | | plain | | Child tables: test_constraints_inh \d+ test_constraints_inh Table "public.test_constraints_inh" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------------------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | val1 | character varying | | | | extended | | val2 | integer | | | | plain | | Inherits: test_constraints DROP TABLE test_constraints_inh; DROP TABLE test_constraints; CREATE TABLE test_ex_constraints ( c circle, EXCLUDE USING gist (c WITH &&) ); CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); \d+ test_ex_constraints Table "public.test_ex_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+---------+--------------+------------- c | circle | | | | plain | | Indexes: "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&) Child tables: test_ex_constraints_inh ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints Table "public.test_ex_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+---------+--------------+------------- c | circle | | | | plain | | Child tables: test_ex_constraints_inh \d+ test_ex_constraints_inh Table "public.test_ex_constraints_inh" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+---------+--------------+------------- c | circle | | | | plain | | Inherits: test_ex_constraints DROP TABLE test_ex_constraints_inh; DROP TABLE test_ex_constraints; -- Test non-inheritable foreign key constraints CREATE TABLE test_primary_constraints(id int PRIMARY KEY); CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); \d+ test_primary_constraints Table "public.test_primary_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | not null | | plain | | Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) \d+ test_foreign_constraints Table "public.test_foreign_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id1 | integer | | | | plain | | Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) Child tables: test_foreign_constraints_inh ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints Table "public.test_foreign_constraints" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id1 | integer | | | | plain | | Child tables: test_foreign_constraints_inh \d+ test_foreign_constraints_inh Table "public.test_foreign_constraints_inh" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id1 | integer | | | | plain | | Inherits: test_foreign_constraints DROP TABLE test_foreign_constraints_inh; DROP TABLE test_foreign_constraints; DROP TABLE test_primary_constraints; -- Test foreign key behavior create table inh_fk_1 (a int primary key); insert into inh_fk_1 values (1), (2), (3); create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete cascade); insert into inh_fk_2 values (11, 1), (22, 2), (33, 3); create table inh_fk_2_child () inherits (inh_fk_2); insert into inh_fk_2_child values (111, 1), (222, 2); delete from inh_fk_1 where a = 1; select * from inh_fk_1 order by 1; a --- 2 3 (2 rows) select * from inh_fk_2 order by 1, 2; x | y -----+--- 22 | 2 33 | 3 111 | 1 222 | 2 (4 rows) drop table inh_fk_1, inh_fk_2, inh_fk_2_child; -- Test that parent and child CHECK constraints can be created in either order create table p1(f1 int); create table p1_c1() inherits(p1); alter table p1 add constraint inh_check_constraint1 check (f1 > 0); alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); NOTICE: merging constraint "inh_check_constraint1" with inherited definition alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); alter table p1 add constraint inh_check_constraint2 check (f1 < 10); NOTICE: merging constraint "inh_check_constraint2" with inherited definition select conrelid::regclass::text as relname, conname, conislocal, coninhcount from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; relname | conname | conislocal | coninhcount ---------+-----------------------+------------+------------- p1 | inh_check_constraint1 | t | 0 p1 | inh_check_constraint2 | t | 0 p1_c1 | inh_check_constraint1 | t | 1 p1_c1 | inh_check_constraint2 | t | 1 (4 rows) drop table p1 cascade; NOTICE: drop cascades to table p1_c1 -- Test that a valid child can have not-valid parent, but not vice versa create table invalid_check_con(f1 int); create table invalid_check_con_child() inherits(invalid_check_con); alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child" alter table invalid_check_con_child drop constraint inh_check_constraint; insert into invalid_check_con values(0); alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; NOTICE: merging constraint "inh_check_constraint" with inherited definition insert into invalid_check_con values(0); -- fail ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint" DETAIL: Failing row contains (0). insert into invalid_check_con_child values(0); -- fail ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint" DETAIL: Failing row contains (0). select conrelid::regclass::text as relname, conname, convalidated, conislocal, coninhcount, connoinherit from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; relname | conname | convalidated | conislocal | coninhcount | connoinherit -------------------------+----------------------+--------------+------------+-------------+-------------- invalid_check_con | inh_check_constraint | f | t | 0 | f invalid_check_con_child | inh_check_constraint | t | t | 1 | f (2 rows) -- We don't drop the invalid_check_con* tables, to test dump/reload with -- -- Test parameterized append plans for inheritance trees -- create temp table patest0 (id, x) as select x, x from generate_series(0,1000) x; create temp table patest1() inherits (patest0); insert into patest1 select x, x from generate_series(0,1000) x; create temp table patest2() inherits (patest0); insert into patest2 select x, x from generate_series(0,1000) x; create index patest0i on patest0(id); create index patest1i on patest1(id); create index patest2i on patest2(id); analyze patest0; analyze patest1; analyze patest2; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; QUERY PLAN ------------------------------------------------------------ Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 patest0_1 Index Cond: (id = int4_tbl.f1) -> Index Scan using patest1i on patest1 patest0_2 Index Cond: (id = int4_tbl.f1) -> Index Scan using patest2i on patest2 patest0_3 Index Cond: (id = int4_tbl.f1) (10 rows) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; id | x | f1 ----+---+---- 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 (3 rows) drop index patest2i; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; QUERY PLAN ------------------------------------------------------------ Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 patest0_1 Index Cond: (id = int4_tbl.f1) -> Index Scan using patest1i on patest1 patest0_2 Index Cond: (id = int4_tbl.f1) -> Seq Scan on patest2 patest0_3 Filter: (int4_tbl.f1 = id) (10 rows) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; id | x | f1 ----+---+---- 0 | 0 | 0 0 | 0 | 0 0 | 0 | 0 (3 rows) drop table patest0 cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table patest1 drop cascades to table patest2 -- -- Test merge-append plans for inheritance trees -- create table matest0 (id serial primary key, name text); create table matest1 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition create table matest2 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition create table matest3 (id integer primary key) inherits (matest0); NOTICE: merging column "id" with inherited definition create index matest0i on matest0 ((1-id)); create index matest1i on matest1 ((1-id)); -- create index matest2i on matest2 ((1-id)); -- intentionally missing create index matest3i on matest3 ((1-id)); insert into matest1 (name) values ('Test 1'); insert into matest1 (name) values ('Test 2'); insert into matest2 (name) values ('Test 3'); insert into matest2 (name) values ('Test 4'); insert into matest3 (name) values ('Test 5'); insert into matest3 (name) values ('Test 6'); set enable_indexscan = off; -- force use of seqscan/sort, so no merge explain (verbose, costs off) select * from matest0 order by 1-id; QUERY PLAN ------------------------------------------------------------ Sort Output: matest0.id, matest0.name, ((1 - matest0.id)) Sort Key: ((1 - matest0.id)) -> Result Output: matest0.id, matest0.name, (1 - matest0.id) -> Append -> Seq Scan on public.matest0 matest0_1 Output: matest0_1.id, matest0_1.name -> Seq Scan on public.matest1 matest0_2 Output: matest0_2.id, matest0_2.name -> Seq Scan on public.matest2 matest0_3 Output: matest0_3.id, matest0_3.name -> Seq Scan on public.matest3 matest0_4 Output: matest0_4.id, matest0_4.name (14 rows) select * from matest0 order by 1-id; id | name ----+-------- 6 | Test 6 5 | Test 5 4 | Test 4 3 | Test 3 2 | Test 2 1 | Test 1 (6 rows) explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN -------------------------------------------------- Aggregate Output: min((1 - matest0.id)) -> Append -> Seq Scan on public.matest0 matest0_1 Output: matest0_1.id -> Seq Scan on public.matest1 matest0_2 Output: matest0_2.id -> Seq Scan on public.matest2 matest0_3 Output: matest0_3.id -> Seq Scan on public.matest3 matest0_4 Output: matest0_4.id (11 rows) select min(1-id) from matest0; min ----- -5 (1 row) reset enable_indexscan; set enable_seqscan = off; -- plan with fewest seqscans should be merge set enable_parallel_append = off; -- Don't let parallel-append interfere explain (verbose, costs off) select * from matest0 order by 1-id; QUERY PLAN ------------------------------------------------------------------------ Merge Append Sort Key: ((1 - matest0.id)) -> Index Scan using matest0i on public.matest0 matest0_1 Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id) -> Index Scan using matest1i on public.matest1 matest0_2 Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id) -> Sort Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id)) Sort Key: ((1 - matest0_3.id)) -> Seq Scan on public.matest2 matest0_3 Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id) -> Index Scan using matest3i on public.matest3 matest0_4 Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id) (13 rows) select * from matest0 order by 1-id; id | name ----+-------- 6 | Test 6 5 | Test 5 4 | Test 4 3 | Test 3 2 | Test 2 1 | Test 1 (6 rows) explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN --------------------------------------------------------------------------------- Result Output: $0 InitPlan 1 (returns $0) -> Limit Output: ((1 - matest0.id)) -> Result Output: ((1 - matest0.id)) -> Merge Append Sort Key: ((1 - matest0.id)) -> Index Scan using matest0i on public.matest0 matest0_1 Output: matest0_1.id, (1 - matest0_1.id) Index Cond: ((1 - matest0_1.id) IS NOT NULL) -> Index Scan using matest1i on public.matest1 matest0_2 Output: matest0_2.id, (1 - matest0_2.id) Index Cond: ((1 - matest0_2.id) IS NOT NULL) -> Sort Output: matest0_3.id, ((1 - matest0_3.id)) Sort Key: ((1 - matest0_3.id)) -> Bitmap Heap Scan on public.matest2 matest0_3 Output: matest0_3.id, (1 - matest0_3.id) Filter: ((1 - matest0_3.id) IS NOT NULL) -> Bitmap Index Scan on matest2_pkey -> Index Scan using matest3i on public.matest3 matest0_4 Output: matest0_4.id, (1 - matest0_4.id) Index Cond: ((1 - matest0_4.id) IS NOT NULL) (25 rows) select min(1-id) from matest0; min ----- -5 (1 row) reset enable_seqscan; reset enable_parallel_append; drop table matest0 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table matest1 drop cascades to table matest2 drop cascades to table matest3 -- -- Check that use of an index with an extraneous column doesn't produce -- a plan with extraneous sorting -- create table matest0 (a int, b int, c int, d int); create table matest1 () inherits(matest0); create index matest0i on matest0 (b, c); create index matest1i on matest1 (b, c); set enable_nestloop = off; -- we want a plan with two MergeAppends explain (costs off) select t1.* from matest0 t1, matest0 t2 where t1.b = t2.b and t2.c = t2.d order by t1.b limit 10; QUERY PLAN ------------------------------------------------------------------- Limit -> Merge Join Merge Cond: (t1.b = t2.b) -> Merge Append Sort Key: t1.b -> Index Scan using matest0i on matest0 t1_1 -> Index Scan using matest1i on matest1 t1_2 -> Materialize -> Merge Append Sort Key: t2.b -> Index Scan using matest0i on matest0 t2_1 Filter: (c = d) -> Index Scan using matest1i on matest1 t2_2 Filter: (c = d) (14 rows) reset enable_nestloop; drop table matest0 cascade; NOTICE: drop cascades to table matest1 -- -- Test merge-append for UNION ALL append relations -- set enable_seqscan = off; set enable_indexscan = on; set enable_bitmapscan = off; -- Check handling of duplicated, constant, or volatile targetlist items explain (costs off) SELECT thousand, tenthous FROM tenk1 UNION ALL SELECT thousand, thousand FROM tenk1 ORDER BY thousand, tenthous; QUERY PLAN ------------------------------------------------------------------------- Merge Append Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort Sort Key: tenk1_1.thousand, tenk1_1.thousand -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 (6 rows) explain (costs off) SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 UNION ALL SELECT 42, 42, hundred FROM tenk1 ORDER BY thousand, tenthous; QUERY PLAN ------------------------------------------------------------------ Merge Append Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort Sort Key: 42, 42 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1 (6 rows) explain (costs off) SELECT thousand, tenthous FROM tenk1 UNION ALL SELECT thousand, random()::integer FROM tenk1 ORDER BY thousand, tenthous; QUERY PLAN ------------------------------------------------------------------------- Merge Append Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort Sort Key: tenk1_1.thousand, ((random())::integer) -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 (6 rows) -- Check min/max aggregate optimization explain (costs off) SELECT min(x) FROM (SELECT unique1 AS x FROM tenk1 a UNION ALL SELECT unique2 AS x FROM tenk1 b) s; QUERY PLAN -------------------------------------------------------------------- Result InitPlan 1 (returns $0) -> Limit -> Merge Append Sort Key: a.unique1 -> Index Only Scan using tenk1_unique1 on tenk1 a Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) (9 rows) explain (costs off) SELECT min(y) FROM (SELECT unique1 AS x, unique1 AS y FROM tenk1 a UNION ALL SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; QUERY PLAN -------------------------------------------------------------------- Result InitPlan 1 (returns $0) -> Limit -> Merge Append Sort Key: a.unique1 -> Index Only Scan using tenk1_unique1 on tenk1 a Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) (9 rows) -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted explain (costs off) SELECT x, y FROM (SELECT thousand AS x, tenthous AS y FROM tenk1 a UNION ALL SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s ORDER BY x, y; QUERY PLAN ------------------------------------------------------------- Merge Append Sort Key: a.thousand, a.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 a -> Sort Sort Key: b.unique2, b.unique2 -> Index Only Scan using tenk1_unique2 on tenk1 b (6 rows) -- exercise rescan code path via a repeatedly-evaluated subquery explain (costs off) SELECT ARRAY(SELECT f.i FROM ( (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) UNION ALL (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) ) f(i) ORDER BY f.i LIMIT 10) FROM generate_series(1, 3) g(i); QUERY PLAN ---------------------------------------------------------------- Function Scan on generate_series g SubPlan 1 -> Limit -> Merge Append Sort Key: ((d.d + g.i)) -> Sort Sort Key: ((d.d + g.i)) -> Function Scan on generate_series d -> Sort Sort Key: ((d_1.d + g.i)) -> Function Scan on generate_series d_1 (11 rows) SELECT ARRAY(SELECT f.i FROM ( (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) UNION ALL (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) ) f(i) ORDER BY f.i LIMIT 10) FROM generate_series(1, 3) g(i); array ------------------------------ {1,5,6,8,11,11,14,16,17,20} {2,6,7,9,12,12,15,17,18,21} {3,7,8,10,13,13,16,18,19,22} (3 rows) reset enable_seqscan; reset enable_indexscan; reset enable_bitmapscan; -- -- Check handling of MULTIEXPR SubPlans in inherited updates -- create table inhpar(f1 int, f2 name); create table inhcld(f2 name, f1 int); alter table inhcld inherit inhpar; insert into inhpar select x, x::text from generate_series(1,5) x; insert into inhcld select x::text, x from generate_series(6,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); QUERY PLAN ------------------------------------------------------------------------- Update on public.inhpar i Update on public.inhpar i_1 Update on public.inhcld i_2 -> Result Output: $2, $3, (SubPlan 1 (returns $2,$3)), i.tableoid, i.ctid -> Append -> Seq Scan on public.inhpar i_1 Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid -> Seq Scan on public.inhcld i_2 Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid SubPlan 1 (returns $2,$3) -> Limit Output: (i.f1), (((i.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl Output: i.f1, ((i.f2)::text || '-'::text) (15 rows) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); select * from inhpar; f1 | f2 ----+----- 1 | 1- 2 | 2- 3 | 3- 4 | 4- 5 | 5- 6 | 6- 7 | 7- 8 | 8- 9 | 9- 10 | 10- (10 rows) drop table inhpar cascade; NOTICE: drop cascades to table inhcld -- -- And the same for partitioned cases -- create table inhpar(f1 int primary key, f2 name) partition by range (f1); create table inhcld1(f2 name, f1 int primary key); create table inhcld2(f1 int primary key, f2 name); alter table inhpar attach partition inhcld1 for values from (1) to (5); alter table inhpar attach partition inhcld2 for values from (5) to (100); insert into inhpar select x, x::text from generate_series(1,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); QUERY PLAN ----------------------------------------------------------------------------------- Update on public.inhpar i Update on public.inhcld1 i_1 Update on public.inhcld2 i_2 -> Append -> Seq Scan on public.inhcld1 i_1 Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_1.tableoid, i_1.ctid SubPlan 1 (returns $2,$3) -> Limit Output: (i_1.f1), (((i_1.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl Output: i_1.f1, ((i_1.f2)::text || '-'::text) -> Seq Scan on public.inhcld2 i_2 Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_2.tableoid, i_2.ctid (13 rows) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); select * from inhpar; f1 | f2 ----+----- 1 | 1- 2 | 2- 3 | 3- 4 | 4- 5 | 5- 6 | 6- 7 | 7- 8 | 8- 9 | 9- 10 | 10- (10 rows) -- Also check ON CONFLICT insert into inhpar as i values (3), (7) on conflict (f1) do update set (f1, f2) = (select i.f1, i.f2 || '+'); select * from inhpar order by f1; -- tuple order might be unstable here f1 | f2 ----+----- 1 | 1- 2 | 2- 3 | 3-+ 4 | 4- 5 | 5- 6 | 6- 7 | 7-+ 8 | 8- 9 | 9- 10 | 10- (10 rows) drop table inhpar cascade; -- -- Check handling of a constant-null CHECK constraint -- create table cnullparent (f1 int); create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent); insert into cnullchild values(1); insert into cnullchild values(2); insert into cnullchild values(null); select * from cnullparent; f1 ---- 1 2 (3 rows) select * from cnullparent where f1 = 2; f1 ---- 2 (1 row) drop table cnullparent cascade; NOTICE: drop cascades to table cnullchild -- -- Check use of temporary tables with inheritance trees -- create table inh_perm_parent (a1 int); create temp table inh_temp_parent (a1 int); create temp table inh_temp_child () inherits (inh_perm_parent); -- ok create table inh_perm_child () inherits (inh_temp_parent); -- error ERROR: cannot inherit from temporary relation "inh_temp_parent" create temp table inh_temp_child_2 () inherits (inh_temp_parent); -- ok insert into inh_perm_parent values (1); insert into inh_temp_parent values (2); insert into inh_temp_child values (3); insert into inh_temp_child_2 values (4); select tableoid::regclass, a1 from inh_perm_parent; tableoid | a1 -----------------+---- inh_perm_parent | 1 inh_temp_child | 3 (2 rows) select tableoid::regclass, a1 from inh_temp_parent; tableoid | a1 ------------------+---- inh_temp_parent | 2 inh_temp_child_2 | 4 (2 rows) drop table inh_perm_parent cascade; NOTICE: drop cascades to table inh_temp_child drop table inh_temp_parent cascade; NOTICE: drop cascades to table inh_temp_child_2 -- -- Check that constraint exclusion works correctly with partitions using -- implicit constraints generated from the partition bound information. -- create table list_parted ( a varchar ) partition by list (a); create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); explain (costs off) select * from list_parted; QUERY PLAN ---------------------------------------------- Append -> Seq Scan on part_ab_cd list_parted_1 -> Seq Scan on part_ef_gh list_parted_2 -> Seq Scan on part_null_xy list_parted_3 (4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------------- Seq Scan on part_null_xy list_parted Filter: (a IS NULL) (2 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN ---------------------------------------------- Append -> Seq Scan on part_ab_cd list_parted_1 Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh list_parted_2 Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy list_parted_3 Filter: (a IS NOT NULL) (7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append -> Seq Scan on part_ab_cd list_parted_1 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh list_parted_2 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) (5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------- Seq Scan on part_ab_cd list_parted Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) (2 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------ Seq Scan on part_ab_cd list_parted Filter: ((a)::text = 'ab'::text) (2 rows) create table range_list_parted ( a int, b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); create table part_10_20_ab partition of part_10_20 for values in ('ab'); create table part_10_20_cd partition of part_10_20 for values in ('cd'); create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); create table part_21_30_ab partition of part_21_30 for values in ('ab'); create table part_21_30_cd partition of part_21_30 for values in ('cd'); create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b); create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; QUERY PLAN -------------------------------------------------------- Append -> Seq Scan on part_1_10_ab range_list_parted_1 -> Seq Scan on part_1_10_cd range_list_parted_2 -> Seq Scan on part_10_20_ab range_list_parted_3 -> Seq Scan on part_10_20_cd range_list_parted_4 -> Seq Scan on part_21_30_ab range_list_parted_5 -> Seq Scan on part_21_30_cd range_list_parted_6 -> Seq Scan on part_40_inf_ab range_list_parted_7 -> Seq Scan on part_40_inf_cd range_list_parted_8 -> Seq Scan on part_40_inf_null range_list_parted_9 (10 rows) explain (costs off) select * from range_list_parted where a = 5; QUERY PLAN ---------------------------------------------------- Append -> Seq Scan on part_1_10_ab range_list_parted_1 Filter: (a = 5) -> Seq Scan on part_1_10_cd range_list_parted_2 Filter: (a = 5) (5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; QUERY PLAN ------------------------------------------------------ Append -> Seq Scan on part_1_10_ab range_list_parted_1 Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab range_list_parted_2 Filter: (b = 'ab'::bpchar) -> Seq Scan on part_21_30_ab range_list_parted_3 Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab range_list_parted_4 Filter: (b = 'ab'::bpchar) (9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append -> Seq Scan on part_1_10_ab range_list_parted_1 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab range_list_parted_2 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab range_list_parted_3 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) (7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; QUERY PLAN -------------------------- Result One-Time Filter: false (2 rows) /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; QUERY PLAN ------------------------------------------------ Seq Scan on part_40_inf_null range_list_parted Filter: (b IS NULL) (2 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN -------------------------------------------------------- Append -> Seq Scan on part_1_10_ab range_list_parted_1 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd range_list_parted_2 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_10_20_ab range_list_parted_3 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_10_20_cd range_list_parted_4 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_21_30_ab range_list_parted_5 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_21_30_cd range_list_parted_6 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_ab range_list_parted_7 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_cd range_list_parted_8 Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null range_list_parted_9 Filter: ((a IS NOT NULL) AND (a < 67)) (19 rows) explain (costs off) select * from range_list_parted where a >= 30; QUERY PLAN -------------------------------------------------------- Append -> Seq Scan on part_40_inf_ab range_list_parted_1 Filter: (a >= 30) -> Seq Scan on part_40_inf_cd range_list_parted_2 Filter: (a >= 30) -> Seq Scan on part_40_inf_null range_list_parted_3 Filter: (a >= 30) (7 rows) drop table list_parted; drop table range_list_parted; -- check that constraint exclusion is able to cope with the partition -- constraint emitted for multi-column range partitioned tables create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); create table mcrparted_def partition of mcrparted default; create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted0 mcrparted_1 Filter: (a = 0) -> Seq Scan on mcrparted_def mcrparted_2 Filter: (a = 0) (5 rows) explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted1 mcrparted_1 Filter: ((a = 10) AND (abs(b) < 5)) -> Seq Scan on mcrparted_def mcrparted_2 Filter: ((a = 10) AND (abs(b) < 5)) (5 rows) explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted1 mcrparted_1 Filter: ((a = 10) AND (abs(b) = 5)) -> Seq Scan on mcrparted2 mcrparted_2 Filter: ((a = 10) AND (abs(b) = 5)) -> Seq Scan on mcrparted_def mcrparted_3 Filter: ((a = 10) AND (abs(b) = 5)) (7 rows) explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted0 mcrparted_1 Filter: (abs(b) = 5) -> Seq Scan on mcrparted1 mcrparted_2 Filter: (abs(b) = 5) -> Seq Scan on mcrparted2 mcrparted_3 Filter: (abs(b) = 5) -> Seq Scan on mcrparted3 mcrparted_4 Filter: (abs(b) = 5) -> Seq Scan on mcrparted4 mcrparted_5 Filter: (abs(b) = 5) -> Seq Scan on mcrparted5 mcrparted_6 Filter: (abs(b) = 5) -> Seq Scan on mcrparted_def mcrparted_7 Filter: (abs(b) = 5) (15 rows) explain (costs off) select * from mcrparted where a > -1; -- scans all partitions QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted0 mcrparted_1 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted1 mcrparted_2 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted2 mcrparted_3 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted3 mcrparted_4 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted4 mcrparted_5 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted5 mcrparted_6 Filter: (a > '-1'::integer) -> Seq Scan on mcrparted_def mcrparted_7 Filter: (a > '-1'::integer) (15 rows) explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 QUERY PLAN ----------------------------------------------------- Seq Scan on mcrparted4 mcrparted Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) (2 rows) explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted3 mcrparted_1 Filter: ((c > 20) AND (a = 20)) -> Seq Scan on mcrparted4 mcrparted_2 Filter: ((c > 20) AND (a = 20)) -> Seq Scan on mcrparted5 mcrparted_3 Filter: ((c > 20) AND (a = 20)) -> Seq Scan on mcrparted_def mcrparted_4 Filter: ((c > 20) AND (a = 20)) (9 rows) -- check that partitioned table Appends cope with being referenced in -- subplans create table parted_minmax (a int, b varchar(16)) partition by range (a); create table parted_minmax1 partition of parted_minmax for values from (1) to (10); create index parted_minmax1i on parted_minmax1 (a, b); insert into parted_minmax values (1,'12345'); explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; QUERY PLAN ------------------------------------------------------------------------------------------------ Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) InitPlan 2 (returns $1) -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) (9 rows) select min(a), max(a) from parted_minmax where b = '12345'; min | max -----+----- 1 | 1 (1 row) drop table parted_minmax; -- Test code that uses Append nodes in place of MergeAppend when the -- partition ordering matches the desired ordering. create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c); -- MergeAppend must be used when a default partition exists explain (costs off) select * from mcrparted order by a, abs(b), c; QUERY PLAN ------------------------------------------------------------------------------- Merge Append Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7 (9 rows) drop table mcrparted_def; -- Append is used for a RANGE partitioned table with no default -- and no subpartitions explain (costs off) select * from mcrparted order by a, abs(b), c; QUERY PLAN ------------------------------------------------------------------------- Append -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 (7 rows) -- Append is used with subpaths in reverse order with backwards index scans explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc; QUERY PLAN ---------------------------------------------------------------------------------- Append -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 (7 rows) -- check that Append plan is used containing a MergeAppend for sub-partitions -- that are unordered. drop table mcrparted5; create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue) partition by list (a); create table mcrparted5a partition of mcrparted5 for values in(20); create table mcrparted5_def partition of mcrparted5 default; explain (costs off) select * from mcrparted order by a, abs(b), c; QUERY PLAN --------------------------------------------------------------------------------------- Append -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 -> Merge Append Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7 -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8 (10 rows) drop table mcrparted5_def; -- check that an Append plan is used and the sub-partitions are flattened -- into the main Append when the sub-partition is unordered but contains -- just a single sub-partition. explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c; QUERY PLAN --------------------------------------------------------------------------- Append -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6 (7 rows) -- check that Append is used when the sub-partitioned tables are pruned -- during planning. explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c; QUERY PLAN ------------------------------------------------------------------------- Append -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 Index Cond: (a < 20) -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 Index Cond: (a < 20) -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 Index Cond: (a < 20) -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 Index Cond: (a < 20) (9 rows) set enable_bitmapscan to off; set enable_sort to off; create table mclparted (a int) partition by list(a); create table mclparted1 partition of mclparted for values in(1); create table mclparted2 partition of mclparted for values in(2); create index on mclparted (a); -- Ensure an Append is used for a list partition with an order by. explain (costs off) select * from mclparted order by a; QUERY PLAN ------------------------------------------------------------------------ Append -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 (3 rows) -- Ensure a MergeAppend is used when a partition exists with interleaved -- datums in the partition bound. create table mclparted3_5 partition of mclparted for values in(3,5); create table mclparted4 partition of mclparted for values in(4); explain (costs off) select * from mclparted order by a; QUERY PLAN ---------------------------------------------------------------------------- Merge Append Sort Key: mclparted.a -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_3 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4 (6 rows) explain (costs off) select * from mclparted where a in(3,4,5) order by a; QUERY PLAN ---------------------------------------------------------------------------- Merge Append Sort Key: mclparted.a -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1 Index Cond: (a = ANY ('{3,4,5}'::integer[])) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2 Index Cond: (a = ANY ('{3,4,5}'::integer[])) (6 rows) -- Introduce a NULL and DEFAULT partition so we can test more complex cases create table mclparted_null partition of mclparted for values in(null); create table mclparted_def partition of mclparted default; -- Append can be used providing we don't scan the interleaved partition explain (costs off) select * from mclparted where a in(1,2,4) order by a; QUERY PLAN ------------------------------------------------------------------------ Append -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 Index Cond: (a = ANY ('{1,2,4}'::integer[])) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 Index Cond: (a = ANY ('{1,2,4}'::integer[])) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3 Index Cond: (a = ANY ('{1,2,4}'::integer[])) (7 rows) explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a; QUERY PLAN -------------------------------------------------------------------------------- Append -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclparted_4 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) (9 rows) -- Test a more complex case where the NULL partition allows some other value drop table mclparted_null; create table mclparted_0_null partition of mclparted for values in(0,null); -- Ensure MergeAppend is used since 0 and NULLs are in the same partition. explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a; QUERY PLAN ------------------------------------------------------------------------------------ Merge Append Sort Key: mclparted.a -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) (10 rows) explain (costs off) select * from mclparted where a in(0,1,2,4) order by a; QUERY PLAN ------------------------------------------------------------------------------------ Merge Append Sort Key: mclparted.a -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1 Index Cond: (a = ANY ('{0,1,2,4}'::integer[])) -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2 Index Cond: (a = ANY ('{0,1,2,4}'::integer[])) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3 Index Cond: (a = ANY ('{0,1,2,4}'::integer[])) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4 Index Cond: (a = ANY ('{0,1,2,4}'::integer[])) (10 rows) -- Ensure Append is used when the null partition is pruned explain (costs off) select * from mclparted where a in(1,2,4) order by a; QUERY PLAN ------------------------------------------------------------------------ Append -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 Index Cond: (a = ANY ('{1,2,4}'::integer[])) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 Index Cond: (a = ANY ('{1,2,4}'::integer[])) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3 Index Cond: (a = ANY ('{1,2,4}'::integer[])) (7 rows) -- Ensure MergeAppend is used when the default partition is not pruned explain (costs off) select * from mclparted where a in(1,2,4,100) order by a; QUERY PLAN ------------------------------------------------------------------------------ Merge Append Sort Key: mclparted.a -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1 Index Cond: (a = ANY ('{1,2,4,100}'::integer[])) -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2 Index Cond: (a = ANY ('{1,2,4,100}'::integer[])) -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3 Index Cond: (a = ANY ('{1,2,4,100}'::integer[])) -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4 Index Cond: (a = ANY ('{1,2,4,100}'::integer[])) (10 rows) drop table mclparted; reset enable_sort; reset enable_bitmapscan; -- Ensure subplans which don't have a path with the correct pathkeys get -- sorted correctly. drop index mcrparted_a_abs_c_idx; create index on mcrparted1 (a, abs(b), c); create index on mcrparted2 (a, abs(b), c); create index on mcrparted3 (a, abs(b), c); create index on mcrparted4 (a, abs(b), c); explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1; QUERY PLAN ------------------------------------------------------------------------------- Limit -> Append -> Sort Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c -> Seq Scan on mcrparted0 mcrparted_1 Filter: (a < 20) -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 Index Cond: (a < 20) -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 Index Cond: (a < 20) -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 Index Cond: (a < 20) (12 rows) set enable_bitmapscan = 0; -- Ensure Append node can be used when the partition is ordered by some -- pathkeys which were deemed redundant. explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c; QUERY PLAN ------------------------------------------------------------------------- Append -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1 Index Cond: (a = 10) -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2 Index Cond: (a = 10) (5 rows) reset enable_bitmapscan; drop table mcrparted; -- Ensure LIST partitions allow an Append to be used instead of a MergeAppend create table bool_lp (b bool) partition by list(b); create table bool_lp_true partition of bool_lp for values in(true); create table bool_lp_false partition of bool_lp for values in(false); create index on bool_lp (b); explain (costs off) select * from bool_lp order by b; QUERY PLAN ---------------------------------------------------------------------------- Append -> Index Only Scan using bool_lp_false_b_idx on bool_lp_false bool_lp_1 -> Index Only Scan using bool_lp_true_b_idx on bool_lp_true bool_lp_2 (3 rows) drop table bool_lp; -- Ensure const bool quals can be properly detected as redundant create table bool_rp (b bool, a int) partition by range(b,a); create table bool_rp_false_1k partition of bool_rp for values from (false,0) to (false,1000); create table bool_rp_true_1k partition of bool_rp for values from (true,0) to (true,1000); create table bool_rp_false_2k partition of bool_rp for values from (false,1000) to (false,2000); create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000); create index on bool_rp (b,a); explain (costs off) select * from bool_rp where b = true order by b,a; QUERY PLAN ---------------------------------------------------------------------------------- Append -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1 Index Cond: (b = true) -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2 Index Cond: (b = true) (5 rows) explain (costs off) select * from bool_rp where b = false order by b,a; QUERY PLAN ------------------------------------------------------------------------------------ Append -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1 Index Cond: (b = false) -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2 Index Cond: (b = false) (5 rows) explain (costs off) select * from bool_rp where b = true order by a; QUERY PLAN ---------------------------------------------------------------------------------- Append -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1 Index Cond: (b = true) -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2 Index Cond: (b = true) (5 rows) explain (costs off) select * from bool_rp where b = false order by a; QUERY PLAN ------------------------------------------------------------------------------------ Append -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1 Index Cond: (b = false) -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2 Index Cond: (b = false) (5 rows) drop table bool_rp; -- Ensure an Append scan is chosen when the partition order is a subset of -- the required order. create table range_parted (a int, b int, c int) partition by range(a, b); create table range_parted1 partition of range_parted for values from (0,0) to (10,10); create table range_parted2 partition of range_parted for values from (10,10) to (20,20); create index on range_parted (a,b,c); explain (costs off) select * from range_parted order by a,b,c; QUERY PLAN ------------------------------------------------------------------------------------- Append -> Index Only Scan using range_parted1_a_b_c_idx on range_parted1 range_parted_1 -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 range_parted_2 (3 rows) explain (costs off) select * from range_parted order by a desc,b desc,c desc; QUERY PLAN ---------------------------------------------------------------------------------------------- Append -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_parted2 range_parted_2 -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1 range_parted_1 (3 rows) drop table range_parted; -- Check that we allow access to a child table's statistics when the user -- has permissions only for the parent table. create table permtest_parent (a int, b text, c text) partition by list (a); create table permtest_child (b text, c text, a int) partition by list (b); create table permtest_grandchild (c text, b text, a int); alter table permtest_child attach partition permtest_grandchild for values in ('a'); alter table permtest_parent attach partition permtest_child for values in (1); create index on permtest_parent (left(c, 3)); insert into permtest_parent select 1, 'a', left(fipshash(i::text), 5) from generate_series(0, 100) i; analyze permtest_parent; create role regress_no_child_access; revoke all on permtest_grandchild from regress_no_child_access; grant select on permtest_parent to regress_no_child_access; set session authorization regress_no_child_access; -- without stats access, these queries would produce hash join plans: explain (costs off) select * from permtest_parent p1 inner join permtest_parent p2 on p1.a = p2.a and p1.c ~ 'a1$'; QUERY PLAN ------------------------------------------ Nested Loop Join Filter: (p1.a = p2.a) -> Seq Scan on permtest_grandchild p1 Filter: (c ~ 'a1$'::text) -> Seq Scan on permtest_grandchild p2 (5 rows) explain (costs off) select * from permtest_parent p1 inner join permtest_parent p2 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$'; QUERY PLAN ---------------------------------------------- Nested Loop Join Filter: (p1.a = p2.a) -> Seq Scan on permtest_grandchild p1 Filter: ("left"(c, 3) ~ 'a1$'::text) -> Seq Scan on permtest_grandchild p2 (5 rows) reset session authorization; revoke all on permtest_parent from regress_no_child_access; grant select(a,c) on permtest_parent to regress_no_child_access; set session authorization regress_no_child_access; explain (costs off) select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2 on p1.a = p2.a and p1.c ~ 'a1$'; QUERY PLAN ------------------------------------------ Nested Loop Join Filter: (p1.a = p2.a) -> Seq Scan on permtest_grandchild p1 Filter: (c ~ 'a1$'::text) -> Seq Scan on permtest_grandchild p2 (5 rows) -- we will not have access to the expression index's stats here: explain (costs off) select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$'; QUERY PLAN ---------------------------------------------------- Hash Join Hash Cond: (p2.a = p1.a) -> Seq Scan on permtest_grandchild p2 -> Hash -> Seq Scan on permtest_grandchild p1 Filter: ("left"(c, 3) ~ 'a1$'::text) (6 rows) reset session authorization; revoke all on permtest_parent from regress_no_child_access; drop role regress_no_child_access; drop table permtest_parent; -- Verify that constraint errors across partition root / child are -- handled correctly (Bug #16293) CREATE TABLE errtst_parent ( partid int not null, shdata int not null, data int NOT NULL DEFAULT 0, CONSTRAINT shdata_small CHECK(shdata < 3) ) PARTITION BY RANGE (partid); -- fast defaults lead to attribute mapping being used in one -- direction, but not the other CREATE TABLE errtst_child_fastdef ( partid int not null, shdata int not null, CONSTRAINT shdata_small CHECK(shdata < 3) ); -- no remapping in either direction necessary CREATE TABLE errtst_child_plaindef ( partid int not null, shdata int not null, data int NOT NULL DEFAULT 0, CONSTRAINT shdata_small CHECK(shdata < 3), CHECK(data < 10) ); -- remapping in both direction CREATE TABLE errtst_child_reorder ( data int NOT NULL DEFAULT 0, shdata int not null, partid int not null, CONSTRAINT shdata_small CHECK(shdata < 3), CHECK(data < 10) ); ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0; ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10); ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10); ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20); ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30); -- insert without child check constraint error INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5'); INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5'); INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5'); -- insert with child check constraint error INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10'); ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" DETAIL: Failing row contains (0, 1, 10). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10'); ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" DETAIL: Failing row contains (10, 1, 10). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10'); ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" DETAIL: Failing row contains (20, 1, 10). -- insert with child not null constraint error INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL); ERROR: null value in column "data" of relation "errtst_child_fastdef" violates not-null constraint DETAIL: Failing row contains (0, 1, null). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL); ERROR: null value in column "data" of relation "errtst_child_plaindef" violates not-null constraint DETAIL: Failing row contains (10, 1, null). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL); ERROR: null value in column "data" of relation "errtst_child_reorder" violates not-null constraint DETAIL: Failing row contains (20, 1, null). -- insert with shared check constraint error INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5'); ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small" DETAIL: Failing row contains (0, 5, 5). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5'); ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small" DETAIL: Failing row contains (10, 5, 5). INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5'); ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small" DETAIL: Failing row contains (20, 5, 5). -- within partition update without child check constraint violation BEGIN; UPDATE errtst_parent SET data = data + 1 WHERE partid = 0; UPDATE errtst_parent SET data = data + 1 WHERE partid = 10; UPDATE errtst_parent SET data = data + 1 WHERE partid = 20; ROLLBACK; -- within partition update with child check constraint violation UPDATE errtst_parent SET data = data + 10 WHERE partid = 0; ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" DETAIL: Failing row contains (0, 1, 15). UPDATE errtst_parent SET data = data + 10 WHERE partid = 10; ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" DETAIL: Failing row contains (10, 1, 15). UPDATE errtst_parent SET data = data + 10 WHERE partid = 20; ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" DETAIL: Failing row contains (20, 1, 15). -- direct leaf partition update, without partition id violation BEGIN; UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0; UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10; UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20; ROLLBACK; -- direct leaf partition update, with partition id violation UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0; ERROR: new row for relation "errtst_child_fastdef" violates partition constraint DETAIL: Failing row contains (10, 1, 5). UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10; ERROR: new row for relation "errtst_child_plaindef" violates partition constraint DETAIL: Failing row contains (20, 1, 5). UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20; ERROR: new row for relation "errtst_child_reorder" violates partition constraint DETAIL: Failing row contains (5, 1, 30). -- partition move, without child check constraint violation BEGIN; UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0; UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10; UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20; ROLLBACK; -- partition move, with child check constraint violation UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0; ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" DETAIL: Failing row contains (10, 1, 15). UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10; ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" DETAIL: Failing row contains (20, 1, 15). UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20; ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" DETAIL: Failing row contains (0, 1, 15). -- partition move, without target partition UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20; ERROR: no partition of relation "errtst_parent" found for row DETAIL: Partition key of the failing row contains (partid) = (30). DROP TABLE errtst_parent;