PG之临时表(TEMPORARY TABLE)

1    882    5

Tags:

👉 本文共约1726个字,系统预计阅读时间或需7分钟。

简介

PostgreSQL中的临时表分两种,一种是会话级临时表,一种是事务级临时表。在会话级临时表中,数据可以存在于整个会话的生命周期中,在事务级临时表中的数据只能存在于事务的生命周期中。默认创建的是会话级别的临时表。

  1. 不管是会话级还是事务级的临时表,当会话结束后,临时表会消失,这和Oracle数据库不同。Oracle数据库当会话结束后,数据消失,而表依然存在。
  2. “ON COMMIT” 子句有三种形式,默认使用的是PRESERVE ROWS,即会话临时表:
    (1)ON COMMIT PRESERVE ROWS 表示临时表的数据在事务结束后保留,默认值,表示会话级临时表;
    (2)ON COMMIT DELETE ROWS 表示临时表的数据在事务结束后truncate掉,表示事务级临时表,事务结束,删除数据;
    (3)ON COMMIT DROP 表示临时表在事务结束后删除,表示事务级临时表,事务结束,删除临时表。
  3. Oracle中的临时表,创建后,虽然各个会话间的数据是互相隔离的,也就是一个会话看不到其他会话的数据,但定义是共用的。而Postgresql中的临时表,创建后,不但不同会话间的数据是相互隔离的,就连临时表的定义也是不同会话间相互隔离的,也就是一个会话创建的临时表,不能被其他会话看到。
  4. 使用pg_basebackup备份时,不会备份TEMPORARY table和 UNLOGGED table,可以参考:https://www.xmmup.com/pg_basebackupbubeifentemporary-tablehe-unlogged-table.html#pg_basebackup_bu_bei_fenTEMPORARY_table_he_UNLOGGED_table

PostgreSQL临时表是schema下所生成的一个特殊的表,这个schema的名称为“pg_temp_n”,其中n代表数字,不同的session数字不同。

一个会话创建的临时表不能被其他会话访问。

创建临时表的关键字“temporary”可以缩写为“temp”。

PostgreSQL为了与其他数据库创建临时表的语句保持兼容,还没有“GLOBAL”和“LOCAL”关键字,但两个关键字没有用处。

示例

1、Postgresql 临时表的会话隔离性

session1:创建了临时表t_tmp。

session2:以另一个会话登录相同用户名和数据库,查看session1中创建的临时表,既不能查询表,也不能查看表结构,但可以通过系统视图看到该临时表的存在。

2、Posgresql临时表的易挥发性

Oracle中的临时表,一个会话的数据在会话退出时会自动消失,但临时表的定义一旦创建,就会一直存在,直到用户手动删除。而Postgresql的临时表不是这样,虽然会话退出会话数据也会自动消失,但定义也会随着会话的退出而消失,也就是说,Postgresql中的临时表的生命周期最长就是会话生命周期,甚至更短。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

首先,退出会话1,然后查询:

上图是前面session1退出会话后,再次登录查询之前创建的临时表t_tmp,已经查不到了,哪怕是临时表定义也没了。

3、Postgresql临时表数据的易挥发性

Oracle中临时表的数据,会随着会话事务或会话的结束而自动消失,主要看创建临时表时的相关选项。Postgresql临时表中的数据,也有类似的功能,除了通过on commit drop选项可以设置事务结束临时表就消失外,还可以通过on commit相关选项,分别控制临时表的数据在事务结束消失(仅仅数据消失,定义还存在)和会话结束消失(数据和表定义都消失)。

可以看到,虽然成功创建了临时表,也往其中成功插入了数据,可是再查询数据时,虽然表定义还在,但数据没了,这是因为定义时用了on commit delete rows选项,就是事务结束,数据就消失,这里的insert into语句默认为一个事务,执行完事务就算结束,所以,插入的数据立刻又消失了,如下图,显式的定义了事务的开始和结束,事务期间,插入的数据可以查到:

但一旦事务结束,这些刚刚插入临时表的数据又立刻不见了,如下:

此外,还可以用on commit preserve rows选项来定义临时表,通过该选项定义的临时表,事务结束时,数据依然还会存在,直到会话结束为止,如下所示:

可见,单个语句的insert事务结束后,依然可以查到数据的存在,这也是临时表定义不设置on commit选项时的默认行为。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

1 条回复

  1. 头像 小麦苗说道:

    全局临时表可以使用插件:pgtt,可以参考:https://pgfans.cn/a?id=1241

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

20 − 5 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部