Postgresql中临时表(temporary table)的特性和用法
熟悉Oracle的人,相比对临时表(temporary table)并不陌生,很多场景对解决问题起到不错的作用,开源库Postgresql中,也有临时表的概念,虽然和Oracle中临时表名字相同,使用方法和特性也有类似的地方,但还是有很多不同的方面,下面就对比Oracle中临时表举例说明下。
1、Postgresql 临时表的会话隔离性
Oracle中的临时表,创建后,虽然各个会话间的数据是互相隔离的,也就是一个会话看不到其他会话的数据,但定义是共用的。而Postgresql中的临时表,创建后,不但不同会话间的数据是相互隔离的,就连临时表的定义也是不同会话间相互隔离的,也就是一个会话创建的临时表,不能被其他会话看到,如下图所示:
session1:创建了临时表t_tmp。
session2:以另一个会话登录相同用户名和数据库,查看session1中创建的临时表,既不能查询表,也不能查看表结构,但可以通过系统视图看到该临时表的存在。
2、Posgresql临时表的易挥发性
Oracle中的临时表,一个会话的数据在会话退出时会自动消失,但临时表的定义一旦创建,就会已会一直存在,知道用户删除。而Postgresql的临时表不是这样,虽然会话退出会话数据也会自动消失,但定义也会随着会话的退出而消失,也就是说,Postgresql中的临时表的生命周期最长就是会话生命周期,甚至更短,如图所示:
上图是前面session1退出会话后,再次登录查询之前创建的临时表t_tmp,已经查不到了,哪怕是临时表定义也没了。
此外,在创建Postgresql临时表时,还可以设置相关选项,使得临时表在事务结束时消失,哪怕会话没结束,也会消失,如图所示:
上图中,虽然创建临时表成功了,但回头查看时却怎么也看不到表定义,虽然接连创建了两边,还是看不到表定义,也不能查,原因就是定义临时表是用了on commit drop选项,这意味着事务结束时,自动删除临时表,本人用的psql客户端工具中,每当完成一个ddl或dml时,会默认认为事务结束,所以,创建临时表的ddl语句被认为一个事务,虽然创建语句成功了,表也创建了,但随后,事务结束后被很快又删除了,所以,无论如何也看不到临时表的定义。但如果显式声明一个事务的开始和结束,即使使用了on commit drop选项,创建临时表后,也是可以看到定义的,插入数据后,也一样可以看到数据,如下图所示:
然后,使用on commit drop选项创建的临时表,一旦创建它的事务结束,临时表和其中的数据也就消失了,如下图所示:
3、Postgresql临时表数据的易挥发性
Oracle中临时表的数据,会随着会话事务或会话的结束而自动消失,主要看创建临时表时的相关选项。Postgresql临时表中的数据,也有类似的功能,除了通过on commit drop选项可以设置事务结束临时表就消失外,还可以通过on commit相关选项,分别控制临时表的数据在事务结束消失(仅仅数据消失,定义还存在)和会话结束消失(数据和表定义都消失),如下通过on commit delete rows定义了临时表:
上图可以看到,虽然成功创建了临时表,也往其中成功插入了数据,可是再查询数据时,虽然表定义还在,但数据没了,这是因为定义时用了on commit delete rows选项,就是事务结束,数据就消失,这里的insert into语句默认为一个事务,执行完事务就算结束,所以,插入的数据立刻又消失了,如下图,显式的定义了事务的开始和结束,事务期间,插入的数据可以查到:
但一旦事务结束,这些刚刚插入临时表的数据又立刻不见了,如下图:
此外,还可以用on commit preserve rows选项来定义临时表,通过该选项定义的临时表,事务结束时,数据依然还会存在,直到会话结束为止,如下图所示:
可见,单个语句的insert事务结束后,依然可以查到数据的存在,这也是临时表定义不设置on commit选项时的默认行为。
----------------------------------------------------------
《高性能SQL调优精要与案例解析》
blog1:http://www.cnblogs.com/lhdz_bj
blog2:http://blog.itpub.net/8484829
blog3:http://blog.csdn.net/tuning_optmization