自我管理PostgreSQL分区表 [英] Self-managing PostgreSQL partition tables
问题描述
我正在尝试使用Postgres进行自我管理的分区表设置。这一切都围绕着这个功能，但我似乎无法让Postgres接受我的表名。自我管理分区表触发函数的任何想法或示例？
I am trying to make a self-managing partition table setup with Postgres. It all revolves around this function but I can't seem to get Postgres to accept my table names. Any ideas or examples of self-managing partition table trigger functions?
我当前的函数：
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 但总是失败：
I want it to auto-create a table called pings_YEAR_DOY_ID but it always fails with:
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
应用更改后并修改它（date是一个unixtimestamp列，我的想法是选择时整数列比timestamp列快）。我得到以下错误，不确定我是否使用使用新的正确语法？
更新功能：
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
推荐答案
您正在混合双精度输出 date_part（），带 text' - '。这对PostgreSQL没有意义。您需要显式转换为 text 。但有一个更简单的方法可以做到这一切：
You are mixing double precision output of date_part() with text '-'. That doesn't make sense to PostgreSQL. You would need an explicit cast to text. But there is a much simpler way to do all of this:
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提供值子句。 在此阅读手册 。请改为：
You can only supply values with the USING clause. Read the manual here. Try instead:
EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
CHECK ("date" >= ''' || startdate || ''' AND
"date" < ''' || enddate || '''))
INHERITS (ping)';
或者更好的是，使用 format（） 。见下文。
Or better yet, use format(). See below.
此外，像 @a_horse回答：你需要将您的文本值放在单引号中。
Also, like @a_horse answered: You need to put your text values in single quotes.
类似这里：
EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);相反：
EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' VALUES ($1.*)'
USING NEW;
相关回答：
- How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?
除此之外：PostgreSQL中的列名允许使用date，它是每个SQL标准中的保留字。不要将列命名为日期，这会导致语法错误混淆。
Aside: While "date" is allowed for a column name in PostgreSQL it is a reserved word in every SQL standard. Don't name your column "date", it leads to confusing syntax errors.
CREATE TABLE ping (ping_id integer, the_date date);
CREATE OR REPLACE FUNCTION trg_ping_partition()
RETURNS trigger AS
$func$
DECLARE
_tbl text := to_char(NEW.the_date, '"ping_"YYYY_DDD_') || NEW.ping_id;
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' -- your schema
AND c.relname = _tbl
AND c.relkind = 'r') THEN
EXECUTE format('CREATE TABLE %I (CHECK (the_date >= %L AND
the_date < %L)) INHERITS (ping)'
, _tbl
, to_char(NEW.the_date, 'YYYY-MM-DD')
, to_char(NEW.the_date + 1, 'YYYY-MM-DD')
);
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(_tbl) || ' VALUES ($1.*)'
USING NEW;
RETURN NULL;
END
$func$ LANGUAGE plpgsql SET search_path = public;
CREATE TRIGGER insbef
BEFORE INSERT ON ping
FOR EACH ROW EXECUTE PROCEDURE trg_ping_partition();
-
更新： 后期版本的Postgres有更优雅的方法来检查表是否存在：
Update: Later versions of Postgres have more elegant ways to check if a table exists:
- How to check if a table exists in a given schema
to_char（）可以花费dateas$ 1。这会自动转换为timestamp。
日期/时间函数手册。to_char()can take adateas$1. That's converted totimestampautomatically.
The manual on date / time functions.（可选）
SETsearch_path您的函数范围，以避免更改search_path设置的不当行为。(Optionally)
SETthesearch_pathfor the scope of your function to avoid misconduct with a changedsearch_pathsetting.多种其他简化和改进。比较代码。
Multiple other simplifications and improvements. Compare the code.
测试：
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);这篇关于自我管理PostgreSQL分区表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
