PostgreSQL | 查LAST DDL TIME，PG的三种方法

励志成为PostgreSQL大神 2021-03-25

6519

前言

今天遇到一个小问题，如果有人 DDL修改了表(例如drop了一个索引后)，如何查询最后的last ddl time。原本以为这是个小问题，结果发现这个问题居然还有点小麻烦。

PG没有LAST DDL TIME

对Oracle而言，当drop index命令在表上执行完毕后。将在dba_objects的LAST_DDL_TIME中记录修改时间。但是，对于PostgreSQL，我在社区里面搜索了相关的信息，发现它是不保存的。以下是两个建议

使用ddl日志。
使用事件触发器。

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)