PostgreSQL Oracle 相容性之 - 全局臨時表 global temp table

标簽

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相容，除了核心層面相容之外，目前隻能在使用時注意。

使用以下方式：

https://github.com/digoal/blog/blob/master/201807/20180715_01.md#1plpgsql%E4%B8%AD 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    
           

https://github.com/digoal/blog/blob/master/201807/20180715_01.md#%E9%A2%84%E5%88%9B%E5%BB%BA%E4%B8%80%E4%BA%9B%E8%A1%A8%E7%BB%93%E6%9E%84 預建立一些表結構

建立父表

預建立一些繼承表

使用時，使用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');  
           

https://github.com/digoal/blog/blob/master/201807/20180715_01.md#3%E7%94%A8%E6%97%B6%E6%8F%90%E5%89%8D%E5%88%9B%E5%BB%BA 3、用時提前建立

1、建立臨時表模闆（一次性，對應Oracle裡面的臨時表）

create table tmp1_template(xxxx);
           

2、以後每次使用某臨時表之前，使用這個臨時表對應的模闆建立。

create temp table tmp_xxx (like 模闆表名 including all);    
           

https://github.com/digoal/blog/blob/master/201807/20180715_01.md#4%E5%85%B6%E4%BB%96 4、其他

https://postgrespro.com/roadmap/56516