src/pg_compat.o$(WIN32RES)

EXTENSION = pg_pathman

EXTVERSION = 1.0

DATA_built =$(EXTENSION)--$(EXTVERSION).sql

EXTVERSION = 1.1

DATA_built = pg_pathman--$(EXTVERSION).sql

DATA = pg_pathman--1.0.sql pg_pathman--1.0--1.1.sql

PGFILEDESC = "pg_pathman - partitioning tool"

REGRESS = pathman_basic\

pathman_runtime_nodes\

pathman_callbacks\

pathman_domains\

pathman_foreign_keys\

pathman_permissions\

pathman_rowmarks

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

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

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

PG_CONFIG = pg_config

The`pg_pathman` module provides optimized partitioning mechanism and functions to manage partitions.

The extension is compatible with PostgreSQL 9.5 (9.6 support is coming soon).

The extension is compatible with PostgreSQL 9.5+.

**Partitioning** means splitting one large table into smaller pieces. Each row in such table is moved to a single partition according to the partitioning key. PostgreSQL supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT. For example:

CREATETABLEtest (idSERIALPRIMARY KEY, titleTEXT);

CREATETABLEtest_1 (CHECK ( id>=100AND id<200 )) INHERITS (test);

CREATETABLEtest_2 (CHECK ( id>=200AND id<300 )) INHERITS (test);

where`VARIABLE` is a partitioning key,`OP` is a comparison operator (supported operators are =, <, <=, >, >=),`CONST` is a scalar value. For example:

WHERE id=150

More interesting features are yet to come. Stay tuned!

* HASH and RANGE partitioning schemes;

* Both automatic and manual partition management;

* Support for integer, floating point, date and other types, including domains;

* Effective query planning for partitioned tables (JOINs, subselects etc);

*`RuntimeAppend` &`RuntimeMergeAppend` custom plan nodes to pick partitions at runtime;

*`PartitionFilter`: an efficient drop-in replacement for INSERT triggers;

* Automatic partition creation for new INSERTed data (only for RANGE partitioning);

* Improved`COPY FROM\TO` statement that is able to insert rows directly into partitions;

* UPDATE triggers generation out of the box (will be replaced with custom nodes too);

* User-defined callbacks for partition creation event handling;

* Non-blocking concurrent table partitioning;

* FDW support (foreign partitions);

* Various GUC toggles and configurable settings.

* Implement LIST partitioning scheme;

create_range_partitions(relation REGCLASS,

attributeTEXT,

start_value ANYELEMENT,

interval ANYELEMENT,

countINTEGER DEFAULTNULL

p_interval ANYELEMENT,

p_countINTEGER DEFAULTNULL

partition_dataBOOLEAN DEFAULT TRUE)

create_range_partitions(relation REGCLASS,

attributeTEXT,

start_value ANYELEMENT,

interval INTERVAL,

countINTEGER DEFAULTNULL,

p_interval INTERVAL,

p_countINTEGER DEFAULTNULL,

partition_dataBOOLEAN DEFAULT TRUE)

Performs RANGE partitioning for`relation` by partitioning key`attribute`.`start_value` argument specifies initial value,`interval` sets the range of values in a single partition,`count` is the number of premade partitions (if not set then pathman tries to determine it based on attribute values). Partition creation callback is invoked for each partition if set beforehand.

attributeTEXT,

start_value ANYELEMENT,

end_value ANYELEMENT,

interval ANYELEMENT,

p_interval ANYELEMENT,

partition_dataBOOLEAN DEFAULT TRUE)

create_partitions_from_range(relation REGCLASS,

attributeTEXT,

start_value ANYELEMENT,

end_value ANYELEMENT,

interval INTERVAL,

p_interval INTERVAL,

partition_dataBOOLEAN DEFAULT TRUE)

Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Partition creation callback is invoked for each partition if set beforehand.

partition_table_concurrently(relation REGCLASS)

partition_table_concurrently(relation REGCLASS,

batch_sizeINTEGER DEFAULT1000,

sleep_time FLOAT8 DEFAULT1.0)

Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity.

Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity. If the worker is unable to lock rows of a batch, it sleeps for`sleep_time` seconds before the next attempt and tries again up to 60 times, and quits if it's still unable to lock the batch.

stop_concurrent_part_task(relation REGCLASS)

split_range_partition(partition REGCLASS,

value ANYELEMENT,

split_value ANYELEMENT,

partition_nameTEXT DEFAULTNULL)

Split RANGE`partition` in two by`value`. Partition creation callback is invoked for a new partition if available.

Split RANGE`partition` in two by`split_value`. Partition creation callback is invoked for a new partition if available.

merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

Merge two adjacent RANGE partitions. First, data from`partition2` is copied to`partition1`, then`partition2` is removed.

append_range_partition(p_relation REGCLASS,

append_range_partition(parent REGCLASS,

partition_nameTEXT DEFAULTNULL,

tablespaceTEXT DEFAULTNULL)

Append new RANGE partition with`pathman_config.range_interval` as interval.

prepend_range_partition(p_relation REGCLASS,

prepend_range_partition(parent REGCLASS,

partition_nameTEXT DEFAULTNULL,

tablespaceTEXT DEFAULTNULL)

(3 rows)

- You can turn foreign tables into partitions using the`attach_range_partition()` function. Rows that were meant to be inserted into parent will be redirected to foreign partitions (as usual, PartitionFilter will be involved), though by default it is prohibited to insert rows into partitions provided not by`postgres_fdw`. Only superuser is allowed to set`pg_pathman.insert_into_fdw` GUC variable.

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

There are several user-accessible[GUC](https://www.postgresql.org/docs/9.5/static/config-setting.html) variables designed to toggle the whole module or specific custom nodes on and off:

-`pg_pathman.enable` --- disable (or enable)`pg_pathman` completely

-`pg_pathman.enable` --- disable (or enable)`pg_pathman`**completely**

-`pg_pathman.enable_runtimeappend` --- toggle`RuntimeAppend` custom node on\off

-`pg_pathman.enable_runtimemergeappend` --- toggle`RuntimeMergeAppend` custom node on\off

-`pg_pathman.enable_partitionfilter` --- toggle`PartitionFilter` custom node on\off

-`pg_pathman.override_copy` --- toggle COPY statement hooking on\off

To**permanently** disable`pg_pathman` for some previously partitioned table, use the`disable_pathman_for()` function:

SELECT disable_pathman_for('range_rel');

All sections and data will remain unchanged and will be handled by the standard PostgreSQL inheritance mechanism.

INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;

INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;

SELECT create_range_partitions('messages', 'id', 1, 100, 2);

WARNING: foreign key'replies_message_id_fkey' references relation'messages'

WARNING: foreign key"replies_message_id_fkey" references relation"messages"

ERROR: relation "messages" is referenced from other relations

ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;

SELECT create_range_partitions('messages', 'id', 1, 100, 2);

\set VERBOSITY terse

CREATE EXTENSION pg_pathman;

CREATE SCHEMA permissions;

CREATE ROLE user1 LOGIN;

CREATE ROLE user2 LOGIN;

GRANT USAGE, CREATE ON SCHEMA permissions TO user1;

GRANT USAGE, CREATE ON SCHEMA permissions TO user2;

ALTER DEFAULT PRIVILEGES FOR ROLE user1

IN SCHEMA permissions

GRANT SELECT, INSERT ON TABLES

TO user2;

/* Switch to #1 */

SET ROLE user1;

CREATE TABLE permissions.user1_table(id serial, a int);

INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;

/* Should fail */

SET ROLE user2;

SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);

NOTICE: sequence "user1_table_seq" does not exist, skipping

WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"

ERROR: new row violates row-level security policy for table "pathman_config"

/* Should be ok */

SET ROLE user1;

SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);

NOTICE: sequence "user1_table_seq" does not exist, skipping

create_range_partitions

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

2

(1 row)

/* Should be able to see */

SET ROLE user2;

SELECT * FROM pathman_config;

partrel | attname | parttype | range_interval

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

permissions.user1_table | id | 2 | 10

(1 row)

SELECT * FROM pathman_config_params;

partrel | enable_parent | auto | init_callback

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

permissions.user1_table | f | t | -

(1 row)

/* Should fail */

SET ROLE user2;

SELECT set_enable_parent('permissions.user1_table', true);

WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"

ERROR: new row violates row-level security policy for table "pathman_config_params"

SELECT set_auto('permissions.user1_table', false);

WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"

ERROR: new row violates row-level security policy for table "pathman_config_params"

/* Should fail */

SET ROLE user2;

DELETE FROM pathman_config

WHERE partrel = 'permissions.user1_table'::regclass;

WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"

/* No rights to insert, should fail */

SET ROLE user2;

INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);

/* Have rights, should be ok (bgw connects as user1) */

SET ROLE user1;

GRANT INSERT ON permissions.user1_table TO user2;

SET ROLE user2;

INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;

id | a

----+---

35 | 0

(1 row)

SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;

relacl

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

{user1=arwdDxt/user1,user2=ar/user1}

(1 row)

/* Try to drop partition, should fail */

SELECT drop_range_partition('permissions.user1_table_4');

ERROR: must be owner of relation user1_table_4

/* Disable automatic partition creation */

SET ROLE user1;

SELECT set_auto('permissions.user1_table', false);

set_auto

----------

(1 row)

/* Partition creation, should fail */

SET ROLE user2;

INSERT INTO permissions.user1_table (id, a) VALUES (55, 0) RETURNING *;

ERROR: no suitable partition for key '55'

/* Finally drop partitions */

SET ROLE user1;

SELECT drop_partitions('permissions.user1_table');

NOTICE: function permissions.user1_table_upd_trig_func() does not exist, skipping

NOTICE: 10 rows copied from permissions.user1_table_1

NOTICE: 10 rows copied from permissions.user1_table_2

NOTICE: 0 rows copied from permissions.user1_table_3

NOTICE: 2 rows copied from permissions.user1_table_4

drop_partitions

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

4

(1 row)

/* Switch to #2 */

SET ROLE user2;

/* Test ddl event trigger */

CREATE TABLE permissions.user2_table(id serial);

SELECT create_hash_partitions('permissions.user2_table', 'id', 3);

create_hash_partitions

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

3

(1 row)

INSERT INTO permissions.user2_table SELECT generate_series(1, 30);

SELECT drop_partitions('permissions.user2_table');

NOTICE: function permissions.user2_table_upd_trig_func() does not exist, skipping

NOTICE: 9 rows copied from permissions.user2_table_0

NOTICE: 11 rows copied from permissions.user2_table_1

NOTICE: 10 rows copied from permissions.user2_table_2

drop_partitions

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

3

(1 row)

/* Finally reset user */

RESET ROLE;

DROP OWNED BY user1;

DROP OWNED BY user2;

DROP USER user1;

DROP USER user2;

DROP SCHEMA permissions CASCADE;

DROP EXTENSION pg_pathman;

v_init_callbackREGPROCEDURE;

PERFORM @extschema@.validate_relname(parent_relid);

IF partition_data= true THEN

PERFORM @extschema@.prevent_relation_modification(parent_relid);

PERFORM @extschema@.lock_partitioned_relation(parent_relid);

END IF;

PERFORM @extschema@.validate_relname(parent_relid);

attribute :=lower(attribute);

PERFORM @extschema@.common_relation_checks(parent_relid, attribute);