PGSQL 的暫存表（temp table）跟 Oracle 不同：PGSQL 的暫存表是 DB Session 私用的；暫存表的生命週期只在 Session 活動期間，登出後就在資料庫裡完全消失。
因此有時候遇到稍微惱人的使用狀況，像是開了很大的暫存表，或是活的很久的暫存表（常駐 AP 咬住的 DB Session），有辦法找出 DB Session 對應的暫存表的話，就蠻有用處的～
- temp table 暫存表跟 temp file 暫存檔不同，暫存表相當於真實表格
- 暫存檔 temp file 是在 SQL 執行過程中產生（CTE materialization、Sorting、external Merge Disk 等發生時）；而暫存表 temp table 是自己執行 CREATE TEMP TABLE 指令產生的表格物件
- 暫存檔的實體檔案，會放置在 $PGDATA/base/pgsql_temp/ 底下；而暫存表則待在普通表格之中（藏樹於林），不過檔名是以 t<NNN>_FFF 的格式（這部份敘述在 PGSQL 手冊的 Database File Layout 章節）
- 暫存表的 namespace（也就是 schema）主要是指定在 pg_temp_<NNN> 這樣的隱藏 schema 底下（有趣的是，這個資訊在手冊上著墨處不多，比較明確的是列在 search_path 這個 GUC 的說明頁面）
- 上述的 <NNN>，在 PGSQL 內部稱為 backend ID（這得要在幾份資料之間兜一下才會確認到的資訊），這跟 pg_stat_activity 會看得到的 PID 不一樣。
直接搜尋有關資料，比較明顯的內容是 PGSQL 的衍伸產品 GPDB 在 pg_stat_activity 有提供 sess_id 欄位呈現。
以及，從 OS 與 PID 的關係，有辦法透過 lsof 找出檔案對應的 open flie PID：Postgres 查找 Temp Table 建立者 - Postgres 筆記 - Ken
不過，要湊出純 PGSQL 手法的尋找方式，得作一點繞道。
- 首先，要先釐清 PGSQL 的 backend ID
- 要確認 backend ID 怎麼跟 PID 對上
- 透過 temp schema 命名方式，在 pg_class 或 pg_stat_user_tables 找出暫存表
- 把上面的東西 JOIN 在一起
找了一陣子，總算是找到 1 跟 2 的方式：這紀錄在兩個隱藏函數 pg_stat_get_backend_pid() 與 pg_stat_get_backend_idset()：後者可以抓出當前 PGSQL 全部運作中的 backend ID，而前者可以從 backend ID 對應出 PID。
edb=# -- 這是 set-returning function，兩個前後順序有差
edb=# SELECT bkend_id, bkend_pid
edb-# FROM pg_stat_get_backend_idset() bkend_id,
edb-# pg_stat_get_backend_pid(bkend_id) bkend_pid;
bkend_id | bkend_pid
----------+-----------
1 | 11308
2 | 11310
3 | 11311
4 | 11380
5 | 11455
6 | 11306
7 | 11305
8 | 11307
(8 rows)
edb=#
通常對應 PID 最常使用的就是搭配 pg_stat_activity 一起看，這邊紀錄一版。
edb=# SELECT bkend_id, a.*
edb-# FROM pg_stat_get_backend_idset() bkend_id,
edb-# pg_stat_get_backend_pid(bkend_id) bkend_pid,
edb-# pg_stat_activity a
edb-# WHERE bkend_pid = a.pid
edb-# AND a.backend_type = 'client backend';
bkend_id | datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
----------+-------+---------+-------+------------+----------+--------------+------------------+-------------+-----------------+-------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+-----------------+------------+--------+-------------+--------------+----------+----------------------------------------------------+----------------
4 | 15579 | edb | 11380 | | 10 | enterprisedb | psql | | | -1 | 26-SEP-22 14:32:25.759978 +00:00 | 26-SEP-22 14:33:31.818314 +00:00 | 26-SEP-22 14:33:31.818314 +00:00 | 26-SEP-22 14:33:31.818318 +00:00 | | | active | | 2200 | | SELECT bkend_id, a.* +| client backend
| | | | | | | | | | | | | | | | | | | | | FROM pg_stat_get_backend_idset() bkend_id, +|
| | | | | | | | | | | | | | | | | | | | | pg_stat_get_backend_pid(bkend_id) bkend_pid, +|
| | | | | | | | | | | | | | | | | | | | | pg_stat_activity a +|
| | | | | | | | | | | | | | | | | | | | | WHERE bkend_pid = a.pid +|
| | | | | | | | | | | | | | | | | | | | | AND a.backend_type = 'client backend'; |
5 | 15579 | edb | 11455 | | 10 | enterprisedb | psql | | | -1 | 26-SEP-22 14:32:54.571466 +00:00 | | | 26-SEP-22 14:32:54.577064 +00:00 | Client | ClientRead | idle | | | | | client backend
(2 rows)
edb=#
接著就是湊一下，就可以得到以下的 SQL
SELECT a.pid, bkend_id,
t.relnamespace::regnamespace, t.relname, t.relfilenode
FROM pg_stat_get_backend_idset() bkend_id,
pg_stat_get_backend_pid(bkend_id) bkend_pid,
pg_stat_activity a,
pg_class t
WHERE bkend_pid=a.pid
AND a.backend_type='client backend'
AND t.relnamespace::regnamespace::text = concat('pg_temp_', bkend_id);
作一點測試：建立 temp table 前/後，以及對照 temp table 的檔名。
edb=# -- 目前 Session 的 PID
edb=# SELECT pg_backend_pid();
pg_backend_pid
----------------
10814
(1 row)
edb=# -- 確認一下現在沒有暫存表
edb=# SELECT a.pid, bkend_id,
edb-# t.relnamespace::regnamespace, t.relname, t.relfilenode
edb-# FROM pg_stat_get_backend_idset() bkend_id,
edb-# pg_stat_get_backend_pid(bkend_id) bkend_pid,
edb-# pg_stat_activity a,
edb-# pg_class t
edb-# WHERE bkend_pid=a.pid
edb-# AND a.backend_type='client backend'
edb-# AND t.relnamespace::regnamespace::text = concat('pg_temp_', bkend_id);
pid | bkend_id | relnamespace | relname | relfilenode
-----+----------+--------------+---------+-------------
(0 rows)
edb=#
edb=# -- 開暫存表
edb=# create temp table xx(id int);
CREATE TABLE
edb=#
edb=# -- 再跑一次就看到了
edb=# SELECT a.pid, bkend_id,
t.relnamespace::regnamespace, t.relname, t.relfilenode
FROM pg_stat_get_backend_idset() bkend_id,
pg_stat_get_backend_pid(bkend_id) bkend_pid,
pg_stat_activity a,
pg_class t
WHERE bkend_pid=a.pid
AND a.backend_type='client backend'
AND t.relnamespace::regnamespace::text = concat('pg_temp_', bkend_id);
pid | bkend_id | relnamespace | relname | relfilenode
-------+----------+--------------+---------+-------------
10814 | 4 | pg_temp_4 | xx | 33518
(1 row)
edb=#
edb=# -- 檢視暫存表檔名
edb=# \! ls $PGDATA/base/15579/t*
/var/lib/edb/as14/data/base/15579/t4_33518
edb=#
完工～注意雖然上面只用相同 session 作檢查，但這個 SQL 只要 superuser，就都可以看其他 session 的狀況～
最後補充：
PGSQL 的 autovacuum 碰不到暫存表：也就是說，若有常駐的 DB session 並且會開著暫存表不斷使用的話，就要留意對暫存表的 VACUUM ANALYZE。
因此這邊的 SQL 可能就有點用處了～留意哪些暫存表變得太老，要調整對應的程式碼。
參考資料
Size of temp tables created in a particular session - Stack Overflow
Where is temporary table created? - Stack Overflow
Postgres 查找 Temp Table 建立者 - Postgres 筆記 - Ken
Temporary files in PostgreSQL – Steps to identify and fix temp file issues - Kloud DB
PostgreSQL temp files usage - DEV Community 👩💻👨💻
PostgreSQL Mailing List: Quirk of pg_temp schemas ...
PostgreSQL Mailing List: Thread: pg_temp schemas : Postgres Professional
pg_stat_get_backend_pid() - pgPedia - a PostgreSQL Encyclopedia
pg_stat_get_backend_idset() - pgPedia - a PostgreSQL Encyclopedia
有一種罕見的 Transaction ID wraparound 叫做 MxID wraparound，偷偷塞在這邊
PostgreSQL: Documentation: 14: 25.1. Routine Vacuuming - 25.1.5.1. Multixacts And Wraparound
PostgreSQL DBA(158) - Locks(tuple locks)_ITPUB博客
postgres/README.tuplock at master
沒有留言:
張貼留言