create_hash_partitions(relation REGCLASS,

attributeTEXT,

partitions_countINTEGER,

partition_dataBOOLEAN DEFAULT TRUE)

partition_dataBOOLEAN DEFAULT TRUE,

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()`).

replace_hash_partition(old_partitionREGCLASS,

new_partitionREGCLASS,

lock_parentBOOL DEFAULT TRUE)

replace_hash_partition(old_partition REGCLASS,

new_partition REGCLASS,

lock_parent BOOL DEFAULT TRUE)

Replaces specified partition of HASH-partitioned table with another table. The`lock_parent` parameter will prevent any INSERT/UPDATE/ALTER TABLE queries to parent table.

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

merge_range_partitions(partitionsREGCLASS[])

merge_range_partitions(partitions REGCLASS[])

Merge several adjacent RANGE partitions (partitions must be specified in ascending or descending order). All the data will be accumulated in the first partition.

Filter: (c1 < 2500)

(12 rows)

/* Test create_range_partitions() +relnames */

/* Test create_range_partitions() +partition_names */

CREATE TABLE test.provided_part_names(id INT NOT NULL);

INSERT INTO test.provided_part_names SELECT generate_series(1, 10);

SELECT create_hash_partitions('test.provided_part_names', 'id', 2,

relnames := ARRAY['p1', 'p2']::TEXT[]);/* ok */

partition_names := ARRAY['p1', 'p2']::TEXT[]);/* ok */

create_hash_partitions

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

2

DELETE FROM calamity.part_test;

/* test function create_hash_partitions() */

SELECT create_hash_partitions('calamity.part_test', 'val', 2,

relnames := ARRAY[]::TEXT[]); /* not ok */

ERROR: 'relnames' and 'tablespaces' may not be empty

partition_names := ARRAY[]::TEXT[]); /* not ok */

ERROR: 'partition_names' and 'tablespaces' may not be empty

SELECT create_hash_partitions('calamity.part_test', 'val', 2,

relnames := ARRAY[ 'p1', NULL ]::TEXT[]); /* not ok */

ERROR: 'relnames' and 'tablespaces' may not contain NULLs

partition_names := ARRAY[ 'p1', NULL ]::TEXT[]); /* not ok */

ERROR: 'partition_names' and 'tablespaces' may not contain NULLs

SELECT create_hash_partitions('calamity.part_test', 'val', 2,

relnames := ARRAY[ ['p1'], ['p2'] ]::TEXT[]); /* not ok */

ERROR: 'relnames' and 'tablespaces' may contain only 1 dimension

partition_names := ARRAY[ ['p1'], ['p2'] ]::TEXT[]); /* not ok */

ERROR: 'partition_names' and 'tablespaces' may contain only 1 dimension

SELECT create_hash_partitions('calamity.part_test', 'val', 2,

relnames := ARRAY['calamity.p1']::TEXT[]); /* not ok */

ERROR: size of 'relnames' must be equal to 'partitions_count'

partition_names := ARRAY['calamity.p1']::TEXT[]); /* not ok */

ERROR: size of 'partition_names' must be equal to 'partitions_count'

SELECT create_hash_partitions('calamity.part_test', 'val', 2,

tablespaces := ARRAY['abcd']::TEXT[]); /* not ok */

ERROR: size of 'tablespaces' must be equal to 'partitions_count'

/* test case when naming sequence does not exist */

CREATE TABLE calamity.no_naming_seq(val INT4 NOT NULL);

SELECT add_to_pathman_config('calamity.no_naming_seq', 'val', '100');

add_to_pathman_config

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

t

(1 row)

select add_range_partition(' calamity.no_naming_seq', 10, 20);

ERROR: auto naming sequence "no_naming_seq_seq" does not exist

DROP TABLE calamity.no_naming_seq CASCADE;

/* test (-inf, +inf) partition creation */

CREATE TABLE calamity.double_inf(val INT4 NOT NULL);

SELECT add_to_pathman_config('calamity.double_inf', 'val', '10');

add_to_pathman_config

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

t

(1 row)

select add_range_partition('calamity.double_inf', NULL::INT4, NULL::INT4,

partition_name := 'double_inf_part');

ERROR: cannot create partition with range (-inf, +inf)

DROP TABLE calamity.double_inf CASCADE;

/* test stub 'enable_parent' value for PATHMAN_CONFIG_PARAMS */

INSERT INTO calamity.part_test SELECT generate_series(1, 30);

SELECT create_range_partitions('calamity.part_test', 'val', 1, 10);

DROP TABLE callbacks.abc CASCADE;

NOTICE: drop cascades to 5 other objects

/* create table in public schema */

CREATE TABLE callbacks.abc(a serial, b int);

SELECT set_init_callback('callbacks.abc',

'callbacks.abc_on_part_created_callback(jsonb)');

set_init_callback

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

(1 row)

SELECT create_range_partitions('callbacks.abc', 'a', 1, 100, 2);

WARNING: callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_1", "range_max": "101", "range_min": "1", "parent_schema": "callbacks", "partition_schema": "callbacks"}

WARNING: callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_2", "range_max": "201", "range_min": "101", "parent_schema": "callbacks", "partition_schema": "callbacks"}

create_range_partitions

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

2

(1 row)

DROP TABLE callbacks.abc CASCADE;

NOTICE: drop cascades to 2 other objects

/* test the temprary deletion of callback function */

CREATE TABLE callbacks.abc(a serial, b int);

SELECT set_init_callback('callbacks.abc',

attributeTEXT,

partitions_countINTEGER,

partition_dataBOOLEAN DEFAULT TRUE,

relnamesTEXT[] DEFAULTNULL,

partition_namesTEXT[] DEFAULTNULL,

tablespacesTEXT[] DEFAULTNULL)

RETURNSINTEGERAS

$$

PERFORM @extschema@.create_hash_partitions_internal(parent_relid,

attribute,

partitions_count,

relnames,

partition_names,

tablespaces);

parent_relidREGCLASS,

attributeTEXT,

partitions_countINTEGER,

relnamesTEXT[] DEFAULTNULL,

partition_namesTEXT[] DEFAULTNULL,

tablespacesTEXT[] DEFAULTNULL)

RETURNS VOIDAS'pg_pathman','create_hash_partitions_internal'

LANGUAGE C;

VACUUM ANALYZEtest.index_on_childs;

EXPLAIN (COSTS OFF)SELECT*FROMtest.index_on_childsWHERE c1>100AND c1<2500AND c2=500;

CREATETABLEtest.provided_part_names(idINTNOT NULL);

INSERT INTOtest.provided_part_namesSELECT generate_series(1,10);

SELECT create_hash_partitions('test.provided_part_names','id',2,

relnames := ARRAY['p1','p2']::TEXT[]);/* ok*/

partition_names := ARRAY['p1','p2']::TEXT[]);/* ok*/

SELECT partitionFROM pathman_partition_list

WHERE parent='test.provided_part_names'::REGCLASS

SELECT create_hash_partitions('calamity.part_test','val',2,

relnames := ARRAY[]::TEXT[]);/* not ok*/

partition_names := ARRAY[]::TEXT[]);/* not ok*/

SELECT create_hash_partitions('calamity.part_test','val',2,

relnames := ARRAY['p1',NULL ]::TEXT[]);/* not ok*/

partition_names := ARRAY['p1',NULL ]::TEXT[]);/* not ok*/

SELECT create_hash_partitions('calamity.part_test','val',2,

relnames := ARRAY[ ['p1'], ['p2'] ]::TEXT[]);/* not ok*/

partition_names := ARRAY[ ['p1'], ['p2'] ]::TEXT[]);/* not ok*/

SELECT create_hash_partitions('calamity.part_test','val',2,

relnames := ARRAY['calamity.p1']::TEXT[]);/* not ok*/

partition_names := ARRAY['calamity.p1']::TEXT[]);/* not ok*/

SELECT create_hash_partitions('calamity.part_test','val',2,

tablespaces := ARRAY['abcd']::TEXT[]);/* not ok*/

CREATETABLEcalamity.no_naming_seq(val INT4NOT NULL);

SELECT add_to_pathman_config('calamity.no_naming_seq','val','100');

select add_range_partition(' calamity.no_naming_seq',10,20);

DROPTABLEcalamity.no_naming_seq CASCADE;

CREATETABLEcalamity.double_inf(val INT4NOT NULL);

SELECT add_to_pathman_config('calamity.double_inf','val','10');

select add_range_partition('calamity.double_inf',NULL::INT4,NULL::INT4,

partition_name :='double_inf_part');

DROPTABLEcalamity.double_inf CASCADE;

INSERT INTOcalamity.part_testSELECT generate_series(1,30);

SELECT create_range_partitions('calamity.part_test','val',1,10);

DROPTABLEcallbacks.abc CASCADE;

CREATETABLEcallbacks.abc(aserial, bint);

SELECT set_init_callback('callbacks.abc',

'callbacks.abc_on_part_created_callback(jsonb)');

SELECT create_range_partitions('callbacks.abc','a',1,100,2);

DROPTABLEcallbacks.abc CASCADE;

CREATETABLEcallbacks.abc(aserial, bint);

SELECT set_init_callback('callbacks.abc',

{

Datumpart_num;

Oidpart_seq_relid;

char*part_seq_relname;

Oidsave_userid;

intsave_sec_context;

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

char*relname;

intattempts_cnt=1000;

part_seq_relid=get_relname_relid(build_sequence_name_internal(parent_relid),

parent_nsp);

part_seq_relname=build_sequence_name_internal(parent_relid);

part_seq_relid=get_relname_relid(part_seq_relname,parent_nsp);

if (!OidIsValid(part_seq_relid))

elog(ERROR,"auto naming sequence \"%s\" does not exist",part_seq_relname);

if (need_priv_escalation)

if (and_oper->args==NIL)

elog(ERROR,"cannot createinfiniterangeconstraint");

elog(ERROR,"cannot createpartition withrange(-inf, +inf)");

return (Node*)and_oper;

}

i;

char**relnames=NULL,

**tablespaces=NULL;

intrelnames_size=0,

tablespaces_size=0;

RangeVar**rangevars=NULL;

char**partition_names=NULL,

**tablespaces=NULL;

intpartition_names_size=0,

tablespaces_size=0;

RangeVar**rangevars=NULL;

if (get_pathman_relation_info(parent_relid))

if (!PG_ARGISNULL(3))

relnames=deconstruct_text_array(PG_GETARG_DATUM(3),&relnames_size);

partition_names=deconstruct_text_array(PG_GETARG_DATUM(3),&partition_names_size);

if (!PG_ARGISNULL(4))

tablespaces=deconstruct_text_array(PG_GETARG_DATUM(4),&tablespaces_size);

if (relnames&&relnames_size!=partitions_count)

elog(ERROR,"size of 'relnames' must be equal to 'partitions_count'");

if (partition_names&&partition_names_size!=partitions_count)

elog(ERROR,"size of 'partition_names' must be equal to 'partitions_count'");

if (tablespaces&&tablespaces_size!=partitions_count)

elog(ERROR,"size of 'tablespaces' must be equal to 'partitions_count'");

if (relnames)

if (partition_names)

{

rangevars=palloc(sizeof(RangeVar)*relnames_size);

for (i=0;i<relnames_size;i++)

rangevars=palloc(sizeof(RangeVar)*partition_names_size);

for (i=0;i<partition_names_size;i++)

{

List*nl=stringToQualifiedNameList(relnames[i]);

List*nl=stringToQualifiedNameList(partition_names[i]);

rangevars[i]=makeRangeVarFromNameList(nl);

}

}

DeepFreeArray(relnames,relnames_size);

DeepFreeArray(partition_names,partition_names_size);

DeepFreeArray(tablespaces,tablespaces_size);

DeepFreeArray(rangevars,relnames_size);

DeepFreeArray(rangevars,partition_names_size);

PG_RETURN_VOID();

}

if (ARR_NDIM(array_ptr)>1)

elog(ERROR,"'relnames' and 'tablespaces' may contain only 1 dimension");

elog(ERROR,"'partition_names' and 'tablespaces' may contain only 1 dimension");

get_typlenbyvalalign(ARR_ELEMTYPE(array_ptr),

&elemlen,&elembyval,&elemalign);

for (i=0;i<arr_size;i++)

{

if (elem_nulls[i])

elog(ERROR,"'relnames' and 'tablespaces' may not contain NULLs");

elog(ERROR,"'partition_names' and 'tablespaces' may not contain NULLs");

strings[i]=TextDatumGetCString(elem_values[i]);

}

returnstrings;

}

elseelog(ERROR,"'relnames' and 'tablespaces' may not be empty");

elseelog(ERROR,"'partition_names' and 'tablespaces' may not be empty");

returnNULL;