PostgreSQL Oracle 兼容性之 - 全局临时表 global temp table

背景

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);    

4、其他

https://postgrespro.com/roadmap/56516

参考

digoal’s 大量PostgreSQL文章入口