当前位置: 首页 > 工具软件 > pg_pathman > 使用案例 >

使用pg_pathman插件

史经业
2023-12-01

背景信息

为了提高分区表的性能,PolarDB PostgreSQL引擎引入了pg_pathman插件。该插件一款分区管理插件,提供了分区优化机制。

创建pg_pathman插件扩展

test=# create extension pg_pathman;
CREATE EXTENSION

查看已安装的扩展

以下命令可以查看已安装的扩展,还可以查看到pg_pathman 的具体版本。

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)

插件升级

PolarDB PostgreSQL引擎会定期对插件进行升级,以提供更优质的数据库服务。而当您需要升级插件版本时,需要:

  • 升级对应集群到最新版本。
  • 执行SQL版本更新命令,如下所示:
    ALTER EXTENSION pg_pathman UPDATE;
    SET pg_pathman.enable = t;

插件特性

  • 目前支持HASH分区、RANGE分区。
  • 支持自动分区管理(通过函数接口创建分区,自动将主表数据迁移到分区表),或手工分区管理(通过函数实现,将已有的表绑定到分区表,或者从分区表剥离)。
  • 支持的分区字段类型包括int、float、date以及其他常用类型,包括自定义的domain。
  • 有效的分区表查询计划(JOINs、subselects 等)。
  • 使用RuntimeAppend & RuntimeMergeAppend 自定义计划节点实现了动态分区选择。
  • PartitionFilter:一种有效的插入触发器替换方法。
  • 支持自动新增分区(目前仅支持RANGE分区表)。
  • 支持copy from/to直接读取或写入分区表,提高效率。
  • 支持分区字段的更新,需要添加触发器,如果不需要更新分区字段,则不建议添加这个触发器,会产生一定的性能影响。
  • 允许用户自定义回调函数,在创建分区时会自动触发。
  • 非堵塞式创建分区表,以及后台自动将主表数据非堵塞式迁移到分区表。
  • 支持FDW,通过配置参数pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)支持postgres_fdw或任意FDW。

插件用法

更多用法,请参见GitHub

相关视图和表

pg_pathman使用函数来维护分区表,并且创建了一些视图,可以查看分区表的状态,具体如下:

  1. pathman_config
    CREATE TABLE IF NOT EXISTS pathman_config (
        partrel         REGCLASS NOT NULL PRIMARY KEY,  -- 主表oid
        attname         TEXT NOT NULL,  -- 分区列名
        parttype        INTEGER NOT NULL,  -- 分区类型(hash or range)
        range_interval  TEXT,  -- range分区的interval
    
        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,  -- 主表oid
        enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,  -- 是否在优化器中过滤主表
        auto           BOOLEAN NOT NULL DEFAULT TRUE,  -- insert时是否自动扩展不存在的分区
        init_callback  REGPROCEDURE NOT NULL DEFAULT 0);  -- create partition时的回调函数oid
  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();

分区管理

  1. RANGE分区有四个管理函数用来创建范围分区。其中两个可以指定起始值、间隔、分区个数,其函数定义如下:
    create_range_partitions(relation       REGCLASS,  -- 主表OID
                            attribute      TEXT,      -- 分区列名
                            start_value    ANYELEMENT,  -- 开始值
                            p_interval     ANYELEMENT,  -- 间隔;任意类型,适合任意类型的分区表
                            p_count        INTEGER DEFAULT NULL,   --  分多少个区
                            partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )
    
    create_range_partitions(relation       REGCLASS,  -- 主表OID
                            attribute      TEXT,      -- 分区列名
                            start_value    ANYELEMENT,  -- 开始值
                            p_interval     INTERVAL,    -- 间隔;interval 类型,用于时间分区表
                            p_count        INTEGER DEFAULT NULL,   --  分多少个区
                            partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )
    另外两个可以指定起始值、终值、间隔,其定义如下:
    create_partitions_from_range(relation       REGCLASS,  -- 主表OID
                                 attribute      TEXT,      -- 分区列名
                                 start_value    ANYELEMENT,  -- 开始值
                                 end_value      ANYELEMENT,  -- 结束值
                                 p_interval     ANYELEMENT,  -- 间隔;任意类型,适合任意类型的分区表
                                 partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )
    
    create_partitions_from_range(relation       REGCLASS,  -- 主表OID
                                 attribute      TEXT,      -- 分区列名
                                 start_value    ANYELEMENT,  -- 开始值
                                 end_value      ANYELEMENT,  -- 结束值
                                 p_interval     INTERVAL,    -- 间隔;interval 类型,用于时间分区表
                                 partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )
    示例如下所示:
    创建需要分区的主表
    postgres=# create table part_test(id int, info text, crt_time timestamp not null);  -- 分区列必须有not null约束  
    CREATE TABLE
    
    插入一批测试数据,模拟已经有数据了的主表
    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)
    
    注意:  
    1. 分区列必须有not null约束  
    2. 分区个数必须能覆盖已有的所有记录  
    
    创建分区,每个分区包含1个月的跨度数据  
    postgres=# select                                             
    create_range_partitions('part_test'::regclass,             -- 主表OID
                            'crt_time',                        -- 分区列名
                            '2016-10-25 00:00:00'::timestamp,  -- 开始值
                            interval '1 month',                -- 间隔;interval 类型,用于时间分区表
                            24,                                -- 分多少个区
                            false) ;                           -- 不迁移数据
    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
    
    
    由于不迁移数据,所以数据还在主表
    postgres=# select count(*) from only part_test;
     count 
    -------
     10000
    (1 row)
    
    
    使用非堵塞式的迁移接口  
    partition_table_concurrently(relation   REGCLASS,              -- 主表OID
                                 batch_size INTEGER DEFAULT 1000,  -- 一个事务批量迁移多少记录
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- 获得行锁失败时,休眠多久再次获取,重试60次退出任务。
    
    
    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)
    
    
    迁移结束后,主表数据已经没有了,全部在分区中
    postgres=# select count(*) from only part_test;
     count 
    -------
         0
    (1 row)
    
    
    数据迁移完成后,建议禁用主表,这样执行计划就不会出现主表了
    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)
    说明 在RANGE分区表使用过程中,建议您:
    • 分区列必须有not null约束。
    • 分区个数必须能覆盖已有的所有记录。
    • 使用非堵塞式迁移接口。
    • 数据迁移完成后,禁用主表。
  2. HASH分区有一个管理函数用来创建范围分区,可以指定起始值、间隔、分区个数,具体如下:
    create_hash_partitions(relation         REGCLASS,  -- 主表OID
                           attribute        TEXT,      -- 分区列名
                           partitions_count INTEGER,   -- 打算创建多少个分区
                           partition_data   BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )
    示例如下所示:
    创建需要分区的主表
    postgres=# create table part_test(id int, info text, crt_time timestamp not null);    -- 分区列必须有not null约束  
    CREATE TABLE
    
    插入一批测试数据,模拟已经有数据了的主表
    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)
    
    注意:  
    1. 分区列必须有not null约束  
    
    创建128个分区
    postgres=# select                                             
    create_hash_partitions('part_test'::regclass,              -- 主表OID
                            'crt_time',                        -- 分区列名
                            128,                               -- 打算创建多少个分区
                            false) ;                           -- 不迁移数据
     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
    
    
    由于不迁移数据,所以数据还在主表
    postgres=# select count(*) from only part_test;
     count 
    -------
     10000
    (1 row)
    
    
    使用非堵塞式的迁移接口  
    partition_table_concurrently(relation   REGCLASS,              -- 主表OID
                                 batch_size INTEGER DEFAULT 1000,  -- 一个事务批量迁移多少记录
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- 获得行锁失败时,休眠多久再次获取,重试60次退出任务。
    
    
    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)
    
    
    迁移结束后,主表数据已经没有了,全部在分区中
    postgres=# select count(*) from only part_test;
     count 
    -------
         0
    (1 row)
    
    
    数据迁移完成后,建议禁用主表,这样执行计划就不会出现主表了
    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..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)
    
    分区表约束如下  
    很显然pg_pathman自动完成了转换,如果是传统的继承,select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; 这种写法是不能筛选分区的。  
    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
    说明 在HASH分区表使用过程中,建议您:
    • 分区列必须有not null约束。
    • 使用非堵塞式迁移接口。
    • 数据迁移完成后,禁用主表。
    • pg_pathman不会受制于表达式的写法,所以select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;这样的写法也能用于HASH分区的。
    • HASH分区列不局限于int类型的列,会使用HASH函数自动转换。
  3. 数据迁移到分区如果创建分区表时,未将主表数据迁移到分区,那么可以使用非堵塞式的迁移接口,将数据迁移到分区。用法如下:
    with tmp as (delete from 主表 limit xx nowait returning *) insert into 分区 select * from tmp
    
    或者使用 select array_agg(ctid) from 主表 limit xx for update nowati 进行标示 然后执行delete和insert。
    函数接口如下:
    partition_table_concurrently(relation   REGCLASS,              -- 主表OID
                                 batch_size INTEGER DEFAULT 1000,  -- 一个事务批量迁移多少记录
                                 sleep_time FLOAT8 DEFAULT 1.0)    -- 获得行锁失败时,休眠多久再次获取,重试60次退出任务。
    示例如下所示:
    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)
    如果停止迁移任务,调用如下函数接口:
    stop_concurrent_part_task(relation REGCLASS)
    查看后台的数据迁移任务。
    postgres=# select * from pathman_concurrent_part_tasks;
     userid | pid | dbid | relid | processed | status 
    --------+-----+------+-------+-----------+--------
    (0 rows)
  4. 分裂范围分区如果某个分区太大,想分裂为两个分区,可以使用如下方法(目前仅支持RANGE分区表):
    split_range_partition(partition      REGCLASS,            -- 分区oid
                          split_value    ANYELEMENT,          -- 分裂值
                          partition_name TEXT DEFAULT NULL)   -- 分裂后新增的分区表名
    示例如下所示:
    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
    分裂
    postgres=# select split_range_partition('part_test_1'::regclass,              -- 分区oid
                          '2016-11-10 00:00:00'::timestamp,     -- 分裂值
                          'part_test_1_2');                     -- 分区表名
                 split_range_partition             
    -----------------------------------------------
     {"2016-10-25 00:00:00","2016-11-25 00:00:00"}
    (1 row)
    分裂后的两个表如下:
    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
    数据会自动迁移到另一个分区。
    postgres=# select count(*) from part_test_1;
     count 
    -------
       373
    (1 row)
    
    postgres=# select count(*) from part_test_1_2;
     count 
    -------
       360
    (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_1_2,    -- 新增的表
                  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. 合并范围分区目前仅支持RANGE 分区,调用如下接口:
    指定两个需要合并分区,必须为相邻分区  
    merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
    示例如下所示:
    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
    不是相邻分区,报错
    
    相邻分区可以合并
    postgres=# select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass) ;
     merge_range_partitions 
    ------------------------
    
    (1 row)
    合并后,会删掉其中一个分区表。
    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. 向后添加范围分区

    如果已经对主表进行了分区,将来需要增加分区的话,有几种方法,一种是向后新增分区(即在末尾追加分区)。

    新增分区时,会使用初次创建该分区表时的interval作为间隔。可以在pathman_config中查询每个分区表初次创建时的interval,如下:
    postgres=# select * from pathman_config;
      partrel  | attname  | parttype | range_interval 
    -----------+----------+----------+----------------
     part_test | crt_time |        2 | 1 mon
    (1 row)
    添加分区接口(目前不支持指定表空间)
    append_range_partition(parent         REGCLASS,            -- 主表OID
                           partition_name TEXT DEFAULT NULL,   -- 新增的分区表名, 默认不需要输入
                           tablespace     TEXT DEFAULT NULL)   -- 新增的分区表放到哪个表空间, 默认不需要输入
    示例如下所示:
    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. 向前添加范围分区在头部追加分区,接口如下:
    prepend_range_partition(parent         REGCLASS,
                            partition_name TEXT DEFAULT NULL,
                            tablespace     TEXT DEFAULT NULL)
    示例如下所示:
    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. 添加分区指定分区起始值的方式添加分区,只要创建的分区和已有分区不会存在数据交叉就可以创建成功。也就是说使用这种方法,不要求强制创建连续的分区,例如已有分区覆盖了2010-2015的范围,您可以直接创建一个2020年的分区表,不需要覆盖2015到2020的范围。接口如下:
    add_range_partition(relation       REGCLASS,    -- 主表OID
                        start_value    ANYELEMENT,  -- 起始值
                        end_value      ANYELEMENT,  -- 结束值
                        partition_name TEXT DEFAULT NULL,  -- 分区名
                        tablespace     TEXT DEFAULT NULL)  -- 分区创建在哪个表空间下
    示例如下所示:
    postgres=# select add_range_partition('part_test'::regclass,    -- 主表OID
                        '2020-01-01 00:00:00'::timestamp,  -- 起始值
                        '2020-02-01 00:00:00'::timestamp); -- 结束值
     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. 删除分区删除单个范围分区,接口如下:
    drop_range_partition(partition TEXT,   -- 分区名称
                        delete_data BOOLEAN DEFAULT TRUE)  -- 是否删除分区数据,如果false,表示分区数据迁移到主表。  
    
    Drop RANGE partition and all of its data if delete_data is true.
    示例如下所示:
    删除分区, 数据迁移到主表  
    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)
    
    删除分区,分区数据也删除,不迁移到主表  
    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)
    删除所有分区,并且指定是否要将数据迁移到主表。接口如下:
    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.
    示例如下所示:
    postgres=# select drop_partitions('part_test'::regclass, false);  -- 删除所有分区表,并将数据迁移到主表
    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. 绑定分区(已有的表加入分区表)将已有的表,绑定到已有的某个分区主表。已有的表与主表要保持一致的结构,包括dropped columns (查看pg_attribute的一致性)。接口如下:
    attach_range_partition(relation    REGCLASS,    -- 主表OID
                           partition   REGCLASS,    -- 分区表OID
                           start_value ANYELEMENT,  -- 起始值
                           end_value   ANYELEMENT)  -- 结束值
    示例如下所示:
    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)
    
    绑定分区时,
    自动创建继承关系,自动创建约束  
    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_range_partition(partition REGCLASS)  -- 指定分区名,转换为普通表
    示例如下所示:
    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. 永久禁止分区表pg_pathman插件您可以针对单个分区主表禁用pg_pathman。接口函数如下:
    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$
    示例如下所示:
    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
    禁用pg_pathman插件后,继承关系和约束不会变化,只是pg_pathman插件不介入custom scan执行计划。禁用pg_pathman插件后的执行计划如下:
    
    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)

    注意 disable_pathman_for没有可逆操作,请慎使用。

高级分区管理

  1. 禁用主表当主表的数据全部迁移到分区后,可以禁用主表。接口函数如下:
    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.
    示例如下所示:
    select set_enable_parent('part_test', false);
  2. 自动扩展分区范围分区表,允许自动扩展分区。如果新插入的数据不在已有的分区范围内,会自动创建分区。
    set_auto(relation REGCLASS, value BOOLEAN)
    
    Enable/disable auto partition propagation (only for RANGE partitioning). 
    
    It is enabled by default.
    示例如下所示:
    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
    
    插入一个不在已有分区范围的值,会根据创建分区时的interval自动扩展若干个分区,这个操作可能很久。  
    postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp);
    
    插入结束后,扩展了好多分区,原因是插入的值跨度范围太大了。  
    
    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,
            .....................................
            很多

    说明 不建议开启自动扩展范围分区,不合理的自动扩展可能会消耗大量的时间。

  3. 回调函数(创建每个分区时都会触发)回调函数是在每创建一个分区时会自动触发调用的函数。例如,可以用在DDL逻辑复制中,将DDL语句记录下来,存放到表中。回调函数如下:
    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"
    }
    示例如下所示:
    回调函数  
    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;
    
    测试表
    postgres=# create table tt(id int, info text, crt_time timestamp not null);
    CREATE TABLE
    
    设置测试表的回调函数
    select set_init_callback('tt'::regclass, 'f_callback_test'::regproc);
    
    创建分区
    postgres=# select                                                          
    create_range_partitions('tt'::regclass,                    -- 主表OID
                            'crt_time',                        -- 分区列名
                            '2016-10-25 00:00:00'::timestamp,  -- 开始值
                            interval '1 month',                -- 间隔;interval 类型,用于时间分区表
                            24,                                -- 分多少个区
                            false) ;
     create_range_partitions 
    -------------------------
                          24
    (1 row)
    
    检查回调函数是否已调用  
    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)
 类似资料: