利用pg_stat_statement记录最后操作时间

最近总是看到有人需要看记录ddl相关的最后一次操作时间，于是突发奇想，没准利用pg_stat_statement，增加一个记录时间的字段没准也可满足需求。

原理

扩展struct pg_stat_statement中的Counters, 增加TimestampTz optime记录操作时间，并在函数pg_stat_statements增加OUT optime timestamptz，使PG_STAT_STATEMENTS_COLS值增加1即可。

代码diff

代码修改不到十行….

jintao@linux:~/personal/code/postgresql/contrib/pg_stat_statements$ git diff .
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
index 811813c491..daf1f6264e 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
@@ -55,7 +55,8 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
     OUT jit_optimization_count int8,
     OUT jit_optimization_time float8,
     OUT jit_emission_count int8,
-    OUT jit_emission_time float8
+    OUT jit_emission_time float8,
+    OUT optime timestamptz
 )
 RETURNS SETOF record
 AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 768cedd91a..c645cead3a 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -200,6 +200,7 @@ typedef struct Counters
        int64           jit_emission_count; /* number of times emission time has been
                                                                         * > 0 */
        double          jit_emission_time;      /* total time to emit jit code */
+    TimestampTz optime;
 } Counters;

 /*
@@ -1402,6 +1403,7 @@ pgss_store(const char *query, uint64 queryId,
                                e->counters.jit_emission_count++;
                        e->counters.jit_emission_time += INSTR_TIME_GET_MILLISEC(jitusage->emission_counter);
                }
+        e->counters.optime = TimestampTzGetDatum(e);

                SpinLockRelease(&e->mutex);
        }
@@ -1451,8 +1453,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
 #define PG_STAT_STATEMENTS_COLS_V1_3   23
 #define PG_STAT_STATEMENTS_COLS_V1_8   32
 #define PG_STAT_STATEMENTS_COLS_V1_9   33
-#define PG_STAT_STATEMENTS_COLS_V1_10  43
-#define PG_STAT_STATEMENTS_COLS                        43      /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_10  44
+#define PG_STAT_STATEMENTS_COLS                        44      /* maximum of above */

 /*
  * Retrieve statement statistics.
@@ -1826,6 +1828,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
                        values[i++] = Float8GetDatumFast(tmp.jit_optimization_time);
                        values[i++] = Int64GetDatumFast(tmp.jit_emission_count);
                        values[i++] = Float8GetDatumFast(tmp.jit_emission_time);
+            values[i++] = TimestampGetDatum(tmp.optime);
                }

                Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :

测试

mydb=# create extension pg_stat_statements ;
CREATE EXTENSION
mydb=# select userid,dbid,query,optime from pg_stat_statements where query ~ 'create|alter|drop ';
 userid | dbid  |                query                |            optime
--------+-------+-------------------------------------+------------------------------
     10 | 16385 | create extension pg_stat_statements | 2004-06-11 16:26:40.47976+08
(1 row)

mydb=# create table test001();
CREATE TABLE
mydb=# select userid,dbid,query,optime from pg_stat_statements where query ~ 'create|alter|drop ';
 userid | dbid  |                query                |            optime
--------+-------+-------------------------------------+-------------------------------
     10 | 16385 | create extension pg_stat_statements | 2004-06-11 16:26:40.47976+08
     10 | 16385 | create table test001()              | 2004-06-11 16:26:40.478224+08
(2 rows)

mydb=# alter table test001 add column id int;
ALTER TABLE
mydb=# select userid,dbid,query,optime from pg_stat_statements where query ~ 'create|alter|drop ';
 userid | dbid  |                 query                 |            optime
--------+-------+---------------------------------------+-------------------------------
     10 | 16385 | alter table test001 add column id int | 2004-06-11 16:26:40.47784+08
     10 | 16385 | create extension pg_stat_statements   | 2004-06-11 16:26:40.47976+08
     10 | 16385 | create table test001()                | 2004-06-11 16:26:40.478224+08
(3 rows)

mydb=# alter table test001 add column name text;
ALTER TABLE
mydb=# select userid,dbid,query,optime from pg_stat_statements where query ~ 'create|alter|drop ';
 userid | dbid  |                  query                   |            optime
--------+-------+------------------------------------------+-------------------------------
     10 | 16385 | alter table test001 add column id int    | 2004-06-11 16:26:40.47784+08
     10 | 16385 | create extension pg_stat_statements      | 2004-06-11 16:26:40.47976+08
     10 | 16385 | alter table test001 add column name text | 2004-06-11 16:26:40.477456+08
     10 | 16385 | create table test001()                   | 2004-06-11 16:26:40.478224+08
(4 rows)

mydb=# drop table test001;
DROP TABLE
mydb=# select userid,dbid,query,optime from pg_stat_statements where query ~ 'create|alter|drop ';
 userid | dbid  |                  query                   |            optime
--------+-------+------------------------------------------+-------------------------------
     10 | 16385 | drop table test001                       | 2004-06-11 16:26:40.477072+08
     10 | 16385 | alter table test001 add column id int    | 2004-06-11 16:26:40.47784+08
     10 | 16385 | create extension pg_stat_statements      | 2004-06-11 16:26:40.47976+08
     10 | 16385 | alter table test001 add column name text | 2004-06-11 16:26:40.477456+08
     10 | 16385 | create table test001()                   | 2004-06-11 16:26:40.478224+08
(5 rows)

看起来效果还不错。

缺点

当pg_stat_statement存储的记录数超过pg_stat_statements.max阈值时，会舍弃操作数较少的记录，因ddl操作又是少数，因此可能需要设置较大pg_stat_statements.max值, 太大的保留数也会占用一定空间。