今天遇到一個小問題,如果有人 DDL修改了表(例如drop了一個索引後),如何查詢最後的last ddl time。原本以為這是個小問題,結果發現這個問題居然還有點小麻煩。
PG沒有LAST DDL TIME對Oracle而言,當drop index命令在表上執行完畢後。將在dba_objects的LAST_DDL_TIME中記錄修改時間。但是,對於PostgreSQL,我在社區裡面搜索了相關的信息,發現它是不保存的。以下是兩個建議
DDL日誌首先,我們嘗試一下DDL日誌,看能否解決這個問題。DDL日誌主要是參數log_statement控制,它有4個選項:none、ddl、mod、all。一般建議開啟ddl,它會記錄 create、alter和drop相關的語句,但不記錄truncate。truncate在文檔中屬於 mod。這倒是讓我有點驚訝。稍後我查了下stackoverflow,發現這主要是針對不同的資料庫有著不同的行為。truncate在Oracle中屬於DDL語句,在PostgreSQL中屬於DML語句。因此,當我們使用DDL日誌記錄語句時,無法記錄到Truncate。這個需要引起注意。因此,當有人通過truncate做了壞事,仍然是無法記錄的。
與DDL log相關的另一個參數是log_line_prefix,它可以幫助我們記錄附加的信息,例如應用程式名、客戶端IP位址等等。
postgres=# alter system set log_statement='ddl' ;
ALTER SYSTEM
postgres=# alter system set log_line_prefix='%t [%p]:user=%u,db=%d,app=%a,client=%r';
ALTER SYSTEM修改完參數重啟一下資料庫。這裡配置了%t,執行時間。%p,進程ID。%u,用戶名。%d,資料庫名字。%a,應用程式名字。%r,遠程主機名或者ip地址,還有遠程的埠號。
然後我們來創建一張表,插入一些數據。
[postgres@freebsd-test ~]$ psql -U postgres -h 192.168.56.143 -p 5432 -W
Password:
psql (13.2)
Type "help" for help.
postgres=# create table test(id numeric);
CREATE TABLE
postgres=# insert into test select generate_series(1,100000);
INSERT 0 100000查看後臺log日誌。
2021-03-02 01:26:41 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG: statement: create table test(id numeric);如果誰執行了DDL語句,可以完美的抓到,這個功能非常有用。讓我們試試刪除索引。
postgres=# create index idx_t1 on test(id);
CREATE INDEX
postgres=# drop index idx_t1;
DROP INDEX
2021-03-02 01:30:20 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG: statement: create index idx_t1 on test(id);
2021-03-02 01:30:45 CST [8330]:user=postgres,db=postgres,app=psql,client=192.168.56.143(65217)LOG: statement: drop index idx_t1;現在誰執行 DDL語句就看得很清楚了。
事件觸發器PostgreSQL從9.3版本開始就支持事件觸發器,現在支持的事件主要有ddl_command_start,ddl_command_end,table_rewrite和sql_drop。目前我們要實現的功能是 LAST DDLTIME。需要使用到DDL_COMMAND_START事件。
我們來簡單測試一下。
CREATE TABLE TAB_EVENT_LOGS(
DATE_TIME TIMESTAMP,
EVENT_NAME TEXT,
REMARKS TEXT
);
CREATE OR REPLACE FUNCTION FN_LOG_EVENT() RETURNS EVENT_TRIGGER
AS
$$
BEGIN
INSERT INTO TAB_EVENT_LOGS(DATE_TIME,EVENT_NAME,REMARKS) VALUES(NOW(),TG_TAG,'Event Logging');
end;
$$
LANGUAGE plpgsql;
CREATE EVENT TRIGGER TRG_LOG_EVENT ON DDL_COMMAND_START EXECUTE PROCEDURE FN_LOG_EVENT();建立日誌表來存放DDL語句記錄,建立函數FN_LOG_EVENT來將資料插入到日誌表。接著指定DDL_COMMAND_START事件,執行FN_LOG_EVENT函數。
我們執行一些DDL語句。
postgres=# create table t(id numeric);
CREATE TABLE
postgres=# select * from TAB_EVENT_LOGS;
date_time | event_name | remarks
---+----+
2021-03-02 04:16:42.910641 | CREATE TABLE | Event Logging
(1 row)
postgres=# alter table t add column name varchar(20);
ALTER TABLE
postgres=# select * from TAB_EVENT_LOGS;
date_time | event_name | remarks
---+----+
2021-03-02 04:16:42.910641 | CREATE TABLE | Event Logging
2021-03-02 04:18:08.580202 | ALTER TABLE | Event Logging
(2 rows)可見ddl操作已被完全記錄下來。在這裡,我只是簡單地記錄一下時間和DDL事件。也可以將用戶名、表名等記錄下來。更多高級用法需要查閱官方文檔 https://www.postgresql.org/docs/current/event-trigger-definition.html。
還有別的辦法嗎?當然也有其他方法,這個方法就是開啟track_commit_timestamp,它可以用來跟蹤記錄事務提交的時間戳。在我們對表進行DDL操作的同時會對pg_class基表也進行更新。現在,我們可以跟蹤pg_class基表,查看對應數據行的 xmin值,然後將它轉換為時間。
postgres=# create table t(id numeric);
CREATE TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname ='t';
pg_xact_commit_timestamp | oid | relname
-+--+----
2021-03-02 04:24:35.582231+08 | 16428 | t
(1 row)下面為表t添加一列。再次查看pg_class表,會發現事務的xmin時間已經更新。
postgres=# alter table t add column name varchar(20);
ALTER TABLE
postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname ='t';
pg_xact_commit_timestamp | oid | relname
-+--+----
2021-03-02 04:27:14.296483+08 | 16428 | t
(1 row)使用此方法,我們實現了查看表的last ddl time。
尾聲上述三種方式都屬於簡單好用的,如果想要追求源碼體驗,可以使用HOOK來實現。