PG之临时表（TEMPORARY TABLE）
Tags： PGTEMPORARY TABLE临时表
简介
PostgreSQL中的临时表分两种，一种是会话级临时表，一种是事务级临时表。在会话级临时表中，数据可以存在于整个会话的生命周期中，在事务级临时表中的数据只能存在于事务的生命周期中。默认创建的是会话级别的临时表。
- 不管是会话级还是事务级的临时表，当会话结束后，临时表会消失，这和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.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。
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) |
但一旦事务结束，这些刚刚插入临时表的数据又立刻不见了，如下：
1 2 3 4 5 6 | lhrdb=*# commit; COMMIT lhrdb=# select * from t_tmp; id ---- (0 rows) |
此外，还可以用on commit preserve rows选项来定义临时表，通过该选项定义的临时表，事务结束时，数据依然还会存在，直到会话结束为止，如下所示：
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 27 28 29 30 31 32 | lhrdb=# drop table t_tmp; DROP TABLE lhrdb=# create temp table t_tmp(id int) on commit preserve rows; CREATE TABLE lhrdb=# insert into t_tmp values(3); INSERT 0 1 lhrdb=# select * from t_tmp; id ---- 3 (1 row) lhrdb=# commit; WARNING: there is no transaction in progress COMMIT lhrdb=# select * from t_tmp; id ---- 3 (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 * from t_tmp; ERROR: relation "t_tmp" does not exist LINE 1: select * from t_tmp; ^ |
可见，单个语句的insert事务结束后，依然可以查到数据的存在，这也是临时表定义不设置on commit选项时的默认行为。
全局临时表可以使用插件：pgtt，可以参考：https://pgfans.cn/a?id=1241