-- #6842 / BUG #19507: auto-named constraints on a partition tree that spans schemas.
-- Each scenario uses its own pair of schemas (sN = parent, pN = child).
-- After the statement under test, the constraints of the whole tree are listed.
\set VERBOSITY terse
\pset format unaligned
\pset tuples_only on

create function tree(root regclass) returns table (l text) language sql as $$
  select format('%s | %s | %s | local=%s inh=%s', c.conrelid::regclass, c.conname, c.contype, c.conislocal, c.coninhcount)
  from pg_constraint c
  where c.conrelid in (select root union all select relid from pg_partition_tree(root)
                       union all select inhrelid from pg_inherits where inhparent = root)
  order by c.conrelid::regclass::text, c.conname $$;

\echo --- A1 report: SET NOT NULL, child has a CHECK named t_a_not_null
create schema s1; create schema p1;
create table s1.t(a int) partition by range (a);
create table p1.t_1_10 partition of s1.t for values from (1) to (10);
alter table p1.t_1_10 add constraint t_a_not_null check (a is not null);
\echo => TEST
alter table s1.t alter column a set not null;
select tree('s1.t');

\echo --- A2 report: ADD CHECK, child has a different CHECK named t_a_check
create schema s2; create schema p2;
create table s2.t(a int) partition by range (a);
create table p2.t_1_10 partition of s2.t for values from (1) to (10);
alter table p2.t_1_10 add constraint t_a_check check (a > 100);
\echo => TEST
alter table s2.t add check (a > 0);
select tree('s2.t');

\echo --- A3 report: ADD NOT NULL
create schema s3; create schema p3;
create table s3.t(a int) partition by range (a);
create table p3.t_1_10 partition of s3.t for values from (1) to (10);
alter table p3.t_1_10 add constraint t_a_not_null check (a is not null);
\echo => TEST
alter table s3.t add not null a;
select tree('s3.t');

\echo --- A4 report: nobody named anything, the partition is also called t
create schema s4; create schema p4;
create table s4.t(a int) partition by range (a);
create table p4.t partition of s4.t for values from (1) to (10);
alter table p4.t add check (a > 1);
\echo => TEST
alter table s4.t add check (a > 0);
select tree('s4.t');

\echo --- B1 plain inheritance (not partitioning), CHECK
create schema s5; create schema p5;
create table s5.t(a int);
create table p5.c() inherits (s5.t);
alter table p5.c add constraint t_a_check check (a > 100);
\echo => TEST
alter table s5.t add check (a > 0);
select tree('s5.t');

\echo --- B2 plain inheritance, SET NOT NULL
create schema s6; create schema p6;
create table s6.t(a int);
create table p6.c() inherits (s6.t);
alter table p6.c add constraint t_a_not_null check (a is not null);
\echo => TEST
alter table s6.t alter column a set not null;
select tree('s6.t');

\echo --- B3 three levels, the conflict is on the leaf, in a third schema
create schema s7; create schema p7; create schema q7;
create table s7.t(a int) partition by range (a);
create table p7.mid partition of s7.t for values from (1) to (100) partition by range (a);
create table q7.leaf partition of p7.mid for values from (1) to (10);
alter table q7.leaf add constraint t_a_check check (a > 100);
\echo => TEST
alter table s7.t add check (a > 0);
select tree('s7.t');

\echo --- B4 two unnamed CHECKs in one command, child owns t_a_check and t_a_check1
create schema s8; create schema p8;
create table s8.t(a int) partition by range (a);
create table p8.t_1_10 partition of s8.t for values from (1) to (10);
alter table p8.t_1_10 add constraint t_a_check check (a > 100), add constraint t_a_check1 check (a > 101);
\echo => TEST
alter table s8.t add check (a > 0), add check (a < 1000);
select tree('s8.t');

\echo --- C1 FOREIGN KEY, child has a CHECK named t_a_fkey
create schema s9; create schema p9;
create table s9.ref(id int primary key);
create table s9.t(a int) partition by range (a);
create table p9.t_1_10 partition of s9.t for values from (1) to (10);
alter table p9.t_1_10 add constraint t_a_fkey check (a > 100);
\echo => TEST
alter table s9.t add foreign key (a) references s9.ref;
select tree('s9.t');

\echo --- C2 ADD PRIMARY KEY (creates the not-null too), child has a CHECK named t_a_not_null
create schema s10; create schema p10;
create table s10.t(a int) partition by range (a);
create table p10.t_1_10 partition of s10.t for values from (1) to (10);
alter table p10.t_1_10 add constraint t_a_not_null check (a is not null);
\echo => TEST
alter table s10.t add primary key (a);
select tree('s10.t');

\echo --- C3 ADD COLUMN ... NOT NULL, child has a CHECK named t_b_not_null
create schema s11; create schema p11;
create table s11.t(a int) partition by range (a);
create table p11.t_1_10 partition of s11.t for values from (1) to (10);
alter table p11.t_1_10 add constraint t_b_not_null check (a is not null);
\echo => TEST
alter table s11.t add column b int not null;
select tree('s11.t');

\echo --- C4 ADD COLUMN ... CHECK, child has a CHECK named t_b_check
create schema s12; create schema p12;
create table s12.t(a int) partition by range (a);
create table p12.t_1_10 partition of s12.t for values from (1) to (10);
alter table p12.t_1_10 add constraint t_b_check check (a > 100);
\echo => TEST
alter table s12.t add column b int check (b > 0);
select tree('s12.t');

\echo --- D1 control: child has the SAME check under the default name (master merges?)
create schema s13; create schema p13;
create table s13.t(a int) partition by range (a);
create table p13.t_1_10 partition of s13.t for values from (1) to (10);
alter table p13.t_1_10 add constraint t_a_check check (a > 0);
\echo => TEST
alter table s13.t add check (a > 0);
select tree('s13.t');

\echo --- D2 control: child already has a real NOT NULL under the default name
create schema s14; create schema p14;
create table s14.t(a int) partition by range (a);
create table p14.t partition of s14.t for values from (1) to (10);
alter table p14.t alter column a set not null;
\echo => TEST
alter table s14.t alter column a set not null;
select tree('s14.t');

\echo --- D3 control: same as A2 but everything in ONE schema
create schema s15;
create table s15.t(a int) partition by range (a);
create table s15.t_1_10 partition of s15.t for values from (1) to (10);
alter table s15.t_1_10 add constraint t_a_check check (a > 100);
\echo => TEST
alter table s15.t add check (a > 0);
select tree('s15.t');

\echo --- D4 control: no conflict at all, names must not change
create schema s16; create schema p16;
create table s16.t(a int) partition by range (a);
create table p16.t_1_10 partition of s16.t for values from (1) to (10);
\echo => TEST
alter table s16.t add check (a > 0);
alter table s16.t alter column a set not null;
select tree('s16.t');