pathman_runtime_nodes\

pathman_callbacks\

pathman_domains\

pathman_foreign_keys

pathman_foreign_keys\

pathman_rowmarks

EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add

EXTRA_CLEAN =$(EXTENSION)--$(EXTVERSION).sql ./isolation_output

* Provide a way to create user-defined partition creation\destruction callbacks (issue[#22](https://github.com/postgrespro/pg_pathman/issues/22))

* Implement LIST partitioning scheme;

* Optimize hash join (both tables are partitioned by join key).

Create new RANGE partition for`relation` with specified range bounds.

drop_range_partition(partitionTEXT)

drop_range_partition(partitionTEXT, delete_dataBOOLEAN DEFAULT TRUE)

Drop RANGE partition and all its data.

Drop RANGE partition and allofits data if`delete_data` is true.

attach_range_partition(relation REGCLASS,

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT0)

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE).

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). The callback must have the following signature:`part_init_callback(args JSONB) RETURNS VOID`. Parameter`arg` consists of several fields whose presence depends on partitioning type:

{

"parent":"abc",

"parttype":"2",

"partition":"abc_4",

"range_max":"401",

"range_min":"301"

}

{

"parent":"abc",

"parttype":"1",

"partition":"abc_0"

}

parttypeINTEGERNOT NULL,

range_intervalTEXT,

CHECK (parttypeIN (1,2))/* check for allowed part types*/

);

CHECK (parttypeIN (1,2))/* check for allowed part types*/ );

This table stores a list of partitioned tables.

partrel REGCLASSNOT NULLPRIMARY KEY,

enable_parentBOOLEANNOT NULL DEFAULT TRUE,

autoBOOLEANNOT NULL DEFAULT TRUE,

init_callback REGPROCEDURENOT NULL DEFAULT0

);

init_callback REGPROCEDURENOT NULL DEFAULT0);

This table stores optional parameters which override standard behavior.

CREATEOR REPLACE FUNCTION@extschema@.show_partition_list()

CREATE OR REPLACEFUNCTIONshow_partition_list()

RETURNS TABLE (

parent REGCLASS,

partition REGCLASS,

(1 row)

-`pathman_partition_list` in conjunction with`drop_range_partition()` can be used to drop RANGE partitions in a more flexible way compared to good old`DROP TABLE`:

SELECT drop_range_partition(partition, false)/* move data to parent*/

FROM pathman_partition_list

WHERE parent='part_test'::regclassAND range_min::int<500;

NOTICE:1 rows copiedfrom part_test_11

NOTICE:100 rows copiedfrom part_test_1

NOTICE:100 rows copiedfrom part_test_2

drop_range_partition

dummy_test_11

dummy_test_1

dummy_test_2

(3 rows)

Consider an example of HASH partitioning. First create a table with some integer column:

To access parent table use ONLY modifier:

EXPLAINSELECT*FROM ONLY items;

QUERY PLAN

idSERIAL,

dtTIMESTAMPNOT NULL,

levelINTEGER,

msgTEXT

);

msgTEXT);

CREATEINDEXON journal(dt);

idINTEGERNOT NULL,

dtTIMESTAMPNOT NULL,

levelINTEGER,

msgTEXT

)SERVER archive_server;

msgTEXT)

SERVER archive_server;

SELECT attach_range_partition('journal','journal_archive','2014-01-01'::date,'2015-01-01'::date);

Here's an example of the query performing filtering by partitioning key:

SELECT*FROM journalWHERE dt>='2015-06-01'AND dt<'2015-06-03';

id | dt | level | msg

CREATE EXTENSION pg_pathman;

CREATE SCHEMA rowmarks;

CREATE TABLE rowmarks.first(id int NOT NULL);

CREATE TABLE rowmarks.second(id int NOT NULL);

INSERT INTO rowmarks.first SELECT generate_series(1, 10);

INSERT INTO rowmarks.second SELECT generate_series(1, 10);

SELECT create_hash_partitions('rowmarks.first', 'id', 5);

create_hash_partitions

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

5

(1 row)

/* Not partitioned */

SELECT * FROM rowmarks.second ORDER BY id FOR UPDATE;

id

----

1

2

3

4

5

6

7

8

9

10

(10 rows)

/* Simple case (plan) */

EXPLAIN (COSTS OFF)

SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;

QUERY PLAN

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

LockRows

-> Sort

Sort Key: first_0.id

-> Append

-> Seq Scan on first_0

-> Seq Scan on first_1

-> Seq Scan on first_2

-> Seq Scan on first_3

-> Seq Scan on first_4

(9 rows)

/* Simple case (execution) */

SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;

id

----

1

2

3

4

5

6

7

8

9

10

(10 rows)

SELECT FROM rowmarks.first ORDER BY id FOR UPDATE;

--

(10 rows)

SELECT tableoid > 0 FROM rowmarks.first ORDER BY id FOR UPDATE;

?column?

----------

t

t

t

t

t

t

t

t

t

t

(10 rows)

/* A little harder (plan) */

EXPLAIN (COSTS OFF)

SELECT * FROM rowmarks.first

WHERE id = (SELECT id FROM rowmarks.first

ORDER BY id

OFFSET 10 LIMIT 1

FOR UPDATE)

FOR SHARE;

QUERY PLAN

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

LockRows

InitPlan 1 (returns $1)

-> Limit

-> LockRows

-> Sort

Sort Key: first_0.id

-> Append

-> Seq Scan on first_0

-> Seq Scan on first_1

-> Seq Scan on first_2

-> Seq Scan on first_3

-> Seq Scan on first_4

-> Custom Scan (RuntimeAppend)

-> Seq Scan on first_0 first

Filter: (id = $1)

-> Seq Scan on first_1 first

Filter: (id = $1)

-> Seq Scan on first_2 first

Filter: (id = $1)

-> Seq Scan on first_3 first

Filter: (id = $1)

-> Seq Scan on first_4 first

Filter: (id = $1)

(23 rows)

/* A little harder (execution) */

SELECT * FROM rowmarks.first

WHERE id = (SELECT id FROM rowmarks.first

ORDER BY id

OFFSET 5 LIMIT 1

FOR UPDATE)

FOR SHARE;

id

----

6

(1 row)

/* Two tables (plan) */

EXPLAIN (COSTS OFF)

SELECT * FROM rowmarks.first

WHERE id = (SELECT id FROM rowmarks.second

ORDER BY id

OFFSET 5 LIMIT 1

FOR UPDATE)

FOR SHARE;

QUERY PLAN

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

LockRows

InitPlan 1 (returns $1)

-> Limit

-> LockRows

-> Sort

Sort Key: second.id

-> Seq Scan on second

-> Custom Scan (RuntimeAppend)

-> Seq Scan on first_0 first

Filter: (id = $1)

-> Seq Scan on first_1 first

Filter: (id = $1)

-> Seq Scan on first_2 first

Filter: (id = $1)

-> Seq Scan on first_3 first

Filter: (id = $1)

-> Seq Scan on first_4 first

Filter: (id = $1)

(18 rows)

/* Two tables (execution) */

SELECT * FROM rowmarks.first

WHERE id = (SELECT id FROM rowmarks.second

ORDER BY id

OFFSET 5 LIMIT 1

FOR UPDATE)

FOR SHARE;

id

----

6

(1 row)

DROP SCHEMA rowmarks CASCADE;

NOTICE: drop cascades to 7 other objects

DETAIL: drop cascades to table rowmarks.first

drop cascades to table rowmarks.second

drop cascades to table rowmarks.first_0

drop cascades to table rowmarks.first_1

drop cascades to table rowmarks.first_2

drop cascades to table rowmarks.first_3

drop cascades to table rowmarks.first_4

DROP EXTENSION pg_pathman;

$$

DECLARE

v_recRECORD;

v_rowsINTEGER;

v_rowsBIGINT;

v_part_countINTEGER :=0;

conf_num_delINTEGER;

v_relkindCHAR;

ORDER BY inhrelidASC)

LOOP

IF NOT delete_data THEN

EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)

INSERT INTO %s SELECT * FROM part_data',

v_rec.tbl::TEXT,

parent_relid::text);

EXECUTE format('INSERT INTO %s SELECT * FROM %s',

parent_relid::TEXT,

v_rec.tbl::TEXT);

GET DIAGNOSTICS v_rows= ROW_COUNT;