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

标签

PostgreSQL , 临时表 , 全局临时表 , unlogged table , advisory lock

这里领取阿里云1折优惠券 

---

背景

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    

用户可以使用以下方式来使用临时表：

方法1(推荐使用)、使用 trigger + inherit 代替临时表 (用户使用形态与ORACLE一致)

思想来自如下，与之不一样的地方是，直接使用的表触发器+继承功能来实现的。

https://www.codeproject.com/Articles/1176045/Oracle-style-global-temporary-tables-for-PostgreSQL

以上这个链接的方法问题：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 te