- 全局临时表
全局临时表的元数据对所有会话可见，会话结束后元数据继续存在。
全局临时表有两种模式：
一种是基于会话级别的(ON COMMIT PRESERVE ROWS)，当会话结束时自动清空用户数据；
一种是基于事务级别的(ON COMMIT DELETE ROWS)，当执行commit或rollback时自动清空用户数据。
表时如果没有指定ON COMMIT选项，则缺省为会话级别。 - 本地临时表
本地临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的数据库节点故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。TEMP和TEMPORARY等价。
语法
CreateTable ::= CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ AUTO_INCREMENT [ = ] value ]
[ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] [ [DEFAULT] COLLATE [ = ] default_collation ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMMENT =? 'text' ];会话一
CREATE GLOBAL TEMPORARY TABLE global_temp_table (
id INT,
name VARCHAR(50)
) ON COMMIT PRESERVE ROWS;
CREATE TEMPORARY TABLE local_temp_table (
id INT,
name VARCHAR(50)
) ON COMMIT PRESERVE ROWS;
auxdb=# SELECT table_name FROM information_schema.tables WHERE table_name = 'global_temp_table';
table_name
-------------------
global_temp_table
(1 row)
auxdb=# SELECT table_name FROM information_schema.tables WHERE table_name = 'local_temp_table';
table_name
------------------
local_temp_table
(1 row)
会话二
auxdb=# SELECT table_name FROM information_schema.tables WHERE table_name = 'global_temp_table';
table_name
-------------------
global_temp_table
(1 row)
auxdb=# SELECT table_name FROM information_schema.tables WHERE table_name = 'local_temp_table';
table_name
------------
(0 rows)
其他注意事项：
- 本地临时表通过每个会话独立的以pg_temp开头的schema来保证只对当前会话可见，因此，不建议用户在日常操作中手动删除以pg_temp，pg_toast_temp开头的schema。
- 如果建表时不指定TEMPORARY/TEMP关键字，而指定表的schema为当前会话的pg_temp_开头的schema，则此表会被创建为临时表。
- ALTER/DROP全局临时表和索引，如果其它会话正在使用它，禁止操作（ALTER INDEX index_name REBUILD除外）。
- 全局临时表的DDL只会影响当前会话的用户数据和索引。例如truncate、reindex、analyze只对当前会话有效。
- 全局临时表功能可以通过设置GUC参数max_active_global_temporary_table控制是否启用。如果max_active_global_temporary_table=0，关闭全局临时表功能。
- 临时表只对当前会话可见，因此不支持与\parallel on并行执行一起使用。
- 临时表不支持主备切换。
- 全局临时表不响应自动清理，在长链接场景使用时尽量使用on commit delete rows的全局临时表，或定期手动执行vacuum，否则可能导致clog日志不回收。
一、临时表
2.1、会话级临时表
会话级临时表中数据的生命周期为整个会话。语法如下
CREATE TEMPORARY | TEMP TABLE ...
TEMP与TEMPORARY等价
auxdb=# create temp table temp_ta(id int , name varchar(20));
CREATE TABLE
auxdb=#
auxdb=# insert into temp_ta values (1,'A');
INSERT 0 1
auxdb=# insert into temp_ta values (2,'B');
INSERT 0 1
auxdb=# insert into temp_ta values (3,'C');
INSERT 0 1
auxdb=#
auxdb=# select * from temp_ta;
id | name
----+------
1 | A
2 | B
3 | C
(3 rows)
auxdb=# \q
[sysomm@db1 ~]$ gsql -r
gsql ((MogDB 5.0.1 build ae6d2ada) compiled at 2023-08-16 09:07:43 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# \c auxdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "auxdb" as user "sysomm".
auxdb=# select * from temp_ta;
ERROR: relation "temp_ta" does not exist on dn_6001
LINE 1: select * from temp_ta;
^
多个会话允许创建同名临时表，如下会话一创建完临时表的同时打开新的会话，会话二创建同名临时表，运行结果如下
auxdb=# create temp table temp_ta(id int , name varchar(20));
CREATE TABLE
auxdb=#
auxdb=# insert into temp_ta values (1,'A');
INSERT 0 1
auxdb=# insert into temp_ta values (2,'B');
INSERT 0 1
auxdb=# insert into temp_ta values (3,'C');
INSERT 0 1
auxdb=#
auxdb=# select * from temp_ta;
id | name
----+------
1 | A
2 | B
3 | C
(3 rows)
auxdb=#
auxdb=#
auxdb=# explain verbose select * from temp_ta;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on pg_temp_dn_6001_1_2_46920789591808.temp_ta (cost=0.00..18.88 rows=888 width=62)
Output: id, name
(2 rows)
auxdb=# create temp table temp_ta(id int , name varchar(20));
CREATE TABLE
auxdb=#
auxdb=# insert into temp_ta values (1,'AB');
INSERT 0 1
auxdb=# insert into temp_ta values (2,'BB');
INSERT 0 1
auxdb=# insert into temp_ta values (3,'CB');
INSERT 0 1
auxdb=#
auxdb=# select * from temp_ta;
id | name
----+------
1 | AB
2 | BB
3 | CB
(3 rows)
auxdb=#
auxdb=# explain verbose select * from temp_ta;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on pg_temp_dn_6001_1_10_46920990131968.temp_ta (cost=0.00..18.88 rows=888 width=62)
Output: id, name
(2 rows)
两者最主要的区别在于，temp_ta的schema不一致！
会话一为pg_temp_dn_6001_1_2_46920789591808.temp_ta
会话二为pg_temp_dn_6001_1_10_46920990131968.temp_ta
2.2、事务级临时表
语法如下
CREATE TEMPORARY | TEMP TABLE ... ON COMMIT ...
在ON COMMIT后面有三个选项分别是
DELETE ROWS：数据仅存在事务周期中，事务提交后，临时表中的数据就清除掉了。
PERSERVE ROWS：（默认）创建会话级临时表的选项，可以不写。
2.2.1、ON COMMIT DELETE ROWS
auxdb=# create temp table temp_ta(id int , name varchar(20)) ON COMMIT DELETE ROWS;
verbose select * from temp_ta;CREATE TABLE
auxdb=#
auxdb=# begin;
BEGIN
auxdb=# insert into temp_ta values (1,'A');
INSERT 0 1
auxdb=# insert into temp_ta values (2,'B');
INSERT 0 1
auxdb=# insert into temp_ta values (3,'C');
INSERT 0 1
auxdb=#
auxdb=# select * from temp_ta;
id | name
----+------
1 | A
2 | B
3 | C
(3 rows)
auxdb=#
auxdb=# end;
COMMIT
auxdb=#
auxdb=# select * from temp_ta;
id | name
----+------
(0 rows)
auxdb=#
auxdb=# explain verbose select * from temp_ta;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on pg_temp_dn_6001_1_4_46920948127488.temp_ta (cost=0.00..18.88 rows=888 width=62)
Output: id, name
(2 rows)
