Copyright (c) 2015-2016, Postgres Professional

Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group

pg_pathman is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Copyright (c) 2015-2017, Postgres Professional

Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALLTHE UNIVERSITY OF CALIFORNIABE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IFTHE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

IN NO EVENT SHALLPOSTGRES PROFESSIONALBE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IFPOSTGRES PROFESSIONAL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIASPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, ANDTHE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

POSTGRES PROFESSIONALSPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, ANDPOSTGRES PROFESSIONAL HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

pathman_rowmarks\

pathman_runtime_nodes\

pathman_utility_stmt_hooking\

pathman_calamity

pathman_calamity\

pathman_join_clause

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

[](https://travis-ci.org/postgrespro/pg_pathman)

[](https://badge.fury.io/pg/pg_pathman)

[](https://codecov.io/gh/postgrespro/pg_pathman)

[](https://raw.githubusercontent.com/postgrespro/pg_pathman/master/LICENSE)

It is essential to restart the PostgreSQL instance. After that, execute the following query in psql:

attributeTEXT,

partitions_countINTEGER,

partition_dataBOOLEAN DEFAULT TRUE,

partition_namesTEXT[] DEFAULTNULL,

tablespacesTEXT[] DEFAULTNULL)

partition_namesTEXT[] DEFAULTNULL,

tablespacesTEXT[] DEFAULTNULL)

Performs HASH partitioning for`relation` by integer key`attribute`. The`partitions_count` parameter specifies the number of partitions to create; it cannot be changed afterwards. If`partition_data` is`true` then all the data will be automatically copied from the parent table to partitions. Note that data migration may took a while to finish and the table will be locked until transaction commits. See`partition_table_concurrently()` for a lock-free way to migrate data. Partition creation callback is invoked for each partition if set beforehand (see`set_init_callback()`).

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

##Feedback

Do not hesitate to post your issues, questions and new ideas at the[issues](https://github.com/postgrespro/pg_pathman/issues) page.

Ildar Musin <i.musin@postgrespro.ru> Postgres Professional Ltd., Russia

Alexander Korotkov <a.korotkov@postgrespro.ru> Postgres Professional Ltd., Russia

Dmitry Ivanov <d.ivanov@postgrespro.ru> Postgres Professional Ltd., Russia

Ildar Musin <i.musin@postgrespro.ru> Postgres Professional Ltd., Russia

Alexander Korotkov <a.korotkov@postgrespro.ru> Postgres Professional Ltd., Russia

Dmitry Ivanov <d.ivanov@postgrespro.ru> Postgres Professional Ltd., Russia

Если у вас есть вопросы или предложения, а также если вы обнаружили ошибки, напишите нам в разделе[issues](https://github.com/postgrespro/pg_pathman/issues).

Ильдар Мусин <i.musin@postgrespro.ru> Postgres Professional, Россия

Александр Коротков <a.korotkov@postgrespro.ru> Postgres Professional, Россия

Дмитрий Иванов <d.ivanov@postgrespro.ru> Postgres Professional, Россия

Ильдар Мусин <i.musin@postgrespro.ru> Postgres Professional, Россия

Александр Коротков <a.korotkov@postgrespro.ru> Postgres Professional, Россия

Дмитрий Иванов <d.ivanov@postgrespro.ru> Postgres Professional, Россия

\set VERBOSITY terse

CREATE SCHEMA pathman;

CREATE EXTENSION pg_pathman SCHEMA pathman;

CREATE SCHEMA test;

/*

* Test push down a join clause into child nodes of append

*/

/* create test tables */

CREATE TABLE test.fk (

id1 INT NOT NULL,

id2 INT NOT NULL,

start_key INT,

end_key INT,

PRIMARY KEY (id1, id2));

CREATE TABLE test.mytbl (

id1 INT NOT NULL,

id2 INT NOT NULL,

key INT NOT NULL,

CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2),

PRIMARY KEY (id1, key));

SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);

create_hash_partitions

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

8

(1 row)

/* ...fill out with test data */

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

INSERT INTO test.mytbl VALUES (1, 1, 5), (1,1,6);

/* gather statistics on test tables to have deterministic plans */

ANALYZE test.fk;

ANALYZE test.mytbl;

/* run test queries */

EXPLAIN (COSTS OFF) /* test plan */

SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key

FROM test.mytbl m JOIN test.fk USING(id1, id2)

WHERE NOT key <@ int4range(6, end_key);

QUERY PLAN

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

Nested Loop

-> Seq Scan on fk

-> Custom Scan (RuntimeAppend)

-> Bitmap Heap Scan on mytbl_0 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_0_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_1 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_1_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_2 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_2_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_3 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_3_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_4 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_4_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_5 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_5_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_6 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_6_pkey

Index Cond: (id1 = fk.id1)

-> Bitmap Heap Scan on mytbl_7 m

Recheck Cond: (id1 = fk.id1)

Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))

-> Bitmap Index Scan on mytbl_7_pkey

Index Cond: (id1 = fk.id1)

(43 rows)

/* test joint data */

SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key

FROM test.mytbl m JOIN test.fk USING(id1, id2)

WHERE NOT key <@ int4range(6, end_key);

tableoid | id1 | id2 | key | start_key | end_key

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

test.mytbl_6 | 1 | 1 | 5 | |

(1 row)

DROP SCHEMA test CASCADE;

NOTICE: drop cascades to 10 other objects

DROP EXTENSION pg_pathman CASCADE;

DROP SCHEMA pathman CASCADE;

6 | hash_2 | 0 | 0

(2 rows)

/* Check dropped colums before partitioning */

CREATE TABLE copy_stmt_hooking.test2 (

a varchar(50),

b varchar(50),

t timestamp without time zone not null

);

ALTER TABLE copy_stmt_hooking.test2 DROP COLUMN a;

SELECT create_range_partitions('copy_stmt_hooking.test2',

't',

'2017-01-01 00:00:00'::timestamp,

interval '1 hour', 5, false

);

NOTICE: sequence "test2_seq" does not exist, skipping

create_range_partitions

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

5

(1 row)

COPY copy_stmt_hooking.test2(t) FROM stdin;

SELECT COUNT(*) FROM copy_stmt_hooking.test2;

count

-------

1

(1 row)

DROP SCHEMA copy_stmt_hooking CASCADE;

NOTICE: drop cascades to7 other objects

NOTICE: drop cascades to798 other objects

/*

* Test auto check constraint renaming

*/

\set VERBOSITY terse

CREATESCHEMApathman;

CREATE EXTENSION pg_pathman SCHEMA pathman;

CREATESCHEMAtest;

CREATETABLEtest.fk (

id1INTNOT NULL,

id2INTNOT NULL,

start_keyINT,

end_keyINT,

PRIMARY KEY (id1, id2));

CREATETABLEtest.mytbl (

id1INTNOT NULL,

id2INTNOT NULL,

keyINTNOT NULL,

CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCEStest.fk(id1, id2),

PRIMARY KEY (id1, key));

SELECTpathman.create_hash_partitions('test.mytbl','id1',8);

INSERT INTOtest.fkVALUES (1,1);

INSERT INTOtest.mytblVALUES (1,1,5), (1,1,6);

ANALYZEtest.fk;

ANALYZEtest.mytbl;

EXPLAIN (COSTS OFF)/* test plan*/

SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key

FROMtest.mytbl mJOINtest.fk USING(id1, id2)

WHERE NOT key<@ int4range(6, end_key);

SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key

FROMtest.mytbl mJOINtest.fk USING(id1, id2)

WHERE NOT key<@ int4range(6, end_key);

DROPSCHEMA test CASCADE;

DROP EXTENSION pg_pathman CASCADE;

DROPSCHEMA pathman CASCADE;

SELECTcount(*)FROM ONLYcopy_stmt_hooking.test;

SELECT*FROMcopy_stmt_hooking.testORDER BY val;

CREATETABLEcopy_stmt_hooking.test2 (

avarchar(50),

bvarchar(50),

ttimestamp without time zonenot null

);

ALTERTABLEcopy_stmt_hooking.test2 DROP COLUMN a;

SELECT create_range_partitions('copy_stmt_hooking.test2',

't',

'2017-01-01 00:00:00'::timestamp,

interval'1 hour',5, false

);

COPYcopy_stmt_hooking.test2(t)FROM stdin;

2017-02-0220:00:00

\.

SELECTCOUNT(*)FROMcopy_stmt_hooking.test2;

DROPSCHEMA copy_stmt_hooking CASCADE;

if (post_parse_analyze_hook!=pathman_post_parse_analysis_hook)

{

char*spl_value;/* value of "shared_preload_libraries" GUC */

Oidsave_userid;

intsave_sec_context;

boolneed_priv_escalation= !superuser();/* we might be a SU */

char*spl_value;/* value of "shared_preload_libraries" GUC */

if (need_priv_escalation)

{

GetUserIdAndSecContext(&save_userid,&save_sec_context);

SetUserIdAndSecContext(BOOTSTRAP_SUPERUSERID,

save_sec_context |SECURITY_LOCAL_USERID_CHANGE);

}

#ifPG_VERSION_NUM >=90600

spl_value=GetConfigOptionByName("shared_preload_libraries",NULL, false);

spl_value=GetConfigOptionByName("shared_preload_libraries",NULL);

if (need_priv_escalation)

SetUserIdAndSecContext(save_userid,save_sec_context);

ereport(ERROR,

(errmsg("extension conflict has been detected"),

errdetail("shared_preload_libraries = \"%s\"",spl_value),

root->append_rel_list=lappend(root->append_rel_list,appinfo);

child_rel->joininfo= (List*)adjust_appendrel_attrs(root,

(Node*)parent_rel->joininfo,

appinfo);

adjust_rel_targetlist_compat(root,child_rel,parent_rel,appinfo);

slot=myslot;

ExecSetSlotDescriptor(slot,RelationGetDescr(child_result_rel->ri_RelationDesc));

ExecStoreTuple(tuple,slot,InvalidBuffer, false);

skip_tuple= false;