CREATE EXTENSION pg_pathman SCHEMA pathman;

CREATE SCHEMA test;

CREATE TABLE test.hash_rel (

idSERIAL PRIMARY KEY,

valueINTEGER);

idSERIAL PRIMARY KEY,

valueINTEGER);

INSERT INTO test.hash_rel VALUES (1, 1);

INSERT INTO test.hash_rel VALUES (2, 2);

INSERT INTO test.hash_rel VALUES (3, 3);

(1 row)

CREATE TABLE test.range_rel (

idSERIAL PRIMARY KEY,

dtTIMESTAMP,

txtTEXT);

idSERIAL PRIMARY KEY,

dtTIMESTAMP,

txtTEXT);

CREATE INDEX ON test.range_rel (dt);

INSERT INTO test.range_rel (dt, txt)

SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;

(1 row)

CREATE TABLE test.num_range_rel (

idSERIAL PRIMARY KEY,

txtTEXT);

idSERIAL PRIMARY KEY,

txtTEXT);

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

NOTICE: sequence "num_range_rel_seq" does not exist, skipping

NOTICE: Copying data to partitions...

(1 row)

INSERT INTO test.num_range_rel

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

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

SELECT COUNT(*) FROM test.num_range_rel;

count

-------

NOTICE: drop cascades to 7 other objects

/* Test automatic partition creation */

CREATE TABLE test.range_rel (

idSERIAL PRIMARY KEY,

dtTIMESTAMP NOT NULL);

idSERIAL PRIMARY KEY,

dtTIMESTAMP NOT NULL);

SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);

NOTICE: Copying data to partitions...

create_range_partitions

/* Check overlaps */

CREATE TABLE test.num_range_rel (

idSERIAL PRIMARY KEY,

txtTEXT);

idSERIAL PRIMARY KEY,

txtTEXT);

SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 1000, 1000, 4);

NOTICE: Copying data to partitions...

create_range_partitions

CREATE EXTENSION pg_pathman;

/* Hash */

CREATE TABLE hash_rel (

idSERIAL PRIMARY KEY,

valueINTEGER NOT NULL);

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);

NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping

/* Range */

CREATE TABLE range_rel (

id SERIAL PRIMARY KEY,

dt TIMESTAMP NOT NULL);

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

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

NOTICE: Copying data to partitions...

-> Seq Scan on range_rel_14

(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);

/* Test UPDATE and DELETE */

EXPLAIN (COSTS OFF) UPDATE 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';

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';

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';

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;

QUERY PLAN

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

Update on range_rel_1 r

-> Hash Join

Hash Cond: (t.id = r.id)

-> Seq Scan on tmp t

-> Hash

-> Index Scan using range_rel_1_pkey 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;

QUERY PLAN

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

Delete on range_rel_1 r

-> Hash Join

Hash Cond: (t.id = r.id)

-> Seq Scan on tmp t

-> Hash

-> Index Scan using range_rel_1_pkey 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;

/* Create range partitions from whole range */

SELECT drop_range_partitions('range_rel');

NOTICE: 0 rows copied from range_rel_15

NOTICE: 30 rows copied from range_rel_9

NOTICE: 31 rows copied from range_rel_8

NOTICE: 31 rows copied from range_rel_7

NOTICE:30 rows copied from range_rel_6

NOTICE:29 rows copied from range_rel_6

NOTICE: 31 rows copied from range_rel_5

NOTICE: 30 rows copied from range_rel_4

NOTICE: 31 rows copied from range_rel_3

NOTICE:45 rows copied from range_rel_1

NOTICE:44 rows copied from range_rel_1

drop_range_partitions

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

14