(1 row)

/* Table in next explain should have number from this query */

SELECT currval('test.num_range_rel_seq');

currval

---------

6

(1 row)

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

QUERY PLAN

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

Append

-> Index Scan usingnum_range_rel_1_pkey onnum_range_rel_1

-> Index Scan usingnum_range_rel_6_pkey onnum_range_rel_6

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

(3 rows)

SELECT pathman.append_range_partition('test.num_range_rel');

append_range_partition

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

test.num_range_rel_6

test.num_range_rel_7

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 4000;

QUERY PLAN

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

Append

-> Seq Scan onnum_range_rel_6

-> Seq Scan onnum_range_rel_7

(2 rows)

SELECT pathman.prepend_range_partition('test.num_range_rel');

prepend_range_partition

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

test.num_range_rel_7

test.num_range_rel_8

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id < 0;

QUERY PLAN

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

Append

-> Seq Scan onnum_range_rel_7

-> Seq Scan onnum_range_rel_8

(2 rows)

SELECT pathman.drop_range_partition('test.num_range_rel_7');

SELECT pathman.drop_range_partition('test.num_range_rel_8');

drop_range_partition

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

test.num_range_rel_7

test.num_range_rel_8

(1 row)

SELECT pathman.drop_range_partition_expand_next('test.num_range_rel_4');

SELECT * FROM pathman.pathman_partition_list WHERE parent = 'test.num_range_rel'::regclass;

parent | partition | parttype | expr | range_min | range_max

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

test.num_range_rel | test.num_range_rel_1 | 2 | id | 0 | 1000

test.num_range_rel | test.num_range_rel_6 | 2 | id | 0 | 1000

test.num_range_rel | test.num_range_rel_2 | 2 | id | 1000 | 2000

test.num_range_rel | test.num_range_rel_3 | 2 | id | 2000 | 3000

test.num_range_rel | test.num_range_rel_6 | 2 | id | 3000 | 5000

test.num_range_rel | test.num_range_rel_7 | 2 | id | 3000 | 5000

(4 rows)

SELECT pathman.drop_range_partition_expand_next('test.num_range_rel_6');

SELECT pathman.drop_range_partition_expand_next('test.num_range_rel_7');

drop_range_partition_expand_next

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

SELECT * FROM pathman.pathman_partition_list WHERE parent = 'test.num_range_rel'::regclass;

parent | partition | parttype | expr | range_min | range_max

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

test.num_range_rel | test.num_range_rel_1 | 2 | id | 0 | 1000

test.num_range_rel | test.num_range_rel_6 | 2 | id | 0 | 1000

test.num_range_rel | test.num_range_rel_2 | 2 | id | 1000 | 2000

test.num_range_rel | test.num_range_rel_3 | 2 | id | 2000 | 3000

(3 rows)

SELECT pathman.append_range_partition('test.range_rel');

append_range_partition

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

test.range_rel_6

test.range_rel_7

(1 row)

SELECT pathman.prepend_range_partition('test.range_rel');

prepend_range_partition

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

test.range_rel_7

test.range_rel_8

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';

QUERY PLAN

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

Append

-> Index Scan usingrange_rel_7_dt_idx onrange_rel_7

-> Index Scan usingrange_rel_8_dt_idx onrange_rel_8

Index Cond: (dt >= 'Mon Dec 15 00:00:00 2014'::timestamp without time zone)

-> Index Scan usingrange_rel_1_dt_idx onrange_rel_1

-> Index Scan usingrange_rel_6_dt_idx onrange_rel_6

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

(5 rows)

SELECT pathman.drop_range_partition('test.range_rel_7');

SELECT pathman.drop_range_partition('test.range_rel_8');

drop_range_partition

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

test.range_rel_7

test.range_rel_8

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';

QUERY PLAN

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

Append

-> Index Scan usingrange_rel_1_dt_idx onrange_rel_1

-> Index Scan usingrange_rel_6_dt_idx onrange_rel_6

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

(3 rows)

SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-01'::DATE);

add_range_partition

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

test.range_rel_8

test.range_rel_9

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';

QUERY PLAN

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

Append

-> Index Scan usingrange_rel_8_dt_idx onrange_rel_8

-> Index Scan usingrange_rel_9_dt_idx onrange_rel_9

Index Cond: (dt >= 'Mon Dec 15 00:00:00 2014'::timestamp without time zone)

-> Index Scan usingrange_rel_1_dt_idx onrange_rel_1

-> Index Scan usingrange_rel_6_dt_idx onrange_rel_6

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

(5 rows)

Append

-> Index Scan using range_rel_archive_dt_idx on range_rel_archive

Index Cond: (dt >= 'Sat Nov 15 00:00:00 2014'::timestamp without time zone)

-> Seq Scan onrange_rel_8

-> Index Scan usingrange_rel_1_dt_idx onrange_rel_1

-> Seq Scan onrange_rel_9

-> Index Scan usingrange_rel_6_dt_idx onrange_rel_6

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

(6 rows)

QUERY PLAN

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

Append

-> Seq Scan onrange_rel_8

-> Index Scan usingrange_rel_1_dt_idx onrange_rel_1

-> Seq Scan onrange_rel_9

-> Index Scan usingrange_rel_6_dt_idx onrange_rel_6

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

(4 rows)

parent | partition | parttype | expr | range_min | range_max

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

test.range_rel | test.range_rel_minus_infinity | 2 | dt | | Mon Dec 01 00:00:00 2014

test.range_rel | test.range_rel_8 | 2 | dt | Mon Dec 01 00:00:00 2014 | Thu Jan 01 00:00:00 2015

test.range_rel | test.range_rel_1 | 2 | dt | Thu Jan 01 00:00:00 2015 | Sun Feb 01 00:00:00 2015

test.range_rel | test.range_rel_9 | 2 | dt | Mon Dec 01 00:00:00 2014 | Thu Jan 01 00:00:00 2015

test.range_rel | test.range_rel_6 | 2 | dt | Thu Jan 01 00:00:00 2015 | Sun Feb 01 00:00:00 2015

test.range_rel | test.range_rel_2 | 2 | dt | Sun Feb 01 00:00:00 2015 | Sun Mar 01 00:00:00 2015

test.range_rel | test.range_rel_3 | 2 | dt | Sun Mar 01 00:00:00 2015 | Wed Apr 01 00:00:00 2015

test.range_rel | test.range_rel_4 | 2 | dt | Wed Apr 01 00:00:00 2015 | Fri May 01 00:00:00 2015

test.range_rel | test.range_rel_6 | 2 | dt | Fri May 01 00:00:00 2015 | Mon Jun 01 00:00:00 2015

test.range_rel | test.range_rel_7 | 2 | dt | Fri May 01 00:00:00 2015 | Mon Jun 01 00:00:00 2015

test.range_rel | test.range_rel_plus_infinity | 2 | dt | Mon Jun 01 00:00:00 2015 |

(8 rows)

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

Append

-> Seq Scan on range_rel_minus_infinity

-> Seq Scan onrange_rel_8

-> Seq Scan onrange_rel_9

(3 rows)

EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-05-01';

QUERY PLAN

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

Append

-> Seq Scan onrange_rel_6

-> Seq Scan onrange_rel_7

-> Seq Scan on range_rel_plus_infinity

(3 rows)

DROP TABLE test.hash_rel CASCADE;

SELECT pathman.drop_partitions('test.num_range_rel');

NOTICE: 999 rows copied from test.num_range_rel_1

NOTICE: 1000 rows copied from test.num_range_rel_2

NOTICE: 1000 rows copied from test.num_range_rel_3

NOTICE: 999 rows copied from test.num_range_rel_6

drop_partitions

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

3

(1 row)

SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);

SELECT pathman.split_range_partition('test."RangeRel_6"', '2015-01-01'::DATE);

split_range_partition

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

{12-31-2014,01-02-2015}

(1 row)

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

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

split_range_partition

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

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

SELECT append_range_partition('test.range_rel');

append_range_partition

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

test.range_rel_14

test.range_rel_15

(1 row)

SELECT prepend_range_partition('test.range_rel');

prepend_range_partition

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

test.range_rel_15

test.range_rel_16

(1 row)

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

QUERY PLAN

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

Append

-> Seq Scan on range_rel_15

-> Seq Scan on range_rel_1

-> Seq Scan on range_rel_16

-> Seq Scan on range_rel_13

-> Seq Scan on range_rel_14

(4 rows)

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

Append

-> Seq Scan on range_rel_12

Filter: (dt > 'Wed Dec 15 00:00:00 2010'::timestamp without time zone)

-> Seq Scan onrange_rel_14

-> Seq Scan onrange_rel_15

(4 rows)

/* Temporary table for JOINs */

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

QUERY PLAN

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

Update onrange_rel_1 r

Update onrange_rel_13 r

-> Hash Join

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

-> Seq Scan on tmp t

-> Hash

-> Index Scan usingrange_rel_1_pkey onrange_rel_1 r

-> Index Scan usingrange_rel_13_pkey onrange_rel_13 r

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

(7 rows)

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

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

QUERY PLAN

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

Delete onrange_rel_1 r

Delete onrange_rel_13 r

-> Hash Join

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

-> Seq Scan on tmp t

-> Hash

-> Index Scan usingrange_rel_1_pkey onrange_rel_1 r

-> Index Scan usingrange_rel_13_pkey onrange_rel_13 r

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

(7 rows)

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

/* Create range partitions from whole range */

SELECT drop_partitions('test.range_rel');

NOTICE: 44 rows copied from test.range_rel_1

NOTICE: 31 rows copied from test.range_rel_3

NOTICE: 30 rows copied from test.range_rel_4

NOTICE: 31 rows copied from test.range_rel_5

NOTICE: 31 rows copied from test.range_rel_10

NOTICE: 30 rows copied from test.range_rel_11

NOTICE: 31 rows copied from test.range_rel_12

NOTICE:14 rows copied from test.range_rel_13

NOTICE:0 rows copied from test.range_rel_14

NOTICE:44 rows copied from test.range_rel_13

NOTICE:14 rows copied from test.range_rel_14

NOTICE: 0 rows copied from test.range_rel_15

NOTICE: 0 rows copied from test.range_rel_16

drop_partitions

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

14

(1 row)

SELECT split_range_partition('domains.dom_table_1', 50);

SELECT split_range_partition('domains.dom_table_14', 50);

split_range_partition

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

{1,201}

Append

-> Seq Scan on dom_table_13

-> Seq Scan on dom_table_11

-> Seq Scan on dom_table_1

-> Seq Scan on dom_table_14

-> Seq Scan on dom_table_15

-> Seq Scan on dom_table_3

-> Seq Scan on dom_table_4

-> Seq Scan on dom_table_5

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

domains.dom_table | domains.dom_table_13 | 2 | val | -199 | -99

domains.dom_table | domains.dom_table_11 | 2 | val | -99 | 1

domains.dom_table | domains.dom_table_1 | 2 | val | 1 | 50

domains.dom_table | domains.dom_table_14 | 2 | val | 50 | 201

domains.dom_table | domains.dom_table_14 | 2 | val | 1 | 50

domains.dom_table | domains.dom_table_15 | 2 | val | 50 | 201

domains.dom_table | domains.dom_table_3 | 2 | val | 201 | 301

domains.dom_table | domains.dom_table_4 | 2 | val | 301 | 401

domains.dom_table | domains.dom_table_5 | 2 | val | 401 | 501

domains.dom_table | domains.dom_table_9 | 2 | val | 801 | 901

domains.dom_table | domains.dom_table_10 | 2 | val | 901 | 1001

domains.dom_table | domains.dom_table_12 | 2 | val | 1001 | 1101

domains.dom_table | domains.dom_table_15 | 2 | val | 1101 | 1201

domains.dom_table | domains.dom_table_16 | 2 | val | 1101 | 1201

(14 rows)

SELECT drop_partitions('domains.dom_table');

NOTICE: 49 rows copied from domains.dom_table_1

NOTICE: 100 rows copied from domains.dom_table_3

NOTICE: 100 rows copied from domains.dom_table_4

NOTICE: 100 rows copied from domains.dom_table_5

NOTICE: 1 rows copied from domains.dom_table_11

NOTICE: 0 rows copied from domains.dom_table_12

NOTICE: 0 rows copied from domains.dom_table_13

NOTICE: 151 rows copied from domains.dom_table_14

NOTICE: 1 rows copied from domains.dom_table_15

NOTICE: 49 rows copied from domains.dom_table_14

NOTICE: 151 rows copied from domains.dom_table_15

NOTICE: 1 rows copied from domains.dom_table_16

drop_partitions

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

14