ReaderMX.
PostgreSQL学徒

你真的搞懂临时数据了吗？

Pxiongcc约 14 分钟

前言

今天群里有位筒子提到："base目录下以t开头的是临时排序文件吗"，类似于下面

Image

最上方的17879634十分熟悉了，就是常规的segment数据文件，那这个t_xxx是什么东西？（说来也巧，在我整理的时候，中午坐我后面的同事就在火急火燎地处理一起因为超过临时文件限制而报错的生产问题）

分析

不难猜测，常规的表文件是以普通数字开头，而这个t文件也是位于Base目录下，那么这个t应该和临时表有关。

在PostgreSQL中，使用create table语法建表总共有3种类型，分别是常规表、无日志表和临时表，其中的GLOBAL和LOCAL仅仅是为了语法兼容，因此PostgreSQL并不支持全局临时表，可以使用pgtt插件。

For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they currently have no effect. Use of these keywords is discouraged, since future versions of PostgreSQL might adopt a more standard-compliant interpretation of their meaning.

postgres=# \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

实验一下，看下差异

[postgres@xiongcc ~]$ psql
psql (14.2)
Type "help" for help.
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          16438
(1 row)
postgres=# create table normal_t(id int);
CREATE TABLE
postgres=# select pg_relation_filepath('normal_t');
 pg_relation_filepath 
----------------------
 base/16415/16696
(1 row)
postgres=# create unlogged table unlog_t(id int);
CREATE TABLE
postgres=# select pg_relation_filepath('unlog_t');
 pg_relation_filepath 
----------------------
 base/16415/16699
(1 row)
postgres=# create temp table temp_t(id int);
CREATE TABLE
postgres=# select pg_relation_filepath('temp_t');
 pg_relation_filepath 
----------------------
 base/16415/t3_16702
(1 row)

这么看就很清晰了，t开头的便是临时表。临时表会位于一个特殊的schema下面，此例是pg_temp_3，可以看到这个3和数据文件t3_16702中的数字3是对应的。

postgres=# \d
            List of relations
  Schema   |   Name   | Type  |  Owner   
-----------+----------+-------+----------
 pg_temp_3 | temp_t   | table | postgres
 public    | normal_t | table | postgres
 public    | t1_bak   | table | postgres
 public    | t2       | table | postgres
 public    | t3       | table | postgres
 public    | t_e      | table | postgres
 public    | unlog_t  | table | postgres
(7 rows)

再开一个会话建个临时表，观察一下差异

[postgres@xiongcc ~]$ psql
psql (14.2)
Type "help" for help.
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          16583
(1 row)
postgres=# create temp table temp_t2(id int);
CREATE TABLE
postgres=# select pg_relation_filepath('temp_t2');
 pg_relation_filepath 
----------------------
 base/16415/t4_16705
(1 row)
postgres=# \dt temp_t2 
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_4 | temp_t2 | table | postgres
(1 row)

可以看到这次又变成pg_temp_4了。那么这个3和4是什么意思呢？这两个数字其实就是BackendId，表示本进程在内存中进程数组中的序号，用于表明当前连接的是哪一个会话。

回想一下之前的锁章节，其中提及到了虚拟事务ID，主要为了避免事务ID回卷，下面这一段源码中的注释挺有用

Transaction and Subtransaction Numbering
----------------------------------------
Transactions and subtransactions are assigned permanent XIDs only when/if
they first do something that requires one --- typically, insert/update/delete
a tuple, though there are a few other places that need an XID assigned.
If a subtransaction requires an XID, we always first assign one to its
parent.  This maintains the invariant that child transactions have XIDs later
than their parents, which is assumed in a number of places.
The subsidiary actions of obtaining a lock on the XID and entering it into
pg_subtrans and PG_PROC are done at the time it is assigned.
A transaction that has no XID still needs to be identified for various
purposes, notably holding locks.  For this purpose we assign a "virtual
transaction ID" or VXID to each top-level transaction.  VXIDs are formed from
two fields, the backendID and a backend-local counter; this arrangement allows
assignment of a new VXID at transaction start without any contention for
shared memory.  To ensure that a VXID isn't re-used too soon after backend
exit, we store the last local counter value into shared memory at backend
exit, and initialize it from the previous value for the same backendID slot
at backend start.  All these counters go back to zero at shared memory
re-initialization, but that's OK because VXIDs never appear anywhere on-disk.
Internally, a backend needs a way to identify subtransactions whether or not
they have XIDs; but this need only lasts as long as the parent top transaction
endures.  Therefore, we have SubTransactionId, which is somewhat like
CommandId in that it's generated from a counter that we reset at the start of
each top transaction.  The top-level transaction itself has SubTransactionId 1,
and subtransactions have IDs 2 and up.  (Zero is reserved for
InvalidSubTransactionId.)  Note that subtransactions do not have their
own VXIDs; they use the parent top transaction's VXID.

虚拟事务ID的定义就涉及到了BackendId

/*
 * Top-level transactions are identified by VirtualTransactionIDs comprising
 * PGPROC fields backendId and lxid.  For recovered prepared transactions, the
 * LocalTransactionId is an ordinary XID; LOCKTAG_VIRTUALTRANSACTION never
 * refers to that kind.  These are guaranteed unique over the short term, but
 * will be reused after a database restart or XID wraparound; hence they
 * should never be stored on disk.
 *
 * Note that struct VirtualTransactionId can not be assumed to be atomically
 * assignable as a whole.  However, type LocalTransactionId is assumed to
 * be atomically assignable, and the backend ID doesn't change often enough
 * to be a problem, so we can fetch or assign the two fields separately.
 * We deliberately refrain from using the struct within PGPROC, to prevent
 * coding errors from trying to use struct assignment with it; instead use
 * GET_VXID_FROM_PGPROC().
 */
typedef struct
{
 BackendId backendId;  /* backendId from PGPROC */
 LocalTransactionId localTransactionId; /* lxid from PGPROC */
} VirtualTransactionId;

注意这个backendId不是操作系统的进程ID，而是PostgreSQL中用来标识进程序列号的ID，至于localTransactionId也是用32位长度来表示的。虚拟事务ID在数据库重起后，就会重新使用，但是在同一个backend id下会按顺序增长。

那让我们看一下pg_locks的virtualtransaction字段，可以看到第一个数字也是3

postgres=# select virtualtransaction,pg_backend_pid() from pg_locks ;
 virtualtransaction | pg_backend_pid 
--------------------+----------------
 3/15               |          16438
 3/15               |          16438
(2 rows)

另外一个会话则是4

postgres=# select virtualtransaction,pg_backend_pid() from pg_locks ;
 virtualtransaction | pg_backend_pid 
--------------------+----------------
 4/15               |          16583
 4/15               |          16583
(2 rows)

除此之外，有没有函数可以直接获取所谓的backendId呢？当然有，pg_stat_get_backend_idset()。见如下例子：

postgres=# select pg_stat_get_backend_idset() as backendId,pg_stat_get_backend_pid(pg_stat_get_backend_idset()) as pid,pg_stat_get_backend_activity(pg_stat_get_backend_idset()) as query;
 backendid |  pid  |                                                                                      query                                                                                      
-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 | 16435 | <command string not enabled>
         2 | 16432 | <command string not enabled>
         3 | 16438 | select pg_stat_get_backend_idset() as backendId,pg_stat_get_backend_pid(pg_stat_get_backend_idset()) as pid,pg_stat_get_backend_activity(pg_stat_get_backend_idset()) as query;
         4 | 16583 | select virtualtransaction,pg_backend_pid() from pg_locks ;
         5 | 16430 | <command string not enabled>
         6 | 16433 | <command string not enabled>
         7 | 16429 | <command string not enabled>
         8 | 16431 | <command string not enabled>
(8 rows)
postgres=# \! ps -ef | egrep '16438|16583' | grep -v 'grep'
postgres 16438 16426  0 13:40 ?        00:00:00 postgres: postgres postgres [local] idle
postgres 16583 16426  0 13:43 ?        00:00:00 postgres: postgres postgres [local] idle

这样就十分清晰了

  • 16438是操作系统的pid，3是进程数组的序号ID，所以是pg_temp_3
  • 16583也是操作系统的pid，4是进程数组的序号ID，所以是pg_temp_4

搜索路径

另外一个需要特别注意的是searth_path的影响。参照官网对于search_path 的说明

Likewise, the current session's temporary-table schema, pg_temp_*nnn*, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first (even before pg_catalog). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for function or operator names.

但是如果当前会话的临时表模式pg_temp_nnn存在的话，也会被搜索到。它可以通过使用别名pg_temp明确地列在路径中。如果它没有被列在路径中，那么它会被首先搜索（甚至在pg_catalog之前）。然而，临时模式只搜索关系（表、视图、序列等）和数据类型名称。它从不搜索函数或操作符的名称。

我在制定生产开发规范的时候，就写死了禁止创建同名的临时表和普通表。

看个例子，新建一个同名的普通表temp_t

postgres=# show search_path ;
   search_path   
-----------------
 "$user", public
(1 row)
postgres=# create table temp_t(id int);
CREATE TABLE
postgres=# create temp table temp_t(id int);
CREATE TABLE
postgres=# \d temp_t 
             Table "pg_temp_3.temp_t"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

可以看到，\d元命令会优先匹配临时表，而非普通表

postgres=# \set ECHO_HIDDEN on
postgres=# \d temp_t
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(temp_t)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
postgres=# SELECT c.oid,    ---SQL摘出来，获取的就是临时表
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(temp_t)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
  oid  |  nspname  | relname 
-------+-----------+---------
 16702 | pg_temp_3 | temp_t
(1 row)

并且更加"有趣" 的是，\d你会发现普通表"消失"了

postgres=# \d
            List of relations
  Schema   |   Name   | Type  |  Owner   
-----------+----------+-------+----------
 pg_temp_3 | temp_t   | table | postgres
 public    | normal_t | table | postgres
 public    | unlog_t  | table | postgres
(3 rows)

需要显式指定全路径才可以显式，因为临时表的优先级比普通表高

postgres=# \dt public.temp_t 
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | temp_t | table | postgres
(1 row)
postgres=# \dt temp_t 
           List of relations
  Schema   |  Name  | Type  |  Owner   
-----------+--------+-------+----------
 pg_temp_3 | temp_t | table | postgres
(1 row)
postgres=# insert into temp_t values(1);
INSERT 0 1
postgres=# select * from temp_t ;       ---优先查询的是临时表
 id 
----
  1
(1 row)
postgres=# select * from public.temp_t ;   ---数据优先进入到了临时表里
 id 
----
(0 rows)

因此假如存在同名的普通表和临时表，会让你的操作结果看起来变得十分费解。比如下面我删了某个表之后表结果\d还能看到表的假象，除非你观察入微。

postgres=# \d
            List of relations
  Schema   |   Name   | Type  |  Owner   
-----------+----------+-------+----------
 pg_temp_3 | temp_t   | table | postgres
 public    | normal_t | table | postgres
 public    | unlog_t  | table | postgres
(3 rows)
postgres=# drop table temp_t ;
DROP TABLE
postgres=# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | normal_t | table | postgres
 public | temp_t   | table | postgres   ---给人一个表依旧坚挺的假象
 public | unlog_t  | table | postgres
(3 rows)

统计信息

另外一个需要注意的是，autovacuum是无法处理临时表的，需要自己手动收集统计信息。

Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via session SQL commands.

看个例子

postgres=# create temp table temp_test(id int,info text);
CREATE TABLE
postgres=# insert into temp_test select n,'test' from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# insert into temp_test select n,'test' from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# select reltuples,relpages from pg_class where relname = 'temp_test';
 reltuples | relpages 
-----------+----------
        -1 |        0
(1 row)
postgres=# select count(*) from pg_stats where tablename = 'temp_test';
 count 
-------
     0
(1 row)
postgres=# update temp_test set info = 'hello';
UPDATE 1100000
postgres=# select reltuples,relpages from pg_class where relname = 'temp_test';
 reltuples | relpages 
-----------+----------
        -1 |        0
(1 row)
postgres=# select count(*) from pg_stats where tablename = 'temp_test';
 count 
-------
     0
(1 row)
postgres=# insert into temp_test select n,'test' from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# select reltuples,relpages from pg_class where relname = 'temp_test';
 reltuples | relpages 
-----------+----------
        -1 |        0
(1 row)
postgres=# select count(*) from pg_stats where tablename = 'temp_test';
 count 
-------
     0
(1 row)

当然也可以建议索引

postgres=# create index myidx_temp on temp_test(info);
CREATE INDEX
postgres=# analyze temp_test ;
ANALYZE
postgres=# select count(*) from pg_stats where tablename = 'temp_test';
 count 
-------
     2
(1 row)

因此，对于临时表，假如有复杂查询的需求，一定要手动analyze收集统计信息。

系统表膨胀

当用户大量使用临时表，频繁的创建（临时表是需要随时用随时建的，每个会话都要自己建，而且每个临时表会在pg_class、pg_attribute 中留下痕迹，用完还需要从系统表中删除这些元数据），因此系统表pg_attribute、pg_rewrite、pg_class会出现大量的死元祖，最终导致系统表膨胀。假如是on commit drop的临时表更严重，事务提交临时表就没了，同理系统表的回收也会受到OldestXmin的限制，假如存在复制槽、2pc等，所以系统表的回收问题也要格外注意。

参数

默认情况下，产生的临时文件会位于base的pgsql_tmp目录下，另外各位可能还会看到SharedFileSets目录，比如pgsql_tmp.sharedfileset

 * SharedFileSets provide a temporary namespace (think directory) so that
 * files can be discovered by name, and a shared ownership semantics so that
 * shared files survive until the last user detaches.
 *
 * SharedFileSets can be used by backends when the temporary files need to be
 * opened/closed multiple times and the underlying files need to survive across
 * transactions.

因为PostgreSQL是多进程的，当并行执行的时候，需要通过sharefileset来共享数据，比如parallel hash join。

Image

和临时表临时数据有关的参数如下

postgres=# select name from pg_settings where name like '%temp%';
             name              
-------------------------------
 log_temp_files
 remove_temp_files_after_crash
 stats_temp_directory
 temp_buffers
 temp_file_limit
 temp_tablespaces
 wal_receiver_create_temp_slot
(7 rows)

  • log_temp_files：用于跟踪临时文件的使用，当查询要使用的内存超出work_mem的大小时（包括排序，IDSTINCT，MERGE JOIN，HASH JOIN，哈希聚合，分组聚合，SRF，递归查询等）便会记录到日志中

  • remove_temp_files_after_crash：14新增的参数，在以前的版本假如实例crash是不会移除临时文件的，正常情况下比如手动cancel掉语句，事务commit等，临时文件会被移除，但是实例crash了临时文件不会被移除，这就可能导致数据库磁盘被打爆。可以去看一下向博的文章~

    Tomáš Vondra sent in another revision of a patch to control the removal temporary files after crash with a new GUC, remove_temp_files_after_crash.

    Image

  • temp_buffers：临时缓冲区，用于数据库会话访问临时表数据，属于本地内存。可以在单独的会话中对该参数进行设置，尤其是需要访问比较大的临时表时会有性能提升，否则就会溢出到磁盘上。

  • temp_file_limit：限制单个会话最多能使用多少临时空间，默认是-1，也就是不会限制，有可能将磁盘打爆。前面也提到了，通常临时空间在事务结束、查询结束后会自动回收。

  • temp_tablespaces：This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespaces. 临时表、索引等对象存放的地方，另外排序产生的临时文件也会在这里。

小结

小结一下

  • 禁止创建同名的普通表和临时表，会使现象十分费解

  • autovacuum不会处理临时表，也就意味着不会去收集统计信息，因此假如有复杂查询，需要查询临时表，需要手动analyze

  • 临时表大量创建销毁也会导致系统表的膨胀

  • 合理配置temp_file_limit，防止过多临时文件

  • 14以前的版本，postmaster启动后会清理残留tempfile，但crash时不会移除生成的临时文件，用于调试目的

    NOTE: we could, but don’t, call this during a post-backend-crash restart cycle. The argument for not doing it is that someone might want to examine the temp files for debugging purposes.

参考

https://opensourcedbtech.com/2020/11/29/temporary-tables-in-postgresql/

https://dba.stackexchange.com/questions/211081/where-is-pg-temp-documented-in-the-pg-manual

https://stackoverflow.com/questions/3037160/where-is-temporary-table-created

https://blog.csdn.net/qq_43687755/article/details/116884505

KEEP THE THREAD GOING

更多来源文章