diff options
Diffstat (limited to 'src/test/regress/expected/inherit.out')
| -rw-r--r-- | src/test/regress/expected/inherit.out | 2532 |
1 files changed, 2532 insertions, 0 deletions
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out new file mode 100644 index 0000000..06f4428 --- /dev/null +++ b/src/test/regress/expected/inherit.out @@ -0,0 +1,2532 @@ +-- +-- 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 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 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 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) + +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) + +drop table mclparted; +-- 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(md5(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; |