标簽
PostgreSQL , 臨時表 , 全局臨時表 , unlogged table , advisory lock
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 暫時不支援類似Oracle風格的臨時表。
PostgreSQL 臨時表結構是會話級别的，而在Oracle中，臨時表的結構是全局有效的，隻是資料會話之間獨立。
Oracle 全局臨時表可以指定SCHEMA，而PostgreSQL的臨時表不能指定SCHEMA，自動在temp臨時SCHEMA中建立。
細節差異詳見：
https://www.codeproject.com/Articles/1176045/Oracle-style-global-temporary-tables-for-PostgreSQL為了讓PostgreSQL臨時表的使用與Oracle相容，除了核心層面相容之外，目前隻能在使用時注意。
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
...........
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
-- 送出後，保留記錄 PRESERVE ROWS
-- 送出後，清空臨時表記錄 DELETE ROWS
-- 送出後，删除臨時表 DROP
使用者可以使用以下方式來使用臨時表：
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E6%96%B9%E6%B3%951%E6%8E%A8%E8%8D%90%E4%BD%BF%E7%94%A8%E4%BD%BF%E7%94%A8-trigger--inherit-%E4%BB%A3%E6%9B%BF%E4%B8%B4%E6%97%B6%E8%A1%A8-%E7%94%A8%E6%88%B7%E4%BD%BF%E7%94%A8%E5%BD%A2%E6%80%81%E4%B8%8Eoracle%E4%B8%80%E8%87%B4 方法1(推薦使用)、使用 trigger + inherit 代替臨時表 (使用者使用形态與ORACLE一緻)
思想來自如下，與之不一樣的地方是，直接使用的表觸發器+繼承功能來實作的。
以上這個連結的方法問題：1、不支援truncate, copy。2、使用函數傳回所有記錄，會有性能問題。3、無法使用遊标。4、索引使用比較麻煩。
本文下面的方法沒有以上問題（直接TRUNCATE的支援需要打個PATCH, 社群已于2018.12.27後支援
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e504f01da11db0181d7b28bb30cb5eeb0767184），所有操作(insert,update,delete,select)與直接使用臨時表一樣。INSERT性能會有下降（使用本方法88417/s，直接寫表1111111/s），一般的使用性能也是足夠夠的；對于PG 10以上版本，使用中間表可以增強性能
《PostgreSQL 10.0 preview 功能增強 - 觸發器函數内置中間表》例子
建立一個臨時表 stage.abc。
1、建立一個schema，放臨時表
create schema IF NOT EXISTS stage;
2、建立表stage.abc
drop table if exists stage.abc;
create table if not exists stage.abc (id int primary key, info text, crt_time timestamp);
3、建立這個"stage.abc表"的觸發器，自動基于它建立臨時表，并加入它的繼承關系
所有PG版本通用，自定義insert before觸發器函數
create or replace function public.abc_tg1() returns trigger as $$
declare
begin
-- 如果臨時表的定義修改，修改這個觸發器的内容，即表名abc，使用你的實際名字替換
-- 注意這裡不要加schema.，否則就寫入非臨時表了。
insert into abc values (new.*);
return null;
-- 第一次插入會插入普通父表，是以建立父表的AFTER觸發器，報錯，即回到這裡處理。
exception when others then
-- 根據臨時表的業務需要使用 on commit PRESERVE|DELETE rows
execute format('create temp table if not exists %I (like %I.%I including all) inherits (%I.%I) on commit PRESERVE ROWS',
TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_TABLE_NAME);
-- 如果臨時表的定義修改，修改這個觸發器的内容，即表名abc，使用你的實際名字替換
-- 注意這裡不要加schema.，否則就寫入非臨時表了。
insert into abc values (new.*);
return null;
end;
$$ language plpgsql strict set client_min_messages = error;
10以後的版本可用批量寫入臨時表加速，自定義insert before觸發器函數
略...
自定義insert after觸發器函數，用于加速insert
(避免每次都要執行
perform 1 from pg_class where relpersistence='t' and relname=TG_TABLE_NAME and pg_table_is_visible(oid);
)
create or replace function public.abc_tg2() returns trigger as $$
declare
begin
RAISE EXCEPTION 'Do not insert direct to parent persistence table.';
return null;
end;
$$ language plpgsql strict set client_min_messages = error;
4、建立stage.abc的insert before觸發器
create trigger tg1 before insert on stage.abc for each row execute function public.abc_tg1();
create trigger tg2 after insert on stage.abc for each row execute function public.abc_tg2();
5、測試，完全OK
insert into stage.abc values (1,'test',now());
insert into stage.abc values (2,'test',now());
postgres=# select tableoid, * from stage.abc;
tableoid | id | info | crt_time
----------+----+------+----------------------------
32224674 | 1 | test | 2018-12-25 09:38:34.252316
32224674 | 2 | test | 2018-12-25 09:38:34.257408
(2 rows)
postgres=# select tableoid, * from only stage.abc;
tableoid | id | info | crt_time
----------+----+------+----------
(0 rows)
postgres=# insert into stage.abc select generate_series(3,10000000);
INSERT 0 0
Time: 113095.297 ms (01:53.095)
postgres=# select count(*) from only stage.abc;
count
-------
0
(1 row)
Time: 0.464 ms
postgres=# select count(*) from stage.abc;
count
----------
10000000
(1 row)
Time: 2109.900 ms (00:02.110)
postgres=# truncate abc;
TRUNCATE TABLE
Time: 149.441 ms
postgres=# insert into abc select generate_series(1,10000000);
INSERT 0 10000000
Time: 9005.758 ms (00:09.006)
postgres=# select 10000000/9.0;
?column?
----------------------
1111111.111111111111
(1 row)
Time: 0.276 ms
postgres=# select 10000000/113.1;
?column?
--------------------
88417.329796640141
(1 row)
Time: 0.287 ms
postgres=# begin;
BEGIN
postgres=# declare a cursor for select * from stage.abc;
DECLARE CURSOR
postgres=# fetch 1 from a;
id | info | crt_time
----+------+----------
2 | |
(1 row)
postgres=# update stage.abc set info='abc',crt_time=now() where CURRENT OF a;
UPDATE 1
postgres=# end;
COMMIT
postgres=# select * from stage.abc where id=2;
id | info | crt_time
----+------+----------------------------
2 | abc | 2018-12-25 14:35:30.043199
(1 row)
postgres=# select * from only stage.abc where id=2;
id | info | crt_time
----+------+----------
(0 rows)
select, delete, update, copy, 遊标 均使用正常。
6、後期對臨時表加索引，直接操作stage.abc即可。因為我們在建立臨時表時，使用了including all子句。
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E5%B0%81%E8%A3%85%E6%88%90%E6%8E%A5%E5%8F%A3%E5%87%BD%E6%95%B0%E4%BD%BF%E7%94%A8%E6%96%B9%E4%BE%BF 封裝成接口函數使用友善。
create or replace function create_temptable(
v_schemaname name,
v_tablename name,
v_on_commit_action text default 'PRESERVE ROWS',
v_tg_schemaname name default 'public'
) returns void as $$
declare
tg_name1 text := 'v'||md5((clock_timestamp()::text||random()::text));
tg_name2 text := 'tg_for_temptable_after_insert_error'; -- 這個函數隻需要一個通用的即可
v_sql1 text;
v_sql2 text;
begin
v_sql1 := format($_$
create or replace function %I.%I() returns trigger as $__$ -- v_tg_schemaname, tg_name1
declare
begin
insert into %I values (new.*); -- v_tablename
return null;
exception when others then
execute format('create temp table if not exists %%I (like %%I.%%I including all) inherits (%%I.%%I) on commit %s', -- v_on_commit_action
TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_TABLE_NAME);
insert into %I values (new.*); -- v_tablename
return null;
end;
$__$ language plpgsql strict set client_min_messages = error;
$_$, v_tg_schemaname, tg_name1, v_tablename, v_on_commit_action, v_tablename);
execute v_sql1;
v_sql2 := format($_$
create or replace function %I.%I() returns trigger as $__$ -- v_tg_schemaname, tg_name2
declare
begin
RAISE EXCEPTION 'Do not insert direct to parent persistence table.';
return null;
end;
$__$ language plpgsql strict set client_min_messages = error;
$_$, v_tg_schemaname, tg_name2 );
execute v_sql2;
execute format($_$create trigger tg1 before insert on %I.%I for each row execute function %I.%I();$_$, v_schemaname, v_tablename, v_tg_schemaname, tg_name1);
execute format($_$create trigger tg2 after insert on %I.%I for each row execute function %I.%I();$_$, v_schemaname, v_tablename, v_tg_schemaname, tg_name2);
end;
$$ language plpgsql strict;
使用舉例
postgres=# drop table stage.abc;
DROP TABLE
postgres=# create table stage.abc(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# select create_temptable('stage','abc');
create_temptable
------------------
(1 row)
postgres=# \d+ stage.abc
Table "stage.abc"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
info | text | | | | extended | |
crt_time | timestamp without time zone | | | | plain | |
Indexes:
"abc_pkey" PRIMARY KEY, btree (id)
Triggers:
tg1 BEFORE INSERT ON stage.abc FOR EACH ROW EXECUTE PROCEDURE v70c22a86a17342eb6cb571349c85274b()
tg2 AFTER INSERT ON stage.abc FOR EACH ROW EXECUTE PROCEDURE tg_for_temptable_after_insert_error()
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E4%BD%BF%E7%94%A8%E9%99%90%E5%88%B6 使用限制
1、truncate暫不支援，請使用delete代替 (或者打patch支援truncate, 社群已支援，如果你使用的是2018.12.27後釋出的版本，不需要打PATCH)
postgres=# truncate stage.abc;
ERROR: 0A000: cannot truncate temporary tables of other sessions
LOCATION: truncate_check_rel, tablecmds.c:1743
Time: 0.626 ms
-- delete正常
delete from stage.abc;
修正這個truncate問題的PATCH如下
https://www.postgresql.org/message-id/flat/20181225004545.GB2334%40paquier.xyz#b08f63fab9997cdf09d879aaaa5a01d3diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ad8c176793..9dabfc0d36 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1416,6 +1416,19 @@ ExecuteTruncate(TruncateStmt *stmt)
/* find_all_inheritors already got lock */
rel = heap_open(childrelid, NoLock);
+
+ /*
+ * It is possible that the parent table has children that are temp
+ * tables of other backends. We cannot safely access such tables
+ * (because of buffering issues), and the best thing to do seems
+ * to be to silently ignore them.
+ */
+ if (RELATION_IS_OTHER_TEMP(newrelation))
+ {
+ heap_close(rel);
+ continue;
+ }
+
truncate_check_rel(RelationGetRelid(rel), rel->rd_rel);
truncate_check_activity(rel);
2、copy to 不支援 (copy from 正常)
原因：copy時，不讀取INHERIT表的内容。
postgres=# copy stage.abc to '/tmp/abc';
COPY 0
是以，直接使用臨時表名可以COPY TO
postgres=# copy abc to '/tmp/abc';
COPY 1000
postgres=# copy stage.abc from '/tmp/abc';
COPY 0
postgres=# select count(*) from stage.abc;
count
-------
1000
(1 row)
3、由于使用了insert觸發器，INSERT時，資料都寫入了繼承的臨時表，是以returning文法無法獲得傳回行數，記錄。
postgres=# insert into stage.abc values (-1) returning *;
id | info | crt_time
----+------+----------
(0 rows)
INSERT 0 0
4、如果需要修改臨時表的表名，必須同時修改觸發器函數的内容。
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E6%96%B9%E6%B3%952%E7%94%A8%E4%B8%B4%E6%97%B6%E8%A1%A8%E6%97%B6%E6%8F%90%E5%89%8D%E5%88%9B%E5%BB%BA 方法2、用臨時表時提前建立
1、建立臨時表模闆（一次性建立）
-- 臨時表模闆
create table tmp1_template(xxxx);
2、以後每次使用某臨時表之前，使用這個模闆表建立臨時表。
create temp table if not exists tmp_xxx (like tmp1_template including all) ON COMMIT DELETE ROWS;
3、以後要修改臨時表的結果，直接修改模闆表
alter table tmp_xxx add column c1 int;
-- 建立臨時表模闆表
create table tmp1_template (id int8 primary key, info text, crt_time timestamp);
-- 每次使用臨時表前，先使用模闆建立
create temp table if not exists tbl_tmp (like tmp1_template including all) ON COMMIT DELETE ROWS;
-- 以後要修改臨時表的結果，直接修改模闆表
alter table tmp1_template add column c1 int;
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E6%96%B9%E6%B3%953plpgsql%E4%B8%AD%E5%8F%AF%E4%BB%A5%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%951%E4%B9%9F%E5%8F%AF%E4%BB%A5%E4%BD%BF%E7%94%A8array%E4%BB%A3%E6%9B%BF%E4%B8%B4%E6%97%B6%E8%A1%A8 方法3、plpgsql中，可以使用方法1，也可以使用ARRAY代替臨時表
建立普通表（預設會建立對應的複合類型），
使用複合類型數組代替臨時表
do language plpgsql $$
declare
res tbl[]; x tbl;
begin
select array_agg(t::tbl) into res from (select id, random()::text, clock_timestamp() from generate_series(1,10) t(id)) t;
raise notice 'res: %', res;
foreach x in array res loop
raise notice 'x: %', x;
end loop;
end;
$$;
NOTICE: res: {"(1,0.0940282950177789,\"2018-07-15 23:14:44.060389\")","(2,0.922331794165075,\"2018-07-15 23:14:44.060404\")","(3,0.857550186105072,\"2018-07-15 23:14:44.060406\")","(4,0.373486907221377,\"2018-07-15 23:14:44.060408\")","(5,0.973780393600464,\"2018-07-15 23:14:44.060409\")","(6,0.502839601133019,\"2018-07-15 23:14:44.060411\")","(7,0.217925263568759,\"2018-07-15 23:14:44.060412\")","(8,0.733274032827467,\"2018-07-15 23:14:44.060413\")","(9,0.62150136847049,\"2018-07-15 23:14:44.060416\")","(10,0.241393140517175,\"2018-07-15 23:14:44.060418\")"}
NOTICE: x: (1,0.0940282950177789,"2018-07-15 23:14:44.060389")
NOTICE: x: (2,0.922331794165075,"2018-07-15 23:14:44.060404")
NOTICE: x: (3,0.857550186105072,"2018-07-15 23:14:44.060406")
NOTICE: x: (4,0.373486907221377,"2018-07-15 23:14:44.060408")
NOTICE: x: (5,0.973780393600464,"2018-07-15 23:14:44.060409")
NOTICE: x: (6,0.502839601133019,"2018-07-15 23:14:44.060411")
NOTICE: x: (7,0.217925263568759,"2018-07-15 23:14:44.060412")
NOTICE: x: (8,0.733274032827467,"2018-07-15 23:14:44.060413")
NOTICE: x: (9,0.62150136847049,"2018-07-15 23:14:44.060416")
NOTICE: x: (10,0.241393140517175,"2018-07-15 23:14:44.060418")
DO
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E6%96%B9%E6%B3%954%E9%A2%84%E5%88%9B%E5%BB%BA%E8%A1%A8%E7%BB%93%E6%9E%84%E4%BD%BF%E7%94%A8%E8%B5%B7%E6%9D%A5%E6%AF%94%E8%BE%83%E5%A4%8D%E6%9D%82%E4%B8%8D%E6%8E%A8%E8%8D%90 方法4、預建立表結構，使用起來比較複雜，不推薦
建立父表
預建立一些繼承表
使用時，使用advisory lock保護，挑選其中一個繼承表使用
-- 建立父表
create table tmp1(id int, info text, crt_time timestamp);
-- 建立100個子表
do language plpgsql $$
declare
begin
for i in 1..100 loop
execute format('create unlogged table tmp1_%s (like tmp1 including all) inherits (tmp1)', i);
end loop;
end;
$$;
建立加鎖函數，傳回值即字尾
create or replace function get_lock() returns int as $$
declare
begin
for i in 1..100 loop
if pg_try_advisory_lock(i) then
return i;
end if;
end loop;
return '-1';
end;
$$ language plpgsql strict;
加鎖，傳回1則使用字尾為1的臨時表
postgres=# select get_lock();
get_lock
----------
1
(1 row)
使用臨時表
truncate tmp1_1;
... 使用 tmp1_1
釋放鎖
postgres=# select pg_advisory_unlock(1);
pg_advisory_unlock
--------------------
t
(1 row)
https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E5%8F%AF%E4%BB%A5%E7%B2%BE%E7%BB%86%E5%8C%96 可以精細化
1、維護1張表，字尾ID為PK，這樣的話advisory lock id在全局都不會沖突
create table catalog_tmp (
tmp_tbl name,
prefix name,
suffix int primary key
);
create index idx_catalog_tmp_1 on catalog_tmp(tmp_tbl);
insert into catalog_tmp select 'tmp1','tmp1',generate_series(1,100);
2、申請臨時表鎖時，使用一個函數，從前面的表中擷取前字尾，直接傳回表名。
create or replace function get_tmp(name) returns text as $$
declare
i int;
v name;
begin
for i,v in select suffix,prefix from catalog_tmp where tmp_tbl=$1
loop
if pg_try_advisory_lock(i) then
return v||'_'||i;
end if;
end loop;
end;
$$ language plpgsql strict;
3、申請臨時表，傳回的就是目前會話可以使用的臨時表名
postgres=# select get_tmp('tmp1');
get_tmp
---------
tmp1_1
(1 row)
4、釋放臨時表的函數。
create or replace function release_tmp(name) returns void as $$
declare
begin
loop
if not pg_advisory_unlock(substring($1,'_(\d*)$')::int) then
return;
end if;
end loop;
end;
$$ language plpgsql strict;
釋放臨時表(注意，不釋放的話，其他會話就不可用使用這個臨時表)
select release_tmp('tmp1_1');
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)