mirror of
https://github.com/HypoPG/hypopg
synced 2026-05-24 09:38:21 +00:00
277 lines
10 KiB
Text
277 lines
10 KiB
Text
-- Creating real and hypothetical tables
|
|
-- =====================================
|
|
-- Real tables
|
|
-- ------------
|
|
-- 1. Range partitioning for hypothetical index
|
|
DROP TABLE IF EXISTS part_range;
|
|
CREATE TABLE part_range (id integer, val text) PARTITION BY RANGE (id);
|
|
CREATE TABLE part_range_1_10000 PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
|
|
CREATE TABLE part_range_10000_20000 PARTITION OF part_range FOR VALUES FROM (10000) TO (20000);
|
|
CREATE TABLE part_range_20000_30000 PARTITION OF part_range FOR VALUES FROM (20000) TO (30000);
|
|
INSERT INTO part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
|
|
-- Hypothetical tables
|
|
-- -------------------
|
|
-- 0. Dropping any hypothetical object
|
|
SELECT * FROM hypopg_reset_index();
|
|
hypopg_reset_index
|
|
--------------------
|
|
|
|
(1 row)
|
|
|
|
SELECT * FROM hypopg_reset_table();
|
|
hypopg_reset_table
|
|
--------------------
|
|
|
|
(1 row)
|
|
|
|
-- 1. Range partitioning for hypothetical index
|
|
DROP TABLE IF EXISTS hypo_part_range;
|
|
CREATE TABLE hypo_part_range (id integer, val text);
|
|
INSERT INTO hypo_part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
|
|
SELECT * FROM hypopg_partition_table('hypo_part_range', 'PARTITION BY RANGE (id)');
|
|
hypopg_partition_table
|
|
------------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
|
|
tablename
|
|
-------------------------
|
|
hypo_part_range_1_10000
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_range_10000_20000', 'PARTITION OF hypo_part_range FOR VALUES FROM (10000) TO (20000)');
|
|
tablename
|
|
-----------------------------
|
|
hypo_part_range_10000_20000
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_range_20000_30000', 'PARTITION OF hypo_part_range FOR VALUES FROM (20000) TO (30000)');
|
|
tablename
|
|
-----------------------------
|
|
hypo_part_range_20000_30000
|
|
(1 row)
|
|
|
|
-- Maintenance
|
|
-- -----------
|
|
VACUUM ANALYZE;
|
|
SELECT * FROM hypopg_analyze('hypo_part_range',100);
|
|
hypopg_analyze
|
|
----------------
|
|
|
|
(1 row)
|
|
|
|
-- Test hypothetical indexes on hypothetical partitioning behavior
|
|
-- ===============================================================
|
|
-- Indexes on root partitioning tree
|
|
-- ---------------------------------
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON part_range (id)');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON hypo_part_range(id)');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
-- Test on real tables
|
|
-- -------------------
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM part_range WHERE id = 42') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*part_range_1_10000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM part_range WHERE id > 28000') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*part_range_20000_30000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
-- Test on hypothetical tables
|
|
-- ---------------------------
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM hypo_part_range WHERE id = 42') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*hypo_part_range_1_10000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM hypo_part_range WHERE id > 28000') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*hypo_part_range_20000_30000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
-- Indexes on specific partitions
|
|
-- ------------------------------
|
|
SELECT * FROM hypopg_reset_index();
|
|
hypopg_reset_index
|
|
--------------------
|
|
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON part_range_1_10000 (id)');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON hypo_part_range_1_10000(id)');
|
|
nb
|
|
----
|
|
1
|
|
(1 row)
|
|
|
|
-- Test on real tables
|
|
-- -------------------
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM part_range WHERE id = 42') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*part_range_1_10000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM part_range WHERE id > 28000') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*part_range_20000_30000';
|
|
count
|
|
-------
|
|
0
|
|
(1 row)
|
|
|
|
-- Test on hypothetical tables
|
|
-- ---------------------------
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM hypo_part_range WHERE id = 42') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*hypo_part_range_1_10000';
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) FROM do_explain ('SELECT * FROM hypo_part_range WHERE id > 28000') e
|
|
WHERE e ~ 'Index.*<\d+>btree.*hypo_part_range_20000_30000';
|
|
count
|
|
-------
|
|
0
|
|
(1 row)
|
|
|
|
-- Sanity checks
|
|
-- -------------
|
|
SELECT * FROM hypopg_reset_index();
|
|
hypopg_reset_index
|
|
--------------------
|
|
|
|
(1 row)
|
|
|
|
-- No hypothetical on non-leaf partition
|
|
DROP TABLE IF EXISTS part_multi;
|
|
CREATE TABLE part_multi(dpt smallint, dt date, val text) PARTITION BY LIST (dpt);
|
|
CREATE TABLE part_multi_2 PARTITION OF part_multi FOR VALUES IN (2) PARTITION BY RANGE(dt);
|
|
CREATE TABLE part_multi_2_q1 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
|
|
CREATE TABLE part_multi_1 PARTITION OF part_multi FOR VALUES IN (1) PARTITION BY RANGE(dt);
|
|
CREATE TABLE part_multi_1_q2 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
|
|
CREATE TABLE part_multi_1_q1 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$) PARTITION BY RANGE (dt);
|
|
CREATE TABLE part_multi_1_q1_b PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$);
|
|
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
|
|
SELECT * FROM hypopg_partition_table('hypo_part_multi', 'PARTITION BY LIST (dpt)');
|
|
hypopg_partition_table
|
|
------------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1', 'PARTITION OF hypo_part_multi FOR VALUES IN (1)', 'PARTITION BY RANGE(dt)');
|
|
tablename
|
|
-------------------
|
|
hypo_part_multi_1
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q2', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
|
|
tablename
|
|
----------------------
|
|
hypo_part_multi_1_q2
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)','PARTITION BY RANGE (dt)');
|
|
tablename
|
|
----------------------
|
|
hypo_part_multi_1_q1
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_b', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$)');
|
|
tablename
|
|
------------------------
|
|
hypo_part_multi_1_q1_b
|
|
(1 row)
|
|
|
|
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
|
|
tablename
|
|
------------------------
|
|
hypo_part_multi_1_q1_a
|
|
(1 row)
|
|
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON part_multi_1 (dpt)');
|
|
ERROR: hypopg: cannot add hypothetical index on non-leaf or non-root partition
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON part_multi_1_q1 (dpt)');
|
|
ERROR: hypopg: cannot add hypothetical index on non-leaf or non-root partition
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON hypo_part_multi_1 (dpt)');
|
|
ERROR: hypopg: cannot add hypothetical index on non-leaf or non-root hypothetical partition
|
|
SELECT COUNT(*) AS nb FROM hypopg_create_index('CREATE INDEX ON hypo_part_multi_1_q1 (dpt)');
|
|
ERROR: hypopg: cannot add hypothetical index on non-leaf or non-root hypothetical partition
|
|
-- pk constraint check
|
|
CREATE TABLE t_pk(id integer primary key, val text) PARTITION BY LIST (val);
|
|
ERROR: insufficient columns in PRIMARY KEY constraint definition
|
|
DETAIL: PRIMARY KEY constraint on table "t_pk" lacks column "val" which is part of the partition key.
|
|
CREATE TABLE hypo_t_pk(id integer primary key, val text);
|
|
SELECT hypopg_partition_table('hypo_t_pk', 'PARTITION BY LIST (val)');
|
|
ERROR: hypopg: insufficient columns in unique constraint definition
|
|
DETAIL: unique constraint on table "hypo_t_pk" lacks column "val" which is part of the hypothetical partition key.
|
|
DROP TABLE hypo_t_pk;
|
|
-- unique constraint check
|
|
CREATE TABLE t_unique(id integer, val text) PARTITION BY LIST (val);
|
|
CREATE UNIQUE INDEX ON t_unique(id);
|
|
ERROR: insufficient columns in UNIQUE constraint definition
|
|
DETAIL: UNIQUE constraint on table "t_unique" lacks column "val" which is part of the partition key.
|
|
CREATE TABLE hypo_t_unique(id integer, val text);
|
|
CREATE UNIQUE INDEX ON hypo_t_unique(id);
|
|
SELECT hypopg_partition_table('hypo_t_unique', 'PARTITION BY LIST (val)');
|
|
ERROR: hypopg: insufficient columns in unique constraint definition
|
|
DETAIL: unique constraint on table "hypo_t_unique" lacks column "val" which is part of the hypothetical partition key.
|
|
DROP TABLE hypo_t_unique;
|
|
CREATE TABLE hypo_t_unique(id integer, val text);
|
|
SELECT count(*) FROM hypopg_create_index('CREATE UNIQUE INDEX on hypo_t_unique (id)');
|
|
count
|
|
-------
|
|
1
|
|
(1 row)
|
|
|
|
SELECT hypopg_partition_table('hypo_t_unique', 'PARTITION BY LIST (val)');
|
|
ERROR: hypopg: insufficient columns in unique hypothetical constraint definition
|
|
DETAIL: unique constraint on table "hypo_t_unique" lacks column "val" which is part of the hypothetical partition key.
|
|
DROP TABLE hypo_t_unique;
|
|
CREATE TABLE hypo_t_unique(id integer, val text);
|
|
SELECT hypopg_partition_table('hypo_t_unique', 'PARTITION BY LIST (val)');
|
|
hypopg_partition_table
|
|
------------------------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT count(*) FROM hypopg_create_index('CREATE UNIQUE INDEX on hypo_t_unique (id)');
|
|
ERROR: hypopg: insufficient columns in unique hypothetical constraint definition
|
|
DETAIL: unique hypothetical constraint on table "hypo_t_unique" lacks column "val" which is part of the hypothetical partition key.
|
|
DROP TABLE t_unique;
|
|
DROP TABLE hypo_t_unique;
|
|
-- constraint exclusion check
|
|
CREATE TABLE t_constrext(c circle, val text, EXCLUDE USING gist(c WITH &&)) PARTITION BY LIST (val);
|
|
ERROR: exclusion constraints are not supported on partitioned tables
|
|
LINE 1: CREATE TABLE t_constrext(c circle, val text, EXCLUDE USING g...
|
|
^
|
|
CREATE TABLE hypo_t_constrext(c circle, val text, EXCLUDE USING gist(c WITH &&));
|
|
SELECT hypopg_partition_table('hypo_t_constrext', 'PARTITION BY LIST (val)');
|
|
ERROR: exclusion constraints are not supported on hypothetically partitioned tables
|
|
DROP TABLE hypo_t_constrext;
|