scratch147.sql

application/sql

Download

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

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


--MULPLT LEVEL check constraint table inhertance. parent is NOT VALID, but children is valid
---tested dump restore ok.
BEGIN;
DROP TABLE IF EXISTS inhck cascade; 
CREATE TABLE inhck (a INTEGER);
insert into inhck values(NULL);
ALTER TABLE inhck ADD CONSTRAINT cc check (a is NOT NULL) NOT VALID;
CREATE TABLE inhck_cc(a INTEGER) INHERITS(inhck);
CREATE TABLE inhck_cc_1(a INTEGER) INHERITS(inhck_cc, inhck);
COMMIT;



--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 inhertance. 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;


--single LEVEL check constraint table inhertance. parent is NOT VALID, but children is valid
---tested dump restore.
BEGIN;
DROP TABLE IF EXISTS inhsinglechk cascade; 
CREATE TABLE inhsinglechk (a INTEGER);
insert into inhsinglechk values(NULL);
ALTER TABLE inhsinglechk ADD CONSTRAINT cc check (a is NOT NULL) NOT VALID;
CREATE TABLE inhsinglechk_cc(a INTEGER) INHERITS(inhsinglechk);
COMMIT;


create or replace function nnconstraint_info(test regclass[])
returns table (conrelid regclass, conname name, convalidated boolean, coninhcount int2,conislocal boolean)
language plpgsql as
$$
begin
    return query 
    EXECUTE format('SELECT conrelid::regclass, conname, convalidated, coninhcount, conislocal
    FROM  pg_constraint
    WHERE conrelid  = ANY($1) ORDER BY 1') USING test;
end;
$$;