GaussDB(DWS)临时表小结

1. 前言

适用版本：【8.1.1(及以上)】

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

2. 语法介绍

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

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=# create temp table tmp1(a int,b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# insert into tmp1 values(1,1);
INSERT 0 1
gaussdb=# create table tmp1(a int,b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# select *from tmp1;
 a | b
---+---
 1 | 1
(1 row)

gaussdb=# select *from public.tmp1;
 a | b
---+---
(0 rows)

视图：基于临时表创建的视图是临时视图。

postgres=# create view tmp_v1 as select *from tmp1;
NOTICE:  view "tmp_v1" will be a temporary view
CREATE VIEW

3. 原理介绍

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

现象一：

一个session_1创建一个临时表：

postgres=#  create temp table tt1(a int);
CREATE TABLE
postgres=# \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 TABLE
postgres=# \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 TABLE
postgres=# 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)

4. 使用注意

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不支持对带有ON COMMIT [DELETE ROWS|DROP]选项的临时表触发autoanalyze，如需收集，需用户手动执行analyze操作
11. 如果创建视图时包含临时表，则该视图会自动转为临时视图

5. 典型场景

临时存储

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

提升性能

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

• 作业中多个SQL有同样的子查询，并且子查询数据量较大。
• Plan cost计算不准，导致子查询hash bucket太小，比如实际数据1000W行，hash bucket只有1000。
• 函数（如substr,to_number）导致大数据量子查询选择度计算不准。
• 多DN环境下对大表做broadcast的子查询。

5.1 复杂业务逻辑使用本地临时表拆分

如果业务SQL语句过于复杂，可以使用本地临时表将执行的中间结果缓存下来，从而将复杂业务逻辑拆分成多个较简单语句。简单语句的统计信息更为准备，且拆分后的业务更易于维护。
拆分常见的场景：

• 作业中多个SQL有同样的子查询，并且子查询数据量较大。
• 子查询cost计算不准。
• 函数（如substr、to_number）导致大数据量子查询选择度计算不准。
• 多DN环境下对大表做broadcast的子查询。

5.2 支持CN节点出现异常。

GaussDB(DWS) 是一款分布式架构的数据库。有多个Coordinator(CN)，关系对等。客户端可以连接任意一个CN。CN上存有表的元数据信息。在执行DDL时，会在所有DN上进行元数据的同步，保证数据一致性。如果某个CN出现异常，会导致创建表、删除表等操作执行失败，进而导致整个作业执行失败。

在这种场景，可以使用本地临时表。本地临时表只在当前会话可见。执行本地临时表的创建、ALTER、删除等操作时，只会在当前CN进行元数据的修改。这样可以不受其他CN节点异常的影响，保证业务使用连续性。

6. 数据清理

1. 会话正常退出
   会话正常退出时， 本地临时表的表定义和数据都会被删除。无法再访问原来的数据。
2. 会话异常退出或者当前CN或者某个DN节点异常时。
   出现异常时，节点的元数据和数据不会被立即删除。 GaussDB(DWS)依赖组件gs_clean工具进行本地临时表的自动定期清理。保证数据在一段周期后得到清理，防止空间持续膨胀。

7. CN Retry

CN Retry功能开启时会为临时表数据记录日志，为保证数据一致性，在使用临时表时不建议切换CN Retry开关状态，保持使用临时表的会话中CN Retry开关始终处于打开状态或者关闭状态。
在打开CN Retry时，DN节点异常重启，临时表的数据可以保证不丢失。DN重启后，仍可以访问之前的会话。
如果希望临时表不记录日志:

set max_query_retry_times = 0;

此时节点异常后会话不再可用。需要退出会话，重跑业务。如果业务中有重试机制，可以采取此种方式。

7. 使用约束

1. 如果上层应用，使用了连接池机制连接GaussDB(DWS)，在使用临时表时，强烈建议将连接归还连接池之前，将临时表主动删除，避免造成连接未断开导致的数据异常。或者使用命令DISCARD TEMP清理会话的临时表信息。
2. 扩容时忽略本地临时表。
3. 不支持gs_dump 本地临时表。

8. 友商差异

Oracle

CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ] table
  { relational_table | object_table | XMLType_table } ;

1. GaussDB(DWS)实质上，使用的是本地临时表，要求每个临时表在访问之前在同一会话或者事务中创建，临时表在session结束或者commit后会自动drop。而Oracle则实质上是全局临时表，在事务结束或者session结束后，临时表的数据会被清除，而表的元数据不会被删除。
2. GaussDB(DWS)创建临时表时不能指定schema，而Oracle查询则可以在创建临时表时指定 schema。

Teradata

Teradata支持两种临时表VOLATILE Table和GLOBAL TEMPORARY TABLE

1. VOLATILE TABLE和GaussDB(DWS)的本地临时表场景基本一致，主要区别在于，VOLATILE TABLE的元数据缓存再cache中，并不会持久化到数据字典中，附加的限制约束相对于GaussDB(DWS)而言更严格
2. GLOBAL TEMPORARY TABLE则基本等同于Oracle的全局临时表，都是在事务或session结束时，清理表内数据，但保留表的元数据。

MySQL

MySQL可以通过CREATE TEMPORARY TABLE 创建临时表，和GaussDB(DWS)的本地临时表行为上基本相同，在会话断开后，自动清理用户创建的临时表。创建和普通表同名临时表，其他会话可以查询到普通表，而创建临时 表的会话会优先查询或处理临时表。

9.总结

本文从语法、原理、使用注意、典型场景和友善差异几个方面，全方位多角度的介绍了Gaussdb的临时表。