postgres 分區觸發器和檢查子表

我找到了一些 用於創建分區表的範例 程式碼。

基本上，在日期欄位上設置主表和分區，然後將其與實體類型連接，從主表繼承。

觸發器的第一次呼叫很好，但隨後的呼叫失敗，因為檢查現有子表的“if”條件會產生 false，但在嘗試為已經存在的表創建表時失敗。

我嘗試了 3 種方法來檢查子表是否存在，但都沒有產生正確的答案。

這兩個陳述證明了矛盾：

SELECT count(*) FROM pg_class WHERE relname='DRIVER_2016_12_28';
count(*)
0

select count(*) from DRIVER_2016_12_28;
count(*)
1

主表：

CREATE TABLE partition_test
(
 pk SERIAL PRIMARY KEY NOT NULL,
 id VARCHAR(36) NOT NULL,
 organizationid VARCHAR(36) NOT NULL,
 lat REAL NOT NULL,
 lon REAL NOT NULL,
 basetype VARCHAR(16) NOT NULL,
 name VARCHAR(64) NOT NULL,
 updatetimestamp TIMESTAMP NOT NULL
) WITHOUT OIDS;

觸發程式碼：

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
 partition_date TEXT;
 partition TEXT;
BEGIN
 partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
 partition := NEW.baseType || '_' || partition_date;

 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
   EXECUTE 'CREATE TABLE ' || partition || ' (check (updatetimestamp = ''' || NEW.updatetimestamp || ''')) INHERITS (' || TG_RELNAME || ');';
   EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (id)';
   EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (organizationid)';
   EXECUTE 'CREATE INDEX ON ' || partition || ' (lat)';
   EXECUTE 'CREATE INDEX ON ' || partition || ' (lon)';
   EXECUTE 'CREATE INDEX ON ' || partition || ' (updatetimestamp)';
   RAISE NOTICE 'A partition has been created %',partition;
 END IF;
 EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
 RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER telemetry_partition_insert_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE telemetry_insert_trigger();

插入範例：

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

第一個輸出

$$ 2016-12-28 12:15:12 $$ $$ 00000 $$已創建分區 DRIVER_2016_12_28$$ 2016-12-28 12:15:12 $$33ms 完成

第二：

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

$$ 2016-12-28 12:16:15 $$ $$ 42P07 $$錯誤：關係“driver_2016_12_28”已經存在$$ 2016-12-28 12:16:15 $$其中： SQL 語句“CREATE TABLE DRIVER_2016_12_28 (check (updatetimestamp = ‘2016-12-28 12:16:15.467012’)) INHERITS (partition_test);”$$ 2016-12-28 12:16:15 $$ PL/pgSQL 函式 telemetry_insert_trigger() 第 11 行的 EXECUTE 語句

問題：如何有效地檢查子表是否存在，以免出現此錯誤？如果您想指出我的觸發器存在的問題，那也很好

我建議quote_ident在生成動態句子時始終使用，以確保您的標識沒有大寫/小寫問題，或者您可以使用帶有空格或特殊字元的標識符，並且如果您的標識是參數，則避免 SQL 注入你的控制。

也就是說，您的觸發函式應如下所示：

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
 partition_date TEXT;
 partition TEXT;
 start_ts TIMESTAMP ;
 end_ts TIMESTAMP ;
BEGIN
 partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
 partition := NEW.baseType || '_' || partition_date;
 start_ts := partition_date::timestamp ;
 end_ts := start_ts + interval '1 day' ;

 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
   EXECUTE 'CREATE TABLE '    || quote_ident(partition) 
       || ' (check (updatetimestamp >= ''' || start_ts || ''' ' 
       || '     AND updatetimestamp <  ''' || end_ts || ''')) ' 
       || ' INHERITS (' || TG_RELNAME || ');';
   EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
   EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
   EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
   EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
   EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
   RAISE NOTICE 'A partition has been created %', partition;
 END IF;
 EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
 RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

查看PostgreSQL 文件中 quote_literal 的使用範例。

作為一個偏好問題，我更喜歡將所有表的名稱都小寫並且沒有特殊字元（這樣我就沒有"Table Names with Double Quotes", 但是table_names_without_quotes），因為我認為程式碼噪音更小，可讀性更強。但是，這又是一個品味問題。

引用自：https://dba.stackexchange.com/questions/159372