工具函数示例

• pg_get_tabledef获取分区表的定义，入参可以为表的OID或者表名。

  SELECT pg_get_tabledef('test_range_pt');
                                                                                                       pg_get_tabledef                                                                                                     
  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   SET search_path = public;                                                                                                                                                                                              +
   CREATE TABLE test_range_pt (                                                                                                                                                                                           +
       a integer,                                                                                                                                                                                                         +
       b integer,                                                                                                                                                                                                         +
       c integer                                                                                                                                                                                                          +
   )                                                                                                                                                                                                                      +
   WITH (orientation=row, compression=no, storage_type=USTORE, segment=off)                                                                                                                                               +
   PARTITION BY RANGE (a)                                                                                                                                                                                                 +
   (                                                                                                                                                                                                                      +
       PARTITION p1 VALUES LESS THAN (2000) TABLESPACE pg_default,                                                                                                                                                        +
       PARTITION p2 VALUES LESS THAN (3000) TABLESPACE pg_default,                                                                                                                                                        +
       PARTITION p3 VALUES LESS THAN (4000) TABLESPACE pg_default,                                                                                                                                                        +
       PARTITION p4 VALUES LESS THAN (5000) TABLESPACE pg_default,                                                                                                                                                        +
       PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE pg_default                                                                                                                                                     +
   )                                                                                                                                                                                                                      +
   ENABLE ROW MOVEMENT;                                                                                                                                                                                                   +
   CREATE INDEX idx_range_a ON test_range_pt USING ubtree (a) LOCAL(PARTITION p1_a_idx, PARTITION p2_a_idx, PARTITION p3_a_idx, PARTITION p4_a_idx, PARTITION p5_a_idx)  WITH (storage_type=USTORE) TABLESPACE pg_default;
  (1 row)

• pg_stat_get_partition_tuples_hot_updated返回给定分区id的分区热更新元组数的统计。

  在分区p1中插入10条数据并更新，统计分区p1的热更新元组数。

  INSERT INTO test_range_pt VALUES(generate_series(1,10),1,1);
  INSERT 0 10
  SELECT pg_stat_get_partition_tuples_hot_updated(49294);
  pg_stat_get_partition_tuples_hot_updated
  ------------------------------------------
  0
  (1 row)
  UPDATE test_range_pt SET b = 2;
  UPDATE 10
  SELECT pg_stat_get_partition_tuples_hot_updated(49294);
  pg_stat_get_partition_tuples_hot_updated
  ------------------------------------------
  10
  (1 row)