时间戳

首页 > 解决方案 > 如何在postgresql中需要时创建函数并触发创建新分区

sql - 如何在postgresql中需要时创建函数并触发创建新分区

问题描述

我有下表：

CREATE TABLE tests.partitioned_table (
    partition_id int4 NOT NULL,
    dummy text NULL,
    CONSTRAINT partitioned_table_pkey PRIMARY KEY (partition_id)
)
PARTITION BY LIST (partition_id);

并且想要创建一个函数，如果我尝试插入一个没有匹配分区的 partition_id 行，它将自动创建一个新分区。

我尝试了以下方法：

CREATE OR REPLACE FUNCTION create_partition_and_insert_partitioned_table() RETURNS trigger AS
  $BODY$
    DECLARE
      partition TEXT;
    BEGIN
      partition := TG_RELNAME || '_' || new.partition_id::text;
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        EXECUTE 'CREATE TABLE ' || partition || ' partition of ' || TG_RELNAME || ' for values in (' || new.partition_id|| ');';
      END IF;
      EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING partition_id;';
      RETURN NULL;
    END;
  $BODY$
     language plpgsql

使用以下触发器：

create trigger auto_partition_partitioned_table
before insert on tests.partitioned_table 
for each row execute procedure create_partition_and_insert_partitioned_table();

但是，当我尝试插入表格时出现错误

' ERROR: no partition of relation "partitioned_table" found for row'

如果我尝试在已经有分区的表中插入一行，我会收到以下错误：

SQL Error [55006]: ERROR: cannot CREATE TABLE .. PARTITION OF "partitioned_table_123" because it is being used by active queries in this session
  Where: SQL statement "CREATE TABLE partitioned_table_123_123 partition of partitioned_table_123 for values in (123);"
PL/pgSQL function create_partition_and_insert_partitioned_table() line 7 at EXECUTE

我怀疑当我运行诸如生成上述错误的查询时：

insert into tests.partitioned_table values (123, 'tete');

在触发触发器并且函数运行之前，“tests.partitioned_table”被“tests.partitioned_table_123”替换，但我不知道如何避免这种情况。

我的另一个想法是，也许我可以在抛出“无关系分区”错误时触发该函数，但我也不确定如何做到这一点。

任何和所有的帮助表示赞赏！

标签： sqlpostgresqldatabase-partitioning

解决方案

对于您的触发器，请尝试以下操作：

CREATE OR REPLACE FUNCTION create_partition_and_insert_partitioned_table() RETURNS trigger AS
  $BODY$
    DECLARE
      partition TEXT;
    BEGIN
      partition := TG_RELNAME || '_' || new.partition_id::text;
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' partition of ' || TG_RELNAME || ' for values in (' || new.partition_id|| ');';


      END IF;
      EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* returning partition_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql;

注意：如果不存在是从 pg_class 中选择的。

推荐阅读