PGSQL 的表格最後交易時間 (9.5+ 適用)

在某些情況下，會想要查看表格最後異動時間。不幸的是，PostgreSQL 沒有這種功能。

表格最後異動時間，在 Greenplum 某些結構表單裡面有紀錄（Append-Only Table 儲存結構）。但是截至 Postgres 12 為止，沒有這種儲存結構（還只有 Heap Table 的結構）。通常有這樣的需要時，就得增加一個時戳欄位，搭配 AP 上面要有相應的設計或是加一點 Trigger 才行。

然而，Postgres 在 v9.5 之後有提供 track_commit_timestamp 功能，可以用來接近這樣的需求～

track_commit_timestamp 啟用「之後」（需要重啟資料庫）的所有資料（每一筆 Data Tuple），都會有紀錄最後 transaction ID（簡稱 xid）。然後就可以透過 pg_xact_commit_timestamp(xid) 查出轉換過來的時間點了～

不過在啟用之前的資料（Row）就沒有這個紀錄了，除非這些資料有再被更新（發生異動），但刪除的話，資料是被標記成無效的資料，也不會有這樣的需要了。

使用方式可以參考以下步驟

  - 修改資料庫參數：這邊建議保持習慣，使用 SQL 指令作異動，不要直接變更檔案，以利於以後的管理

ALTER SYSTEM SET track_commit_timestamp = on;

  - 重啟資料庫：這邊在 CentOS 作示範

service postgresql-11 restart

  - 正常匯入新資料：就輸入資料。。
  - 查詢資料：使用隱藏欄位 xmin（表示 transaction min ID）

SELECT max(pg_xact_commit_timestamp(xmin)) FROM YOUR_TABLE_NAME;

原則上這樣就會顯示每張表最近一次交易活動的時間

參考資料
Table 9.76. Committed transaction information - PostgreSQL: Documentation: current: 9.26. System Information Functions and Operators
Getting last modification date of a PostgreSQL database table - Database Administrators Stack Exchange