工具函数示例

• 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)                            + 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;(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 10SELECT 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 10SELECT pg_stat_get_partition_tuples_hot_updated(49294);pg_stat_get_partition_tuples_hot_updated------------------------------------------10(1 row)