This topic describes common usage scenarios of the pg_pathman plug-in.

Background information

To improve the performance of partitioned tables, the pg_pathman plug-in is introduced to PolarDB for PostgreSQL. This plug-in enables you to manage partitions and optimize partitioning.

Create the pg_pathman extension

test=# create extension pg_pathman;
CREATE EXTENSION

View installed extensions

Run the following commands to view installed extensions and the version of the pg_pathman plug-in.

test=# \dx
                     List of installed extensions
    Name    | Version |   Schema   |           Description
------------+---------+------------+----------------------------------
 pg_pathman | 1.5     | public     | Partitioning tool for PostgreSQL
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Upgrade the plug-in

PolarDB for PostgreSQL upgrades the plug-in on a regular basis to provide better database services. To upgrade the plug-in, perform the following steps:

  • Upgrade the corresponding cluster to the latest version. You need to submit a ticket to perform the upgrade because this function is in the development phase.
  • Execute the following statements to complete the update:
    ALTER EXTENSION pg_pathman UPDATE;
    SET pg_pathman.enable = t;

Features

  • Support for hash and range partitioning.
  • Support for automatic and manual partition management. In automatic partition management, the system uses functions to create partitions and migrate data in primary tables to partitions. In manual partition management, you can use functions to attach existing tables to partitioned tables or detach tables from partitioned tables.
  • Support for several partition fields including custom domains and common data types such as integer, floating point, and date.
  • Effective query planning for partitioned tables by using joins and subselects.
  • RuntimeAppend and RuntimeMergeAppend custom plan nodes to pick partitions at runtime.
  • PartitionFilter: an efficient drop-in replacement for INSERT triggers.
  • Automatic partition creation for newly inserted data (only for range partitioning).
  • Support for the COPY FROM/TO statement, allowing direct read or write operations on partitions.
  • Partition fields update. To update partition fields, you need to add a trigger. If you do not need to update partition fields, we recommend that you do not add the trigger to avoid negative impacts on performance.
  • User-defined callback functions are automatically triggered when partitions are created.
  • Non-blocking concurrent table partitioning and automatic data migration from primary tables to partitions in the background.
  • Support for postgres_fdw or any Foreign Data Wrappers (FDW) by configuring the pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw) parameter.

Usage

For more information, see GitHub.

Views and tables

The pg_pathman plug-in uses functions to maintain partitioned tables and creates views for viewing the status of partitioned tables, as described in the following examples:

  1. pathman_config
    CREATE TABLE IF NOT EXISTS pathman_config (
        partrel         REGCLASS NOT NULL PRIMARY KEY,  -- The OID of the primary table.
        attname         TEXT NOT NULL,  -- The column name of the partition.
        parttype        INTEGER NOT NULL,  -- The type of the partition (hash or range).
        range_interval  TEXT,  -- The interval of range partitions.
    
        CHECK (parttype IN (1, 2)) /* check for allowed part types */ );
  2. pathman_config_params
    CREATE TABLE IF NOT EXISTS pathman_config_params (
        partrel        REGCLASS NOT NULL PRIMARY KEY,  -- The OID of the primary table.
        enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to filter the primary table in the optimizer.
        auto           BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to automatically expand partitions that do not exist during INSERT operations.
        init_callback  REGPROCEDURE NOT NULL DEFAULT 0);  -- The OID of the callback function when the partition is created.
  3. pathman_concurrent_part_tasks
    -- helper SRF function
    CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()  
    RETURNS TABLE (
        userid     REGROLE,
        pid        INT,
        dbid       OID,
        relid      REGCLASS,
        processed  INT,
        status     TEXT)
    AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
    LANGUAGE C STRICT;
    
    CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
    AS SELECT * FROM show_concurrent_part_tasks();
  4. pathman_partition_list
    -- helper SRF function
    CREATE OR REPLACE FUNCTION show_partition_list()
    RETURNS TABLE (
        parent     REGCLASS,
        partition  REGCLASS,
        parttype   INT4,
        partattr   TEXT,
        range_min  TEXT,
        range_max  TEXT)
    AS 'pg_pathman', 'show_partition_list_internal'
    LANGUAGE C STRICT;
    
    CREATE OR REPLACE VIEW pathman_partition_list
    AS SELECT * FROM show_partition_list();

Partition management

  1. Range partitions
    Four management functions are used to create range partitions. Two functions are used to specify the start value, interval, and number of partitions. The definition of these two functions are as follows:
    create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                            attribute      TEXT,      -- The column name of the partition.
                            start_value    ANYELEMENT,  -- The start value.
                            p_interval     ANYELEMENT,  -- The interval, applicable to any type of partitioned tables.
                            p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                            partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.
    
    create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                            attribute      TEXT,      -- The column name of the partition.
                            start_value    ANYELEMENT,  -- The start value.
                            p_interval     INTERVAL,    -- The interval of the interval type, applicable to ingestion-time partitioned tables.
                            p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                            partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.
    The other two functions are used to specify the start value, end value, and interval. The definitions are as follows:
    create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                                 attribute      TEXT,      -- The column name of the partition.
                                 start_value    ANYELEMENT,  -- The start value.
                                 end_value      ANYELEMENT,  -- The end value.
                                 p_interval     ANYELEMENT,  -- The interval, applicable to any type of partitioned tables.
                                 partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.
    
    create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                                 attribute      TEXT,      -- The column name of the partition.
                                 start_value    ANYELEMENT,  -- The start value.
                                 end_value      ANYELEMENT,  -- The end value.
                                 p_interval     INTERVAL,    -- The interval of the interval type, applicable to ingestion-time partitioned tables.
                                 partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.
    Example:
    Create a primary table that needs to be partitioned.
    postgres=# create table part_test(id int, info text, crt_time timestamp not null);  -- All partition columns must contain the NOT NULL constraint.  
    CREATE TABLE
    
    Insert a large amount of test data to simulate a primary table that already contains data.
    postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); 
    INSERT 0 10000
    postgres=# select * from part_test limit 10;                    
     id |               info               |          crt_time          
    ----+----------------------------------+----------------------------
      1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713
      2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893
      3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904
      4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691
      5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916
      6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921
      7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693
      8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936
      9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942
     10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947
    (10 rows)
    
    Note:  
    1. All partition columns must contain the NOT NULL constraint.  
    2. The number of partitions must be sufficient to cover all existing records.  
    
    Create partitions and ensure that each partition contains one month of data  
    postgres=# select                                             
    create_range_partitions('part_test'::regclass,             -- The OID of the primary table.
                            'crt_time',                        -- The column name of the partition.
                            '2016-10-25 00:00:00'::timestamp,  -- The start value.
                            interval '1 month',                -- The interval of the interval type, applicable to ingestion-time partitioned tables.
                            24,                                -- The number of partitions.
                            false) ;                           -- The data is not migrated.
    NOTICE:  sequence "part_test_seq" does not exist, skipping
     create_range_partitions 
    -------------------------
                          24
    (1 row)
    postgres-# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_1,
                  part_test_10,
                  part_test_11,
                  part_test_12,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_2,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_3,
                  part_test_4,
                  part_test_5,
                  part_test_6,
                  part_test_7,
                  part_test_8,
                  part_test_9
    
    
    The data is still in the primary table because it is not migrated.
    postgres=# select count(*) from only part_test;
     count 
    -------
     10000
    (1 row)
    
    
    Run non-blocking data migration.  
    partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                                 batch_size INTEGER DEFAULT 1000,  -- The number of records to copy from the primary table at a time.
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting.
    
    
    postgres=# select partition_table_concurrently('part_test'::regclass,
                                 10000,
                                 1.0);
    NOTICE:  worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test');
     partition_table_concurrently 
    ------------------------------
    
    (1 row)
    
    
    After the migration, all data is migrated to the partitions, and the primary table is empty.
    postgres=# select count(*) from only part_test;
     count 
    -------
         0
    (1 row)
    
    
    After the data is migrated, we recommend that you disable the primary table so that the primary table will not be included in the execution plan.
    postgres=# select set_enable_parent('part_test'::regclass, false);
     set_enable_parent 
    -------------------
    
    (1 row)
    
    postgres=# explain select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;
                                       QUERY PLAN                                    
    ---------------------------------------------------------------------------------
     Append  (cost=0.00..16.18 rows=1 width=45)
       ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)
             Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
    (3 rows)
    Note When using range partitioning, note the following items:
    • All partition columns must contain the NOT NULL constraint.
    • The number of partitions must be sufficient to cover all existing records.
    • Run non-blocking data migration.
    • After data migration is completed, disable the primary table.
  2. Hash partitioning
    You can use a management function to create range partitions. You can specify the start value, interval, and number of partitions, as described in the following examples:
    create_hash_partitions(relation         REGCLASS,  -- The OID of the primary table.
                           attribute        TEXT,      -- The column name of the partition.
                           partitions_count INTEGER,   -- The number of partitions to be created.
                           partition_data   BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.
    Example:
    Create a primary table that needs to be partitioned.
    postgres=# create table part_test(id int, info text, crt_time timestamp not null);    -- All partition columns must contain the NOT NULL constraint.  
    CREATE TABLE
    
    Insert a large amount of test data to simulate a primary table that already contains data.
    postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); 
    INSERT 0 10000
    postgres=# select * from part_test limit 10;   
     id |               info               |          crt_time          
    ----+----------------------------------+----------------------------
      1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879
      2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048
      3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059
      4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065
      5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407
      6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076
      7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081
      8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087
      9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092
     10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097
    (10 rows)
    
    Note:  
    1. All partition columns must contain the NOT NULL constraint.  
    
    Create 128 partitions
    postgres=# select                                             
    create_hash_partitions('part_test'::regclass, -- The OID of the primary table.
                            'crt_time',                        -- The column name of the partition.
                            128,                               -- The number of partitions to be created.
                            false) ;                           -- The data is not migrated.
     create_hash_partitions 
    ------------------------
                        128
    (1 row)
    
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_0,
                  part_test_1,
                  part_test_10,
                  part_test_100,
                  part_test_101,
                  part_test_102,
                  part_test_103,
                  part_test_104,
                  part_test_105,
                  part_test_106,
                  part_test_107,
                  part_test_108,
                  part_test_109,
                  part_test_11,
                  part_test_110,
                  part_test_111,
                  part_test_112,
                  part_test_113,
                  part_test_114,
                  part_test_115,
                  part_test_116,
                  part_test_117,
                  part_test_118,
                  part_test_119,
                  part_test_12,
                  part_test_120,
                  part_test_121,
                  part_test_122,
                  part_test_123,
                  part_test_124,
                  part_test_125,
                  part_test_126,
                  part_test_127,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_2,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_25,
                  part_test_26,
                  part_test_27,
                  part_test_28,
                  part_test_29,
                  part_test_3,
                  part_test_30,
                  part_test_31,
                  part_test_32,
                  part_test_33,
                  part_test_34,
                  part_test_35,
                  part_test_36,
                  part_test_37,
                  part_test_38,
                  part_test_39,
                  part_test_4,
                  part_test_40,
                  part_test_41,
                  part_test_42,
                  part_test_43,
                  part_test_44,
                  part_test_45,
                  part_test_46,
                  part_test_47,
                  part_test_48,
                  part_test_49,
                  part_test_5,
                  part_test_50,
                  part_test_51,
                  part_test_52,
                  part_test_53,
                  part_test_54,
                  part_test_55,
                  part_test_56,
                  part_test_57,
                  part_test_58,
                  part_test_59,
                  part_test_6,
                  part_test_60,
                  part_test_61,
                  part_test_62,
                  part_test_63,
                  part_test_64,
                  part_test_65,
                  part_test_66,
                  part_test_67,
                  part_test_68,
                  part_test_69,
                  part_test_7,
                  part_test_70,
                  part_test_71,
                  part_test_72,
                  part_test_73,
                  part_test_74,
                  part_test_75,
                  part_test_76,
                  part_test_77,
                  part_test_78,
                  part_test_79,
                  part_test_8,
                  part_test_80,
                  part_test_81,
                  part_test_82,
                  part_test_83,
                  part_test_84,
                  part_test_85,
                  part_test_86,
                  part_test_87,
                  part_test_88,
                  part_test_89,
                  part_test_9,
                  part_test_90,
                  part_test_91,
                  part_test_92,
                  part_test_93,
                  part_test_94,
                  part_test_95,
                  part_test_96,
                  part_test_97,
                  part_test_98,
                  part_test_99
    
    
    The data is still in the primary table because it is not migrated.
    postgres=# select count(*) from only part_test;
     count 
    -------
     10000
    (1 row)
    
    
    Run non-blocking data migration  
    partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                                 batch_size INTEGER DEFAULT 1000,  -- The number of records to copy from the primary table at a time.
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting.
    
    
    postgres=# select partition_table_concurrently('part_test'::regclass,
                                 10000,
                                 1.0);
    NOTICE:  worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test');
     partition_table_concurrently 
    ------------------------------
    
    (1 row)
    
    
    After the migration, all data is migrated to the partitions, and the primary table is empty.
    postgres=# select count(*) from only part_test;
     count 
    -------
         0
    (1 row)
    
    
    After the data is migrated, we recommend that you disable the primary table so that the primary table will not be included in the execution plan.
    postgres=# select set_enable_parent('part_test'::regclass, false);
     set_enable_parent 
    -------------------
    
    (1 row)
    
    Query only a single partition.
    postgres=# explain select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;
                                       QUERY PLAN                                    
    ---------------------------------------------------------------------------------
     Append  (cost=0.00..1.91 rows=1 width=45)
       ->  Seq Scan on part_test_122  (cost=0.00..1.91 rows=1 width=45)
             Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
    (3 rows)
    
    The constraints on partitions are as follows:  
    pg_pathman automatically completes the conversion. For traditional inheritance, expressions similar to select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; cannot filter partitions.  
    postgres=# \d+ part_test_122
                                    Table "public.part_test_122"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122)
    Inherits: part_test
    Note When using hash partitioning, note the following items:
    • All partition columns must contain the NOT NULL constraint.
    • Run non-blocking data migration.
    • After data migration is completed, disable the primary table.
    • pg_pathman is not subject to expressions. So the command select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; can also be used for hash partitioning.
    • HASH partition columns are not limited to int type columns. The column types are automatically converted by a hash function.
  3. Migrate data to a partition
    If the data of the primary table is not migrated to partitions when the partitions are created, the data can be migrated to the partitions by calling a non-blocking migration function. Run the following commands:
    with tmp as (delete from a primary table limit xx nowait returning *) insert into a partition select * from tmp
    
    You can also use select array_agg(ctid) from a primary table limit xx for update nowati. Then execute the DELETE and INSERT statements.
    The function is as follows:
    partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                                 batch_size INTEGER DEFAULT 1000, -- The number of records to copy from the primary table at a time.
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting.
    Example:
    postgres=# select partition_table_concurrently('part_test'::regclass,
                                 10000,
                                 1.0);
    NOTICE:  worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test');
     partition_table_concurrently 
    ------------------------------
    
    (1 row)
    To stop the migration task, call the following function:
    stop_concurrent_part_task(relation REGCLASS)
    View the background data migration task.
    postgres=# select * from pathman_concurrent_part_tasks;
     userid | pid | dbid | relid | processed | status 
    --------+-----+------+-------+-----------+--------
    (0 rows)
  4. Split range partitions
    If a partition is too large and you want to split the partition into two partitions, use the following method (only range partitions are supported):
    split_range_partition(partition      REGCLASS,            -- The OID of the partition.
                          split_value    ANYELEMENT,          -- The split value.
                          partition_name TEXT DEFAULT NULL)   -- The name of the new partition.
    Example:
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_1,
                  part_test_10,
                  part_test_11,
                  part_test_12,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_2,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_3,
                  part_test_4,
                  part_test_5,
                  part_test_6,
                  part_test_7,
                  part_test_8,
                  part_test_9
    
    postgres=# \d+ part_test_1
                                     Table "public.part_test_1"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    Splitting
    postgres=# select split_range_partition('part_test_1'::regclass,              -- The OID of the partition.
                          '2016-11-10 00:00:00'::timestamp,     -- The split value.
                          'part_test_1_2');                     -- The name of the partitioned table.
                 split_range_partition             
    -----------------------------------------------
     {"2016-10-25 00:00:00","2016-11-25 00:00:00"}
    (1 row)
    Two tables that are created from splitting are as follows:
    postgres=# \d+ part_test_1
                                     Table "public.part_test_1"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    postgres=# \d+ part_test_1_2 
                                    Table "public.part_test_1_2"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    Data is automatically migrated to the other partition.
    postgres=# select count(*) from part_test_1;
     count 
    -------
       373
    (1 row)
    
    postgres=# select count(*) from part_test_1_2;
     count 
    -------
       360
    (1 row)
    The inheritance relationship is as follows:
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_1,
                  part_test_10,
                  part_test_11,
                  part_test_12,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_1_2,    -- The added table.
                  part_test_2,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_3,
                  part_test_4,
                  part_test_5,
                  part_test_6,
                  part_test_7,
                  part_test_8,
                  part_test_9
  5. Merge range partitions
    Only range partitions are supported. Call the following function:
    Specify two partitions to be merged, which must be adjacent partitions.  
    merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
    Example:
    postgres=# select merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;
    ERROR:  merge failed, partitions must be adjacent
    CONTEXT:  PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISE
    SQL statement "SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)"
    PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTE
    An error is returned because the partitions are not adjacent.
    
    Adjacent partitions can be merged.
    postgres=# select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass) ;
     merge_range_partitions 
    ------------------------
    
    (1 row)
    After the merge is completed, one of the partitions are deleted.
    postgres=# \d part_test_1_2
    Did not find any relation named "part_test_1_2".
    
    postgres=# \d part_test_1
                 Table "public.part_test_1"
      Column  |            Type             | Modifiers 
    ----------+-----------------------------+-----------
     id       | integer                     | 
     info     | text                        | 
     crt_time | timestamp without time zone | not null
    Check constraints:
        "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    postgres=# select count(*) from part_test_1;
     count 
    -------
       733
    (1 row)
  6. Add a range partition following the last partition

    There are several methods to add partitions for primary tables that have been previously partitioned. One method is to add partitions following the last partition.

    When a new partition is added, the interval of the previously partitioned table will be used. You can query the interval of each partitioned table when it is created for the first time by running the pathman_config command:
    postgres=# select * from pathman_config;
      partrel  | attname  | parttype | range_interval 
    -----------+----------+----------+----------------
     part_test | crt_time |        2 | 1 mon
    (1 row)
    Add a new range partition (the tablespace cannot be specified).
    append_range_partition(parent         REGCLASS,            -- The OID of the primary table.
                           partition_name TEXT DEFAULT NULL,   -- (Optional) The name of the new partition. Default value: null.
                           tablespace     TEXT DEFAULT NULL)   -- (Optional) The tablespace where the new partition is stored. Default value: null.
    Example:
    postgres=# select append_range_partition('part_test'::regclass);
     append_range_partition 
    ------------------------
     public.part_test_25
    (1 row)
    
    postgres=# \d+ part_test_25
                                    Table "public.part_test_25"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    postgres=# \d+ part_test_24
                                    Table "public.part_test_24"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
  7. Add a range partition at the beginning of partitions
    Add a partition at the beginning of the partitions. The function is as follows:
    prepend_range_partition(parent         REGCLASS,
                            partition_name TEXT DEFAULT NULL,
                            tablespace     TEXT DEFAULT NULL)
    Example:
    postgres=# select prepend_range_partition('part_test'::regclass);
     prepend_range_partition 
    -------------------------
     public.part_test_26
    (1 row)
    
    postgres=# \d+ part_test_26
                                    Table "public.part_test_26"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    postgres=# \d+ part_test_1
                                     Table "public.part_test_1"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
  8. Add a partition
    You can create new partitions by specifying the start value of the partitions. New partitions can be created if the ranges do not overlap with existing partitions. This method allows you to create non-continuous partitions. For example, if the range of existing partitions are from 2010 to 2015, you can create a new partition from 2020. You do not need to create a partition between 2015 and 2020. The function is as follows:
    add_range_partition(relation       REGCLASS,    -- The OID of the primary table.
                        start_value    ANYELEMENT,  -- The start value.
                        end_value      ANYELEMENT,  -- The end value.
                        partition_name TEXT DEFAULT NULL,  -- The name of the partition.
                        tablespace TEXT DEFAULT NULL) -- The name of the tablespace in which a partition resides.
    Example:
    postgres=# select add_range_partition('part_test'::regclass,    -- The OID of the primary table.
                        '2020-01-01 00:00:00'::timestamp,  -- The start value.
                        '2020-02-01 00:00:00'::timestamp); -- The end value.
     add_range_partition 
    ---------------------
     public.part_test_27
    (1 row)
    
    postgres=# \d+ part_test_27
                                    Table "public.part_test_27"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
    Inherits: part_test
  9. Delete a partition
    To delete a single partition range, call the following function:
    drop_range_partition(partition TEXT,   -- The name of the partition to be deleted.
                        delete_data BOOLEAN DEFAULT TRUE) -- Specifies whether to delete the data of the partition. If you set the value to FALSE, the data of the partition is migrated to the primary table.  
    
    Drop RANGE partition and all of its data if delete_data is true.
    Example:
    Delete a partition and migrate data of the partition to the primary table.  
    postgres=# select drop_range_partition('part_test_1',false);
    NOTICE:  733 rows copied from part_test_1
     drop_range_partition 
    ----------------------
     part_test_1
    (1 row)
    
    postgres=# select drop_range_partition('part_test_2',false);
    NOTICE:  720 rows copied from part_test_2
     drop_range_partition 
    ----------------------
     part_test_2
    (1 row)
    
    postgres=# select count(*) from part_test;
     count 
    -------
     10000
    (1 row)
    
    Delete a partition and the data of the partition without migrating the data to the primary table.  
    postgres=# select drop_range_partition('part_test_3',true);
     drop_range_partition 
    ----------------------
     part_test_3
    (1 row)
    
    postgres=# select count(*) from part_test;
     count 
    -------
      9256
    (1 row)
    
    postgres=# select count(*) from only part_test;
     count 
    -------
      1453
    (1 row)
    Delete all partitions and specify whether to migrate data to the primary table. The function is as follows:
    drop_partitions(parent      REGCLASS,
                    delete_data BOOLEAN DEFAULT FALSE)
    
    Drop partitions of the parent table (both foreign and local relations). 
    If delete_data is false, the data is copied to the parent table first. 
    Default is false.
    Example:
    postgres=# select drop_partitions('part_test'::regclass, false);  -- Delete all partitions and migrate the data to the primary table.
    NOTICE:  function public.part_test_upd_trig_func() does not exist, skipping
    NOTICE:  744 rows copied from part_test_4
    NOTICE:  672 rows copied from part_test_5
    NOTICE:  744 rows copied from part_test_6
    NOTICE:  720 rows copied from part_test_7
    NOTICE:  744 rows copied from part_test_8
    NOTICE:  720 rows copied from part_test_9
    NOTICE:  744 rows copied from part_test_10
    NOTICE:  744 rows copied from part_test_11
    NOTICE:  720 rows copied from part_test_12
    NOTICE:  744 rows copied from part_test_13
    NOTICE:  507 rows copied from part_test_14
    NOTICE:  0 rows copied from part_test_15
    NOTICE:  0 rows copied from part_test_16
    NOTICE:  0 rows copied from part_test_17
    NOTICE:  0 rows copied from part_test_18
    NOTICE:  0 rows copied from part_test_19
    NOTICE:  0 rows copied from part_test_20
    NOTICE:  0 rows copied from part_test_21
    NOTICE:  0 rows copied from part_test_22
    NOTICE:  0 rows copied from part_test_23
    NOTICE:  0 rows copied from part_test_24
    NOTICE:  0 rows copied from part_test_25
    NOTICE:  0 rows copied from part_test_26
    NOTICE:  0 rows copied from part_test_27
     drop_partitions 
    -----------------
                  24
    (1 row)
    
    postgres=# select count(*) from part_test;
     count 
    -------
      9256
    (1 row)
    
    postgres=# \dt part_test_4
    No matching relations found.
  10. Attach a table to a partitioned table
    Attach a table to a partitioned primary table. The table must have the same schema as the primary table, including the dropped columns (check the consistency of pg_attribute). The function is as follows:
    attach_range_partition(relation    REGCLASS,    -- The OID of the primary table.
                           partition   REGCLASS,    -- The OID of the partition table.
                           start_value ANYELEMENT,  -- The start value.
                           end_value   ANYELEMENT)  -- The start value.
    Example:
    postgres=# create table part_test_1 (like part_test including all);
    CREATE TABLE
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    
    postgres=# \d+ part_test_1
                                     Table "public.part_test_1"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    
    postgres=# select attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);
     attach_range_partition 
    ------------------------
     part_test_1
    (1 row)
    
    When the table is attached,
    inheritance relationships and constraints are created automatically.  
    postgres=# \d+ part_test_1
                                     Table "public.part_test_1"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_1_3_check" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone)
    Inherits: part_test
  11. Detach a partition from the primary table (convert the partition into a normal table)
    Delete a partition from the primary table inheritance. The data is not deleted. The inheritance and constraints are deleted. The function is as follows:
    detach_range_partition(partition REGCLASS)  -- Specify the name of the partition and convert the partition to a normal table.
    Example:
    postgres=# select count(*) from part_test;
     count 
    -------
      9256
    (1 row)
    
    postgres=# select count(*) from part_test_2;
     count 
    -------
       733
    (1 row)
    
    postgres=# select detach_range_partition('part_test_2');
     detach_range_partition 
    ------------------------
     part_test_2
    (1 row)
    
    postgres=# select count(*) from part_test_2;
     count 
    -------
       733
    (1 row)
    
    postgres=# select count(*) from part_test;
     count 
    -------
      8523
    (1 row)
  12. Permanently disable the pg_pathman plug-in for a partitioned table
    You can disable the pg_pathman plug-in for a single partitioned primary table. The function is as follows:
    disable_pathman_for(relation TEXT)
    
    Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. 
    All partitions and data remain unchanged.
    
    postgres=# \sf disable_pathman_for
    CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
     RETURNS void
     LANGUAGE plpgsql
     STRICT
    AS $function$
    BEGIN
            PERFORM public.validate_relname(parent_relid);
    
            DELETE FROM public.pathman_config WHERE partrel = parent_relid;
            PERFORM public.drop_triggers(parent_relid);
    
            /* Notify backend about changes */
            PERFORM public.on_remove_partitions(parent_relid);
    END
    $function$
    Example:
    postgres=# select disable_pathman_for('part_test');
    NOTICE:  drop cascades to 23 other objects
    DETAIL:  drop cascades to trigger part_test_upd_trig on table part_test_3
    drop cascades to trigger part_test_upd_trig on table part_test_4
    drop cascades to trigger part_test_upd_trig on table part_test_5
    drop cascades to trigger part_test_upd_trig on table part_test_6
    drop cascades to trigger part_test_upd_trig on table part_test_7
    drop cascades to trigger part_test_upd_trig on table part_test_8
    drop cascades to trigger part_test_upd_trig on table part_test_9
    drop cascades to trigger part_test_upd_trig on table part_test_10
    drop cascades to trigger part_test_upd_trig on table part_test_11
    drop cascades to trigger part_test_upd_trig on table part_test_12
    drop cascades to trigger part_test_upd_trig on table part_test_13
    drop cascades to trigger part_test_upd_trig on table part_test_14
    drop cascades to trigger part_test_upd_trig on table part_test_15
    drop cascades to trigger part_test_upd_trig on table part_test_16
    drop cascades to trigger part_test_upd_trig on table part_test_17
    drop cascades to trigger part_test_upd_trig on table part_test_18
    drop cascades to trigger part_test_upd_trig on table part_test_19
    drop cascades to trigger part_test_upd_trig on table part_test_20
    drop cascades to trigger part_test_upd_trig on table part_test_21
    drop cascades to trigger part_test_upd_trig on table part_test_22
    drop cascades to trigger part_test_upd_trig on table part_test_23
    drop cascades to trigger part_test_upd_trig on table part_test_24
    drop cascades to trigger part_test_upd_trig on table part_test_25
     disable_pathman_for 
    ---------------------
    
    (1 row)
    
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_10,
                  part_test_11,
                  part_test_12,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_25,
                  part_test_26,
                  part_test_27,
                  part_test_28,
                  part_test_29,
                  part_test_3,
                  part_test_30,
                  part_test_31,
                  part_test_32,
                  part_test_33,
                  part_test_34,
                  part_test_35,
                  part_test_4,
                  part_test_5,
                  part_test_6,
                  part_test_7,
                  part_test_8,
                  part_test_9
    
    postgres=# \d+ part_test_10
                                    Table "public.part_test_10"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    After the pg_pathman plug-in is disabled, the inheritance and constraints remain unchanged. The pg_pathman plug-in does not intervene in the custom scan execution plan. The execution plan after the pg_pathman plug-in is disabled:
    postgres=# 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)
    Notice The disable_pathman_for operation is irreversible. Proceed with caution.

Advanced partition management

  1. Disable a primary table
    After all data of a primary table is migrated to the partitions, you can disable the primary table. The function is as follows:
    set_enable_parent(relation REGCLASS, value BOOLEAN)
    
    
    Include/exclude parent table into/from query plan. 
    
    In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. 
    
    You can use disable_parent() if you are never going to use parent table as a storage. 
    
    Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions. 
    
    If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. 
    
    Otherwise it is enabled.
    Example:
    select set_enable_parent('part_test', false);
  2. Auto partition propagation
    Auto partition propagation is supported for range partitioned tables. If the inserted data is not within the range of the existing partitions, a partition is automatically created.
    set_auto(relation REGCLASS, value BOOLEAN)
    
    Enable/disable auto partition propagation (only for RANGE partitioning). 
    
    It is enabled by default.
    Example:
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_10,
                  part_test_11,
                  part_test_12,
                  part_test_13,
                  part_test_14,
                  part_test_15,
                  part_test_16,
                  part_test_17,
                  part_test_18,
                  part_test_19,
                  part_test_20,
                  part_test_21,
                  part_test_22,
                  part_test_23,
                  part_test_24,
                  part_test_25,
                  part_test_26,
                  part_test_3,
                  part_test_4,
                  part_test_5,
                  part_test_6,
                  part_test_7,
                  part_test_8,
                  part_test_9
    
    postgres=# \d+ part_test_26
                                    Table "public.part_test_26"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    postgres=# \d+ part_test_25
                                    Table "public.part_test_25"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Check constraints:
        "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone)
    Inherits: part_test
    
    When the inserted data is beyond the partitioning range, a large number of new partitions are created based on the interval when the primary table is partitioned.  
    postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp);
    
    After the data is inserted, a large number of partitions are created because the range of the inserted values is too large.  
    
    postgres=# \d+ part_test
                                      Table "public.part_test"
      Column  |            Type             | Modifiers | Storage  | Stats target | Description 
    ----------+-----------------------------+-----------+----------+--------------+-------------
     id       | integer                     |           | plain    |              | 
     info     | text                        |           | extended |              | 
     crt_time | timestamp without time zone | not null  | plain    |              | 
    Child tables: part_test_10,
                  part_test_100,
                  part_test_1000,
                  part_test_1001,
            .....................................
            A large number of partitions
    Note We recommend that you disable auto partition propagation for range partitioning because inappropriate auto propagation may consume a lot of time.
  3. Callback functions that are triggered for each partition creation
    A callback function is a function that is automatically triggered for each partition creation. For example, a callback function can record the DDL statements that you use to run logical replication and store the statements in a table. The callback function is as follows:
    set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
    
    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:
    
    /* RANGE-partitioned table abc (child abc_4) */
    {
        "parent":    "abc",
        "parttype":  "2",
        "partition": "abc_4",
        "range_max": "401",
        "range_min": "301"
    }
    
    /* HASH-partitioned table abc (child abc_0) */
    {
        "parent":    "abc",
        "parttype":  "1",
        "partition": "abc_0"
    }
    Example:
    Callback function  
    postgres=# 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
        raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';
        insert into rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
      elsif ($1->>'parttype')::int = 2 then
        raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';
        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;
    
    Test table
    postgres=# create table tt(id int, info text, crt_time timestamp not null);
    CREATE TABLE
    
    Set the callback function for the test table.
    select set_init_callback('tt'::regclass, 'f_callback_test'::regproc);
    
    Create a partition
    postgres=# select                                                          
    create_range_partitions('tt'::regclass,                    -- The OID of the primary table.
                            'crt_time',                        -- The column name of the partition.
                            '2016-10-25 00:00:00'::timestamp,  -- The start value.
                            interval '1 month',                -- The interval of the interval type, applicable to ingestion-time partitioned tables.
                            24,                                -- The number of partitions.
                            false) ;
     create_range_partitions 
    -------------------------
                          24
    (1 row)
    
    Check whether the callback function is called.  
    postgres=# select * from rec_part_ddl;
     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
      3 | tt     |        2 | tt_3      | 2017-01-25 00:00:00 | 2016-12-25 00:00:00
      4 | tt     |        2 | tt_4      | 2017-02-25 00:00:00 | 2017-01-25 00:00:00
      5 | tt     |        2 | tt_5      | 2017-03-25 00:00:00 | 2017-02-25 00:00:00
      6 | tt     |        2 | tt_6      | 2017-04-25 00:00:00 | 2017-03-25 00:00:00
      7 | tt     |        2 | tt_7      | 2017-05-25 00:00:00 | 2017-04-25 00:00:00
      8 | tt     |        2 | tt_8      | 2017-06-25 00:00:00 | 2017-05-25 00:00:00
      9 | tt     |        2 | tt_9      | 2017-07-25 00:00:00 | 2017-06-25 00:00:00
     10 | tt     |        2 | tt_10     | 2017-08-25 00:00:00 | 2017-07-25 00:00:00
     11 | tt     |        2 | tt_11     | 2017-09-25 00:00:00 | 2017-08-25 00:00:00
     12 | tt     |        2 | tt_12     | 2017-10-25 00:00:00 | 2017-09-25 00:00:00
     13 | tt     |        2 | tt_13     | 2017-11-25 00:00:00 | 2017-10-25 00:00:00
     14 | tt     |        2 | tt_14     | 2017-12-25 00:00:00 | 2017-11-25 00:00:00
     15 | tt     |        2 | tt_15     | 2018-01-25 00:00:00 | 2017-12-25 00:00:00
     16 | tt     |        2 | tt_16     | 2018-02-25 00:00:00 | 2018-01-25 00:00:00
     17 | tt     |        2 | tt_17     | 2018-03-25 00:00:00 | 2018-02-25 00:00:00
     18 | tt     |        2 | tt_18     | 2018-04-25 00:00:00 | 2018-03-25 00:00:00
     19 | tt     |        2 | tt_19     | 2018-05-25 00:00:00 | 2018-04-25 00:00:00
     20 | tt     |        2 | tt_20     | 2018-06-25 00:00:00 | 2018-05-25 00:00:00
     21 | tt     |        2 | tt_21     | 2018-07-25 00:00:00 | 2018-06-25 00:00:00
     22 | tt     |        2 | tt_22     | 2018-08-25 00:00:00 | 2018-07-25 00:00:00
     23 | tt     |        2 | tt_23     | 2018-09-25 00:00:00 | 2018-08-25 00:00:00
     24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
    (24 rows)