Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / inherit_optimizer.out
blob: ef4a44eac331f2aae36fa93262e8468c30312343 [file] [log] [blame]
--
-- Test inheritance features
--
CREATE TABLE a (aa TEXT) distributed randomly;
CREATE TABLE b (bb TEXT) INHERITS (a);
CREATE TABLE c (cc TEXT) INHERITS (a);
CREATE TABLE d (dd TEXT) INHERITS (b,c,a);
NOTICE: merging multiple inherited definitions of column "aa"
NOTICE: merging multiple inherited definitions of column "aa"
INSERT INTO a(aa) VALUES('aaa');
INSERT INTO a(aa) VALUES('aaaa');
INSERT INTO a(aa) VALUES('aaaaa');
INSERT INTO a(aa) VALUES('aaaaaa');
INSERT INTO a(aa) VALUES('aaaaaaa');
INSERT INTO a(aa) VALUES('aaaaaaaa');
INSERT INTO b(aa) VALUES('bbb');
INSERT INTO b(aa) VALUES('bbbb');
INSERT INTO b(aa) VALUES('bbbbb');
INSERT INTO b(aa) VALUES('bbbbbb');
INSERT INTO b(aa) VALUES('bbbbbbb');
INSERT INTO b(aa) VALUES('bbbbbbbb');
INSERT INTO c(aa) VALUES('ccc');
INSERT INTO c(aa) VALUES('cccc');
INSERT INTO c(aa) VALUES('ccccc');
INSERT INTO c(aa) VALUES('cccccc');
INSERT INTO c(aa) VALUES('ccccccc');
INSERT INTO c(aa) VALUES('cccccccc');
INSERT INTO d(aa) VALUES('ddd');
INSERT INTO d(aa) VALUES('dddd');
INSERT INTO d(aa) VALUES('ddddd');
INSERT INTO d(aa) VALUES('dddddd');
INSERT INTO d(aa) VALUES('ddddddd');
INSERT INTO d(aa) VALUES('dddddddd');
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----------
a | aaa
a | aaaa
a | aaaaa
a | aaaaaa
a | aaaaaaa
a | aaaaaaaa
b | bbb
b | bbbb
b | bbbbb
b | bbbbbb
b | bbbbbbb
b | bbbbbbbb
c | ccc
c | cccc
c | ccccc
c | cccccc
c | ccccccc
c | cccccccc
d | ddd
d | dddd
d | ddddd
d | dddddd
d | ddddddd
d | dddddddd
(24 rows)
SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----------+----
b | bbb |
b | bbbb |
b | bbbbb |
b | bbbbbb |
b | bbbbbbb |
b | bbbbbbbb |
d | ddd |
d | dddd |
d | ddddd |
d | dddddd |
d | ddddddd |
d | dddddddd |
(12 rows)
SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
d | ddd |
d | dddd |
d | ddddd |
d | dddddd |
d | ddddddd |
d | dddddddd |
(12 rows)
SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----------+----+----+----
d | ddd | | |
d | dddd | | |
d | ddddd | | |
d | dddddd | | |
d | ddddddd | | |
d | dddddddd | | |
(6 rows)
SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----------
a | aaa
a | aaaa
a | aaaaa
a | aaaaaa
a | aaaaaaa
a | aaaaaaaa
(6 rows)
SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----------+----
b | bbb |
b | bbbb |
b | bbbbb |
b | bbbbbb |
b | bbbbbbb |
b | bbbbbbbb |
(6 rows)
SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
(6 rows)
SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----------+----+----+----
d | ddd | | |
d | dddd | | |
d | ddddd | | |
d | dddddd | | |
d | ddddddd | | |
d | dddddddd | | |
(6 rows)
UPDATE a SET aa='zzzz' WHERE aa='aaaa';
UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
UPDATE b SET aa='zzz' WHERE aa='aaa';
UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----------
a | zzzz
a | zzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
b | bbb
b | bbbb
b | bbbbb
b | bbbbbb
b | bbbbbbb
b | bbbbbbbb
c | ccc
c | cccc
c | ccccc
c | cccccc
c | ccccccc
c | cccccccc
d | ddd
d | dddd
d | ddddd
d | dddddd
d | ddddddd
d | dddddddd
(24 rows)
SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----------+----
b | bbb |
b | bbbb |
b | bbbbb |
b | bbbbbb |
b | bbbbbbb |
b | bbbbbbbb |
d | ddd |
d | dddd |
d | ddddd |
d | dddddd |
d | ddddddd |
d | dddddddd |
(12 rows)
SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
d | ddd |
d | dddd |
d | ddddd |
d | dddddd |
d | ddddddd |
d | dddddddd |
(12 rows)
SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----------+----+----+----
d | ddd | | |
d | dddd | | |
d | ddddd | | |
d | dddddd | | |
d | ddddddd | | |
d | dddddddd | | |
(6 rows)
SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+--------
a | zzzz
a | zzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
(6 rows)
SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----------+----
b | bbb |
b | bbbb |
b | bbbbb |
b | bbbbbb |
b | bbbbbbb |
b | bbbbbbbb |
(6 rows)
SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
(6 rows)
SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----------+----+----+----
d | ddd | | |
d | dddd | | |
d | ddddd | | |
d | dddddd | | |
d | ddddddd | | |
d | dddddddd | | |
(6 rows)
UPDATE b SET aa='new';
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----------
a | zzzz
a | zzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
b | new
b | new
b | new
b | new
b | new
b | new
c | ccc
c | cccc
c | ccccc
c | cccccc
c | ccccccc
c | cccccccc
d | new
d | new
d | new
d | new
d | new
d | new
(24 rows)
SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+-----+----
b | new |
b | new |
b | new |
b | new |
b | new |
b | new |
d | new |
d | new |
d | new |
d | new |
d | new |
d | new |
(12 rows)
SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
d | new |
d | new |
d | new |
d | new |
d | new |
d | new |
(12 rows)
SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+-----+----+----+----
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
(6 rows)
SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+--------
a | zzzz
a | zzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
a | zzzzzz
(6 rows)
SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+-----+----
b | new |
b | new |
b | new |
b | new |
b | new |
b | new |
(6 rows)
SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----------+----
c | ccc |
c | cccc |
c | ccccc |
c | cccccc |
c | ccccccc |
c | cccccccc |
(6 rows)
SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+-----+----+----+----
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
(6 rows)
UPDATE a SET aa='new';
DELETE FROM ONLY c WHERE aa='new';
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+-----
a | new
a | new
a | new
a | new
a | new
a | new
b | new
b | new
b | new
b | new
b | new
b | new
d | new
d | new
d | new
d | new
d | new
d | new
(18 rows)
SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+-----+----
b | new |
b | new |
b | new |
b | new |
b | new |
b | new |
d | new |
d | new |
d | new |
d | new |
d | new |
d | new |
(12 rows)
SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+-----+----
d | new |
d | new |
d | new |
d | new |
d | new |
d | new |
(6 rows)
SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+-----+----+----+----
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
(6 rows)
SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+-----
a | new
a | new
a | new
a | new
a | new
a | new
(6 rows)
SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+-----+----
b | new |
b | new |
b | new |
b | new |
b | new |
b | new |
(6 rows)
SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----+----
(0 rows)
SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+-----+----+----+----
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
d | new | | |
(6 rows)
DELETE FROM a;
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----
(0 rows)
SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----+----
(0 rows)
SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----+----
(0 rows)
SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----+----+----+----
(0 rows)
SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
relname | aa
---------+----
(0 rows)
SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
relname | aa | bb
---------+----+----
(0 rows)
SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
relname | aa | cc
---------+----+----
(0 rows)
SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
relname | aa | bb | cc | dd
---------+----+----+----+----
(0 rows)
-- Confirm PRIMARY KEY adds NOT NULL constraint to child table
CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
INSERT INTO z VALUES (NULL, 'text'); -- should fail
ERROR: null value in column "aa" of relation "z" violates not-null constraint
DETAIL: Failing row contains (null, text).
-- Check inherited UPDATE with all children excluded
create table some_tab (a int, b int) distributed randomly;
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, NULL::integer
One-Time Filter: false
Optimizer: Postgres query optimizer
(5 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
-----------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: some_tab.b, some_tab.a
-> Update on public.some_tab
Output: some_tab.b, some_tab.a
-> Result
Output: (some_tab.a + 1), NULL::oid, NULL::tid, NULL::integer
One-Time Filter: false
Optimizer: Postgres query optimizer
(8 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) distributed randomly;
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=0)
One-Time Filter: false
Optimizer: Postgres query optimizer
(4 rows)
drop table parted_tab;
-- Check UPDATE with multi-level partitioned inherited target
create table mlparted_tab (a int, b char, c text) partition by list (a);
create table mlparted_tab_part1 partition of mlparted_tab for values in (1);
create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b);
create table mlparted_tab_part3 partition of mlparted_tab for values in (3);
create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a');
create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b');
insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a');
update mlparted_tab mlp set c = 'xxx'
from
(select a from some_tab union all select a+1 from some_tab) ss (a)
where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2;
relname | a | b | c
---------------------+---+---+-----
mlparted_tab_part1 | 1 | a |
mlparted_tab_part2a | 2 | a |
mlparted_tab_part2b | 2 | b | xxx
mlparted_tab_part3 | 3 | a | xxx
(4 rows)
drop table mlparted_tab;
drop table some_tab cascade;
NOTICE: drop cascades to table some_tab_child
/* Test multiple inheritance of column defaults */
CREATE TABLE firstparent (tomorrow date default now()::date + 1);
CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
NOTICE: merging multiple inherited definitions of column "tomorrow"
CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
NOTICE: merging multiple inherited definitions of column "tomorrow"
ERROR: column "tomorrow" inherits conflicting default values
HINT: To resolve the conflict, specify a default explicitly.
CREATE TABLE otherchild (tomorrow date default now())
INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
NOTICE: merging multiple inherited definitions of column "tomorrow"
NOTICE: merging column "tomorrow" with inherited definition
DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
-- Test changing the type of inherited columns
insert into d values('test','one','two','three');
alter table z drop constraint z_pkey;
alter table a alter column aa type integer using bit_length(aa);
select * from d;
aa | bb | cc | dd
----+-----+-----+-------
32 | one | two | three
(1 row)
-- The above verified that we can change the type of a multiply-inherited
-- column; but we should reject that if any definition was inherited from
-- an unrelated parent.
create temp table parent1(f1 int, f2 int);
create temp table parent2(f1 int, f3 bigint);
create temp table childtab(f4 int) inherits(parent1, parent2);
NOTICE: merging multiple inherited definitions of column "f1"
alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2
ERROR: cannot alter inherited column "f1" of relation "childtab"
alter table parent1 alter column f2 type bigint; -- ok
-- Test non-inheritable parent constraints
create table p1(ff1 int);
alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
alter table p1 add constraint p2chk check (ff1 > 10);
-- connoinherit should be true for NO INHERIT constraint
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;
relname | conname | contype | conislocal | coninhcount | connoinherit
---------+---------+---------+------------+-------------+--------------
p1 | p1chk | c | t | 0 | t
p1 | p2chk | c | t | 0 | f
(2 rows)
-- Test that child does not inherit NO INHERIT constraints
create table c1 () inherits (p1);
\d p1
Table "public.p1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
ff1 | integer | | |
Check constraints:
"p1chk" CHECK (ff1 > 0) NO INHERIT
"p2chk" CHECK (ff1 > 10)
Number of child tables: 1 (Use \d+ to list them.)
Distributed by: (ff1)
\d c1
Table "public.c1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
ff1 | integer | | |
Check constraints:
"p2chk" CHECK (ff1 > 10)
Inherits: p1
Distributed by: (ff1)
-- Test that child does not override inheritable constraints of the parent
create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails
ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2"
drop table p1 cascade;
NOTICE: drop cascades to table c1
-- Tests for casting between the rowtypes of parent and child
-- tables. See the pgsql-hackers thread beginning Dec. 4/04
create table base (i integer);
create table derived () inherits (base);
create table more_derived (like derived, b int) inherits (derived);
NOTICE: merging column "i" with inherited definition
insert into derived (i) values (0);
select derived::base from derived;
derived
---------
(0)
(1 row)
select NULL::derived::base;
base
------
(1 row)
-- remove redundant conversions.
explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived;
QUERY PLAN
-------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: ((ROW(i, b)::more_derived)::base)
-> Seq Scan on public.more_derived
Output: (ROW(i, b)::more_derived)::base
Optimizer: Postgres query optimizer
(5 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);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create function p2text(p2) returns text as 'select $1.f1' language sql;
create table c1(f3 int) inherits(p1,p2);
insert into c1 values(123456789, 'hi', 42);
select p2text(c1.*) from c1;
p2text
--------
hi
(1 row)
drop function p2text(p2);
drop table c1;
drop table p2;
drop table p1;
CREATE TABLE ac (aa TEXT);
alter table ac add constraint ac_check check (aa is not null);
CREATE TABLE bc (bb TEXT) INHERITS (ac);
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
bc | ac_check | c | f | 1 | (aa IS NOT NULL)
(2 rows)
insert into ac (aa) values (NULL);
ERROR: new row for relation "ac" violates check constraint "ac_check"
DETAIL: Failing row contains (null).
insert into bc (aa) values (NULL);
ERROR: new row for relation "bc" violates check constraint "ac_check"
DETAIL: Failing row contains (null, null).
alter table bc drop constraint ac_check; -- fail, disallowed
ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
alter table ac drop constraint ac_check;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
-- try the unnamed-constraint case
alter table ac add check (aa is not null);
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+-------------+---------+------------+-------------+------------------
ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
(2 rows)
insert into ac (aa) values (NULL);
ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
DETAIL: Failing row contains (null).
insert into bc (aa) values (NULL);
ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
DETAIL: Failing row contains (null, null).
alter table bc drop constraint ac_aa_check; -- fail, disallowed
ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
alter table ac drop constraint ac_aa_check;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
alter table ac add constraint ac_check check (aa is not null);
alter table bc no inherit ac;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
bc | ac_check | c | t | 0 | (aa IS NOT NULL)
(2 rows)
alter table bc drop constraint ac_check;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+----------+---------+------------+-------------+------------------
ac | ac_check | c | t | 0 | (aa IS NOT NULL)
(1 row)
alter table ac drop constraint ac_check;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
(0 rows)
drop table bc;
drop table ac;
create table ac (a int constraint check_a check (a <> 0));
create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
NOTICE: merging column "a" with inherited definition
NOTICE: merging constraint "check_a" with inherited definition
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
bc | check_a | c | t | 1 | (a <> 0)
bc | check_b | c | t | 0 | (b <> 0)
(3 rows)
drop table bc;
drop table ac;
create table ac (a int constraint check_a check (a <> 0));
create table bc (b int constraint check_b check (b <> 0));
create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
bc | check_b | c | t | 0 | (b <> 0)
cc | check_a | c | f | 1 | (a <> 0)
cc | check_b | c | f | 1 | (b <> 0)
cc | check_c | c | t | 0 | (c <> 0)
(5 rows)
alter table cc no inherit bc;
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+----------
ac | check_a | c | t | 0 | (a <> 0)
bc | check_b | c | t | 0 | (b <> 0)
cc | check_a | c | f | 1 | (a <> 0)
cc | check_b | c | t | 0 | (b <> 0)
cc | check_c | c | t | 0 | (c <> 0)
(5 rows)
drop table cc;
drop table bc;
drop table ac;
create table p1(f1 int);
create table p2(f2 int);
create table c1(f3 int) inherits(p1,p2);
insert into c1 values(1,-1,2);
alter table p2 add constraint cc check (f2>0); -- fail
ERROR: check constraint "cc" of relation "c1" is violated by some row
alter table p2 add check (f2>0); -- check it without a name, too
ERROR: check constraint "p2_f2_check" of relation "c1" is violated by some row
delete from c1;
insert into c1 values(1,1,2);
alter table p2 add check (f2>0);
insert into c1 values(1,-1,2); -- fail
ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
DETAIL: Failing row contains (1, -1, 2).
create table c2(f3 int) inherits(p1,p2);
\d c2
Table "public.c2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
f1 | integer | | |
f2 | integer | | |
f3 | integer | | |
Check constraints:
"p2_f2_check" CHECK (f2 > 0)
Inherits: p1,
p2
Distributed by: (f1)
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
Distributed by: (f1)
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
Distributed by: (f1)
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
Distributed by: (f1)
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
Distributed by: (f1)
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
Distributed by: (aa)
DROP TABLE inhts;
-- Test for renaming in diamond inheritance
CREATE TABLE inht2 (x int) INHERITS (inht1);
CREATE TABLE inht3 (y int) INHERITS (inht1);
CREATE TABLE inht4 (z int) INHERITS (inht2, inht3);
NOTICE: merging multiple inherited definitions of column "aa"
NOTICE: merging multiple inherited definitions of column "b"
ALTER TABLE inht1 RENAME aa TO aaa;
\d+ inht4
Table "public.inht4"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
aaa | integer | | | | plain | |
b | integer | | | | plain | |
x | integer | | | | plain | |
y | integer | | | | plain | |
z | integer | | | | plain | |
Inherits: inht2,
inht3
Distributed by: (aaa)
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
Distributed by: (aaaa)
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
Distributed by: (val1, val2)
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
Distributed by: (val1, val2)
\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
Distributed by: (val1, val2)
DROP TABLE test_constraints_inh;
DROP TABLE test_constraints;
CREATE TABLE test_ex_constraints (
c circle,
dkey inet,
EXCLUDE USING gist (dkey inet_ops WITH =, 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 | |
dkey | inet | | | | main | |
Indexes:
"test_ex_constraints_dkey_c_excl" EXCLUDE USING gist (dkey inet_ops WITH =, c WITH &&)
Child tables: test_ex_constraints_inh
Distributed by: (dkey)
ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_dkey_c_excl;
\d+ test_ex_constraints
Table "public.test_ex_constraints"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
c | circle | | | | plain | |
dkey | inet | | | | main | |
Child tables: test_ex_constraints_inh
Distributed by: (dkey)
\d+ test_ex_constraints_inh
Table "public.test_ex_constraints_inh"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
c | circle | | | | plain | |
dkey | inet | | | | main | |
Inherits: test_ex_constraints
Distributed by: (dkey)
DROP TABLE test_ex_constraints_inh;
DROP TABLE test_ex_constraints;
-- Test non-inheritable foreign key constraints
CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
\d+ test_primary_constraints
Table "public.test_primary_constraints"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
Indexes:
"test_primary_constraints_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
Distributed by: (id)
\d+ test_foreign_constraints
Table "public.test_foreign_constraints"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id1 | integer | | | | plain | |
Foreign-key constraints:
"test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
Child tables: test_foreign_constraints_inh
Distributed by: (id1)
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
\d+ test_foreign_constraints
Table "public.test_foreign_constraints"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id1 | integer | | | | plain | |
Child tables: test_foreign_constraints_inh
Distributed by: (id1)
\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
Distributed by: (id1)
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);
-- The cascading deletion doesn't work on GPDB, because foreign keys are not
-- enforced in general. So this produces different result than on upstream.
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
-----+---
11 | 1
22 | 2
33 | 3
111 | 1
222 | 2
(5 rows)
drop table inh_fk_1, inh_fk_2, inh_fk_2_child;
-- Test that parent and child CHECK constraints can be created in either order
create table p1(f1 int);
create table p1_c1() inherits(p1);
alter table p1 add constraint inh_check_constraint1 check (f1 > 0);
alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0);
NOTICE: merging constraint "inh_check_constraint1" with inherited definition
alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10);
alter table p1 add constraint inh_check_constraint2 check (f1 < 10);
NOTICE: merging constraint "inh_check_constraint2" with inherited definition
select conrelid::regclass::text as relname, conname, conislocal, coninhcount
from pg_constraint where conname like 'inh\_check\_constraint%'
order by 1, 2;
relname | conname | conislocal | coninhcount
---------+-----------------------+------------+-------------
p1 | inh_check_constraint1 | t | 0
p1 | inh_check_constraint2 | t | 0
p1_c1 | inh_check_constraint1 | t | 1
p1_c1 | inh_check_constraint2 | t | 1
(4 rows)
drop table p1 cascade;
NOTICE: drop cascades to table p1_c1
-- Test that a valid child can have not-valid parent, but not vice versa
create table invalid_check_con(f1 int);
create table invalid_check_con_child() inherits(invalid_check_con);
alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid;
alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail
ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child"
alter table invalid_check_con_child drop constraint inh_check_constraint;
insert into invalid_check_con values(0);
alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0);
alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid;
NOTICE: merging constraint "inh_check_constraint" with inherited definition
insert into invalid_check_con values(0); -- fail
ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint"
DETAIL: Failing row contains (0).
insert into invalid_check_con_child values(0); -- fail
ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint"
DETAIL: Failing row contains (0).
select conrelid::regclass::text as relname, conname,
convalidated, conislocal, coninhcount, connoinherit
from pg_constraint where conname like 'inh\_check\_constraint%'
order by 1, 2;
relname | conname | convalidated | conislocal | coninhcount | connoinherit
-------------------------+----------------------+--------------+------------+-------------+--------------
invalid_check_con | inh_check_constraint | f | t | 0 | f
invalid_check_con_child | inh_check_constraint | t | t | 1 | f
(2 rows)
-- We don't drop the invalid_check_con* tables, to test dump/reload with
--
-- Test parameterized append plans for inheritance trees
--
create temp table patest0 (id, x) as
select x, x from generate_series(0,1000) x
distributed by (id);
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;
set enable_seqscan=off;
set enable_bitmapscan=off;
explain (costs off)
select * from patest0 join (select f1 from int4_tbl where f1 < 10 and f1 > -10 limit 1) ss on id = f1;
QUERY PLAN
----------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (patest0.id = int4_tbl.f1)
-> Append
-> Index Scan using patest0i on patest0 patest0_1
-> Index Scan using patest1i on patest1 patest0_2
-> Index Scan using patest2i on patest2 patest0_3
-> Hash
-> Redistribute Motion 1:3 (slice2; segments: 1)
Hash Key: int4_tbl.f1
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Limit
-> Seq Scan on int4_tbl
Filter: ((f1 < 10) AND (f1 > '-10'::integer))
Optimizer: Postgres query optimizer
(16 rows)
select * from patest0 join (select f1 from int4_tbl where f1 < 10 and f1 > -10 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 where f1 < 10 and f1 > -10 limit 1) ss on id = f1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (patest0.id = int4_tbl.f1)
-> Append
-> Index Scan using patest0i on patest0 patest0_1
-> Index Scan using patest1i on patest1 patest0_2
-> Seq Scan on patest2 patest0_3
-> Hash
-> Redistribute Motion 1:3 (slice2; segments: 1)
Hash Key: int4_tbl.f1
-> Limit
-> Gather Motion 3:1 (slice3; segments: 3)
-> Limit
-> Seq Scan on int4_tbl
Filter: ((f1 < 10) AND (f1 > '-10'::integer))
Optimizer: Postgres query optimizer
(16 rows)
select * from patest0 join (select f1 from int4_tbl where f1 < 10 and f1 > -10 limit 1) ss on id = f1;
id | x | f1
----+---+----
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
(3 rows)
reset enable_seqscan;
reset enable_bitmapscan;
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
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: matest0.id, matest0.name, ((1 - matest0.id))
Merge Key: ((1 - matest0.id))
-> 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
Settings: enable_indexscan = 'off', optimizer = 'on'
Optimizer: Postgres query optimizer
(19 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
-----------------------------------------------------
Finalize Aggregate
Output: min((1 - matest0.id))
-> Gather Motion 3:1 (slice1; segments: 3)
Output: (PARTIAL min((1 - matest0.id)))
-> Partial Aggregate
Output: PARTIAL 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
Settings: enable_indexscan = 'off', optimizer = 'on'
Optimizer: Postgres query optimizer
(17 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
-- GPDB_92_MERGE_FIXME: the cost of bitmap scan is not correct?
-- the cost of merge append with index scan is bigger than the cost
-- of append with bitmapscan + sort
set enable_bitmapscan = off;
explain (verbose, costs off) select * from matest0 order by 1-id;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: matest0.id, matest0.name, ((1 - matest0.id))
Merge Key: ((1 - matest0.id))
-> Merge Append
Sort Key: ((1 - matest0.id))
-> Index Scan using matest0i on public.matest0 matest0_1
Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id)
-> Index Scan using matest1i on public.matest1 matest0_2
Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id)
-> Sort
Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id))
Sort Key: ((1 - matest0_3.id))
-> Seq Scan on public.matest2 matest0_3
Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id)
-> Index Scan using matest3i on public.matest3 matest0_4
Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id)
Settings: enable_bitmapscan = 'off', enable_parallel_append = 'off', enable_seqscan = 'off', optimizer = 'on'
Optimizer: Postgres query optimizer
(18 rows)
select * from matest0 order by 1-id;
id | name
----+--------
6 | Test 6
5 | Test 5
4 | Test 4
3 | Test 3
2 | Test 2
1 | Test 1
(6 rows)
explain (verbose, costs off) select min(1-id) from matest0;
QUERY PLAN
--------------------------------------------------------------------------------------------
Result
Output: $0
InitPlan 1 (returns $0) (slice1)
-> Limit
Output: ((1 - matest0.id))
-> Gather Motion 3:1 (slice2; segments: 3)
Output: ((1 - matest0.id))
Merge Key: ((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))
-> Index Only Scan using matest2_pkey on public.matest2 matest0_3
Output: matest0_3.id, (1 - matest0_3.id)
Filter: ((1 - matest0_3.id) IS NOT NULL)
-> 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)
Settings: enable_bitmapscan = 'off', enable_parallel_append = 'off', enable_seqscan = 'off', optimizer = 'on'
Optimizer: Postgres query optimizer
(29 rows)
select min(1-id) from matest0;
min
-----
-5
(1 row)
reset enable_seqscan;
reset enable_parallel_append;
reset enable_bitmapscan;
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
set enable_mergejoin=on;
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)
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: t1.b
-> Merge Append
Sort Key: t1.b
-> Index Scan using matest0i on matest0 t1_1
-> Index Scan using matest1i on matest1 t1_2
-> Materialize
-> Gather Motion 3:1 (slice2; segments: 3)
Merge Key: t2.b
-> 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)
Optimizer: Postgres query optimizer
(19 rows)
reset enable_nestloop;
drop table matest0 cascade;
NOTICE: drop cascades to table matest1
--
-- Test merge-append for UNION ALL append relations
--
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
-- GPDB: coerce the planner to choose Merge Append plans for the below queries.
-- In upstream, the Merge Append is cheaper, but in GPDB the Sort within each
-- segment only has to sort 1 / 3 of the data (with three segments), making
-- Sort + Append cheaper. Compensate by pretending that there are more rows in
-- the table.
begin;
set allow_system_table_mods = on;
update pg_class set reltuples = 100000 where oid = 'tenk1'::regclass;
-- 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
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: tenk1.thousand, tenk1.tenthous
-> Sort
Sort Key: tenk1.thousand, tenk1.tenthous
-> Append
-> Seq Scan on tenk1
-> Seq Scan on tenk1 tenk1_1
Optimizer: Pivotal Optimizer (GPORCA)
(8 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
------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: tenk1.thousand, tenk1.tenthous
-> Sort
Sort Key: tenk1.thousand, tenk1.tenthous
-> Append
-> Seq Scan on tenk1
-> Seq Scan on tenk1 tenk1_1
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
explain (costs off)
SELECT thousand, tenthous FROM tenk1
UNION ALL
SELECT thousand, random()::integer FROM tenk1
ORDER BY thousand, tenthous;
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: tenk1.thousand, tenk1.tenthous
-> Sort
Sort Key: tenk1.thousand, tenk1.tenthous
-> Append
-> Seq Scan on tenk1
-> Seq Scan on tenk1 tenk1_1
Optimizer: Pivotal Optimizer (GPORCA)
(8 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
---------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Append
-> Seq Scan on tenk1 a
-> Seq Scan on tenk1 b
Optimizer: Pivotal Optimizer (GPORCA) version 2.74.0
(7 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
---------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Append
-> Seq Scan on tenk1 a
-> Seq Scan on tenk1 b
Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
(7 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
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: a.thousand, a.tenthous
-> Sort
Sort Key: a.thousand, a.tenthous
-> Append
-> Seq Scan on tenk1 a
-> Seq Scan on tenk1 b
Optimizer: Pivotal Optimizer (GPORCA) version 2.74.0
(8 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
Optimizer: Postgres query optimizer
(12 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;
rollback;
--
-- 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
----
2
1
(3 rows)
select * from cnullparent where f1 = 2;
f1
----
2
(1 row)
drop table cnullparent cascade;
NOTICE: drop cascades to table cnullchild
--
-- Check use of temporary tables with inheritance trees
--
create table inh_perm_parent (a1 int);
create temp table inh_temp_parent (a1 int);
create temp table inh_temp_child () inherits (inh_perm_parent); -- ok
create table inh_perm_child () inherits (inh_temp_parent); -- error
ERROR: cannot inherit from temporary relation "inh_temp_parent"
create temp table inh_temp_child_2 () inherits (inh_temp_parent); -- ok
insert into inh_perm_parent values (1);
insert into inh_temp_parent values (2);
insert into inh_temp_child values (3);
insert into inh_temp_child_2 values (4);
select tableoid::regclass, a1 from inh_perm_parent;
tableoid | a1
-----------------+----
inh_perm_parent | 1
inh_temp_child | 3
(2 rows)
select tableoid::regclass, a1 from inh_temp_parent;
tableoid | a1
------------------+----
inh_temp_parent | 2
inh_temp_child_2 | 4
(2 rows)
drop table inh_perm_parent cascade;
NOTICE: drop cascades to table inh_temp_child
drop table inh_temp_parent cascade;
NOTICE: drop cascades to table inh_temp_child_2
--
-- Check that constraint exclusion works correctly with partitions using
-- implicit constraints generated from the partition bound information.
--
create table list_parted (
a varchar
) partition by list (a);
create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
create table part_null_xy partition of list_parted for values in (null, 'xy');
explain (costs off) select * from list_parted;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 3 (out of 3)
Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)
explain (costs off) select * from list_parted where a is null;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 1 (out of 3)
Filter: (a IS NULL)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
explain (costs off) select * from list_parted where a is not null;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 3 (out of 3)
Filter: (NOT (a IS NULL))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
QUERY PLAN
----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 2 (out of 3)
Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
QUERY PLAN
---------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 1 (out of 3)
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
----------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Dynamic Seq Scan on list_parted
Number of partitions to scan: 1 (out of 3)
Filter: ((a)::text = 'ab'::text)
Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)
create table range_list_parted (
a int,
b char(2)
) partition by range (a);
create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
create table part_1_10_ab partition of part_1_10 for values in ('ab');
create table part_1_10_cd partition of part_1_10 for values in ('cd');
create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
create table part_10_20_ab partition of part_10_20 for values in ('ab');
create table part_10_20_cd partition of part_10_20 for values in ('cd');
create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b);
create table part_21_30_ab partition of part_21_30 for values in ('ab');
create table part_21_30_cd partition of part_21_30 for values in ('cd');
create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b);
create table part_40_inf_ab partition of part_40_inf for values in ('ab');
create table part_40_inf_cd partition of part_40_inf for values in ('cd');
create table part_40_inf_null partition of part_40_inf for values in (null);
explain (costs off) select * from range_list_parted;
QUERY PLAN
--------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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
Optimizer: Postgres query optimizer
(12 rows)
explain (costs off) select * from range_list_parted where a = 5;
QUERY PLAN
----------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> 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)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from range_list_parted where b = 'ab';
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
QUERY PLAN
-----------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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))
Optimizer: Postgres query optimizer
(9 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
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on part_40_inf_null range_list_parted
Filter: (b IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
QUERY PLAN
------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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))
Optimizer: Postgres query optimizer
(21 rows)
explain (costs off) select * from range_list_parted where a >= 30;
QUERY PLAN
--------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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)
Optimizer: Postgres query optimizer
(9 rows)
drop table list_parted;
drop table range_list_parted;
-- check that constraint exclusion is able to cope with the partition
-- constraint emitted for multi-column range partitioned tables
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
create table mcrparted_def partition of mcrparted default;
create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mcrparted0 mcrparted_1
Filter: (a = 0)
-> Seq Scan on mcrparted_def mcrparted_2
Filter: (a = 0)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def
QUERY PLAN
---------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> 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))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def
QUERY PLAN
---------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> 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))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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)
Optimizer: Postgres query optimizer
(17 rows)
explain (costs off) select * from mcrparted where a > -1; -- scans all partitions
QUERY PLAN
-------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> 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)
Optimizer: Postgres query optimizer
(17 rows)
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
QUERY PLAN
-----------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mcrparted4 mcrparted
Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
QUERY PLAN
-----------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> 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))
Optimizer: Postgres query optimizer
(11 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
---------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
-> Dynamic Seq Scan on parted_minmax
Number of partitions to scan: 1 (out of 1)
Filter: ((b)::text = '12345'::text)
Optimizer: Pivotal Optimizer (GPORCA)
(7 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
-------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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
Optimizer: Postgres query optimizer
(12 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
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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
Optimizer: Postgres query optimizer
(10 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
----------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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
Optimizer: Postgres query optimizer
(10 rows)
-- check that Append plan is used containing a MergeAppend for sub-partitions
-- that are unordered.
drop table mcrparted5;
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue) partition by list (a);
create table mcrparted5a partition of mcrparted5 for values in(20);
create table mcrparted5_def partition of mcrparted5 default;
explain (costs off) select * from mcrparted order by a, abs(b), c;
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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
Optimizer: Postgres query optimizer
(13 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
---------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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
Optimizer: Postgres query optimizer
(10 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
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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)
Optimizer: Postgres query optimizer
(12 rows)
create table mclparted (a int) partition by list(a);
create table mclparted1 partition of mclparted for values in(1);
create table mclparted2 partition of mclparted for values in(2);
create index on mclparted (a);
-- Ensure an Append is used for a list partition with an order by.
explain (costs off) select * from mclparted order by a;
QUERY PLAN
------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: a
-> Sort
Sort Key: a
-> Dynamic Seq Scan on mclparted
Number of partitions to scan: 2 (out of 2)
Optimizer: Pivotal Optimizer (GPORCA)
(7 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
------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: a
-> Sort
Sort Key: a
-> Dynamic Seq Scan on mclparted
Number of partitions to scan: 4 (out of 4)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
drop table mclparted;
-- Ensure subplans which don't have a path with the correct pathkeys get
-- sorted correctly.
drop index mcrparted_a_abs_c_idx;
create index on mcrparted1 (a, abs(b), c);
create index on mcrparted2 (a, abs(b), c);
create index on mcrparted3 (a, abs(b), c);
create index on mcrparted4 (a, abs(b), c);
explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit
-> Gather Motion 3:1 (slice1; segments: 3)
Merge Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
-> 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)
Optimizer: Postgres query optimizer
(16 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
-------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: (abs(mcrparted.b)), mcrparted.c
-> 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)
Optimizer: Postgres query optimizer
(8 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
------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: b
-> Sort
Sort Key: b
-> Dynamic Seq Scan on bool_lp
Number of partitions to scan: 2 (out of 2)
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
drop table bool_lp;
-- Ensure const bool quals can be properly detected as redundant
create table bool_rp (b bool, a int) partition by range(b,a);
create table bool_rp_false_1k partition of bool_rp for values from (false,0) to (false,1000);
create table bool_rp_true_1k partition of bool_rp for values from (true,0) to (true,1000);
create table bool_rp_false_2k partition of bool_rp for values from (false,1000) to (false,2000);
create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000);
create index on bool_rp (b,a);
explain (costs off) select * from bool_rp where b = true order by b,a;
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: bool_rp.b, bool_rp.a
-> 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)
Optimizer: Postgres query optimizer
(8 rows)
explain (costs off) select * from bool_rp where b = false order by b,a;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: bool_rp.b, bool_rp.a
-> 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)
Optimizer: Postgres query optimizer
(8 rows)
-- GPDB: force the planner to choose same plan as in upstream
set enable_seqscan=off;
set enable_bitmapscan=off;
explain (costs off) select * from bool_rp where b = true order by a;
QUERY PLAN
------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: bool_rp.a
-> Sort
Sort Key: bool_rp.a
-> 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)
Optimizer: Postgres query optimizer
(10 rows)
explain (costs off) select * from bool_rp where b = false order by a;
QUERY PLAN
--------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: bool_rp.a
-> Sort
Sort Key: bool_rp.a
-> 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)
Optimizer: Postgres query optimizer
(10 rows)
reset enable_seqscan;
reset enable_bitmapscan;
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
----------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: range_parted.a, range_parted.b, range_parted.c
-> 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
Optimizer: Postgres query optimizer
(6 rows)
explain (costs off) select * from range_parted order by a desc,b desc,c desc;
QUERY PLAN
-------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Merge Key: range_parted.a, range_parted.b, range_parted.c
-> 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
Optimizer: Postgres query optimizer
(6 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) distributed by (a);
create table permtest_grandchild (c text, b text, a int) distributed by (a);
alter table permtest_child attach partition permtest_grandchild for values in ('a');
alter table permtest_parent attach partition permtest_child for values in (1);
create index on permtest_parent (left(c, 3));
insert into permtest_parent
select 1, 'a', left(md5(i::text), 5) from generate_series(0, 100) i;
analyze permtest_parent;
create role regress_no_child_access;
revoke all on permtest_grandchild from regress_no_child_access;
NOTICE: no privileges could be revoked
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
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (p2.a = p1.a)
-> Seq Scan on permtest_grandchild p2
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on permtest_grandchild p1
Filter: (c ~ 'a1$'::text)
Optimizer: Postgres query optimizer
(9 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
----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (p2.a = p1.a)
-> Seq Scan on permtest_grandchild p2
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on permtest_grandchild p1
Filter: ("left"(c, 3) ~ 'a1$'::text)
Optimizer: Postgres query optimizer
(9 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
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (p2.a = p1.a)
-> Seq Scan on permtest_grandchild p2
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on permtest_grandchild p1
Filter: (c ~ 'a1$'::text)
Optimizer: Postgres query optimizer
(9 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
----------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Join
Hash Cond: (p2.a = p1.a)
-> Seq Scan on permtest_grandchild p2
-> Hash
-> Partition Selector (selector id: $0)
-> Seq Scan on permtest_grandchild p1
Filter: ("left"(c, 3) ~ 'a1$'::text)
Optimizer: Postgres query optimizer
(9 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)
) distributed by (partid);
ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
-- insert without child check constraint error
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
-- insert with child check constraint error
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
DETAIL: Failing row contains (0, 1, 10).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
DETAIL: Failing row contains (10, 1, 10).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
DETAIL: Failing row contains (20, 1, 10).
-- insert with child not null constraint error
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
ERROR: null value in column "data" of relation "errtst_child_fastdef" violates not-null constraint
DETAIL: Failing row contains (0, 1, null).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
ERROR: null value in column "data" of relation "errtst_child_plaindef" violates not-null constraint
DETAIL: Failing row contains (10, 1, null).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
ERROR: null value in column "data" of relation "errtst_child_reorder" violates not-null constraint
DETAIL: Failing row contains (20, 1, null).
-- insert with shared check constraint error
INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
DETAIL: Failing row contains (0, 5, 5).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
DETAIL: Failing row contains (10, 5, 5).
INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
DETAIL: Failing row contains (20, 5, 5).
-- within partition update without child check constraint violation
BEGIN;
UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
ROLLBACK;
-- within partition update with child check constraint violation
UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
DETAIL: Failing row contains (0, 1, 15).
UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
DETAIL: Failing row contains (10, 1, 15).
UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
DETAIL: Failing row contains (20, 1, 15).
-- direct leaf partition update, without partition id violation
BEGIN;
UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
ROLLBACK;
-- direct leaf partition update, with partition id violation
UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
DETAIL: Failing row contains (10, 1, 5).
UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
DETAIL: Failing row contains (20, 1, 5).
UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
ERROR: new row for relation "errtst_child_reorder" violates partition constraint
DETAIL: Failing row contains (5, 1, 30).
-- partition move, without child check constraint violation
BEGIN;
UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
ROLLBACK;
-- partition move, with child check constraint violation
UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
DETAIL: Failing row contains (10, 1, 15).
UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
DETAIL: Failing row contains (20, 1, 15).
UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
DETAIL: Failing row contains (0, 1, 15).
-- partition move, without target partition
UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
ERROR: no partition of relation "errtst_parent" found for row
DETAIL: Partition key of the failing row contains (partid) = (30).
DROP TABLE errtst_parent;