inherit_optimizer.out

--
-- Test inheritance features
--
CREATE TABLE a (dummy serial, aa TEXT);
CREATE TABLE b (bb TEXT) INHERITS (a);
CREATE TABLE c (cc TEXT) INHERITS (a);
CREATE TABLE d (dd TEXT) INHERITS (b,c,a);
NOTICE:  merging multiple inherited definitions of column "dummy"
NOTICE:  merging multiple inherited definitions of column "aa"
NOTICE:  merging multiple inherited definitions of column "dummy"
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 | dummy |    aa    
---------+-------+----------
 a       |     1 | aaa
 a       |     2 | aaaa
 a       |     3 | aaaaa
 a       |     4 | aaaaaa
 a       |     5 | aaaaaaa
 a       |     6 | aaaaaaaa
 b       |     7 | bbb
 b       |     8 | bbbb
 b       |     9 | bbbbb
 b       |    10 | bbbbbb
 b       |    11 | bbbbbbb
 b       |    12 | bbbbbbbb
 c       |    13 | ccc
 c       |    14 | cccc
 c       |    15 | ccccc
 c       |    16 | cccccc
 c       |    17 | ccccccc
 c       |    18 | cccccccc
 d       |    19 | ddd
 d       |    20 | dddd
 d       |    21 | ddddd
 d       |    22 | dddddd
 d       |    23 | ddddddd
 d       |    24 | dddddddd
(24 rows)

SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb 
---------+-------+----------+----
 b       |     7 | bbb      | 
 b       |     8 | bbbb     | 
 b       |     9 | bbbbb    | 
 b       |    10 | bbbbbb   | 
 b       |    11 | bbbbbbb  | 
 b       |    12 | bbbbbbbb | 
 d       |    19 | ddd      | 
 d       |    20 | dddd     | 
 d       |    21 | ddddd    | 
 d       |    22 | dddddd   | 
 d       |    23 | ddddddd  | 
 d       |    24 | dddddddd | 
(12 rows)

SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
 d       |    19 | ddd      | 
 d       |    20 | dddd     | 
 d       |    21 | ddddd    | 
 d       |    22 | dddddd   | 
 d       |    23 | ddddddd  | 
 d       |    24 | dddddddd | 
(12 rows)

SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb | cc | dd 
---------+-------+----------+----+----+----
 d       |    19 | ddd      |    |    | 
 d       |    20 | dddd     |    |    | 
 d       |    21 | ddddd    |    |    | 
 d       |    22 | dddddd   |    |    | 
 d       |    23 | ddddddd  |    |    | 
 d       |    24 | dddddddd |    |    | 
(6 rows)

SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy |    aa    
---------+-------+----------
 a       |     1 | aaa
 a       |     2 | aaaa
 a       |     3 | aaaaa
 a       |     4 | aaaaaa
 a       |     5 | aaaaaaa
 a       |     6 | aaaaaaaa
(6 rows)

SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb 
---------+-------+----------+----
 b       |     7 | bbb      | 
 b       |     8 | bbbb     | 
 b       |     9 | bbbbb    | 
 b       |    10 | bbbbbb   | 
 b       |    11 | bbbbbbb  | 
 b       |    12 | bbbbbbbb | 
(6 rows)

SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
(6 rows)

SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb | cc | dd 
---------+-------+----------+----+----+----
 d       |    19 | ddd      |    |    | 
 d       |    20 | dddd     |    |    | 
 d       |    21 | ddddd    |    |    | 
 d       |    22 | dddddd   |    |    | 
 d       |    23 | ddddddd  |    |    | 
 d       |    24 | 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 | dummy |    aa    
---------+-------+----------
 a       |     1 | zzzzzz
 a       |     2 | zzzz
 a       |     3 | zzzzz
 a       |     4 | zzzzzz
 a       |     5 | zzzzzz
 a       |     6 | zzzzzz
 b       |     7 | bbb
 b       |     8 | bbbb
 b       |     9 | bbbbb
 b       |    10 | bbbbbb
 b       |    11 | bbbbbbb
 b       |    12 | bbbbbbbb
 c       |    13 | ccc
 c       |    14 | cccc
 c       |    15 | ccccc
 c       |    16 | cccccc
 c       |    17 | ccccccc
 c       |    18 | cccccccc
 d       |    19 | ddd
 d       |    20 | dddd
 d       |    21 | ddddd
 d       |    22 | dddddd
 d       |    23 | ddddddd
 d       |    24 | dddddddd
(24 rows)

SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb 
---------+-------+----------+----
 b       |     7 | bbb      | 
 b       |     8 | bbbb     | 
 b       |     9 | bbbbb    | 
 b       |    10 | bbbbbb   | 
 b       |    11 | bbbbbbb  | 
 b       |    12 | bbbbbbbb | 
 d       |    19 | ddd      | 
 d       |    20 | dddd     | 
 d       |    21 | ddddd    | 
 d       |    22 | dddddd   | 
 d       |    23 | ddddddd  | 
 d       |    24 | dddddddd | 
(12 rows)

SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
 d       |    19 | ddd      | 
 d       |    20 | dddd     | 
 d       |    21 | ddddd    | 
 d       |    22 | dddddd   | 
 d       |    23 | ddddddd  | 
 d       |    24 | dddddddd | 
(12 rows)

SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb | cc | dd 
---------+-------+----------+----+----+----
 d       |    19 | ddd      |    |    | 
 d       |    20 | dddd     |    |    | 
 d       |    21 | ddddd    |    |    | 
 d       |    22 | dddddd   |    |    | 
 d       |    23 | ddddddd  |    |    | 
 d       |    24 | dddddddd |    |    | 
(6 rows)

SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy |   aa   
---------+-------+--------
 a       |     1 | zzzzzz
 a       |     2 | zzzz
 a       |     3 | zzzzz
 a       |     4 | zzzzzz
 a       |     5 | zzzzzz
 a       |     6 | zzzzzz
(6 rows)

SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb 
---------+-------+----------+----
 b       |     7 | bbb      | 
 b       |     8 | bbbb     | 
 b       |     9 | bbbbb    | 
 b       |    10 | bbbbbb   | 
 b       |    11 | bbbbbbb  | 
 b       |    12 | bbbbbbbb | 
(6 rows)

SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
(6 rows)

SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy |    aa    | bb | cc | dd 
---------+-------+----------+----+----+----
 d       |    19 | ddd      |    |    | 
 d       |    20 | dddd     |    |    | 
 d       |    21 | ddddd    |    |    | 
 d       |    22 | dddddd   |    |    | 
 d       |    23 | ddddddd  |    |    | 
 d       |    24 | dddddddd |    |    | 
(6 rows)

UPDATE b SET aa='new';
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy |    aa    
---------+-------+----------
 a       |     1 | zzzzzz
 a       |     2 | zzzz
 a       |     3 | zzzzz
 a       |     4 | zzzzzz
 a       |     5 | zzzzzz
 a       |     6 | zzzzzz
 b       |     7 | new
 b       |     8 | new
 b       |     9 | new
 b       |    10 | new
 b       |    11 | new
 b       |    12 | new
 c       |    13 | ccc
 c       |    14 | cccc
 c       |    15 | ccccc
 c       |    16 | cccccc
 c       |    17 | ccccccc
 c       |    18 | cccccccc
 d       |    19 | new
 d       |    20 | new
 d       |    21 | new
 d       |    22 | new
 d       |    23 | new
 d       |    24 | new
(24 rows)

SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa  | bb 
---------+-------+-----+----
 b       |     7 | new | 
 b       |     8 | new | 
 b       |     9 | new | 
 b       |    10 | new | 
 b       |    11 | new | 
 b       |    12 | new | 
 d       |    19 | new | 
 d       |    20 | new | 
 d       |    21 | new | 
 d       |    22 | new | 
 d       |    23 | new | 
 d       |    24 | new | 
(12 rows)

SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
 d       |    19 | new      | 
 d       |    20 | new      | 
 d       |    21 | new      | 
 d       |    22 | new      | 
 d       |    23 | new      | 
 d       |    24 | new      | 
(12 rows)

SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | aa  | bb | cc | dd 
---------+-------+-----+----+----+----
 d       |    19 | new |    |    | 
 d       |    20 | new |    |    | 
 d       |    21 | new |    |    | 
 d       |    22 | new |    |    | 
 d       |    23 | new |    |    | 
 d       |    24 | new |    |    | 
(6 rows)

SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy |   aa   
---------+-------+--------
 a       |     1 | zzzzzz
 a       |     2 | zzzz
 a       |     3 | zzzzz
 a       |     4 | zzzzzz
 a       |     5 | zzzzzz
 a       |     6 | zzzzzz
(6 rows)

SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa  | bb 
---------+-------+-----+----
 b       |     7 | new | 
 b       |     8 | new | 
 b       |     9 | new | 
 b       |    10 | new | 
 b       |    11 | new | 
 b       |    12 | new | 
(6 rows)

SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy |    aa    | cc 
---------+-------+----------+----
 c       |    13 | ccc      | 
 c       |    14 | cccc     | 
 c       |    15 | ccccc    | 
 c       |    16 | cccccc   | 
 c       |    17 | ccccccc  | 
 c       |    18 | cccccccc | 
(6 rows)

SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | aa  | bb | cc | dd 
---------+-------+-----+----+----+----
 d       |    19 | new |    |    | 
 d       |    20 | new |    |    | 
 d       |    21 | new |    |    | 
 d       |    22 | new |    |    | 
 d       |    23 | new |    |    | 
 d       |    24 | 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 | dummy | aa  
---------+-------+-----
 a       |     1 | new
 a       |     2 | new
 a       |     3 | new
 a       |     4 | new
 a       |     5 | new
 a       |     6 | new
 b       |     7 | new
 b       |     8 | new
 b       |     9 | new
 b       |    10 | new
 b       |    11 | new
 b       |    12 | new
 d       |    19 | new
 d       |    20 | new
 d       |    21 | new
 d       |    22 | new
 d       |    23 | new
 d       |    24 | new
(18 rows)

SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa  | bb 
---------+-------+-----+----
 b       |     7 | new | 
 b       |     8 | new | 
 b       |     9 | new | 
 b       |    10 | new | 
 b       |    11 | new | 
 b       |    12 | new | 
 d       |    19 | new | 
 d       |    20 | new | 
 d       |    21 | new | 
 d       |    22 | new | 
 d       |    23 | new | 
 d       |    24 | new | 
(12 rows)

SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy | aa  | cc 
---------+-------+-----+----
 d       |    19 | new | 
 d       |    20 | new | 
 d       |    21 | new | 
 d       |    22 | new | 
 d       |    23 | new | 
 d       |    24 | new | 
(6 rows)

SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | aa  | bb | cc | dd 
---------+-------+-----+----+----+----
 d       |    19 | new |    |    | 
 d       |    20 | new |    |    | 
 d       |    21 | new |    |    | 
 d       |    22 | new |    |    | 
 d       |    23 | new |    |    | 
 d       |    24 | new |    |    | 
(6 rows)

SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy | aa  
---------+-------+-----
 a       |     1 | new
 a       |     2 | new
 a       |     3 | new
 a       |     4 | new
 a       |     5 | new
 a       |     6 | new
(6 rows)

SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa  | bb 
---------+-------+-----+----
 b       |     7 | new | 
 b       |     8 | new | 
 b       |     9 | new | 
 b       |    10 | new | 
 b       |    11 | new | 
 b       |    12 | new | 
(6 rows)

SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy | aa | cc 
---------+-------+----+----
(0 rows)

SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | aa  | bb | cc | dd 
---------+-------+-----+----+----+----
 d       |    19 | new |    |    | 
 d       |    20 | new |    |    | 
 d       |    21 | new |    |    | 
 d       |    22 | new |    |    | 
 d       |    23 | new |    |    | 
 d       |    24 | new |    |    | 
(6 rows)

DELETE FROM a;
SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy | aa 
---------+-------+----
(0 rows)

SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa | bb 
---------+-------+----+----
(0 rows)

SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy | aa | cc 
---------+-------+----+----
(0 rows)

SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | aa | bb | cc | dd 
---------+-------+----+----+----+----
(0 rows)

SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
 relname | dummy | aa 
---------+-------+----
(0 rows)

SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
 relname | dummy | aa | bb 
---------+-------+----+----
(0 rows)

SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
 relname | dummy | aa | cc 
---------+-------+----+----
(0 rows)

SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
 relname | dummy | 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 "dummy" violates not-null constraint  (seg1 172.17.0.2:25433 pid=366731)
DETAIL:  Failing row contains (null, text, null).
-- 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)

/* 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');
ERROR:  invalid input syntax for integer: "test"
LINE 1: 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;
 dummy | aa | bb | cc | dd 
-------+----+----+----+----
(0 rows)

-- check that oid column is handled properly during alter table inherit
create table oid_parent (a int) with oids;
NOTICE:  OIDS=TRUE is not recommended for user-created tables
HINT:  Use OIDS=FALSE to prevent wrap-around of the OID counter.
create table oid_child () inherits (oid_parent);
NOTICE:  OIDS=TRUE is not recommended for user-created tables
HINT:  Use OIDS=FALSE to prevent wrap-around of the OID counter.
select attinhcount, attislocal from pg_attribute
  where attrelid = 'oid_child'::regclass and attname = 'oid';
 attinhcount | attislocal 
-------------+------------
           1 | f
(1 row)

drop table oid_child;
create table oid_child (a int) without oids;
alter table oid_child inherit oid_parent;  -- fail
ERROR:  table "oid_child" without OIDs cannot inherit from table "oid_parent" with OIDs
alter table oid_child set with oids;
NOTICE:  OIDS=TRUE is not recommended for user-created tables
HINT:  Use OIDS=FALSE to prevent wrap-around of the OID counter.
select attinhcount, attislocal from pg_attribute
  where attrelid = 'oid_child'::regclass and attname = 'oid';
 attinhcount | attislocal 
-------------+------------
           0 | t
(1 row)

alter table oid_child inherit oid_parent;
select attinhcount, attislocal from pg_attribute
  where attrelid = 'oid_child'::regclass and attname = 'oid';
 attinhcount | attislocal 
-------------+------------
           1 | t
(1 row)

alter table oid_child set without oids;  -- fail
ERROR:  cannot drop inherited column "oid"
alter table oid_parent set without oids;
select attinhcount, attislocal from pg_attribute
  where attrelid = 'oid_child'::regclass and attname = 'oid';
 attinhcount | attislocal 
-------------+------------
           0 | t
(1 row)

alter table oid_child set without oids;
select attinhcount, attislocal from pg_attribute
  where attrelid = 'oid_child'::regclass and attname = 'oid';
 attinhcount | attislocal 
-------------+------------
(0 rows)

drop table oid_parent cascade;
NOTICE:  drop cascades to table oid_child
-- 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   | Modifiers 
--------+---------+-----------
 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   | Modifiers 
--------+---------+-----------
 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);
insert into derived (i) values (0);
select derived::base from derived;
 derived 
---------
 (0)
(1 row)

drop table derived;
drop table base;
create table p1(ff1 int);
create table p2(f1 text);
create function p2text(p2) returns text as 'select $1.f1' language sql;
create table c1(f3 int) inherits(p1,p2);
insert into c1 values(123456789, 'hi', 42);
select p2text(c1.*) from c1;
 p2text 
--------
 hi
(1 row)

drop function p2text(p2);
drop table c1;
drop table p2;
drop table p1;
CREATE TABLE ac (aa TEXT);
alter table ac add constraint ac_check check (aa is not null);
CREATE TABLE bc (bb TEXT) INHERITS (ac);
select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
 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, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
 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" is violated by some row
alter table p2 add check (f2>0);  -- check it without a name, too
ERROR:  check constraint "p2_f2_check" 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   | Modifiers 
--------+---------+-----------
 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   | Modifiers 
--------+---------+-----------
 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   | Modifiers 
--------+---------+-----------
 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       | Modifiers 
--------+------------------+-----------
 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       | Modifiers 
--------+------------------+-----------
 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   | Modifiers | 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   | Modifiers | 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   | Modifiers | 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] contraints
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        | Modifiers | 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        | Modifiers | 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        | Modifiers | 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;
-- start_ignore
-- GPDB does not support exclusion constraints, so no need to run this test
CREATE TABLE test_ex_constraints (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);
CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
\d+ test_ex_constraints
                 Table "public.test_ex_constraints"
 Column |  Type  | Modifiers | Storage | Stats target | Description 
--------+--------+-----------+---------+--------------+-------------
 c      | circle |           | plain   |              | 
Indexes:
    "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
Child tables: test_ex_constraints_inh
Distributed randomly

ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
\d+ test_ex_constraints
                 Table "public.test_ex_constraints"
 Column |  Type  | Modifiers | Storage | Stats target | Description 
--------+--------+-----------+---------+--------------+-------------
 c      | circle |           | plain   |              | 
Child tables: test_ex_constraints_inh
Distributed randomly

\d+ test_ex_constraints_inh
               Table "public.test_ex_constraints_inh"
 Column |  Type  | Modifiers | Storage | Stats target | Description 
--------+--------+-----------+---------+--------------+-------------
 c      | circle |           | plain   |              | 
Inherits: test_ex_constraints
Distributed randomly

DROP TABLE test_ex_constraints_inh;
DROP TABLE test_ex_constraints;
-- end_ignore
-- Test non-inheritable foreign key contraints
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   | Modifiers | 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   | Modifiers | 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   | Modifiers | 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   | Modifiers | 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 that parent and child CHECK constraints can be created in either order
create table p1(f1 int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database 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 table p1_c1() inherits(p1);
NOTICE:  table has parent, setting distribution columns to match parent table
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
NOTICE:  merging constraint "inh_check_constraint1" with inherited definition  (seg0 127.0.0.1:25432 pid=11727)
NOTICE:  merging constraint "inh_check_constraint1" with inherited definition  (seg1 127.0.0.1:25433 pid=11728)
NOTICE:  merging constraint "inh_check_constraint1" with inherited definition  (seg2 127.0.0.1:25434 pid=11729)
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
NOTICE:  merging constraint "inh_check_constraint2" with inherited definition  (seg0 127.0.0.1:25432 pid=11727)
NOTICE:  merging constraint "inh_check_constraint2" with inherited definition  (seg2 127.0.0.1:25434 pid=11729)
NOTICE:  merging constraint "inh_check_constraint2" with inherited definition  (seg1 127.0.0.1:25433 pid=11728)
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);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database 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 table invalid_check_con_child() inherits(invalid_check_con);
NOTICE:  table has parent, setting distribution columns to match parent table
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
NOTICE:  merging constraint "inh_check_constraint" with inherited definition  (seg1 127.0.0.1:25433 pid=11728)
NOTICE:  merging constraint "inh_check_constraint" with inherited definition  (seg2 127.0.0.1:25434 pid=11729)
NOTICE:  merging constraint "inh_check_constraint" with inherited definition  (seg0 127.0.0.1:25432 pid=11727)
insert into invalid_check_con values(0); -- fail
ERROR:  new row for relation "invalid_check_con" violates check constraint "inh_check_constraint"  (seg0 127.0.0.1:25432 pid=11727)
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"  (seg0 127.0.0.1:25432 pid=11727)
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  (slice3; segments: 3)
   ->  Hash Join
         Hash Cond: (patest0.id = int4_tbl.f1)
         ->  Append
               ->  Index Scan using patest0i on patest0
               ->  Index Scan using patest1i on patest1
               ->  Index Scan using patest2i on patest2
         ->  Hash
               ->  Redistribute Motion 1:3  (slice2; segments: 1)
                     Hash Key: int4_tbl.f1
                     ->  Limit
                           ->  Gather Motion 3:1  (slice1; segments: 3)
                                 ->  Limit
                                       ->  Seq Scan on int4_tbl
                                             Filter: ((f1 < 10) AND (f1 > (-10)))
 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  (slice3; segments: 3)
   ->  Hash Join
         Hash Cond: (patest0.id = int4_tbl.f1)
         ->  Append
               ->  Index Scan using patest0i on patest0
               ->  Index Scan using patest1i on patest1
               ->  Seq Scan on patest2
         ->  Hash
               ->  Redistribute Motion 1:3  (slice2; segments: 1)
                     Hash Key: int4_tbl.f1
                     ->  Limit
                           ->  Gather Motion 3:1  (slice1; segments: 3)
                                 ->  Limit
                                       ->  Seq Scan on int4_tbl
                                             Filter: ((f1 < 10) AND (f1 > (-10)))
 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
                           Output: matest0.id, matest0.name
                     ->  Seq Scan on public.matest1
                           Output: matest1.id, matest1.name
                     ->  Seq Scan on public.matest2
                           Output: matest2.id, matest2.name
                     ->  Seq Scan on public.matest3
                           Output: matest3.id, matest3.name
 Optimizer: Postgres query optimizer
 Settings: enable_indexscan=off, optimizer=on
(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                     
----------------------------------------------------
 Aggregate
   Output: min((min((1 - matest0.id))))
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Output: (min((1 - matest0.id)))
         ->  Aggregate
               Output: min((1 - matest0.id))
               ->  Append
                     ->  Seq Scan on public.matest0
                           Output: matest0.id
                     ->  Seq Scan on public.matest1
                           Output: matest1.id
                     ->  Seq Scan on public.matest2
                           Output: matest2.id
                     ->  Seq Scan on public.matest3
                           Output: matest3.id
 Optimizer: Postgres query optimizer
 Settings: enable_indexscan=off, optimizer=off
(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
-- 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
               Output: matest0.id, matest0.name, (1 - matest0.id)
         ->  Index Scan using matest1i on public.matest1
               Output: matest1.id, matest1.name, (1 - matest1.id)
         ->  Sort
               Output: matest2.id, matest2.name, ((1 - matest2.id))
               Sort Key: ((1 - matest2.id))
               ->  Seq Scan on public.matest2
                     Output: matest2.id, matest2.name, (1 - matest2.id)
         ->  Index Scan using matest3i on public.matest3
               Output: matest3.id, matest3.name, (1 - matest3.id)
 Optimizer: Postgres query optimizer
 Settings: enable_bitmapscan=off, enable_seqscan=off, optimizer=on
(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)  (slice2)
     ->  Limit
           Output: ((1 - matest0.id))
           ->  Gather Motion 3:1  (slice1; 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
                                   Output: matest0.id, (1 - matest0.id)
                                   Index Cond: ((1 - matest0.id) IS NOT NULL)
                             ->  Index Scan using matest1i on public.matest1
                                   Output: matest1.id, (1 - matest1.id)
                                   Index Cond: ((1 - matest1.id) IS NOT NULL)
                             ->  Sort
                                   Output: matest2.id, ((1 - matest2.id))
                                   Sort Key: ((1 - matest2.id))
                                   ->  Index Only Scan using matest2_pkey on public.matest2
                                         Output: matest2.id, (1 - matest2.id)
                                         Filter: ((1 - matest2.id) IS NOT NULL)
                             ->  Index Scan using matest3i on public.matest3
                                   Output: matest3.id, (1 - matest3.id)
                                   Index Cond: ((1 - matest3.id) IS NOT NULL)
 Optimizer: Postgres query optimizer
 Settings: enable_bitmapscan=off, enable_seqscan=off, optimizer=off
(29 rows)

select min(1-id) from matest0;
 min 
-----
  -5
(1 row)

reset enable_seqscan;
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
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
   ->  Gather Motion 3:1  (slice2; segments: 3)
         Merge Key: t1.b
         ->  Limit
               ->  Sort
                     Sort Key: t1.b
                     ->  Hash Join
                           Hash Cond: (t1.b = t2.b)
                           ->  Append
                                 ->  Seq Scan on matest0 t1
                                 ->  Seq Scan on matest1 t1_1
                           ->  Hash
                                 ->  Broadcast Motion 3:3  (slice1; segments: 3)
                                       ->  Append
                                             ->  Seq Scan on matest0 t2
                                                   Filter: (c = d)
                                             ->  Seq Scan on matest1 t2_1
                                                   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;
-- 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: PQO version 2.74.0
(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
               ->  Result
                     ->  Seq Scan on tenk1
               ->  Result
                     ->  Seq Scan on tenk1 tenk1_1
 Optimizer: PQO version 2.74.0
(10 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
               ->  Result
                     ->  Seq Scan on tenk1 tenk1_1
 Optimizer: PQO version 2.74.0
(9 rows)

-- Check min/max aggregate optimization
-- GPDB: simple union all pull up is disabled in pull_up_subqueries(), so the
-- min/max optimization doesn't kick in.
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                     
---------------------------------------------------
 Aggregate
   ->  Gather Motion 3:1  (slice1; segments: 3)
         ->  Aggregate
               ->  Append
                     ->  Seq Scan on tenk1
                     ->  Seq Scan on tenk1 tenk1_1
 Optimizer: PQO 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                     
---------------------------------------------------
 Aggregate
   ->  Gather Motion 3:1  (slice1; segments: 3)
         ->  Aggregate
               ->  Append
                     ->  Result
                           ->  Seq Scan on tenk1
                     ->  Seq Scan on tenk1 tenk1_1
 Optimizer: PQO version 2.74.0
(8 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: tenk1.thousand, tenk1.tenthous
   ->  Sort
         Sort Key: tenk1.thousand, tenk1.tenthous
         ->  Append
               ->  Seq Scan on tenk1
               ->  Seq Scan on tenk1 tenk1_1
 Optimizer: PQO 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
           ->  Sort
                 Sort Key: ((d.d + g.i))
                 ->  Append
                       ->  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
(13 rows)

SELECT
    ARRAY(SELECT f.i FROM (
        (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
        UNION ALL
        (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
    ) f(i)
    ORDER BY f.i LIMIT 10)
FROM generate_series(1, 3) g(i);
            array             
------------------------------
 {1,5,6,8,11,11,14,16,17,20}
 {2,6,7,9,12,12,15,17,18,21}
 {3,7,8,10,13,13,16,18,19,22}
(3 rows)

reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
--
-- Check handling of a constant-null CHECK constraint
--
create table cnullparent (f1 int);
create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent);
insert into cnullchild values(1);
insert into cnullchild values(2);
insert into cnullchild values(null);
select * from cnullparent;
 f1 
----
  2
   
  1
(3 rows)

select * from cnullparent where f1 = 2;
 f1 
----
  2
(1 row)

drop table cnullparent cascade;
NOTICE:  drop cascades to table cnullchild