Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / index_constraint_naming_partition.out
blob: a934d8c406cae62a74f7f10e14aadc9048a3dc5c [file] [log] [blame]
--
-- testing that constraint types that are backed by indices(UNIQUE, PRIMARY KEY,
-- EXCLUSION) have constraint names that match the name of their corresponding
-- indices. This file covers partition tables only, and covers all mechanisms
-- of DDL use of partition tables(EXCHANGE, ADD PARTITION, CREATE, etc). We do
-- so for a TWO LEVEL(root and leaf) hierarchy and a THREE LEVEL hierarchy
--
-- We also add test here for partitioned indices. Logically, these are more
-- or less tested via index-backed constraints, but it is a different DDL so
-- we have to assume as testers that the server code paths are different.
--
-- we have our test cases here, but the expected file contains the desired
-- outcomes. In other words, we do not validate the results here but do so
-- in the expected file.
DROP schema IF EXISTS index_constraint_naming_partition cascade;
NOTICE: schema "index_constraint_naming_partition" does not exist, skipping
CREATE schema index_constraint_naming_partition;
SET search_path='index_constraint_naming_partition';
DROP FUNCTION IF EXISTS partition_tables(text);
NOTICE: function partition_tables(text) does not exist, skipping
CREATE FUNCTION partition_tables(tab text) RETURNS TABLE(partition_name regclass, parent_name regclass, root_name regclass)
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT relid, parentrelid, pg_partition_root(relid) FROM pg_partition_tree(tab);
$fn$;
DROP FUNCTION IF EXISTS constraints_and_indices();
NOTICE: function constraints_and_indices() does not exist, skipping
CREATE FUNCTION constraints_and_indices() RETURNS TABLE(table_name regclass, constraint_name name, index_name regclass, constraint_type char, connspoid OID)
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT
con.conrelid::regclass,
con.conname,
con.conindid::regclass,
con.contype::char,
con.connamespace
FROM
pg_constraint con
WHERE
con.contype != 'c'
ORDER BY conrelid
;
$fn$;
--displays all dependencies and their types
DROP FUNCTION IF EXISTS dependencies();
NOTICE: function dependencies() does not exist, skipping
CREATE FUNCTION dependencies() RETURNS TABLE( depname NAME, classtype "char", depnsoid OID,
refname NAME, refclasstype "char", refnsoid OID,
classid REGCLASS, objid OID, objsubid INTEGER,
refclassid REGCLASS, refobjid OID, refobjsubid OID, deptype "char" )
LANGUAGE SQL STABLE STRICT AS $fn$
WITH RECURSIVE
w AS (
SELECT classid::regclass,
objid,
objsubid,
refclassid::regclass,
refobjid,
refobjsubid,
deptype
FROM pg_depend d
WHERE classid IN ('pg_constraint'::regclass, 'pg_class'::regclass)
AND (objid > 16384 OR refobjid > 16384)
UNION
SELECT d2.*
FROM w
INNER JOIN pg_depend d2
ON (w.refclassid, w.refobjid, w.refobjsubid) =
(d2.classid, d2.objid, d2.objsubid)
)
SELECT COALESCE(con.conname, c.relname, t.typname, nsp.nspname) AS depname,
COALESCE(con.contype, c.relkind, '-') as classtype,
COALESCE(con.connamespace, c.relnamespace, t.typnamespace, nsp.oid) as depnsoid,
COALESCE(con2.conname, c2.relname, t2.typname, nsp2.nspname) AS refname,
COALESCE(con2.contype, c2.relkind, '-') as refclasstype,
COALESCE(con2.connamespace, c2.relnamespace, t2.typnamespace, nsp2.oid) as refnsoid,
w.*
FROM w
LEFT JOIN pg_constraint con
ON classid = 'pg_constraint'::regclass AND objid = con.oid
LEFT JOIN pg_class c ON classid = 'pg_class'::regclass AND objid = c.oid
LEFT JOIN pg_type t ON classid = 'pg_type'::regclass AND objid = t.oid
LEFT JOIN pg_namespace nsp
ON classid = 'pg_namespace'::regclass AND objid = nsp.oid
LEFT JOIN pg_constraint con2
ON refclassid = 'pg_constraint'::regclass AND
refobjid = con2.oid
LEFT JOIN pg_class c2
ON refclassid = 'pg_class'::regclass AND refobjid = c2.oid AND c2.relkind NOT IN ('p', 'I')
LEFT JOIN pg_type t2
ON refclassid = 'pg_type'::regclass AND refobjid = t2.oid
LEFT JOIN pg_namespace nsp2 ON refclassid = 'pg_namespace'::regclass AND
refobjid = nsp2.oid
;
$fn$;
--dependencies() table but with namespace name instead of namespace oid
DROP FUNCTION IF EXISTS dependencies_nsname();
NOTICE: function dependencies_nsname() does not exist, skipping
CREATE FUNCTION dependencies_nsname() RETURNS TABLE(depname NAME, classtype "char", depnsname NAME,
refname NAME, refclasstype "char", refnsname NAME,
classid REGCLASS, objsubid INTEGER,
refclassid REGCLASS, refobjsubid OID, deptype "char")
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT t.depname, t.classtype, nsp.nspname as depnsame, t.refname, t.refclasstype,
nsp2.nspname as refnsname, t.classid, t.objsubid, t.refclassid, t.refobjsubid, t.deptype
FROM
(SELECT depname, classtype, depnsoid, refname, refclasstype, refnsoid,
classid, objsubid, refclassid, refobjsubid, deptype
FROM dependencies()
WHERE
(refclassid='pg_class'::REGCLASS OR refclassid='pg_constraint'::REGCLASS) AND (classtype!='c')
AND (NOT (classtype='r' AND classid='pg_class'::REGCLASS AND refclasstype='r' AND refclassid='pg_class'::REGCLASS))
) as t
JOIN pg_namespace nsp ON t.depnsoid=nsp.oid
JOIN pg_namespace nsp2 ON t.refnsoid=nsp2.oid;
$fn$;
DROP FUNCTION IF EXISTS partition_tables_show_all(text);
NOTICE: function partition_tables_show_all(text) does not exist, skipping
CREATE FUNCTION partition_tables_show_all(tab text) RETURNS TABLE(partition_name regclass, parent_name regclass, root_name regclass, constraint_name name, index_name name, constraint_type char)
--connspname NAME) --, deptype "char")
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT p.partition_name, p.parent_name, p.root_name, c.constraint_name, c.index_name, c.constraint_type --nsp.nspname --, d.deptype
FROM
(SELECT partition_name, parent_name, root_name FROM partition_tables(tab)) as p
LEFT JOIN
(SELECT table_name, constraint_name, CAST(index_name as name), constraint_type, connspoid from constraints_and_indices()) as c
ON p.partition_name::name = CAST(c.table_name as name)
JOIN pg_class as pgc ON p.root_name::name=pgc.relname --find root_name's namespace name
JOIN pg_namespace as nsp
ON pgc.relnamespace=nsp.oid WHERE nsp.nspname='index_constraint_naming_partition'
ORDER BY p.partition_name::name, p.parent_name::name, p.root_name::name, c.constraint_name, c.index_name, c.constraint_type; --make sure output is stabely ordered for pg_regress
$fn$;
DROP FUNCTION IF EXISTS dependencies_show_for_idx();
NOTICE: function dependencies_show_for_idx() does not exist, skipping
CREATE FUNCTION dependencies_show_for_idx() RETURNS TABLE(depname name, refname name, depType "char")
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT t.depname, t.refname, t.deptype FROM
(SELECT depname, depnsname, refname, refnsname, deptype
FROM dependencies_nsname()
WHERE
((depname LIKE '%idx%') OR (refname LIKE '%idx%'))
AND (depnsname='index_constraint_naming_partition' AND refnsname='index_constraint_naming_partition')) as t
ORDER BY t.depname, t.refname, t.deptype; --make sure output is stabely ordered for pg_regress
$fn$;
--return all dependencies of interest here...
DROP FUNCTION IF EXISTS dependencies_show_for_cons_idx();
NOTICE: function dependencies_show_for_cons_idx() does not exist, skipping
CREATE FUNCTION dependencies_show_for_cons_idx() RETURNS TABLE(depname NAME, classtype "char",
refname NAME, refclasstype "char", classid REGCLASS, objsubid INTEGER,
refclassid REGCLASS, refobjsubid OID, deptype "char")
LANGUAGE SQL STABLE STRICT AS $fn$
SELECT t.depname, t.classtype, t.refname, t.refclasstype, t.classid, t.objsubid, t.refclassid, t.refobjsubid, t.deptype FROM
(SELECT depname, classtype, depnsname, refname, refclasstype, refnsname, classid, objsubid, refclassid, refobjsubid, deptype FROM dependencies_nsname() WHERE
(refclassid='pg_class'::REGCLASS OR refclassid='pg_constraint'::REGCLASS) AND (classtype!='c')
AND (NOT (classtype='r' AND classid='pg_class'::REGCLASS AND refclasstype='r' AND refclassid='pg_class'::REGCLASS))
AND (depnsname='index_constraint_naming_partition' AND refnsname='index_constraint_naming_partition')) as t
ORDER BY t.classtype, t.refclasstype, t.deptype, t.depname, t.refname, t.classid, t.objsubid, t.refclassid, t.refobjsubid; --make sure output is stabely ordered for pg_regress
$fn$;
--return all dependencies of interest here...
--############################ TWO LEVEL ##################################
DROP FUNCTION IF EXISTS recreate_two_level_table();
NOTICE: function recreate_two_level_table() does not exist, skipping
CREATE FUNCTION recreate_two_level_table() RETURNS VOID
LANGUAGE SQL VOLATILE STRICT AS $fn$
DROP TABLE IF EXISTS r;
CREATE TABLE r (
r_key INTEGER NOT NULL,
r_name CHAR(25)
)
DISTRIBUTED BY (r_key)
PARTITION BY range (r_key)
(
PARTITION r1 START (0),
PARTITION r2 START (5) END (8)
);
$fn$;
-- validate that there are no constraints when we start
SELECT * FROM partition_tables_show_all('r');
ERROR: relation "r" does not exist
CONTEXT: SQL function "partition_tables" statement 1
SQL function "partition_tables_show_all" statement 1
-- UNIQUE constraint: validate we correctly add it and can only drop from root
SELECT recreate_two_level_table();
NOTICE: table "r" does not exist, skipping
recreate_two_level_table
--------------------------
(1 row)
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(13 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------------------+-----------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(3 rows)
INSERT INTO r VALUES (1,'xxxx'),(1,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r1_r_key_r_name_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key, r_name)=(1, xxxx ) already exists.
INSERT INTO r VALUES (6,'xxxx'),(6,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_r_key_r_name_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key, r_name)=(6, xxxx ) already exists.
ALTER TABLE r ADD PARTITION added_part START(8) INCLUSIVE END (10) EXCLUSIVE;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_added_part_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(19 rows)
INSERT INTO r VALUES (9,'xxxx'),(9,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_added_part_r_key_r_name_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key, r_name)=(9, xxxx ) already exists.
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(4 rows)
ALTER TABLE r_1_prt_r1 DROP CONSTRAINT r_1_prt_r1_r_key_r_name_key; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_r_key_r_name_key" of relation "r_1_prt_r1"
INSERT INTO r VALUES (1,'xxxx'),(1,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r1_r_key_r_name_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key, r_name)=(1, xxxx ) already exists.
INSERT INTO r VALUES (6,'xxxx'),(6,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_r_key_r_name_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key, r_name)=(6, xxxx ) already exists.
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(4 rows)
ALTER TABLE r ADD DEFAULT PARTITION d;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_added_part_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_d_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(25 rows)
INSERT INTO r VALUES (22,'xxxx'),(22,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_d_r_key_r_name_key" (seg0 127.0.0.1:25432 pid=78718)
DETAIL: Key (r_key, r_name)=(22, xxxx ) already exists.
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_d | r | r | r_1_prt_d_r_key_r_name_key | r_1_prt_d_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(5 rows)
ALTER TABLE r SPLIT PARTITION r2 AT (6) INTO (PARTITION r2_split_l, PARTITION r2_split_r);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_split_l_r_key_r_name_key | i | r_1_prt_r2_split_l | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_split_r_r_key_r_name_key | i | r_1_prt_r2_split_r | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_split_l_r_key_r_name_key | i | r_1_prt_r2_split_l_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_split_r_r_key_r_name_key | i | r_1_prt_r2_split_r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_added_part_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_d_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_split_l_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_split_r_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(31 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_d | r | r | r_1_prt_d_r_key_r_name_key | r_1_prt_d_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2_split_l | r | r | r_1_prt_r2_split_l_r_key_r_name_key | r_1_prt_r2_split_l_r_key_r_name_key | u
r_1_prt_r2_split_r | r | r | r_1_prt_r2_split_r_r_key_r_name_key | r_1_prt_r2_split_r_r_key_r_name_key | u
(6 rows)
INSERT INTO r VALUES (5,'xxxx'),(5,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_split_l_r_key_r_name_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key, r_name)=(5, xxxx ) already exists.
INSERT INTO r VALUES (7,'xxxx'),(7,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_split_r_r_key_r_name_key" (seg0 127.0.0.1:25432 pid=78718)
DETAIL: Key (r_key, r_name)=(7, xxxx ) already exists.
alter table r rename partition r1 to r1_renamed;
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_d | r | r | r_1_prt_d_r_key_r_name_key | r_1_prt_d_r_key_r_name_key | u
r_1_prt_r1_renamed | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2_split_l | r | r | r_1_prt_r2_split_l_r_key_r_name_key | r_1_prt_r2_split_l_r_key_r_name_key | u
r_1_prt_r2_split_r | r | r | r_1_prt_r2_split_r_r_key_r_name_key | r_1_prt_r2_split_r_r_key_r_name_key | u
(6 rows)
INSERT INTO r VALUES (1,'xxxx'),(1,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_r1_r_key_r_name_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key, r_name)=(1, xxxx ) already exists.
ALTER TABLE r DROP PARTITION r1_renamed;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_split_l_r_key_r_name_key | i | r_1_prt_r2_split_l | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_split_r_r_key_r_name_key | i | r_1_prt_r2_split_r | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | i | r_1_prt_added_part_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_d_r_key_r_name_key | i | r_1_prt_d_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_split_l_r_key_r_name_key | i | r_1_prt_r2_split_l_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_split_r_r_key_r_name_key | i | r_1_prt_r2_split_r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_added_part_r_key_r_name_key | u | r_1_prt_added_part | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_d_r_key_r_name_key | u | r_1_prt_d | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_split_l_r_key_r_name_key | u | r_1_prt_r2_split_l | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_split_r_r_key_r_name_key | u | r_1_prt_r2_split_r | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_added_part_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_d_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_split_l_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_split_r_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(25 rows)
INSERT INTO r VALUES (1,'xxxx'),(1,'xxxx'); --should be prevented by constraint
ERROR: duplicate key value violates unique constraint "r_1_prt_d_r_key_r_name_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key, r_name)=(1, xxxx ) already exists.
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_added_part | r | r | r_1_prt_added_part_r_key_r_name_key | r_1_prt_added_part_r_key_r_name_key | u
r_1_prt_d | r | r | r_1_prt_d_r_key_r_name_key | r_1_prt_d_r_key_r_name_key | u
r_1_prt_r2_split_l | r | r | r_1_prt_r2_split_l_r_key_r_name_key | r_1_prt_r2_split_l_r_key_r_name_key | u
r_1_prt_r2_split_r | r | r | r_1_prt_r2_split_r_r_key_r_name_key | r_1_prt_r2_split_r_r_key_r_name_key | u
(5 rows)
ALTER TABLE r DROP CONSTRAINT r_r_key_r_name_key; --should work
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
---------+-----------+---------+--------------+---------+----------+------------+-------------+---------
(0 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_added_part | r | r | | |
r_1_prt_d | r | r | | |
r_1_prt_r2_split_l | r | r | | |
r_1_prt_r2_split_r | r | r | | |
(5 rows)
INSERT INTO r VALUES (1,'xxxx'),(1,'xxxx'); --should be allowed: no unique constraint exists
INSERT INTO r VALUES (6,'xxxx'),(6,'xxxx'); --should be allowed: no unique constraint exists
INSERT INTO r VALUES (9,'xxxx'),(9,'xxxx'); --should be allowed: no unique constraint exists
INSERT INTO r VALUES (22,'xxxx'),(22,'xxxx'); --should be allowed: no unique constraint exists
-- EXCHANGE... prepare table e and swap with partition
SELECT recreate_two_level_table();
recreate_two_level_table
--------------------------
(1 row)
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(13 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------------------+-----------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(3 rows)
CREATE TABLE e (LIKE r INCLUDING CONSTRAINTS INCLUDING INDEXES);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
e_r_key_r_name_key | i | e_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
e_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
e_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(16 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------------------+-----------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key | r_1_prt_r1_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(3 rows)
ALTER TABLE r EXCHANGE PARTITION r1 WITH TABLE e;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
e_r_key_r_name_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
e_r_key_r_name_key | i | e_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
e_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
e_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
e_r_key_r_name_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
e_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(16 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------------------+-----------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | e_r_key_r_name_key | e_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(3 rows)
--MAKE SURE PARTITION CONSTRAINT EXISTS...
-- UNIQUE create constraint inside CREATE TABLE DDL...this is different
DROP TABLE IF EXISTS r;
CREATE TABLE r (r_key INTEGER NOT NULL,UNIQUE(r_key)) DISTRIBUTED BY (r_key)
PARTITION BY range (r_key) (PARTITION r1 START (0), PARTITION r2 START (5) END (8));
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_key | I | r_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_key | i | r_1_prt_r1_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_key | i | r_1_prt_r2_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_key | u | r_r_key_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_key | u | r_r_key_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(14 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+----------------------+----------------------+-----------------
r | | r | r_r_key_key | r_r_key_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_key | r_1_prt_r1_r_key_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_key | r_1_prt_r2_r_key_key | u
(3 rows)
INSERT INTO r VALUES (1),(1);
ERROR: duplicate key value violates unique constraint "r_1_prt_r1_r_key_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key)=(1) already exists.
INSERT INTO r VALUES (6),(6);
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_r_key_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key)=(6) already exists.
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_key | I | r_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_key | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_key | i | r_1_prt_r1_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_key | i | r_1_prt_r2_r_key_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_key | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_key | u | r_r_key_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_key | u | r_r_key_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(14 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+----------------------+----------------------+-----------------
r | | r | r_r_key_key | r_r_key_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_key | r_1_prt_r1_r_key_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_key | r_1_prt_r2_r_key_key | u
(3 rows)
INSERT INTO r VALUES (1),(1);
ERROR: duplicate key value violates unique constraint "r_1_prt_r1_r_key_key" (seg1 127.0.0.1:25433 pid=78719)
DETAIL: Key (r_key)=(1) already exists.
INSERT INTO r VALUES (6),(6);
ERROR: duplicate key value violates unique constraint "r_1_prt_r2_r_key_key" (seg2 127.0.0.1:25434 pid=78720)
DETAIL: Key (r_key)=(6) already exists.
--PRIMARY KEY constraint: validate we correctly add it and can only drop from root
SELECT recreate_two_level_table();
recreate_two_level_table
--------------------------
(1 row)
ALTER TABLE r ADD PRIMARY KEY(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_pkey | I | r_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | i | r_1_prt_r1_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_pkey | i | r_1_prt_r2_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_pkey | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_pkey | p | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_pkey | p | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_pkey | p | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_pkey | p | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_pkey | p | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_pkey | p | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(16 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+-----------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
(3 rows)
ALTER TABLE r_1_prt_r1 DROP CONSTRAINT r_1_prt_r1_pkey; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_pkey" of relation "r_1_prt_r1"
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+-----------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
(3 rows)
ALTER TABLE r DROP CONSTRAINT r_pkey; --should work
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(3 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r2 | r | r | | |
(3 rows)
--EXCLUSION constraint: validate we cannot create an exclusion constraint on a
--partition table. These are disallowed in GPDB.
SELECT recreate_two_level_table();
recreate_two_level_table
--------------------------
(1 row)
ALTER TABLE r ADD EXCLUDE (r_key WITH =,r_name WITH =);
ERROR: exclusion constraints are not supported on partitioned tables
LINE 1: ALTER TABLE r ADD EXCLUDE (r_key WITH =,r_name WITH =);
^
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(3 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r2 | r | r | | |
(3 rows)
ALTER TABLE r_1_prt_r1 DROP CONSTRAINT r_1_prt_r1_r_key_r_name_excl; --should fail
ERROR: constraint "r_1_prt_r1_r_key_r_name_excl" of relation "r_1_prt_r1" does not exist
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r2 | r | r | | |
(3 rows)
--new partition table (4) should be created with constraints.
DROP TABLE IF EXISTS r;
CREATE TABLE r (
r_key INTEGER NOT NULL,
r_name CHAR(25)
)
DISTRIBUTED BY (r_key)
PARTITION BY list (r_key)
(
PARTITION r1 VALUES (0),
PARTITION r2 VALUES (1)
);
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
------------------------------+-----------+------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | i | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(16 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+------------------------------+------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
(3 rows)
ALTER TABLE r ADD PARTITION r3 VALUES(2);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
------------------------------+-----------+------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r3_r_key_r_name_key | i | r_1_prt_r3 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | i | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | i | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r3_r_key_r_name_key | i | r_1_prt_r3_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r3_r_key_r_name_key | u | r_1_prt_r3 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_r_key_r_name_key | u | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r3_r_key_r_name_key | u | r_1_prt_r3 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r3_r_key_r_name_key | u | r_1_prt_r3 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r3_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(22 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+------------------------------+------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r3 | r | r | r_1_prt_r3_r_key_r_name_key | r_1_prt_r3_r_key_r_name_key | u
(4 rows)
-- PRIMARY create constraint inside CREATE TABLE DDL...this is different
DROP TABLE IF EXISTS r;
CREATE TABLE r (r_key INTEGER NOT NULL,PRIMARY KEY (r_key)) DISTRIBUTED BY (r_key)
PARTITION BY range (r_key) (PARTITION r1 START (0), PARTITION r2 START (5) END (8));
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_pkey | I | r_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | i | r_1_prt_r1_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_pkey | i | r_1_prt_r2_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | i | r_1_prt_r1 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_pkey | i | r_1_prt_r2 | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_pkey | p | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_pkey | p | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_pkey | p | r_1_prt_r1 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_pkey | p | r_1_prt_r2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(14 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+-----------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
(3 rows)
ALTER TABLE r DROP CONSTRAINT r_pkey; --should work
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(3 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
----------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r2 | r | r | | |
(3 rows)
DROP TABLE r;
-- --########### THREE LEVEL ###############################
DROP FUNCTION IF EXISTS recreate_three_level_table();
NOTICE: function recreate_three_level_table() does not exist, skipping
CREATE FUNCTION recreate_three_level_table() RETURNS VOID
LANGUAGE SQL VOLATILE STRICT AS $fn$
DROP TABLE IF EXISTS r;
CREATE TABLE r (
r_key INTEGER NOT NULL,
r_name CHAR(25)
)
DISTRIBUTED BY (r_key)
PARTITION BY range (r_key)
SUBPARTITION BY list (r_name) SUBPARTITION TEMPLATE
(
SUBPARTITION a VALUES ('A'),
SUBPARTITION b VALUES ('B')
)
(
PARTITION r1 START (0),
PARTITION r2 START (5) END (8)
);
$fn$;
-- validate that there are no constraints when we start
SELECT * FROM partition_tables_show_all('r');
ERROR: relation "r" does not exist
CONTEXT: SQL function "partition_tables" statement 1
SQL function "partition_tables_show_all" statement 1
-- UNIQUE constraint: validate we correctly add it and can only drop from root
SELECT recreate_three_level_table();
NOTICE: table "r" does not exist, skipping
recreate_three_level_table
----------------------------
(1 row)
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(32 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r_1_prt_r1 DROP CONSTRAINT r_1_prt_r1_r_key_r_name_key1; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_r_key_r_name_key1" of relation "r_1_prt_r1"
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r_1_prt_r1_2_prt_a DROP CONSTRAINT r_1_prt_r1_2_prt_a_r_key_r_name_key; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_2_prt_a_r_key_r_name_key" of relation "r_1_prt_r1_2_prt_a"
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r DROP CONSTRAINT r_r_key_r_name_key; --should work
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(3 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | | |
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | | |
r_1_prt_r2 | r | r | | |
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | | |
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | | |
(7 rows)
--PRIMARY KEY constraint: validate we correctly add it and can only drop from root
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
ALTER TABLE r ADD PRIMARY KEY(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_pkey | I | r_1_prt_r1_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_pkey | I | r_1_prt_r2_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_pkey | I | r_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | i | r_1_prt_r1_2_prt_a_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_pkey | i | r_1_prt_r1_2_prt_b_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_pkey | i | r_1_prt_r2_2_prt_a_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_pkey | i | r_1_prt_r2_2_prt_b_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_pkey | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_pkey | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_pkey | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(32 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------+-------------------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_pkey | r_1_prt_r1_2_prt_a_pkey | p
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_pkey | r_1_prt_r1_2_prt_b_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_pkey | r_1_prt_r2_2_prt_a_pkey | p
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_pkey | r_1_prt_r2_2_prt_b_pkey | p
(7 rows)
ALTER TABLE r_1_prt_r1 DROP CONSTRAINT r_1_prt_r1_pkey; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_pkey" of relation "r_1_prt_r1"
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------+-------------------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_pkey | r_1_prt_r1_2_prt_a_pkey | p
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_pkey | r_1_prt_r1_2_prt_b_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_pkey | r_1_prt_r2_2_prt_a_pkey | p
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_pkey | r_1_prt_r2_2_prt_b_pkey | p
(7 rows)
ALTER TABLE r_1_prt_r1_2_prt_a DROP CONSTRAINT r_1_prt_r1_2_prt_a_pkey; --should fail
ERROR: cannot drop inherited constraint "r_1_prt_r1_2_prt_a_pkey" of relation "r_1_prt_r1_2_prt_a"
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------+-------------------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_pkey | r_1_prt_r1_2_prt_a_pkey | p
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_pkey | r_1_prt_r1_2_prt_b_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_pkey | r_1_prt_r2_2_prt_a_pkey | p
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_pkey | r_1_prt_r2_2_prt_b_pkey | p
(7 rows)
ALTER TABLE r DROP CONSTRAINT r_pkey; --should work
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-----------------------------+-----------+-----------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(3 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | | |
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | | |
r_1_prt_r2 | r | r | | |
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | | |
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | | |
(7 rows)
--New partition table 'C' should be created with constraints.
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(32 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r ALTER PARTITION r1 ADD PARTITION c VALUES ('C');
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_c_r_key_r_name_key | i | r_1_prt_r1_2_prt_c | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_c_r_key_r_name_key | i | r_1_prt_r1_2_prt_c_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_c_r_key_r_name_key | u | r_1_prt_r1_2_prt_c | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_c_r_key_r_name_key | u | r_1_prt_r1_2_prt_c | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_c_r_key_r_name_key | u | r_1_prt_r1_2_prt_c | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_c_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(38 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r1_2_prt_c | r_1_prt_r1 | r | r_1_prt_r1_2_prt_c_r_key_r_name_key | r_1_prt_r1_2_prt_c_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(8 rows)
-- EXCHANGE... prepare table e2 and swap with partition
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
ALTER TABLE r ADD UNIQUE(r_key,r_name);
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(32 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
CREATE TABLE e2 (LIKE r INCLUDING CONSTRAINTS INCLUDING INDEXES);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
e2_r_key_r_name_key | i | e2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
e2_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
e2_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(35 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key | r_1_prt_r1_2_prt_a_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r ALTER PARTITION r1 EXCHANGE PARTITION a WITH TABLE e2;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
e2_r_key_r_name_key | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
e2_r_key_r_name_key | i | e2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
e2_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
e2_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
e2_r_key_r_name_key | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
e2_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(35 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------------------+-------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | e2_r_key_r_name_key | e2_r_key_r_name_key | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
-- UNIQUE: create constraint inside CREATE TABLE DDL...this is different
DROP TABLE IF EXISTS r;
CREATE TABLE r (r_key INTEGER NOT NULL,r_name CHAR(25), UNIQUE(r_key, r_name))
DISTRIBUTED BY (r_key) PARTITION BY range (r_key)
SUBPARTITION BY list (r_name) SUBPARTITION TEMPLATE
(SUBPARTITION a VALUES ('A'),SUBPARTITION b VALUES ('B'))
(PARTITION r1 START (0),PARTITION r2 START (5) END (8));
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
--------------------------------------+-----------+--------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_r_key_r_name_key1 | I | r_1_prt_r1_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_r_key_r_name_key | I | r_1_prt_r2_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_r_key_r_name_key | I | r_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | i | r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_r_key_r_name_key | i | r_1_prt_r1_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_r_key_r_name_key | i | r_1_prt_r2_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_r_key_r_name_key | i | r_1_prt_r2_2_prt_b_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_r_key_r_name_key | u | r_1_prt_r1_r_key_r_name_key1 | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_r_key_r_name_key1 | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_r_key_r_name_key | u | r_1_prt_r2_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_r_key_r_name_key | u | r_r_key_r_name_key | u | pg_constraint | 0 | pg_constraint | 0 | P
(35 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+--------------------------------------+--------------------------------------+-----------------
r | | r | r_r_key_r_name_key | r_r_key_r_name_key | u
r_1_prt_r1 | r | r | r_1_prt_r1_r_key_r_name_key1 | r_1_prt_r1_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_r_key_r_name_key1 | r_1_prt_r1_2_prt_a_r_key_r_name_key1 | u
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_r_key_r_name_key | r_1_prt_r1_2_prt_b_r_key_r_name_key | u
r_1_prt_r2 | r | r | r_1_prt_r2_r_key_r_name_key | r_1_prt_r2_r_key_r_name_key | u
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_r_key_r_name_key | r_1_prt_r2_2_prt_a_r_key_r_name_key | u
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_r_key_r_name_key | r_1_prt_r2_2_prt_b_r_key_r_name_key | u
(7 rows)
ALTER TABLE r DROP CONSTRAINT r_r_key_r_name_key;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(6 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | | |
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | | |
r_1_prt_r2 | r | r | | |
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | | |
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | | |
(7 rows)
-- PRIMARY: create constraint inside CREATE TABLE DDL...this is different
DROP TABLE IF EXISTS r;
CREATE TABLE r (r_key INTEGER NOT NULL,r_name CHAR(25), PRIMARY KEY(r_key, r_name))
DISTRIBUTED BY (r_key) PARTITION BY range (r_key)
SUBPARTITION BY list (r_name) SUBPARTITION TEMPLATE
(SUBPARTITION a VALUES ('A'),SUBPARTITION b VALUES ('B'))
(PARTITION r1 START (0),PARTITION r2 START (5) END (8));
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_pkey | I | r_1_prt_r1_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_pkey | I | r_1_prt_r2_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_pkey | I | r_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | i | r_1_prt_r1_2_prt_a_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_b_pkey | i | r_1_prt_r1_2_prt_b_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_a_pkey | i | r_1_prt_r2_2_prt_a_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r2_2_prt_b_pkey | i | r_1_prt_r2_2_prt_b_pkey | p | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | i | r_1_prt_r1_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_pkey | i | r_1_prt_r1_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_pkey | i | r_1_prt_r2_2_prt_a | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_pkey | i | r_1_prt_r2_2_prt_b | r | pg_class | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r2_pkey | p | r_pkey | p | pg_constraint | 0 | pg_constraint | 0 | P
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 0 | S
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_pkey | p | r_1_prt_r1_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_b_pkey | p | r_1_prt_r1_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_a_pkey | p | r_1_prt_r2_2_prt_a | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r2_2_prt_b_pkey | p | r_1_prt_r2_2_prt_b | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(35 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-------------------------+-------------------------+-----------------
r | | r | r_pkey | r_pkey | p
r_1_prt_r1 | r | r | r_1_prt_r1_pkey | r_1_prt_r1_pkey | p
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | r_1_prt_r1_2_prt_a_pkey | r_1_prt_r1_2_prt_a_pkey | p
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | r_1_prt_r1_2_prt_b_pkey | r_1_prt_r1_2_prt_b_pkey | p
r_1_prt_r2 | r | r | r_1_prt_r2_pkey | r_1_prt_r2_pkey | p
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | r_1_prt_r2_2_prt_a_pkey | r_1_prt_r2_2_prt_a_pkey | p
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | r_1_prt_r2_2_prt_b_pkey | r_1_prt_r2_2_prt_b_pkey | p
(7 rows)
ALTER TABLE r DROP CONSTRAINT r_pkey;
SELECT * FROM dependencies_show_for_cons_idx();
depname | classtype | refname | refclasstype | classid | objsubid | refclassid | refobjsubid | deptype
-------------------------------------+-----------+-------------------------------------+--------------+---------------+----------+---------------+-------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_key | i | r_1_prt_r1_2_prt_a_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_r_key_r_name_key | i | r_1_prt_r1_r_key_r_name_key | u | pg_class | 0 | pg_constraint | 0 | i
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_2_prt_a_r_key_r_name_key | u | e2 | r | pg_constraint | 0 | pg_class | 2 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 1 | a
r_1_prt_r1_r_key_r_name_key | u | e | r | pg_constraint | 0 | pg_class | 2 | a
(6 rows)
SELECT * FROM partition_tables_show_all('r');
partition_name | parent_name | root_name | constraint_name | index_name | constraint_type
--------------------+-------------+-----------+-----------------+------------+-----------------
r | | r | | |
r_1_prt_r1 | r | r | | |
r_1_prt_r1_2_prt_a | r_1_prt_r1 | r | | |
r_1_prt_r1_2_prt_b | r_1_prt_r1 | r | | |
r_1_prt_r2 | r | r | | |
r_1_prt_r2_2_prt_a | r_1_prt_r2 | r | | |
r_1_prt_r2_2_prt_b | r_1_prt_r2 | r | | |
(7 rows)
-- PARTITIONED INDEX TESTS--------------
-- UNIQUE index: validate we can add a unique index to any node in the partition
-- hierarchy and that that index is added to all descendents. Furthermore, no
-- descendent can remove that index; it can only be removed from the creating
-- node.
--create the index at the root; should only be able to drop it from root
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_onroot ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
DROP INDEX r_1_prt_r2_r_key_r_name_idx; --should fail: cannot drop from non-creating node
ERROR: cannot drop index r_1_prt_r2_r_key_r_name_idx because index myidx_onroot requires it
HINT: You can drop index myidx_onroot instead.
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
DROP INDEX r_1_prt_r2_2_prt_b_r_key_r_name_idx; --should fail: cannot drop from non-creating node
ERROR: cannot drop index r_1_prt_r2_2_prt_b_r_key_r_name_idx because index r_1_prt_r2_r_key_r_name_idx requires it
HINT: You can drop index r_1_prt_r2_r_key_r_name_idx instead.
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
DROP INDEX myidx_onroot; --works: is root of index hierarchy
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
---------+---------+---------
(0 rows)
--create the index starting at mid-level; should only be able to drop it from mid-level
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_midlevel ON r_1_prt_r1 USING btree(r_key, r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
DROP INDEX r_1_prt_r1_2_prt_a_r_key_r_name_idx; --should fail: cannot drop from non-creating node
ERROR: cannot drop index r_1_prt_r1_2_prt_a_r_key_r_name_idx because index myidx_midlevel requires it
HINT: You can drop index myidx_midlevel instead.
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
DROP INDEX myidx_midlevel; --works: is root of index hierarchy
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
---------+---------+---------
(0 rows)
--we can create more than one index on a table(same as upstream 11)
--yes, this is inconsistent with index subsumption but upstream does it this way
--in particular, there's no subsumption going on here.
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
CREATE UNIQUE INDEX ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
--------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx1 | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx1 | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx1 | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx1 | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx1 | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx1 | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx1 | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx1 | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx1 | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx1 | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx1 | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx1 | r_1_prt_r2_2_prt_b | a
(24 rows)
--test subsumption of lower-level index from upper-level one
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_midlevel ON r_1_prt_r1 USING btree(r_key, r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
CREATE UNIQUE INDEX myidx_onroot ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
--show subsumption works across rename
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_midlevel ON r_1_prt_r1 USING btree(r_key, r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
ALTER INDEX myidx_midlevel RENAME TO myidx_midlevel_renamed;
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
CREATE UNIQUE INDEX myidx_midlevel ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
--show that renaming still does not allow a name conflict
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_midlevel ON r_1_prt_r1 USING btree(r_key, r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
ALTER INDEX myidx_midlevel RENAME TO myidx_midlevel_renamed;
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
CREATE UNIQUE INDEX myidx_midlevel_renamed ON r USING btree(r_key,r_name); --should fail: index already exists
ERROR: relation "myidx_midlevel_renamed" already exists
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
(6 rows)
--show that renaming a mid-level index still prevents it from being dropped.
SELECT recreate_three_level_table();
recreate_three_level_table
----------------------------
(1 row)
CREATE UNIQUE INDEX myidx_onroot ON r USING btree(r_key,r_name);
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
ALTER INDEX r_1_prt_r1_r_key_r_name_idx RENAME TO middle_index_renamed;
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
DROP INDEX middle_index_renamed; --should fail: this renamed index is still controlled by the root index that created it
ERROR: cannot drop index middle_index_renamed because index myidx_onroot requires it
HINT: You can drop index myidx_onroot instead.
SELECT * FROM dependencies_show_for_idx();
depname | refname | deptype
-------------------------------------+--------------------+---------
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | S
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_a_r_key_r_name_idx | r_1_prt_r1_2_prt_a | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | S
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r1_2_prt_b_r_key_r_name_idx | r_1_prt_r1_2_prt_b | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | S
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_a_r_key_r_name_idx | r_1_prt_r2_2_prt_a | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | S
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
r_1_prt_r2_2_prt_b_r_key_r_name_idx | r_1_prt_r2_2_prt_b | a
(12 rows)
DROP TABLE IF EXISTS r;