PostgreSQL Oracle 兼容性之 - 全局临时表 global temp table-阿里云开发者社区

开发者社区> 德哥> 正文

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

简介:
+关注继续查看

标签

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL临时表机制的演变(5.6-8.0)
MySQL8.0 临时表 新特性
894 0
SQL语句查询结果集中的动态修改案例(临时表+游标)
本文转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090314.html 曾经一位朋友问我这样一个问题:怎样在查询出来的结果集中增加一个新列(有规律)? 如:数据库中的结构和数据如下: (tableName : People)...
618 0
【PG云栖周刊】第2期·PostGIS北京3月活动,存储扩展引擎zheap,Oracle兼容性之 - 数据类型
3月17-18 PG象行中国2018-地理信息处理GIS专题(北京站),活动地点:北京师范大学 艺术楼 201教室;PostgreSQL最新存储扩展引擎zheap,计划加入到PG12;PostgreSQL 10.3更新版本发布;云数据库PPAS Oracle兼容性 - 数据类型;PostgreSQL中HOOK的使用,避免误删库的问题。
4793 0
sql创建临时表或视图增加 自增列
select row_number()over(order by [createtime] asc) ida,* from [Opro].[dbo].[DailyReport2010])
745 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载