摘要：临时表作为一个 SQL 标准中的表类型，各个厂商在实现时，往往却不相同，甚至行为上也存在差异，本文小结下 GaussDB(DWS)的临时表使用场景。

本文分享自华为云社区《GaussDB(DWS)临时表小结》，作者： sincatter 。

语法介绍

如下为创建表的基本语法（详见手册）：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name     ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]        | table_constraint        | LIKE source_table [ like_option [...] ] }        [, ... ])    [ WITH ( {storage_parameter = value} [, ... ] ) ]    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]    [ COMPRESS | NOCOMPRESS ]    [ TABLESPACE tablespace_name ]    [ DISTRIBUTE BY { REPLICATION | { HASH ( column_name [,...] ) } } ]    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

​其中临时表相关的关键字有：

• [ GLOBAL | LOCAL ]

创建临时表时可以在 TEMP 或 TEMPORARY 前指定 GLOBAL 或 LOCAL 关键字。目前 GaussDB(DWS)设立这两个关键字，仅仅是为了兼容 SQL 标准，实际行为上无论指定的是 GLOBAL 还是 LOCAL，GaussDB(DWS)都只会创建为本地临时表，即只有 LOCAL 关键字是有效的。

• { TEMPORARY | TEMP }

TEMP 和 TEMPORARY 等价。如果指定 TEMP 或 TEMPORARY 关键字，则创建的表为临时表。临时表行为上的主要特征为只在当前会话可见，本会话结束后会自动删除。由于临时表只在当前会话创建，对于涉及对临时表操作的 DDL 语句，很容易产生 DDL 失败的报错。因此，建议 DDL 语句中不要对临时表进行操作。

• ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP}

ON COMMIT 选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。有以下三个选项：

• PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。

• DELETE ROWS：提交时删除临时表中数据。

• DROP：提交时删除此临时表。

原理介绍

GaussDB(DWS)的临时表机制继承自 PostgreSQL，临时表在元数据和数据存储上与普通表基本无差异，具体来说，临时表是通过建表时将其 Schema 指定为与 session id 相关的一个 schema，其他 session 实际上也是可以在系统表中查看到当前临时表的元数据。GaussDB(DWS)会利用 schema 进行临时表的不同 session 间隔离。这里通过两个现象去说明这个机制：

现象一：

一个 session_1 创建一个临时表：

postgres=#  create temp table tt1(a int);CREATE TABLEpostgres=# \d                                         List of relations                  Schema                  | Name | Type  | Owner |             Storage              ------------------------------------------+------+-------+-------+---------------------------------- pg_temp_coordinator1_2_4_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no}(1 row)postgres=# select relname,relnamespace from pg_class where relname like 'tt%'; relname | relnamespace ---------+-------------- tt1     |        24600(1 row)

​另外一个 session_2，可以一样可以通过 pg_class 查看临时表的表结构：

postgres=#  select relname,relnamespace from pg_class where relname like 'tt%'; relname | relnamespace ---------+-------------- tt1     |        24600(1 row)

​但 session_2 中是无法查看当前临时表中的数据：

postgres=# select * from pg_temp_coordinator1_2_4_139820525504256.tt1;ERROR:  Can only access temp objects of the current session.LINE 1: select * from pg_temp_coordinator1_2_4_139820525504256.tt1;

​现象二：

创建一个临时表后，再根据这个临时表的 schema，去创建一个相同 schema 的普通表：

postgres=# create temp table tt1(a int);CREATE TABLEpostgres=# \d                                         List of relations                  Schema                  | Name | Type  | Owner |             Storage              ------------------------------------------+------+-------+-------+---------------------------------- pg_temp_coordinator1_2_3_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no}(1 row)postgres=# create table pg_temp_coordinator1_2_3_139820525504256.tt2(a int);CREATE TABLEpostgres=# select relname,relnamespace from pg_class where relname like 'tt%'; relname | relnamespace ---------+-------------- tt1     |        24592 tt2     |        24592(2 rows)

​随后，退出当前 session，重新连接查看表状态，我们会神奇发现之前创建的临时表 tt1 消失的同时，创建的普通表 tt2 也一样消失了。

postgres=# select relname,relnamespace from pg_class where relname like 'tt%'; relname | relnamespace ---------+--------------(0 rows)

使用注意

1.   与使用永久表相比，使用临时表可以提高性能，但存在丢失数据的风险。临时表只在当前会话可见，本会话结束后将自动删除。如果数据丢失是不可接受的，请使用永久表。

2.   临时表对应的 sechema 在搜索路径中的优先级是高于其他 sechma 的，即临时表对应 schema 具有第一搜索优先级。

3.   \parallel 模式不支持创建临时表！如需使用临时表，需要在开启 parallel 之前就创建好，并在 parallel 内部使用。parallel 内部创建的临时表不会生效。

4.   PG_TOTAL_USER_RESOURCE_INFO 视图中的 used_temp_space 和 total_temp_space 可以查看当前临时表的相关空间使用情况

5.   创建临时表时，会同步创建临时 Schema，这些临时 Schema 的名称类似于 pg_temp_*和 pg_toast_temp_*

6.   CN Retry 功能开启时会为临时表数据记录日志，为保证数据一致性，在使用临时表时不能切换 CN Retry 开关状态，保持使用临时表的会话中 CN Retry 开关始终处于打开状态或者关闭状态。

7.   临时表和非日志表的存储方式建议和基表相同。当基表为行存（列存）表时，临时表和非日志表也推荐创建为行存（列存）表，可以避免行列混合关联带来的高计算代价

8.   如果上层应用，使用了连接池机制连接 GaussDB(DWS)，在使用临时表时，强烈建议将连接归还连接池之前，将临时表主动删除，避免造成连接未断开导致的数据异常

9.   在每个会话第一次使用临时表之前可以改变 temp_buffers 的值，之后的设置将是无效的

10. autoanalyze 不支持对带有 ONCOMMIT [DELETE ROWS|DROP]选项的临时表触发 autoanalyze，如需收集，需用户手动执行 analyze 操作

11. 如果创建视图时包含临时表，则该视图会自动转为临时视图

典型场景

临时存储

临时表可以减少冗余中间表的存在，在一些复杂操作时，往往需要借助一些中间表去完成功能，但一般来说普通表的创建是需要数据库管理员来统计创建维护的。临时表的存在就允许中间表用完即清，减少数据库系统中冗余表的存在。另外，临时表在使用时数据是 session 间隔离的，其他 session 不能看到当前 session 的数据，数据安全性在一定程度上也更好。

提升性能

对于过于复杂并且不易通过普通优化方法调整性能的 SQL 可以考虑拆分的方法，把 SQL 中某一部分拆分成独立的 SQL 并把执行结果存入临时表，拆分常见的场景包括但不限于：

• 作业中多个 SQL 有同样的子查询，并且子查询数据量较大。

• Plan cost 计算不准，导致子查询 hash bucket 太小，比如实际数据 1000W 行，hash bucket 只有 1000。

• 函数（如 substr,to_number）导致大数据量子查询选择度计算不准。

• 多 DN 环境下对大表做 broadcast 的子查询。

点击关注，第一时间了解华为云新鲜技术~