合 PG和GreenPlum中的临时表（TEMPORARY TABLE）
Tags： PGGreenPlumPostgreSQL临时表TEMPORARY TABLE
👉 本文共约2201个字，系统预计阅读时间或需9分钟。
目录
简介
PostgreSQL中的临时表分两种，一种是会话级临时表，一种是事务级临时表。
在会话级临时表中，数据可以存在于整个会话的生命周期中，
在事务级临时表中的数据只能存在于事务的生命周期中。默认创建的是会话级别的临时表。
- 不管是会话级还是事务级的临时表，当会话结束后，临时表会消失（被drop掉），这和Oracle数据库不同。Oracle数据库当会话结束后，数据消失，而表定义依然存在。
- “ON COMMIT” 子句有三种形式,默认使用的是PRESERVE ROWS，即会话临时表：
(1)ON COMMIT PRESERVE ROWS 表示临时表的数据在事务结束后保留，默认值，表示会话级临时表;
(2)ON COMMIT DELETE ROWS 表示临时表的数据在事务结束后truncate掉，表示事务级临时表，事务结束，删除数据;
(3)ON COMMIT DROP 表示临时表在事务结束后删除，表示事务级临时表，事务结束，删除临时表。 - Oracle中的临时表，创建后，虽然各个会话间的数据是互相隔离的，也就是一个会话看不到其他会话的数据，但定义是共用的。而Postgresql中的临时表，创建后，不但不同会话间的数据是相互隔离的，就连临时表的定义也是不同会话间相互隔离的，也就是一个会话创建的临时表，不能被其他会话看到。
- 使用pg_basebackup备份时，不会备份TEMPORARY table和 UNLOGGED table，可以参考：https://www.dbaup.com/pg_basebackupbubeifentemporary-tablehe-unlogged-table.html#pg_basebackup_bu_bei_fenTEMPORARY_table_he_UNLOGGED_table
- PostgreSQL临时表是schema下所生成的一个特殊的表，这个schema的名称为“pg_temp_n”，其中n代表session_id（GP有这个字段，PG没有这个字段），不同的会话数字不同。
- 在PG中，一个会话创建的会话临时表不能被其他会话访问（ERROR: cannot access temporary tables of other sessions）。 但是，在GreenPlum中，一个会话创建的会话临时表是可以被其它会话访问和操作的，需要加上schema。
- 创建临时表的关键字“temporary”可以缩写为“temp”。
- PostgreSQL为了与其他数据库创建临时表的语句保持兼容，还没有“GLOBAL”和“LOCAL”关键字，但两个关键字没有用处。
实验SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create temp table t_tmp(id int); create temp table t_tmp1(id int) on commit delete rows; select schemaname,tablename,tableowner from pg_tables a where tablename like 't_tmp%'; -- GP查询，PG没有这个字段 select sess_id from pg_stat_activity AS pgsa WHERE pgsa.pid in (pg_backend_pid()); select * from pg_temp_9019501.t_tmp; select * from pg_temp_9019501.t_tmp1; insert into t_tmp values (1); insert into t_tmp1 values (1); |
示例
1、Postgresql 临时表的会话隔离性
session1：创建了临时表t_tmp。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | lhrdb=# create temp table t_tmp(id int); CREATE TABLE lhrdb=# \d t_tmp Table "pg_temp_4.t_tmp" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | lhrdb=# \d List of relations Schema | Name | Type | Owner -----------+-------+-------+---------- pg_temp_4 | t_tmp | table | postgres (1 row) lhrdb=# select schemaname,tablename,tableowner from pg_tables a where tablename='t_tmp'; schemaname | tablename | tableowner ------------+-----------+------------ pg_temp_4 | t_tmp | postgres (1 row) lhrdb=# select pg_backend_pid(); pg_backend_pid ---------------- 4891 (1 row) |
session2：以另一个会话登录相同用户名和数据库，查看session1中创建的临时表，既不能查询表，也不能查看表结构，但可以通过系统视图看到该临时表的存在。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | lhrdb=# \d Did not find any relations. lhrdb=# select * from t_tmp; ERROR: relation "t_tmp" does not exist LINE 1: select * from t_tmp; lhrdb=# select schemaname,tablename,tableowner from pg_tables a where tablename='t_tmp'; schemaname | tablename | tableowner ------------+-----------+------------ pg_temp_4 | t_tmp | postgres (1 row) lhrdb=# select pg_backend_pid(); pg_backend_pid ---------------- 5033 (1 row) |
2、Posgresql临时表的易挥发性
Oracle中的临时表，一个会话的数据在会话退出时会自动消失，但临时表的定义一旦创建，就会一直存在，直到用户手动删除。而Postgresql的临时表不是这样，虽然会话退出会话数据也会自动消失，但定义也会随着会话的退出而消失，也就是说，Postgresql中的临时表的生命周期最长就是会话生命周期，甚至更短。
首先，退出会话1，然后查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | lhrdb=# select pg_backend_pid(); pg_backend_pid ---------------- 4891 (1 row) lhrdb=# exit C:\Users\lhrxxt>psql -U postgres -h192.168.66.35 -p 15433 -d lhrdb Password for user postgres: psql (13.3) Type "help" for help. lhrdb=# select pg_backend_pid(); pg_backend_pid ---------------- 5673 (1 row) lhrdb=# select schemaname,tablename,tableowner from pg_tables a where tablename='t_tmp'; schemaname | tablename | tableowner ------------+-----------+------------ (0 rows) |
上图是前面session1退出会话后，再次登录查询之前创建的临时表t_tmp，已经查不到了，哪怕是临时表定义也没了。
3、Postgresql临时表数据的易挥发性
Oracle中临时表的数据，会随着会话事务或会话的结束而自动消失，主要看创建临时表时的相关选项。Postgresql临时表中的数据，也有类似的功能，除了通过on commit drop选项可以设置事务结束临时表就消失外，还可以通过on commit相关选项，分别控制临时表的数据在事务结束消失（仅仅数据消失，定义还存在）和会话结束消失（数据和表定义都消失）。
1 2 3 4 5 6 7 8 9 | lhrdb=# create temp table t_tmp(id int) on commit delete rows; CREATE TABLE lhrdb=# lhrdb=# insert into t_tmp values(1); INSERT 0 1 lhrdb=# select * from t_tmp; id ---- (0 rows) |
可以看到，虽然成功创建了临时表，也往其中成功插入了数据，可是再查询数据时，虽然表定义还在，但数据没了，这是因为定义时用了on commit delete rows选项，就是事务结束，数据就消失，这里的insert into语句默认为一个事务，执行完事务就算结束，所以，插入的数据立刻又消失了，如下图，显式的定义了事务的开始和结束，事务期间，插入的数据可以查到：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | lhrdb=# begin; BEGIN lhrdb=*# insert into t_tmp values(1); INSERT 0 1 lhrdb=*# select * from t_tmp; id ---- 1 (1 row) lhrdb=*# insert into t_tmp values(2); INSERT 0 1 lhrdb=*# select * from t_tmp; id ---- 1 2 (2 rows) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
全局临时表可以使用插件：pgtt，可以参考：https://pgfans.cn/a?id=1241