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

-------

-> Seq Scan on range_rel_4

(8 rows)

/*

* Test CTE query

*/

EXPLAIN (COSTS OFF)

WITH ttt AS (SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-15')

SELECT * FROM ttt;

QUERY PLAN

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

CTE Scan on ttt

CTE ttt

-> Append

-> Seq Scan on range_rel_2

-> Index Scan using range_rel_3_dt_idx on range_rel_3

Index Cond: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)

(6 rows)

EXPLAIN (COSTS OFF)

WITH ttt AS (SELECT * FROM test.hash_rel WHERE value = 2)

SELECT * FROM ttt;

QUERY PLAN

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

CTE Scan on ttt

CTE ttt

-> Append

-> Seq Scan on hash_rel_2

Filter: (value = 2)

(5 rows)

/*

* Test split and merge

*/

Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)

(4 rows)

CREATE TABLE test.range_rel_test1 (

id SERIAL PRIMARY KEY,

dt TIMESTAMP,

txt TEXT,

abc INTEGER);

SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test1', '2013-01-01'::DATE, '2014-01-01'::DATE);

ERROR: Partition must have the exact same structure as parent P0001

CREATE TABLE test.range_rel_test2 (

id SERIAL PRIMARY KEY,

dt TIMESTAMP);

SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test2', '2013-01-01'::DATE, '2014-01-01'::DATE);

ERROR: Partition must have the exact same structure as parent P0001

/*

* Clean up

*/

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