
--MULPLT LEVEL not-null constraint partitioning. parent is NOT VALID, but children is valid
---tested dump restore not ok.
-- convalidated is ok, conislocal is not ok
--ok.
BEGIN;
DROP TABLE if exists partednn;
CREATE TABLE partednn (id bigint default 1,id_abc bigint) PARTITION BY LIST (id);
CREATE TABLE partednn_1 (id bigint default 1,id_abc bigint) partition by list(id);
ALTER TABLE partednn ATTACH PARTITION partednn_1 FOR VALUES IN ('1', '2', NULL);
ALTER TABLE partednn add CONSTRAINT dummy_constr not null id not valid;

CREATE TABLE partednn_11 (id bigint default 1,id_abc bigint);
ALTER TABLE partednn_11 add CONSTRAINT dummy_constr not null id;
ALTER TABLE partednn_1 ATTACH PARTITION partednn_11 FOR VALUES IN ('1', '2', NULL);
COMMIT;


--MULPLT LEVEL not-null constraint table inheritance. parent is NOT VALID, but children is valid
---tested dump restore not ok.
-- convalidated not ok conislocal is ok
-- --now ok
BEGIN;
DROP TABLE IF EXISTS inhnn cascade;
CREATE TABLE inhnn (a INTEGER);
insert into inhnn values(NULL);
ALTER TABLE inhnn ADD CONSTRAINT cc not null a NOT VALID;
CREATE TABLE inhnn_cc(a INTEGER) INHERITS(inhnn);
CREATE TABLE inhnn_cc_1(a INTEGER) INHERITS(inhnn_cc, inhnn);
COMMIT;


--single LEVEL not-null constraint partitioning. parent is NOT VALID, but children is valid
---tested dump restore not ok.
-- convalidated is ok conislocal is not ok
-- now ok
BEGIN;
DROP TABLE if exists singlepp;
CREATE TABLE singlepp (id bigint default 1,id_abc bigint) PARTITION BY LIST (id);
alter TABLE singlepp add CONSTRAINT dummy_constr not null id not valid;
CREATE TABLE singlepp_1 (id bigint default 1,id_abc bigint);
alter TABLE singlepp_1 add CONSTRAINT dummy_constr not null id;
ALTER TABLE singlepp ATTACH PARTITION singlepp_1 FOR VALUES IN ('1');
COMMIT;


drop table if exists before_dump;
create table before_dump as
select conrelid::regclass::text, conname, convalidated, coninhcount, conislocal, conparentid, contype
from pg_constraint
where conrelid::regclass::text = 
ANY('{partednn, partednn_1, partednn_11, singlepp, singlepp_1, inhnn, inhnn_cc, inhnn_cc_1}')
order by 1,2;

/*
$BIN6/pg_dump --dbname=src6 --no-statistics --clean \
    --table-and-children=*partednn* \
    --table-and-children=*inhnn* \
    --table-and-children=*singlepp* \
    --no-owner --verbose --column-inserts --file=dump.sql --no-acl --verbose > dir_format.txt 2>&1

--pipe dump content dump.sql to psql. then capture meta data in after_dump

drop table if exists after_dump;
create table after_dump as
select conrelid::regclass::text, conname, convalidated, coninhcount, conislocal, conparentid, contype
from pg_constraint
where conrelid::regclass::text = 
ANY('{partednn, partednn_1, partednn_11, singlepp, singlepp_1, inhnn, inhnn_cc, inhnn_cc_1}')
order by 1,2;


--final test.
select * from after_dump except all select * from before_dump;
select * from before_dump except all select * from after_dump;
*/