标签
PostgreSQL , 临时表 , 全局临时表 , unlogged table , advisory lock
背景
PostgreSQL 临时表结构是会话级别的，而在Oracle中，临时表的结构是全局有效的，只是数据会话之间独立。
为了让PostgreSQL临时表的使用与Oracle兼容，除了内核层面兼容之外，目前只能在使用时注意。
使用以下方式：
1、plpgsql中
创建普通表（默认会创建对应的复合类型），
使用复合类型数组代替临时表
例子
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
预创建一些表结构
创建父表
预创建一些继承表
使用时，使用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)
可以精细化
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');
3、用时提前创建
1、创建临时表模板（一次性，对应Oracle里面的临时表）
create table tmp1_template(xxxx);
2、以后每次使用某临时表之前，使用这个临时表对应的模板创建。
create temp table tmp_xxx (like 模板表名 including all);