Postgres 查找 Temp Table 建立者

Postgres 目前無系統表可以查詢 Temp Table 的建立者，

當某個 Temp Table 使用過多資源或是引發一些異常行為需要清除時，

又無法透過詢問使用者確認具體是哪個連線造成，

就需要透過一些小手段來達成。

下面分享一種查詢的方式：

1. 開一個 Session 建立暫存表，並檢查對應的 OID 與 Session PID，模擬有使用者建立了一個暫存表格行使用。

edb=# set temp_tablespaces TO tbl;
SET

edb=# create temp table ta (id int);
CREATE TABLE

edb=# \d+ ta
                                          Table "pg_temp_6.ta"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |              |
Tablespace: "tbl"

Access method: heap


edb=# select 'pg_temp_6.ta'::regclass::oid;
  oid
-------
 76179
(1 row)

edb=# select pg_backend_pid();
 pg_backend_pid
----------------
         959435
(1 row)

edb=# select * from ta;
 id
----
(0 rows)

2. 查詢系統表列出暫存表格清單，並找到對應檔案路徑

relpersistence = p (一般表格) u (UNLOGGED TABLE) t (TEMP TABLE)

edb=# select oid, relname, pg_relation_filepath(relfilenode) from pg_class where relpersistence = 't';
-[ RECORD 1 ]--------+-----------------------------------------------
oid                  | 76179
relname              | ta
pg_relation_filepath | pg_tblspc/76176/PG_14_202107181/15602/t6_76179

Time: 1.443 ms
edb=#

3. 透過 lsof 去查看開檔案紀錄，

* 必須要在暫存表建立後有進行任何 SELECT 或 DML 才有辦法查的到。

[root@EDB ~]# lsof | grep t6_76179
edb-postm  959435                   enterprisedb   83u      REG             253,19          0    394027 /pgdata/as14/tbl/PG_14_202107181/15602/t6_76179

4. 透過前面對應到的 PID 去查詢系統表 pg_stat_activity 找到對應的連線，就抓到兇手了~

剔除該連線後對應的 Temp Table 則會清除。

edb=#  select * from pg_stat_activity where pid = 959435;
-[ RECORD 1 ]----+------------------------------
datid            | 15602
datname          | edb
pid              | 959435
leader_pid       | (NULL)
usesysid         | 10
usename          | enterprisedb
application_name | psql
client_addr      | (NULL)
client_hostname  | (NULL)
client_port      | -1
backend_start    | 2022-09-26 15:54:12.167124+08
xact_start       | (NULL)
query_start      | 2022-09-26 16:18:53.620343+08
state_change     | 2022-09-26 16:18:53.620802+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      | (NULL)
backend_xmin     | (NULL)
query_id         | 4305914582893203376
query            | select 1;
backend_type     | client backend

Time: 1.602 ms
edb=#