自管理的PostgreSQL分区表

Question

我正在尝试使用Postgres制作一个自管理分区表设置。这一切都围绕着这个函数展开，但我似乎无法让Postgres接受我的表名。有任何想法或者自管理分区表触发器函数的例子吗？

我的当前函数：

DECLARE
day integer;
year integer;
tablename text;
startdate text;
enddate text;
BEGIN
day:=date_part('doy',to_timestamp(NEW.date));
year:=date_part('year',to_timestamp(NEW.date));
tablename:='pings_'||year||'_'||day||'_'||NEW.id;
-- RAISE EXCEPTION 'tablename=%',tablename;
PERFORM 'tablename' FROM pg_tables WHERE 'schemaname'=tablename;
-- RAISE EXCEPTION 'found=%',FOUND;
IF FOUND <> TRUE THEN
    startdate:=date_part('year',to_timestamp(NEW.date))||'-'||date_part('month',to_timestamp(NEW.date))||'-'||date_part('day',to_timestamp(NEW.date));
    enddate:=startdate::timestamp + INTERVAL '1 day';
    EXECUTE 'CREATE TABLE $1 (
        CHECK ( date >= DATE $2 AND date < DATE $3 )
    ) INHERITS (pings)' USING quote_ident(tablename),startdate,enddate;
END IF;
EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);
RETURN NULL;
END;

我想让它自动创建一个名为 pings_YEAR_DOY_ID 的表格，但它总是失败并显示以下错误信息：

2011-10-24 13:39:04 CDT [15804]: [1-1] ERROR:  invalid input syntax for type double precision: "-" at character 45
2011-10-24 13:39:04 CDT [15804]: [2-1] QUERY:  SELECT date_part('year',to_timestamp( $1 ))+'-'+date_part('month',to_timestamp( $2 ))+'-'+date_part('day',to_timestamp( $3 ))
2011-10-24 13:39:04 CDT [15804]: [3-1] CONTEXT:  PL/pgSQL function "ping_partition" line 15 at assignment
2011-10-24 13:39:04 CDT [15804]: [4-1] STATEMENT:  INSERT INTO pings VALUES (0,0,5);

尝试2

应用更改并进行了一些修改（日期是unixtimestamp列，我的想法是在选择时整数列比时间戳列快），我得到了下面的错误，不确定是否使用了USING NEW的正确语法？

更新后的函数：

CREATE FUNCTION ping_partition() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$DECLARE
day integer;
year integer;
tablename text;
startdate text;
enddate text;
BEGIN
day:=date_part('doy',to_timestamp(NEW.date));
year:=date_part('year',to_timestamp(NEW.date));
tablename:='pings_'||year||'_'||day||'_'||NEW.id;
-- RAISE EXCEPTION 'tablename=%',tablename;
PERFORM 'tablename' FROM pg_tables WHERE 'schemaname'=tablename;
-- RAISE EXCEPTION 'found=%',FOUND;
IF FOUND <> TRUE THEN
    startdate := to_char(to_timestamp(NEW.date), 'YYYY-MM-DD');
    enddate:=startdate::timestamp + INTERVAL '1 day';
    EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
        CHECK ( date >= EXTRACT(EPOCH FROM DATE ' || quote_literal(startdate) || ')
            AND date < EXTRACT(EPOCH FROM DATE ' || quote_literal(enddate) || ') )
    ) INHERITS (pings)';
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' SELECT $1' USING NEW; 
RETURN NULL;
END;
$_$;

我的声明：

INSERT INTO pings VALUES (0,0,5);

SQL错误：

ERROR:  column "date" is of type integer but expression is of type pings
LINE 1: INSERT INTO pings_1969_365_0 SELECT $1
                                            ^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO pings_1969_365_0 SELECT $1
CONTEXT:  PL/pgSQL function "ping_partition" line 22 at EXECUTE statement

- ehiller

---

为什么你没有粘贴函数头？只发布函数体是没有意义的。这可能对你的问题至关重要。 - Erwin Brandstetter

抱歉，我没有看到我的帖子上的评论。我正在看你的，我的错，现在已经更新了。 - ehiller

Answer 1

注意：自Postgres 10以来，通常使用声明式分区优于此情况下所使用的继承分区。

---

您混合了date_part()的double precision输出和text '-'。这对PostgreSQL来说没有意义。您需要将其显式转换为text。但有一种更简单的方法可以完成所有这些操作：

startdate:=date_part('year',to_timestamp(NEW.date))
||'-'||date_part('month',to_timestamp(NEW.date))
||'-'||date_part('day',to_timestamp(NEW.date));

请改用以下方式：

startdate := to_char(NEW.date, 'YYYY-MM-DD');

这也没有意义:

EXECUTE 'CREATE TABLE $1 (
        CHECK (date >= DATE $2 AND date < DATE $3 )
    ) INHERITS (pings)' USING quote_ident(tablename),startdate,enddate;

只能使用USING子句提供值。在这里阅读手册。尝试改用以下方式：

EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
            CHECK ("date" >= ''' || startdate || ''' AND
                   "date" <  ''' || enddate   || '''))
            INHERITS (ping)';

或者更好的办法是使用format()函数。如下所示。

另外，就像@a_horse回答的那样：你需要将文本值放在单引号内。

这里也类似：

EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);

改为：

EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' VALUES ($1.*)'
USING NEW;

相关答案：

• 如何在PostgreSQL 8.2中动态使用TG_TABLE_NAME？

顺便提一下：尽管在PostgreSQL中允许使用“date”作为列名，但它是每个SQL标准中的保留字。不要将列命名为“date”，否则会导致混淆的语法错误。

完整的可工作演示

CREATE TABLE ping (ping_id integer, the_date date);

CREATE OR REPLACE FUNCTION trg_ping_partition()
  RETURNS trigger
  LANGUAGE plpgsql SET client_min_messages = 'WARNING' AS
$func$
DECLARE
   _schema text := 'public';  -- double-quoted if necessary
   _tbl text := to_char(NEW.the_date, '"ping_"YYYY_DDD_') || NEW.ping_id;
BEGIN
   EXECUTE format('CREATE TABLE IF NOT EXISTS %1$s.%2$s
                   (CHECK (the_date >= %3$L
                       AND the_date <  %4$L)) INHERITS (%1$s.ping)'
           , _schema   -- %1$s
           , _tbl      -- %2$s  -- legal(!) name needs no quotes
           , to_char(NEW.the_date,     'YYYY-MM-DD')  -- %3$L
           , to_char(NEW.the_date + 1, 'YYYY-MM-DD')  -- %4$L
           );

   EXECUTE 'INSERT INTO ' || _tbl || ' VALUES ($1.*)'
   USING NEW; 

   RETURN NULL;
END
$func$;

CREATE TRIGGER insbef
BEFORE INSERT ON ping
FOR EACH ROW EXECUTE FUNCTION trg_ping_partition();

Postgres 9.1新增了IF NOT EXISTS子句，用于CREATE TABLE。详情请参见：

• PostgreSQL create table if not exists

Postgres 11添加了更合适的语法变体EXECUTE FUNCTION用于触发器。在旧版本中请使用EXECUTE PROCEDURE。 详情请参见：

• Trigger uses a procedure or a function?

to_char()可以将date作为$1传入。它会自动转换为timestamp。详情请参见：

• 日期/时间函数手册

我使用SET client_min_messages = 'WARNING'来控制函数的消息级别，以消除IF NOT EXISTS引起的大量通知。

还有其他多个简化和改进。请比较代码。

测试：

INSERT INTO ping VALUES (1, now()::date);
INSERT INTO ping VALUES (2, now()::date);
INSERT INTO ping VALUES (2, now()::date + 1);
INSERT INTO ping VALUES (2, now()::date + 1);

fiddle
_{旧版SQL Fiddle}

- Erwin Brandstetter

---

谢谢，这帮了我很多（我也更新了我的帖子）。我认为我在使用EXECUTE中的USING NEW语法方面遇到了一些问题，但除了你指向我的内容之外，我找不到任何好的在线示例。 - ehiller

@variable：好的，我已经修正了我的答案并提供了完整的解决方案。但请仔细阅读Pavel的答案。最好提前创建分区表！ - Erwin Brandstetter

我已经将您的代码修改为我的最终函数，非常感谢您的帮助。我已经将自动删除、索引和ID集成在其中。此外，我将日期列设置为bigint而不是date，我猜测这样在SELECTS上会更快。谢谢！ - ehiller

Answer 2

在PostgreSQL中，动态分区只是一个不好的想法。在多用户环境下，您的代码不安全。要使其安全，您必须使用锁定，这会降低执行速度。最佳分区数约为100个。您可以提前轻松创建这么多分区，以极大地简化所需的分区逻辑。

- Pavel Stehule

---

你能详细说明一下在多用户环境下为什么不安全吗？（我猜你是指“多连接环境”）。这对我来说并不是显而易见的。 - jpmc26

没有锁定会出现竞态条件。你可以有两个并行的插入 - “IF NOT EXISTS”对于两个连接都是真的，而“EXECUTE”将在两个连接中被处理 - 一个应该会失败，一个插入将丢失。可能对于一些高频数据和不重要的数据来说，这可能不是问题，但它仍然是脏代码。 - Pavel Stehule

Answer 3

您需要将日期字面量放在单引号中。目前，您正在执行类似于以下内容的操作：

 CHECK ( date >= DATE 2011-10-25 AND date < DATE 2011-11-25 )

这是一个无效的日期格式。在这种情况下，2011-10-25被解释为2011减去10减去25

您的代码需要使用单引号将日期字面量括起来以创建SQL：

CHECK ( date >= DATE '2011-10-25' AND date < DATE '2011-11-25' )

- user330315

Answer 4

我已经完全理解了它并且它运行得非常好，甚至还有一个30天后自动删除的功能。我希望这可以帮助到未来寻找自动分区触发器函数的人。

CREATE FUNCTION ping_partition() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
_keepdate text;
_tablename text;
_startdate text;
_enddate text;
_result record;
BEGIN
_keepdate:=to_char(to_timestamp(NEW.date) - interval '30 days', 'YYYY-MM-DD');
_startdate := to_char(to_timestamp(NEW.date), 'YYYY-MM-DD');
_tablename:='pings_'||NEW.id||'_'||_startdate;
PERFORM 1
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
AND    c.relname = _tablename
AND    n.nspname = 'pinglog';
IF NOT FOUND THEN
    _enddate:=_startdate::timestamp + INTERVAL '1 day';
    EXECUTE 'CREATE TABLE pinglog.' || quote_ident(_tablename) || ' (
        CHECK ( date >= EXTRACT(EPOCH FROM DATE ' || quote_literal(_startdate) || ')
            AND date < EXTRACT(EPOCH FROM DATE ' || quote_literal(_enddate) || ')
            AND id = ' || quote_literal(NEW.id) || '
        )
    ) INHERITS (pinglog.pings)';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx1') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (microseconds) WHERE microseconds IS NULL';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx2') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (date, id)';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx3') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (date, id, microseconds) WHERE microseconds IS NULL';
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;
FOR _result IN SELECT * FROM pg_tables WHERE schemaname='pinglog' LOOP
    IF char_length(substring(_result.tablename from '[0-9-]*$')) <> 0 AND (to_timestamp(NEW.date) - interval '30 days') > to_timestamp(substring(_result.tablename from '[0-9-]*$'),'YYYY-MM-DD') THEN
        -- RAISE EXCEPTION 'timestamp=%,table=%,found=%',to_timestamp(substring(_result.tablename from '[0-9-]*$'),'YYYY-MM-DD'),_result.tablename,char_length(substring(_result.tablename from '[0-9-]*$'));
        -- could have it check for non-existant ids as well, or for archive bit and only delete if the archive bit is not set
        EXECUTE 'DROP TABLE ' || quote_ident(_result.tablename);
    END IF;
END LOOP;
RETURN NULL;
END;
$_$;

- ehiller