\set VERBOSITY terse

CREATE SCHEMA test;

CREATE EXTENSION pathman;

CREATE TABLE hash_rel (

CREATE TABLEtest.hash_rel (

id SERIAL PRIMARY KEY,

value INTEGER);

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

NOTICE: trigger "hash_rel_insert_trigger" for relation "hash_rel" does not exist, skipping

NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping

NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping

NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping

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

NOTICE: trigger "test_hash_rel_insert_trigger" for relation "test.hash_rel" does not exist, skipping

NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping

create_hash_partitions

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

(1 row)

CREATE TABLE range_rel (

CREATE TABLEtest.range_rel (

id SERIAL PRIMARY KEY,

dt TIMESTAMP,

txt TEXT);

SELECT create_range_partitions('range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);

SELECT create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);

NOTICE: sequence "range_rel_seq" does not exist, skipping

create_range_partitions

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

(1 row)

CREATE TABLE num_range_rel (

CREATE TABLEtest.num_range_rel (

id SERIAL PRIMARY KEY,

txt TEXT);

SELECT create_range_partitions('num_range_rel', 'id', 0, 1000, 3);

SELECT create_range_partitions('test.num_range_rel', 'id', 0, 1000, 3);

NOTICE: sequence "num_range_rel_seq" does not exist, skipping

create_range_partitions

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

(1 row)

INSERT INTO hash_rel VALUES (1, 1);

INSERT INTO hash_rel VALUES (2, 2);

INSERT INTO hash_rel VALUES (3, 3);

INSERT INTO hash_rel VALUES (4, 4);

INSERT INTO hash_rel VALUES (5, 5);

INSERT INTO hash_rel VALUES (6, 6);

INSERT INTO num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;

INSERT INTOtest.hash_rel VALUES (1, 1);

INSERT INTOtest.hash_rel VALUES (2, 2);

INSERT INTOtest.hash_rel VALUES (3, 3);

INSERT INTOtest.hash_rel VALUES (4, 4);

INSERT INTOtest.hash_rel VALUES (5, 5);

INSERT INTOtest.hash_rel VALUES (6, 6);

INSERT INTOtest.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;

VACUUM;

SET enable_indexscan = OFF;

SET enable_bitmapscan = OFF;

SET enable_seqscan = ON;

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel;

QUERY PLAN

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

Append

-> Seq Scan on hash_rel_2

(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE value = 2;

QUERY PLAN

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

Append

-> Seq Scan on hash_rel_2

Filter: (value = 2)

(3 rows)

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE value = 2 OR value = 1;

QUERY PLAN

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

Append

Filter: (value = 2)

(5 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id > 2500;

QUERY PLAN

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

Append

-> Seq Scan on num_range_rel_4

(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id >= 1000 AND id < 3000;

QUERY PLAN

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

Append

-> Seq Scan on num_range_rel_2

-> Seq Scan on num_range_rel_3

(3 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id >= 1500 AND id < 2500;

QUERY PLAN

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

Append

Filter: (id < 2500)

(5 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);

QUERY PLAN

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

Append

SET enable_indexscan = ON;

SET enable_bitmapscan = OFF;

SET enable_seqscan = OFF;

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel;

QUERY PLAN

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

Append

-> Seq Scan on hash_rel_2

(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE value = 2;

QUERY PLAN

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

Append

-> Seq Scan on hash_rel_2

Filter: (value = 2)

(3 rows)

EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;

EXPLAIN (COSTS OFF) SELECT * FROMtest.hash_rel WHERE value = 2 OR value = 1;

QUERY PLAN

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

Append

Filter: (value = 2)

(5 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id > 2500;

QUERY PLAN

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

Append

-> Seq Scan on num_range_rel_4

(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id >= 1000 AND id < 3000;

QUERY PLAN

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

Append

-> Seq Scan on num_range_rel_2

-> Seq Scan on num_range_rel_3

(3 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id >= 1500 AND id < 2500;

QUERY PLAN

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

Append

Index Cond: (id < 2500)

(5 rows)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);

QUERY PLAN

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

Append

* Test split and merge

*/

/* Split first partition in half */

SELECT split_range_partition('num_range_rel_1', 500);

SELECT split_range_partition('test.num_range_rel_1', 500);

NOTICE: Creating new partition...

NOTICE: Copying data to new partition...

NOTICE: Altering original partition...

{0,1000}

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id BETWEEN 100 AND 700;

QUERY PLAN

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

Append

Index Cond: (id <= 700)

(5 rows)

SELECT split_range_partition('range_rel_1', '2015-01-15'::DATE);

SELECT split_range_partition('test.range_rel_1', '2015-01-15'::DATE);

NOTICE: Creating new partition...

NOTICE: Copying data to new partition...

NOTICE: Altering original partition...

(1 row)

/* Merge two partitions into one */

SELECT merge_range_partitions('num_range_rel_1', 'num_range_rel_' || currval('num_range_rel_seq'));

SELECT merge_range_partitions('test.num_range_rel_1', 'test.num_range_rel_' || currval('test.num_range_rel_seq'));

NOTICE: Altering first partition...

NOTICE: Copying data...

NOTICE: Dropping second partition...

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;

EXPLAIN (COSTS OFF) SELECT * FROMtest.num_range_rel WHERE id BETWEEN 100 AND 700;

QUERY PLAN

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

Append

-> Index Scan using num_range_rel_1_pkey on num_range_rel_1

Index Cond: ((id >= 100) AND (id <= 700))

(3 rows)

SELECT merge_range_partitions('range_rel_1', 'range_rel_' || currval('range_rel_seq'));

SELECT merge_range_partitions('test.range_rel_1', 'test.range_rel_' || currval('test.range_rel_seq'));

NOTICE: Altering first partition...

NOTICE: Copying data...

NOTICE: Dropping second partition...

(1 row)

/* Append and prepend partitions */

SELECT append_partition('num_range_rel');

SELECT append_partition('test.num_range_rel');

NOTICE: Appending new partition...

NOTICE: Done!

append_partition

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

(1 row)

SELECT prepend_partition('num_range_rel');

SELECT prepend_partition('test.num_range_rel');

NOTICE: Prepending new partition...

NOTICE: Done!

prepend_partition

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

(1 row)

SELECT append_partition('range_rel');

SELECT append_partition('test.range_rel');

NOTICE: Appending new partition...

NOTICE: Done!

append_partition

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

(1 row)

SELECT prepend_partition('range_rel');

SELECT prepend_partition('test.range_rel');

NOTICE: Prepending new partition...

NOTICE: Done!

prepend_partition

/*

* Clean up

*/

SELECT drop_hash_partitions('hash_rel');

NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping

NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping

SELECT drop_hash_partitions('test.hash_rel');

drop_hash_partitions

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

(1 row)

DROP TABLE hash_rel CASCADE;

DROP TABLEtest.hash_rel CASCADE;

NOTICE: drop cascades to 3 other objects

SELECT drop_range_partitions('num_range_rel');

SELECT drop_range_partitions('test.num_range_rel');

drop_range_partitions

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

(1 row)

DROP TABLE num_range_rel CASCADE;

DROP TABLE test.num_range_rel CASCADE;

NOTICE: drop cascades to 6 other objects

DROP EXTENSION pathman;

) RETURNS VOIDAS

$$

relation :=lower(relation);

relation :=validate_relname(relation);

IF EXISTS (SELECT*FROM pathman_configWHERE relname= relation) THEN

RAISE EXCEPTION'Reltion "%s" has already been partitioned', relation;

PERFORM on_create_partitions(relid);

PERFORM on_create_partitions(relation::regclass::integer);

END

$$ LANGUAGE plpgsql;

END $body$ LANGUAGE plpgsql;';

triggerTEXT :='

FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';

FOR EACH ROW EXECUTE PROCEDURE %2$s_hash_insert_trigger_func();';

fieldsTEXT;

fields_formatTEXT;

insert_stmtTEXT;

func := format(func, relation, attr, partitions_count, insert_stmt);

trigger := format(trigger, relation::regclass::text);

trigger := format(trigger, get_schema_qualified_name(relation::regclass), relation);

EXECUTE func;

EXECUTE trigger;

END

RETURNS VOIDAS

$$

EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %1$s', relation::regclass::text);

EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %s'

, get_schema_qualified_name(relation::regclass)

, relation);

EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func()', relation::regclass::text);