| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/backend/snowball/libstemmer/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 16 kB |
|
Quelle inherit.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- 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). -- ... but not UNIQUE. CREATE TEMP TABLE z2 (b TEXT, UNIQUE(aa, b)) inherits (a); INSERT INTO z2 VALUES (NULL, 'text'); -- should work -- 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 lis 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.conn 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_d 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_e 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_e 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_e 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_e 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_e 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_e 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_e 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)) inhe 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_e 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_e 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_e 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, ADD COLUMN j bigint DEFAULT 1; NOTICE: merging definition of column "i" for child "inhtd" NOTICE: merging definition of column "i" for child "inhtd" NOTICE: merging definition of column "j" for child "inhtd" NOTICE: merging definition of column "j" for child "inhtd" \d+ inhta Table "public.inhta" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- i | integer | | | | plain | | j | bigint | | | 1 | plain | | Child tables: inhtb, inhtd \d+ inhtd Table "public.inhtd" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- i | integer | | | | plain | | j | bigint | | | 1 | plain | | Inherits: inhta, inhtb, inhtc 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" FORE Not-null constraints: "test_primary_constraints_id_not_null" NOT NULL "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_constra Child tables: test_foreign_constraints_inh ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fke \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 alter table p1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; alter table p1_c1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; NOTICE: merging constraint "inh_check_constraint3" with inherited definition alter table p1_c1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; alter table p1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; NOTICE: merging constraint "inh_check_constraint4" with inherited definition -- allowed to merge enforced constraint with parent's not enforced constraint alter table p1_c1 add constraint inh_check_constraint5 check (f1 < 10) enforced; alter table p1 add constraint inh_check_constraint5 check (f1 < 10) not enforced; NOTICE: merging constraint "inh_check_constraint5" with inherited definition alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced; alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced; NOTICE: merging constraint "inh_check_constraint6" with inherited definition alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced; alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced; NOTICE: merging constraint "inh_check_constraint9" with inherited definition -- the not-valid state of the child constraint will be ignored here. alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced; alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced; NOTICE: merging constraint "inh_check_constraint10" with inherited definition create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); NOTICE: merging column "f1" with inherited definition NOTICE: merging constraint "inh_check_constraint4" with inherited definition -- but reverse is not allowed alter table p1_c1 add constraint inh_check_constraint7 check (f1 < 10) not enforced; alter table p1 add constraint inh_check_constraint7 check (f1 < 10) enforced; ERROR: constraint "inh_check_constraint7" conflicts with NOT ENFORCED constraint on relat alter table p1 add constraint inh_check_constraint8 check (f1 < 10) enforced; alter table p1_c1 add constraint inh_check_constraint8 check (f1 < 10) not enforced; ERROR: constraint "inh_check_constraint8" conflicts with NOT ENFORCED constraint on relat create table p1_fail(f1 int constraint inh_check_constraint2 check (f1 < 10) not enforced) in NOTICE: merging column "f1" with inherited definition ERROR: constraint "inh_check_constraint2" conflicts with NOT ENFORCED constraint on relat -- constraints with different enforceability can be merged by marking them as ENFORCED create table p1_c3() inherits(p1, p1_c1); NOTICE: merging multiple inherited definitions of column "f1" -- but not allowed if the child constraint is explicitly asked to be NOT ENFORCED create table p1_fail(f1 int constraint inh_check_constraint6 check (f1 < 10) not enforced) in NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging column "f1" with inherited definition ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constraint on relat select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforc from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; relname | conname | conislocal | coninhcount | conenforced | convalidated ---------+------------------------+------------+-------------+-------------+---- p1 | inh_check_constraint1 | t | 0 | t | t p1 | inh_check_constraint10 | t | 0 | f | f p1 | inh_check_constraint2 | t | 0 | t | t p1 | inh_check_constraint3 | t | 0 | f | f p1 | inh_check_constraint4 | t | 0 | f | f p1 | inh_check_constraint5 | t | 0 | f | f p1 | inh_check_constraint6 | t | 0 | f | f p1 | inh_check_constraint8 | t | 0 | t | t p1 | inh_check_constraint9 | t | 0 | f | f p1_c1 | inh_check_constraint1 | t | 1 | t | t p1_c1 | inh_check_constraint10 | t | 1 | t | t p1_c1 | inh_check_constraint2 | t | 1 | t | t p1_c1 | inh_check_constraint3 | t | 1 | f | f p1_c1 | inh_check_constraint4 | t | 1 | f | f p1_c1 | inh_check_constraint5 | t | 1 | t | t p1_c1 | inh_check_constraint6 | t | 1 | t | t p1_c1 | inh_check_constraint7 | t | 0 | f | f p1_c1 | inh_check_constraint8 | f | 1 | t | t p1_c1 | inh_check_constraint9 | t | 1 | t | f p1_c2 | inh_check_constraint1 | f | 1 | t | t p1_c2 | inh_check_constraint10 | f | 1 | f | f p1_c2 | inh_check_constraint2 | f | 1 | t | t p1_c2 | inh_check_constraint3 | f | 1 | f | f p1_c2 | inh_check_constraint4 | t | 1 | t | t p1_c2 | inh_check_constraint5 | f | 1 | f | f p1_c2 | inh_check_constraint6 | f | 1 | f | f p1_c2 | inh_check_constraint8 | f | 1 | t | t p1_c2 | inh_check_constraint9 | f | 1 | f | f p1_c3 | inh_check_constraint1 | f | 2 | t | t p1_c3 | inh_check_constraint10 | f | 2 | t | t p1_c3 | inh_check_constraint2 | f | 2 | t | t p1_c3 | inh_check_constraint3 | f | 2 | f | f p1_c3 | inh_check_constraint4 | f | 2 | f | f p1_c3 | inh_check_constraint5 | f | 2 | t | t p1_c3 | inh_check_constraint6 | f | 2 | t | t p1_c3 | inh_check_constraint7 | f | 1 | f | f p1_c3 | inh_check_constraint8 | f | 2 | t | t p1_c3 | inh_check_constraint9 | f | 2 | t | t (38 rows) drop table p1 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table p1_c1 drop cascades to table p1_c2 drop cascades to table p1_c3 -- -- Similarly, check the merging of existing constraints; a parent constraint -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the -- reverse is not allowed. -- create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); alter table p1_c1 inherit p1; drop table p1 cascade; NOTICE: drop cascades to table p1_c1 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); alter table p1_c1 inherit p1; ERROR: constraint "p1_a_check" conflicts with NOT ENFORCED constraint on child table "p1_c1 drop table p1, p1_c1; -- -- Test DROP behavior of multiply-defined CHECK constraints -- create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1); NOTICE: merging column "f1" with inherited definition NOTICE: merging constraint "f1_pos" with inherited definition alter table p1_c1 drop constraint f1_pos; ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1" alter table p1 drop constraint f1_pos; \d p1_c1 Table "public.p1_c1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Check constraints: "f1_pos" CHECK (f1 > 0) Inherits: p1 drop table p1 cascade; NOTICE: drop cascades to table p1_c1 create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); create table p2(f1 int constraint f1_pos CHECK (f1 > 0)); create table p1p2_c1 (f1 int) inherits (p1, p2); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging column "f1" with inherited definition create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging column "f1" with inherited definition NOTICE: merging constraint "f1_pos" with inherited definition alter table p2 drop constraint f1_pos; alter table p1 drop constraint f1_pos; \d p1p2_c* Table "public.p1p2_c1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Inherits: p1, p2 Table "public.p1p2_c2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Check constraints: "f1_pos" CHECK (f1 > 0) Inherits: p1, p2 drop table p1, p2 cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table p1p2_c1 drop cascades to table p1p2_c2 create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); create table p1_c1() inherits (p1); create table p1_c2() inherits (p1); create table p1_c1c2() inherits (p1_c1, p1_c2); NOTICE: merging multiple inherited definitions of column "f1" \d p1_c1c2 Table "public.p1_c1c2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Check constraints: "f1_pos" CHECK (f1 > 0) Inherits: p1_c1, p1_c2 alter table p1 drop constraint f1_pos; \d p1_c1c2 Table "public.p1_c1c2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Inherits: p1_c1, p1_c2 drop table p1 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table p1_c1 drop cascades to table p1_c2 drop cascades to table p1_c1c2 create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); create table p1_c1() inherits (p1); create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1); NOTICE: merging constraint "f1_pos" with inherited definition create table p1_c1c2() inherits (p1_c1, p1_c2, p1); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "f1" alter table p1_c2 drop constraint f1_pos; ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c2" alter table p1 drop constraint f1_pos; alter table p1_c1c2 drop constraint f1_pos; ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1c2" alter table p1_c2 drop constraint f1_pos; \d p1_c1c2 Table "public.p1_c1c2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- f1 | integer | | | Inherits: p1_c1, p1_c2, p1 drop table p1 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table p1_c1 drop cascades to table p1_c2 drop cascades to table p1_c1c2 -- 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 v 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 " 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_cons 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_chec 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 Disabled: true 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) (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 -------------------------------------------------------------------------------- Result Output: (InitPlan 1).col1 InitPlan 1 -> 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; explain (verbose, costs off) -- bug #18652 select 1 - id as c from (select id from matest3 t1 union all select id * 2 from matest3 t2) ss order by c; QUERY PLAN ------------------------------------------------------------ Result Output: ((1 - t1.id)) -> Merge Append Sort Key: ((1 - t1.id)) -> Index Scan using matest3i on public.matest3 t1 Output: t1.id, (1 - t1.id) -> Sort Output: ((t2.id * 2)), ((1 - (t2.id * 2))) Sort Key: ((1 - (t2.id * 2))) -> Seq Scan on public.matest3 t2 Output: (t2.id * 2), (1 - (t2.id * 2)) (11 rows) select 1 - id as c from (select id from matest3 t1 union all select id * 2 from matest3 t2) ss order by c; c ----- -11 -9 -5 -4 (4 rows) 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 a MergeAppend plan where one child requires a sort create table matest0(a int primary key); create table matest1() inherits (matest0); insert into matest0 select generate_series(1, 400); insert into matest1 select generate_series(1, 200); analyze matest0; analyze matest1; explain (costs off) select * from matest0 where a < 100 order by a; QUERY PLAN --------------------------------------------------------------- Merge Append Sort Key: matest0.a -> Index Only Scan using matest0_pkey on matest0 matest0_1 Index Cond: (a < 100) -> Sort Sort Key: matest0_2.a -> Seq Scan on matest1 matest0_2 Filter: (a < 100) (8 rows) 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 -> 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 -> 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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), 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 -> 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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_1.tableoid, i_1.ctid SubPlan 1 -> 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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), 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 -- -- Test inheritance of NOT NULL constraints -- create table pp1 (f1 int); create table cc1 (f2 text, f3 int) inherits (pp1); create table cc2 (f4 float) inherits (pp1,cc1); NOTICE: merging multiple inherited definitions of column "f1" create table cc3 () inherits (pp1,cc1,cc2); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "f2" NOTICE: merging multiple inherited definitions of column "f3" alter table pp1 alter f1 set not null; \d+ cc3 Table "public.cc3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) Inherits: pp1, cc1, cc2 alter table cc3 no inherit pp1; alter table cc3 no inherit cc1; alter table cc3 no inherit cc2; \d+ cc3 Table "public.cc3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" drop table cc3; -- named NOT NULL constraint alter table cc1 add column a2 int constraint nn not null; \d+ cc1 Table "public.cc1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) "nn" NOT NULL "a2" Inherits: pp1 Child tables: cc2 \d+ cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) "nn" NOT NULL "a2" (inherited) Inherits: pp1, cc1 alter table pp1 alter column f1 set not null; \d+ pp1 Table "public.pp1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" Child tables: cc1, cc2 \d+ cc1 Table "public.cc1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) "nn" NOT NULL "a2" Inherits: pp1 Child tables: cc2 \d+ cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) "nn" NOT NULL "a2" (inherited) Inherits: pp1, cc1 -- cannot create table with inconsistent NO INHERIT constraint create table cc3 (a2 int not null no inherit) inherits (cc1); NOTICE: moving and merging column "a2" with inherited definition DETAIL: User-specified column moved to the position of the inherited column. ERROR: cannot define not-null constraint with NO INHERIT on column "a2" DETAIL: The column has an inherited not-null constraint. -- change NO INHERIT status of inherited constraint: no dice, it's inherited alter table cc2 add not null a2 no inherit; ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "cc2" HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CO -- remove constraint from cc2: no dice, it's inherited alter table cc2 alter column a2 drop not null; ERROR: cannot drop inherited constraint "nn" of relation "cc2" -- remove constraint from cc1, should succeed alter table cc1 alter column a2 drop not null; \d+ cc1 Table "public.cc1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | a2 | integer | | | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) Inherits: pp1 Child tables: cc2 -- same for cc2 alter table cc2 alter column f1 drop not null; ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2" \d+ cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | a2 | integer | | | | plain | | Not-null constraints: "pp1_f1_not_null" NOT NULL "f1" (inherited) Inherits: pp1, cc1 -- remove from cc1, should fail again alter table cc1 alter column f1 drop not null; ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc1" -- remove from pp1, should succeed alter table pp1 alter column f1 drop not null; \d+ pp1 Table "public.pp1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | | | plain | | Child tables: cc1, cc2 alter table pp1 add primary key (f1); -- Leave these tables around, for pg_upgrade testing -- test that removing inheritance of NOT NULL NO INHERIT works correctly create table inh_parent (f1 int not null no inherit, f2 int not null no inherit); create table inh_child (f1 int not null no inherit, f2 int); alter table inh_child inherit inh_parent; alter table inh_child no inherit inh_parent; \d+ inh_child Table "public.inh_child" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | f2 | integer | | | | plain | | Not-null constraints: "inh_child_f1_not_null" NOT NULL "f1" NO INHERIT drop table inh_parent, inh_child; -- test that inhcount is updated correctly through multiple inheritance create table inh_pp1 (f1 int); create table inh_cc1 (f2 text, f3 int) inherits (inh_pp1); create table inh_cc2(f4 float) inherits(inh_pp1,inh_cc1); NOTICE: merging multiple inherited definitions of column "f1" alter table inh_pp1 alter column f1 set not null; alter table inh_cc2 no inherit inh_pp1; alter table inh_cc2 no inherit inh_cc1; \d+ inh_cc2 Table "public.inh_cc2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | not null | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | Not-null constraints: "inh_pp1_f1_not_null" NOT NULL "f1" drop table inh_pp1, inh_cc1, inh_cc2; create table inh_pp1 (f1 int not null); create table inh_cc1 (f2 text, f3 int) inherits (inh_pp1); create table inh_cc2(f4 float) inherits(inh_pp1,inh_cc1); NOTICE: merging multiple inherited definitions of column "f1" alter table inh_pp1 alter column f1 drop not null; \d+ inh_cc2 Table "public.inh_cc2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+----------+-------- f1 | integer | | | | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | double precision | | | | plain | | Inherits: inh_pp1, inh_cc1 drop table inh_pp1, inh_cc1, inh_cc2; -- Test a not-null addition that must walk down the hierarchy CREATE TABLE inh_parent (); CREATE TABLE inh_child (i int) INHERITS (inh_parent); CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child); ALTER TABLE inh_parent ADD COLUMN i int NOT NULL; NOTICE: merging definition of column "i" for child "inh_child" NOTICE: merging definition of column "i" for child "inh_grandchild" drop table inh_parent, inh_child, inh_grandchild; -- Test the same constraint name for different columns in different parents create table inh_parent1(a int constraint nn not null); create table inh_parent2(b int constraint nn not null); create table inh_child1 () inherits (inh_parent1, inh_parent2); \d+ inh_child1 Table "public.inh_child1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | not null | | plain | | b | integer | | not null | | plain | | Not-null constraints: "nn" NOT NULL "a" (inherited) "inh_child1_b_not_null" NOT NULL "b" (inherited) Inherits: inh_parent1, inh_parent2 create table inh_child2 (constraint foo not null a) inherits (inh_parent1, inh_parent2); alter table inh_child2 no inherit inh_parent2; \d+ inh_child2 Table "public.inh_child2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | not null | | plain | | b | integer | | not null | | plain | | Not-null constraints: "foo" NOT NULL "a" (local, inherited) "nn" NOT NULL "b" Inherits: inh_parent1 drop table inh_parent1, inh_parent2, inh_child1, inh_child2; -- Test multiple parents with overlapping primary keys create table inh_parent1(a int, b int, c int, primary key (a, b)); create table inh_parent2(d int, e int, b int, primary key (d, b)); create table inh_child() inherits (inh_parent1, inh_parent2); NOTICE: merging multiple inherited definitions of column "b" select conrelid::regclass, conname, contype, conkey, coninhcount, conislocal, connoinherit from pg_constraint where contype in ('n','p') and conrelid::regclass::text in ('inh_child', 'inh_parent1', 'inh_parent2') order by 1, 2; conrelid | conname | contype | conkey | coninhcount | conislocal | connoinherit -------------+------------------------+---------+--------+-------------+-------- inh_parent1 | inh_parent1_a_not_null | n | {1} | 0 | t | f inh_parent1 | inh_parent1_b_not_null | n | {2} | 0 | t | f inh_parent1 | inh_parent1_pkey | p | {1,2} | 0 | t | t inh_parent2 | inh_parent2_b_not_null | n | {3} | 0 | t | f inh_parent2 | inh_parent2_d_not_null | n | {1} | 0 | t | f inh_parent2 | inh_parent2_pkey | p | {1,3} | 0 | t | t inh_child | inh_parent1_a_not_null | n | {1} | 1 | f | f inh_child | inh_parent1_b_not_null | n | {2} | 2 | f | f inh_child | inh_parent2_d_not_null | n | {4} | 1 | f | f (9 rows) \d+ inh_child Table "public.inh_child" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | not null | | plain | | b | integer | | not null | | plain | | c | integer | | | | plain | | d | integer | | not null | | plain | | e | integer | | | | plain | | Not-null constraints: "inh_parent1_a_not_null" NOT NULL "a" (inherited) "inh_parent1_b_not_null" NOT NULL "b" (inherited) "inh_parent2_d_not_null" NOT NULL "d" (inherited) Inherits: inh_parent1, inh_parent2 drop table inh_parent1, inh_parent2, inh_child; -- NOT NULL NO INHERIT create table inh_nn_parent(a int); create table inh_nn_child() inherits (inh_nn_parent); alter table inh_nn_parent add not null a no inherit; create table inh_nn_child2() inherits (inh_nn_parent); select conrelid::regclass, conname, contype, conkey, (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text like 'inh\_nn\_%' order by 2, 1; conrelid | conname | contype | conkey | attname | coninhcount | conislocal | connoinherit ---------------+--------------------------+---------+--------+---------+-------- inh_nn_parent | inh_nn_parent_a_not_null | n | {1} | a | 0 | t | t (1 row) \d+ inh_nn* Table "public.inh_nn_child" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | | | plain | | Inherits: inh_nn_parent Table "public.inh_nn_child2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | | | plain | | Inherits: inh_nn_parent Table "public.inh_nn_parent" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- a | integer | | not null | | plain | | Not-null constraints: "inh_nn_parent_a_not_null" NOT NULL "a" NO INHERIT Child tables: inh_nn_child, inh_nn_child2 drop table inh_nn_parent, inh_nn_child, inh_nn_child2; CREATE TABLE inh_nn_parent (a int, NOT NULL a NO INHERIT); CREATE TABLE inh_nn_child() INHERITS (inh_nn_parent); ALTER TABLE inh_nn_parent ADD CONSTRAINT nna NOT NULL a; ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CO ALTER TABLE inh_nn_parent ALTER a SET NOT NULL; ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on DROP TABLE inh_nn_parent cascade; NOTICE: drop cascades to table inh_nn_child -- Adding a PK at the top level of a hierarchy should cause all descendants -- to be checked for nulls, even past a no-inherit constraint CREATE TABLE inh_nn_lvl1 (a int); CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1); CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2); ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); ERROR: cannot change NO INHERIT status of NOT NULL constraint "foo" on relation "inh_nn_lvl3" HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CO DROP TABLE inh_nn_lvl1, inh_nn_lvl2, inh_nn_lvl3; -- Disallow specifying conflicting NO INHERIT flags for the same constraint CREATE TABLE inh_nn1 (a int primary key, b int, not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" CREATE TABLE inh_nn1 (a int not null); CREATE TABLE inh_nn2 (a int not null no inherit) INHERITS (inh_nn1); NOTICE: merging column "a" with inherited definition ERROR: cannot define not-null constraint with NO INHERIT on column "a" DETAIL: The column has an inherited not-null constraint. CREATE TABLE inh_nn3 (a int not null, b int, not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" CREATE TABLE inh_nn4 (a int not null no inherit, b int, not null a); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" DROP TABLE IF EXISTS inh_nn1, inh_nn2, inh_nn3, inh_nn4; NOTICE: table "inh_nn2" does not exist, skipping NOTICE: table "inh_nn3" does not exist, skipping NOTICE: table "inh_nn4" does not exist, skipping -- -- test inherit/deinherit -- create table inh_parent(f1 int); create table inh_child1(f1 int not null); create table inh_child2(f1 int); -- inh_child1 should have not null constraint alter table inh_child1 inherit inh_parent; -- should fail, missing NOT NULL constraint alter table inh_child2 inherit inh_child1; ERROR: column "f1" in child table "inh_child2" must be marked NOT NULL alter table inh_child2 alter column f1 set not null; alter table inh_child2 inherit inh_child1; -- add NOT NULL constraint recursively alter table inh_parent alter column f1 set not null; \d+ inh_parent Table "public.inh_parent" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_parent_f1_not_null" NOT NULL "f1" Child tables: inh_child1 \d+ inh_child1 Table "public.inh_child1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_child1_f1_not_null" NOT NULL "f1" (local, inherited) Inherits: inh_parent Child tables: inh_child2 \d+ inh_child2 Table "public.inh_child2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) Inherits: inh_child1 select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass) order by 2, 1; conrelid | conname | contype | coninhcount | conislocal ------------+------------------------+---------+-------------+------------ inh_child1 | inh_child1_f1_not_null | n | 1 | t inh_child2 | inh_child2_f1_not_null | n | 1 | t inh_parent | inh_parent_f1_not_null | n | 0 | t (3 rows) -- -- test deinherit procedure -- -- deinherit inh_child1 create table inh_child3 () inherits (inh_child1); alter table inh_child1 no inherit inh_parent; \d+ inh_parent Table "public.inh_parent" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_parent_f1_not_null" NOT NULL "f1" \d+ inh_child1 Table "public.inh_child1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_child1_f1_not_null" NOT NULL "f1" Child tables: inh_child2, inh_child3 \d+ inh_child2 Table "public.inh_child2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+--- f1 | integer | | not null | | plain | | Not-null constraints: "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) Inherits: inh_child1 select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_child3') order by 2, 1; conrelid | conname | contype | coninhcount | conislocal ------------+------------------------+---------+-------------+------------ inh_child1 | inh_child1_f1_not_null | n | 0 | t inh_child3 | inh_child1_f1_not_null | n | 1 | f inh_child2 | inh_child2_f1_not_null | n | 1 | t inh_parent | inh_parent_f1_not_null | n | 0 | t (4 rows) drop table inh_parent, inh_child1, inh_child2, inh_child3; -- ALTER TABLE INHERIT ensures that the child has not-null constraints create table inh_parent (a int not null); create table inh_child (a int); alter table inh_child inherit inh_parent; -- nope ERROR: column "a" in child table "inh_child" must be marked NOT NULL drop table inh_parent, inh_child; -- Can't merge a NO INHERIT constraint with a normal one create table inh_parent (a int not null); create table inh_child (a int not null no inherit); alter table inh_child inherit inh_parent; ERROR: constraint "inh_child_a_not_null" conflicts with non-inherited constraint on chil drop table inh_parent, inh_child; -- don't interfere with other types of constraints create table inh_parent (a int primary key); create table inh_child (a int primary key) inherits (inh_parent); NOTICE: merging column "a" with inherited definition alter table inh_parent add constraint inh_parent_excl exclude ((1) with =); alter table inh_parent add constraint inh_parent_uq unique (a); alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent ( create table inh_child2 () inherits (inh_parent); create table inh_child3 (like inh_parent); alter table inh_child3 inherit inh_parent; select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child order by 2, 1; conrelid | conname | contype | coninhcount | conislocal ------------+-----------------------+---------+-------------+------------ inh_child | inh_child_a_not_null | n | 1 | t inh_child | inh_child_pkey | p | 0 | t inh_parent | inh_parent_a_not_null | n | 0 | t inh_child2 | inh_parent_a_not_null | n | 1 | f inh_child3 | inh_parent_a_not_null | n | 1 | t inh_parent | inh_parent_excl | x | 0 | t inh_parent | inh_parent_fk | f | 0 | t inh_parent | inh_parent_pkey | p | 0 | t inh_parent | inh_parent_uq | u | 0 | t (9 rows) drop table inh_parent, inh_child, inh_child2, inh_child3; -- -- test multi inheritance tree -- create table inh_parent(f1 int not null); create table inh_child1() inherits(inh_parent); create table inh_child2() inherits(inh_parent); create table inh_child3() inherits(inh_child1, inh_child2); NOTICE: merging multiple inherited definitions of column "f1" -- show constraint info select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, order by 2, conrelid::regclass::text; conrelid | conname | contype | coninhcount | conislocal ------------+------------------------+---------+-------------+------------ inh_child1 | inh_parent_f1_not_null | n | 1 | f inh_child2 | inh_parent_f1_not_null | n | 1 | f inh_child3 | inh_parent_f1_not_null | n | 2 | f inh_parent | inh_parent_f1_not_null | n | 0 | t (4 rows) drop table inh_parent cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table inh_child1 drop cascades to table inh_child2 drop cascades to table inh_child3 -- test child table with inherited columns and -- with explicitly specified not null constraints create table inh_parent_1(f1 int); create table inh_parent_2(f2 text); create table inh_child(f1 int not null, f2 text not null) inherits(inh_parent_1, inh_parent NOTICE: merging column "f1" with inherited definition NOTICE: merging column "f2" with inherited definition -- show constraint info select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid in ('inh_parent_1'::regclass, 'inh_parent_2'::regclass, 'inh_child'::regcla order by 2, conrelid::regclass::text; conrelid | conname | contype | coninhcount | conislocal -----------+-----------------------+---------+-------------+------------ inh_child | inh_child_f1_not_null | n | 0 | t inh_child | inh_child_f2_not_null | n | 0 | t (2 rows) -- also drops inh_child table drop table inh_parent_1 cascade; NOTICE: drop cascades to table inh_child drop table inh_parent_2; -- test multi layer inheritance tree create table inh_p1(f1 int not null); create table inh_p2(f1 int not null); create table inh_p3(f2 int); create table inh_p4(f1 int not null, f3 text not null); create table inh_multiparent() inherits(inh_p1, inh_p2, inh_p3, inh_p4); NOTICE: merging multiple inherited definitions of column "f1" NOTICE: merging multiple inherited definitions of column "f1" -- constraint on f1 should have three parents select conrelid::regclass, contype, conname, (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid::regclass in ('inh_p1', 'inh_p2', 'inh_p3', 'inh_p4', 'inh_multiparent') order by conrelid::regclass::text, conname; conrelid | contype | conname | attname | coninhcount | conislocal -----------------+---------+--------------------+---------+-------------+------- inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f inh_p1 | n | inh_p1_f1_not_null | f1 | 0 | t inh_p2 | n | inh_p2_f1_not_null | f1 | 0 | t inh_p4 | n | inh_p4_f1_not_null | f1 | 0 | t inh_p4 | n | inh_p4_f3_not_null | f3 | 0 | t (6 rows) create table inh_multiparent2 (a int not null, f1 int) inherits(inh_p3, inh_multiparent); NOTICE: merging multiple inherited definitions of column "f2" NOTICE: merging column "f1" with inherited definition select conrelid::regclass, contype, conname, (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), coninhcount, conislocal from pg_constraint where contype = 'n' and conrelid::regclass in ('inh_p3', 'inh_multiparent', 'inh_multiparent2') order by conrelid::regclass::text, conname; conrelid | contype | conname | attname | coninhcount | conislocal ------------------+---------+-----------------------------+---------+----------- inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f inh_multiparent2 | n | inh_multiparent2_a_not_null | a | 0 | t inh_multiparent2 | n | inh_p1_f1_not_null | f1 | 1 | f inh_multiparent2 | n | inh_p4_f3_not_null | f3 | 1 | f (5 rows) drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table inh_multiparent drop cascades to table inh_multiparent2 -- -- Test ALTER CONSTRAINT SET [NO] INHERIT -- create table inh_nn1 (f1 int not null no inherit); create table inh_nn2 (f2 text, f3 int, f1 int); alter table inh_nn2 inherit inh_nn1; create table inh_nn3 (f4 float) inherits (inh_nn2); create table inh_nn4 (f5 int, f4 float, f2 text, f3 int, f1 int); alter table inh_nn4 inherit inh_nn2, inherit inh_nn1, inherit inh_nn3; alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') order by 2, 1; conrelid | conname | conkey | coninhcount | conislocal | connoinherit ----------+---------------------+--------+-------------+------------+----------- inh_nn1 | inh_nn1_f1_not_null | {1} | 0 | t | f inh_nn2 | inh_nn1_f1_not_null | {3} | 1 | f | f inh_nn3 | inh_nn1_f1_not_null | {3} | 1 | f | f inh_nn4 | inh_nn1_f1_not_null | {5} | 3 | f | f (4 rows) -- ALTER CONSTRAINT NO INHERIT should work on top-level constraints alter table inh_nn1 alter constraint inh_nn1_f1_not_null no inherit; select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') order by 2, 1; conrelid | conname | conkey | coninhcount | conislocal | connoinherit ----------+---------------------+--------+-------------+------------+----------- inh_nn1 | inh_nn1_f1_not_null | {1} | 0 | t | t inh_nn2 | inh_nn1_f1_not_null | {3} | 0 | t | f inh_nn3 | inh_nn1_f1_not_null | {3} | 1 | f | f inh_nn4 | inh_nn1_f1_not_null | {5} | 2 | t | f (4 rows) -- A constraint that's NO INHERIT can be dropped without damaging children alter table inh_nn1 drop constraint inh_nn1_f1_not_null; select conrelid::regclass, conname, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') order by 2, 1; conrelid | conname | coninhcount | conislocal | connoinherit ----------+---------------------+-------------+------------+-------------- inh_nn2 | inh_nn1_f1_not_null | 0 | t | f inh_nn3 | inh_nn1_f1_not_null | 1 | f | f inh_nn4 | inh_nn1_f1_not_null | 2 | t | f (3 rows) drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4; -- Test inherit constraint and make sure it validates. create table inh_nn1 (f1 int not null no inherit); create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); insert into inh_nn2 values(NULL, 'sample', 1); alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; ERROR: column "f1" of relation "inh_nn2" contains null values delete from inh_nn2; create table inh_nn3 () inherits (inh_nn2); create table inh_nn4 () inherits (inh_nn1, inh_nn2); NOTICE: merging multiple inherited definitions of column "f1" alter table inh_nn1 -- test multicommand alter table while at it alter constraint inh_nn1_f1_not_null inherit, alter constraint inh_nn1_f1_not_null no inherit; select conrelid::regclass, conname, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') order by 2, 1; conrelid | conname | coninhcount | conislocal | connoinherit ----------+---------------------+-------------+------------+-------------- inh_nn1 | inh_nn1_f1_not_null | 0 | t | t inh_nn2 | inh_nn1_f1_not_null | 0 | t | f inh_nn3 | inh_nn1_f1_not_null | 1 | f | f inh_nn4 | inh_nn1_f1_not_null | 1 | t | f (4 rows) drop table inh_nn1, inh_nn2, inh_nn3, inh_nn4; -- Test not null inherit constraint which already exists on child table. create table inh_nn1 (f1 int not null no inherit); create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); create table inh_nn3 (f4 float, constraint nn3_f1 not null f1 no inherit) inherits (inh_nn1, i NOTICE: merging multiple inherited definitions of column "f1" select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3') order by 2, 1; conrelid | conname | conkey | coninhcount | conislocal | connoinherit ----------+---------------------+--------+-------------+------------+----------- inh_nn1 | inh_nn1_f1_not_null | {1} | 0 | t | t inh_nn3 | nn3_f1 | {1} | 0 | t | t (2 rows) -- error: inh_nn3 has an incompatible NO INHERIT constraint alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn3_f1" on relation "inh_nn3" alter table inh_nn3 alter constraint nn3_f1 inherit; alter table inh_nn1 alter constraint inh_nn1_f1_not_null inherit; -- now it works select conrelid::regclass, conname, conkey, coninhcount, conislocal, connoinherit from pg_constraint where contype = 'n' and conrelid::regclass::text in ('inh_nn1', 'inh_nn2', 'inh_nn3') order by 2, 1; conrelid | conname | conkey | coninhcount | conislocal | connoinherit ----------+---------------------+--------+-------------+------------+----------- inh_nn1 | inh_nn1_f1_not_null | {1} | 0 | t | f inh_nn2 | inh_nn1_f1_not_null | {1} | 1 | f | f inh_nn3 | nn3_f1 | {1} | 2 | t | f (3 rows) drop table inh_nn1, inh_nn2, inh_nn3; -- Negative scenarios for alter constraint .. inherit. create table inh_nn1 (f1 int check(f1 > 5) primary key references inh_nn1, f2 int not null); -- constraints other than not-null are not supported alter table inh_nn1 alter constraint inh_nn1_f1_check inherit; ERROR: constraint "inh_nn1_f1_check" of relation "inh_nn1" is not a not-null constraint alter table inh_nn1 alter constraint inh_nn1_pkey inherit; ERROR: constraint "inh_nn1_pkey" of relation "inh_nn1" is not a not-null constraint alter table inh_nn1 alter constraint inh_nn1_f1_fkey inherit; ERROR: constraint "inh_nn1_f1_fkey" of relation "inh_nn1" is not a not-null constraint -- try to drop a nonexistant constraint alter table inh_nn1 alter constraint foo inherit; ERROR: constraint "foo" of relation "inh_nn1" does not exist -- Can't modify inheritability of inherited constraints create table inh_nn2 () inherits (inh_nn1); alter table inh_nn2 alter constraint inh_nn1_f2_not_null no inherit; ERROR: cannot alter inherited constraint "inh_nn1_f2_not_null" on relation "inh_nn2" drop table inh_nn1, inh_nn2; -- -- Mixed ownership inheritance tree -- create role regress_alice; create role regress_bob; grant all on schema public to regress_alice, regress_bob; grant regress_alice to regress_bob; set session authorization regress_alice; create table inh_parent (a int not null); set session authorization regress_bob; create table inh_child () inherits (inh_parent); set session authorization regress_alice; -- alice can't do this: she doesn't own inh_child alter table inh_parent alter a drop not null; ERROR: must be owner of table inh_child set session authorization regress_bob; alter table inh_parent alter a drop not null; reset session authorization; drop table inh_parent, inh_child; revoke all on schema public from regress_alice, regress_bob; drop role regress_alice, regress_bob; -- -- 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 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) partitio 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) partitio 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) p 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, minvalu 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, maxv 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, mcrpa 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, mcrp 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 mcrparte 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 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 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) InitPlan 2 -> 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, maxv 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,2 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 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 (2 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_chi 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_chi 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_chil 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 con 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 co 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 con 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_smal 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_sma 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_smal 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_chi 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_chi 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_chil 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_chi 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_chil 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_chi 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; -- Check that we have the correct tuples estimate for an appendrel create table tuplesest_parted (a int, b int, c float) partition by range(a); create table tuplesest_parted1 partition of tuplesest_parted for values from (0) to (100); create table tuplesest_parted2 partition of tuplesest_parted for values from (100) to (200) create table tuplesest_tab (a int, b int); insert into tuplesest_parted select i%200, i%300, i%400 from generate_series(1, 1000)i; insert into tuplesest_tab select i, i from generate_series(1, 100)i; analyze tuplesest_parted; analyze tuplesest_tab; explain (costs off) select * from tuplesest_tab join (select b from tuplesest_parted where c < 100 group by b) sub on tuplesest_tab.a = sub.b; QUERY PLAN -------------------------------------------------------------------- Hash Join Hash Cond: (tuplesest_parted.b = tuplesest_tab.a) -> HashAggregate Group Key: tuplesest_parted.b -> Append -> Seq Scan on tuplesest_parted1 tuplesest_parted_1 Filter: (c < '100'::double precision) -> Seq Scan on tuplesest_parted2 tuplesest_parted_2 Filter: (c < '100'::double precision) -> Hash -> Seq Scan on tuplesest_tab (11 rows) drop table tuplesest_parted; drop table tuplesest_tab; [Seitenstruktur0.150Druckenetwas mehr zur Ethik2026-08-08] |
2026-08-25