PostgreSQLOracle相容性之-全域性臨時表globaltemptable

標籤

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

4、其他

https://postgrespro.com/roadmap/56516