/* Test that everything works fine without schemas */

CREATE EXTENSION pg_pathman;

/* Hash */

CREATE TABLE hash_rel (

CREATE TABLEtest.hash_rel (

idSERIAL PRIMARY KEY,

valueINTEGER NOT NULL);

INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;

SELECT create_hash_partitions('hash_rel', 'value', 3);

INSERT INTOtest.hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;

SELECT create_hash_partitions('test.hash_rel', 'value', 3);

create_hash_partitions

------------------------

3

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE id = 1234;

QUERY PLAN

------------------------------------------------------

Append

(7 rows)

/* Range */

CREATE TABLE range_rel (

CREATE TABLEtest.range_rel (

idSERIAL PRIMARY KEY,

dtTIMESTAMP NOT NULL,

valueINTEGER);

INSERT INTO range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;

SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);

NOTICE: sequence "range_rel_seq" does not exist, skipping

INSERT INTO test.range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;

SELECT create_range_partitions('test.range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);

create_range_partitions

-------------------------

12

(1 row)

SELECT merge_range_partitions('range_rel_1', 'range_rel_2');

SELECT merge_range_partitions('test.range_rel_1', 'test.range_rel_2');

merge_range_partitions

------------------------

(1 row)

SELECT split_range_partition('range_rel_1', '2010-02-15'::date);

SELECT split_range_partition('test.range_rel_1', '2010-02-15'::date);

split_range_partition

-------------------------

{01-01-2010,03-01-2010}

(1 row)

SELECT append_range_partition('range_rel');

SELECT append_range_partition('test.range_rel');

append_range_partition

------------------------

public.range_rel_14

test.range_rel_14

(1 row)

SELECT prepend_range_partition('range_rel');

SELECT prepend_range_partition('test.range_rel');

prepend_range_partition

-------------------------

public.range_rel_15

test.range_rel_15

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';

EXPLAIN (COSTS OFF) SELECT * FROMtest.range_rel WHERE dt < '2010-03-01';

QUERY PLAN

--------------------------------

Append

-> Seq Scan on range_rel_13

(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';

EXPLAIN (COSTS OFF) SELECT * FROMtest.range_rel WHERE dt > '2010-12-15';

QUERY PLAN

--------------------------------------------------------------------------------

Append

(4 rows)

/* Temporary table for JOINs */

CREATE TABLE tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);

INSERT INTO tmp VALUES (1, 1), (2, 2);

CREATE TABLEtest.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);

INSERT INTOtest.tmp VALUES (1, 1), (2, 2);

/* Test UPDATE and DELETE */

EXPLAIN (COSTS OFF) UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';

EXPLAIN (COSTS OFF) UPDATEtest.range_rel SET value = 111 WHERE dt = '2010-06-15';

QUERY PLAN

--------------------------------------------------------------------------------

Update on range_rel_6

-> Seq Scan on range_rel_6

Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)

(3 rows)

UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';

SELECT * FROM range_rel WHERE dt = '2010-06-15';

UPDATEtest.range_rel SET value = 111 WHERE dt = '2010-06-15';

SELECT * FROMtest.range_rel WHERE dt = '2010-06-15';

id | dt | value

-----+--------------------------+-------

166 | Tue Jun 15 00:00:00 2010 | 111

(1 row)

EXPLAIN (COSTS OFF) DELETE FROM range_rel WHERE dt = '2010-06-15';

EXPLAIN (COSTS OFF) DELETE FROMtest.range_rel WHERE dt = '2010-06-15';

QUERY PLAN

--------------------------------------------------------------------------------

Delete on range_rel_6

-> Seq Scan on range_rel_6

Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)

(3 rows)

DELETE FROM range_rel WHERE dt = '2010-06-15';

SELECT * FROM range_rel WHERE dt = '2010-06-15';

DELETE FROMtest.range_rel WHERE dt = '2010-06-15';

SELECT * FROMtest.range_rel WHERE dt = '2010-06-15';

id | dt | value

----+----+-------

(0 rows)

EXPLAIN (COSTS OFF) UPDATE range_rel r SET value = t.value FROM tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;

EXPLAIN (COSTS OFF) UPDATEtest.range_rel r SET value = t.value FROMtest.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;

QUERY PLAN

--------------------------------------------------------------------------------------------

Update on range_rel_1 r

Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)

(7 rows)

UPDATE range_rel r SET value = t.value FROM tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;

EXPLAIN (COSTS OFF) DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;

UPDATEtest.range_rel r SET value = t.value FROMtest.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;

EXPLAIN (COSTS OFF) DELETE FROMtest.range_rel r USINGtest.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;

QUERY PLAN

--------------------------------------------------------------------------------------------

Delete on range_rel_1 r

Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)

(7 rows)

DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;

DELETE FROMtest.range_rel r USINGtest.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;

/* Create range partitions from whole range */

SELECT drop_partitions('range_rel');

NOTICE: functionpublic.range_rel_upd_trig_func() does not exist, skipping

NOTICE: 44 rows copied from range_rel_1

NOTICE: 31 rows copied from range_rel_3

NOTICE: 30 rows copied from range_rel_4

NOTICE: 31 rows copied from range_rel_5

NOTICE: 29 rows copied from range_rel_6

NOTICE: 31 rows copied from range_rel_7

NOTICE: 31 rows copied from range_rel_8

NOTICE: 30 rows copied from range_rel_9

NOTICE: 31 rows copied from range_rel_10

NOTICE: 30 rows copied from range_rel_11

NOTICE: 31 rows copied from range_rel_12

NOTICE: 14 rows copied from range_rel_13

NOTICE: 0 rows copied from range_rel_14

NOTICE: 0 rows copied from range_rel_15

SELECT drop_partitions('test.range_rel');

NOTICE: functiontest.range_rel_upd_trig_func() does not exist, skipping

NOTICE: 44 rows copied fromtest.range_rel_1

NOTICE: 31 rows copied fromtest.range_rel_3

NOTICE: 30 rows copied fromtest.range_rel_4

NOTICE: 31 rows copied fromtest.range_rel_5

NOTICE: 29 rows copied fromtest.range_rel_6

NOTICE: 31 rows copied fromtest.range_rel_7

NOTICE: 31 rows copied fromtest.range_rel_8

NOTICE: 30 rows copied fromtest.range_rel_9

NOTICE: 31 rows copied fromtest.range_rel_10

NOTICE: 30 rows copied fromtest.range_rel_11

NOTICE: 31 rows copied fromtest.range_rel_12

NOTICE: 14 rows copied fromtest.range_rel_13

NOTICE: 0 rows copied fromtest.range_rel_14

NOTICE: 0 rows copied fromtest.range_rel_15

drop_partitions

-----------------

14

(1 row)

SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);

SELECT create_partitions_from_range('test.range_rel', 'id', 1, 1000, 100);

create_partitions_from_range

------------------------------

10

(1 row)

SELECT drop_partitions('range_rel', TRUE);

NOTICE: functionpublic.range_rel_upd_trig_func() does not exist, skipping

SELECT drop_partitions('test.range_rel', TRUE);

NOTICE: functiontest.range_rel_upd_trig_func() does not exist, skipping

drop_partitions

-----------------

10

(1 row)

SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);

SELECT create_partitions_from_range('test.range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);

create_partitions_from_range

------------------------------

12

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt = '2015-12-15';

EXPLAIN (COSTS OFF) SELECT * FROMtest.range_rel WHERE dt = '2015-12-15';

QUERY PLAN

--------------------------------------------------------------------------------

Append

(3 rows)

/* Test foreign keys */

CREATE TABLE messages(id SERIAL PRIMARY KEY, msg TEXT);

CREATE TABLE replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES messages(id), msg TEXT);

INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;

INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;

SELECT create_range_partitions('messages', 'id', 1, 100, 2);

WARNING: foreign key "replies_message_id_fkey" references relation "messages"

ERROR: relation "messages" is referenced from other relations

ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;

SELECT create_range_partitions('messages', 'id', 1, 100, 2);

CREATE TABLEtest.messages(id SERIAL PRIMARY KEY, msg TEXT);

CREATE TABLEtest.replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCEStest.messages(id), msg TEXT);

INSERT INTOtest.messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;

INSERT INTOtest.replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;

SELECT create_range_partitions('test.messages', 'id', 1, 100, 2);

WARNING: foreign key "replies_message_id_fkey" references relation "test.messages"

ERROR: relation "test.messages" is referenced from other relations

ALTER TABLEtest.replies DROP CONSTRAINT replies_message_id_fkey;

SELECT create_range_partitions('test.messages', 'id', 1, 100, 2);

NOTICE: sequence "messages_seq" does not exist, skipping

create_range_partitions

-------------------------

2

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM messages;

EXPLAIN (COSTS OFF) SELECT * FROMtest.messages;

QUERY PLAN

------------------------------

Append

-> Seq Scan on messages_1

-> Seq Scan on messages_2

(3 rows)

DROP TABLE messages, replies CASCADE;

DROP TABLEtest.messages,test.replies CASCADE;

NOTICE: drop cascades to 2 other objects

/* Special test case (quals generation) -- fixing commit f603e6c5 */

CREATE TABLE special_case_1_ind_o_s(val serial, comment text);

INSERT INTO special_case_1_ind_o_s SELECT generate_series(1, 200), NULL;

SELECT create_range_partitions('special_case_1_ind_o_s', 'val', 1, 50);

CREATE TABLEtest.special_case_1_ind_o_s(val serial, comment text);

INSERT INTOtest.special_case_1_ind_o_s SELECT generate_series(1, 200), NULL;

SELECT create_range_partitions('test.special_case_1_ind_o_s', 'val', 1, 50);

NOTICE: sequence "special_case_1_ind_o_s_seq" does not exist, skipping

create_range_partitions

-------------------------

4

(1 row)

INSERT INTO special_case_1_ind_o_s_2 SELECT 75 FROM generate_series(1, 6000);

CREATE INDEX ON special_case_1_ind_o_s_2 (val, comment);

VACUUM ANALYZE special_case_1_ind_o_s_2;

EXPLAIN (COSTS OFF) SELECT * FROM special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

INSERT INTOtest.special_case_1_ind_o_s_2 SELECT 75 FROM generate_series(1, 6000);

CREATE INDEX ONtest.special_case_1_ind_o_s_2 (val, comment);

VACUUM ANALYZEtest.special_case_1_ind_o_s_2;

EXPLAIN (COSTS OFF) SELECT * FROMtest.special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

QUERY PLAN

--------------------------------------------------------------------------------------------------

Append

Index Cond: ((val < 75) AND (comment = 'a'::text))

(5 rows)

SELECT set_enable_parent('special_case_1_ind_o_s', true);

SELECT set_enable_parent('test.special_case_1_ind_o_s', true);

set_enable_parent

-------------------

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

EXPLAIN (COSTS OFF) SELECT * FROMtest.special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

QUERY PLAN

--------------------------------------------------------------------------------------------------

Append

Index Cond: ((val < 75) AND (comment = 'a'::text))

(7 rows)

SELECT set_enable_parent('special_case_1_ind_o_s', false);

SELECT set_enable_parent('test.special_case_1_ind_o_s', false);

set_enable_parent

-------------------

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

EXPLAIN (COSTS OFF) SELECT * FROMtest.special_case_1_ind_o_s WHERE val < 75 AND comment = 'a';

QUERY PLAN

--------------------------------------------------------------------------------------------------

Append

Index Cond: ((val < 75) AND (comment = 'a'::text))

(5 rows)

/* Test index scans on child relation under enable_parent is set */

CREATE TABLE test.index_on_childs(c1 integer not null, c2 integer);

CREATE INDEX ON test.index_on_childs(c2);

INSERT INTO test.index_on_childs SELECT i, (random()*10000)::integer FROM generate_series(1, 10000) i;

SELECT create_range_partitions('test.index_on_childs', 'c1', 1, 1000, 0, false);

NOTICE: sequence "index_on_childs_seq" does not exist, skipping

create_range_partitions

-------------------------

0

(1 row)

SELECT add_range_partition('test.index_on_childs', 1, 1000, 'test.index_on_childs_1_1K');

add_range_partition

---------------------------

test.index_on_childs_1_1K

(1 row)

SELECT append_range_partition('test.index_on_childs', 'test.index_on_childs_1K_2K');

append_range_partition

----------------------------

test.index_on_childs_1K_2K

(1 row)

SELECT append_range_partition('test.index_on_childs', 'test.index_on_childs_2K_3K');

append_range_partition

----------------------------

test.index_on_childs_2K_3K

(1 row)

SELECT append_range_partition('test.index_on_childs', 'test.index_on_childs_3K_4K');

append_range_partition

----------------------------

test.index_on_childs_3K_4K

(1 row)

SELECT append_range_partition('test.index_on_childs', 'test.index_on_childs_4K_5K');

append_range_partition

----------------------------

test.index_on_childs_4K_5K

(1 row)

SELECT set_enable_parent('test.index_on_childs', true);

set_enable_parent

-------------------

(1 row)

VACUUM ANALYZE test.index_on_childs;

EXPLAIN (COSTS OFF) SELECT * FROM test.index_on_childs WHERE c1 > 100 AND c1 < 2500 AND c2 = 500;

QUERY PLAN

------------------------------------------------------------------------------

Append

-> Index Scan using index_on_childs_c2_idx on index_on_childs

Index Cond: (c2 = 500)

Filter: ((c1 > 100) AND (c1 < 2500))

-> Index Scan using index_on_childs_1_1k_c2_idx on index_on_childs_1_1k

Index Cond: (c2 = 500)

Filter: (c1 > 100)

-> Index Scan using index_on_childs_1k_2k_c2_idx on index_on_childs_1k_2k

Index Cond: (c2 = 500)

-> Index Scan using index_on_childs_2k_3k_c2_idx on index_on_childs_2k_3k

Index Cond: (c2 = 500)

Filter: (c1 < 2500)

(12 rows)

DROP SCHEMA test CASCADE;

NOTICE: drop cascades to13 other objects

NOTICE: drop cascades to45 other objects

DROP EXTENSION pg_pathman CASCADE;

NOTICE: drop cascades to 3 other objects

DROP SCHEMA pathman CASCADE;