PolarDB:pg_pathman

Append a partition

Appends a new partition at the high end of the range, using the interval from pathman_config. Note: the tablespace parameter is accepted in the function signature but cannot be specified.

append_range_partition(
    parent         REGCLASS,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Example:

SELECT append_range_partition('part_test'::regclass);
-- Creates part_test_25 covering 2018-10-25 to 2018-11-25

Prepend a partition

Prepends a new partition at the low end of the range:

prepend_range_partition(
    parent         REGCLASS,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Example:

SELECT prepend_range_partition('part_test'::regclass);
-- Creates part_test_26 covering 2016-09-25 to 2016-10-25

Add a partition with an explicit range

Creates a partition for any non-overlapping range, including gaps in the existing range:

add_range_partition(
    relation       REGCLASS,
    start_value    ANYELEMENT,
    end_value      ANYELEMENT,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Example — add a partition for January 2020 without filling the gap between 2018 and 2020:

SELECT add_range_partition(
    'part_test'::regclass,
    '2020-01-01 00:00:00'::timestamp,
    '2020-02-01 00:00:00'::timestamp
);
-- Creates part_test_27 covering 2020-01-01 to 2020-02-01

Disable the primary table

After migrating all data to partitions, exclude the primary table from execution plans:

set_enable_parent(relation REGCLASS, value BOOLEAN)

The default depends on the partition_data parameter used during initial partitioning:

• If partition_data was true, data was migrated immediately and the primary table is disabled automatically.

• If partition_data was false, the primary table remains enabled until you disable it explicitly.

Example:

SELECT set_enable_parent('part_test', false);

Enable or disable automatic partition propagation

For range partitioning, pg_pathman can create new partitions automatically when inserted data falls outside existing partition bounds.

set_auto(relation REGCLASS, value BOOLEAN)
-- Default: true (enabled)

Example:

SELECT set_auto('part_test', false);

Disable pg_pathman for a table

Remove pg_pathman management from a primary table. This deletes the table's entry from pathman_config and drops any associated triggers, but leaves existing partitions and their data intact.

SELECT disable_pathman_for('part_test');

After disabling, execution plans revert to standard PostgreSQL inheritance behavior (including the primary table in scans even if it is empty):

EXPLAIN SELECT * FROM part_test WHERE crt_time = '2017-06-25 00:00:00'::timestamp;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.00 rows=2 width=45)
   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
   ->  Seq Scan on part_test_10  (cost=0.00..16.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)

Configure a partition creation callback

Register a callback function that pg_pathman calls each time a partition is created (for both hash and range partitions):

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

The callback must have this signature:

part_init_callback(args JSONB) RETURNS VOID

The args JSON object contains different fields depending on the partition type:

/* Range partition */
{
    "parent":    "abc",
    "parttype":  "2",
    "partition": "abc_4",
    "range_max": "401",
    "range_min": "301"
}

/* Hash partition */
{
    "parent":    "abc",
    "parttype":  "1",
    "partition": "abc_0"
}

Example: log partition creation events

1. Create a callback function that records partition metadata to an audit table:

    CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS
    $$
    DECLARE
    BEGIN
        CREATE TABLE IF NOT EXISTS rec_part_ddl(
            id        serial primary key,
            parent    name,
            parttype  int,
            partition name,
            range_max text,
            range_min text
        );
        IF ($1->>'parttype')::int = 1 THEN
            INSERT INTO rec_part_ddl(parent, parttype, partition)
            VALUES (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
        ELSIF ($1->>'parttype')::int = 2 THEN
            INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min)
            VALUES (
                ($1->>'parent')::name,
                ($1->>'parttype')::int,
                ($1->>'partition')::name,
                $1->>'range_max',
                $1->>'range_min'
            );
        END IF;
    END;
    $$ LANGUAGE plpgsql STRICT;

2. Register the callback and create partitions:

    CREATE TABLE tt(id int, info text, crt_time timestamp NOT NULL);
   
    SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc);
   
    SELECT create_range_partitions(
        'tt'::regclass,
        'crt_time',
        '2016-10-25 00:00:00'::timestamp,
        interval '1 month',
        24,
        false
    );

3. Verify the callback was invoked for each partition:

    SELECT * FROM rec_part_ddl;

   Expected output (24 rows, one per partition):

     id | parent | parttype | partition |      range_max      |      range_min
    ----+--------+----------+-----------+---------------------+---------------------
      1 | tt     |        2 | tt_1      | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
      2 | tt     |        2 | tt_2      | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
    ...
     24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
    (24 rows)