解析PostgreSQL触发器记录数据库操作的SQL语句

引言

在数据库操作中，跟踪和记录执行的 SQL 语句对于调试和审计非常重要。本篇博客将介绍如何使用 PostgreSQL 触发器来记录数据库操作的 SQL 语句，并提供一个解决方案。

问题背景

在许多应用程序中，我们经常需要了解数据库中执行的 SQL 语句，以便追踪和监控应用的行为。然而，仅仅依赖应用程序日志并不能提供完整的信息，特别是在多个客户端或多个应用程序同时对数据库进行操作的情况下。

解决方案

为了解决这个问题，我们可以使用 PostgreSQL 的触发器功能。触发器是一种在数据库操作之前或之后自动执行的函数。通过创建一个触发器，我们可以捕获并记录数据库操作的 SQL 语句。

以下是解决方案的步骤：

1. 创建一个日志表：首先，我们需要创建一个用于存储操作日志的表。该表应包含列来存储时间戳、用户名、操作类型和 SQL 语句。下面是一个示例的表结构：

DROP TABLE IF EXISTS "tb_sys_operation_log";
CREATE TABLE "tb_sys_operation_log" (
  "id" SERIAL PRIMARY KEY,
  "operation_type" varchar(50) COLLATE "pg_catalog"."default",
  "operation_tablename" varchar(50) COLLATE "pg_catalog"."default",
  "sql_statement" text COLLATE "pg_catalog"."default",
  "operation_pid" int4 ,
  "old_values" JSONB,
  "new_values" JSONB,
  "ipaddr" inet,
  "operation_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "username" varchar(255) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Primary Key structure for table operation_log
-- ----------------------------
ALTER TABLE "tb_sys_operation_log" ADD CONSTRAINT "tb_sys_operation_log_pkey" PRIMARY KEY ("id");

2. 创建触发器函数：接下来，我们需要创建一个触发器函数，该函数将在数据库操作时被调用，并将操作的相关信息插入到日志表中。以下是一个示例的触发器函数：

CREATE OR REPLACE FUNCTION "sys_log_operation"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
  full_sql_statement TEXT;
  stat_queryid numeric;
  operation_type TEXT;
  old_column_data JSONB;
  new_column_data JSONB;
BEGIN
    -- 获取触发器操作类型
    IF (TG_OP = 'INSERT') THEN
        operation_type := 'INSERT';
        SELECT to_jsonb(NEW) INTO new_column_data;
    ELSIF (TG_OP = 'UPDATE') THEN
        operation_type := 'UPDATE';
        SELECT to_jsonb(OLD) INTO old_column_data;
        SELECT to_jsonb(NEW) INTO new_column_data;
    ELSIF (TG_OP = 'DELETE') THEN
        operation_type := 'DELETE';
        SELECT to_jsonb(OLD) INTO old_column_data;
    END IF;
		
		SELECT query INTO full_sql_statement
    FROM pg_stat_activity
    WHERE pg_stat_activity.pid = pg_backend_pid();

    -- 插入操作信息到日志表
    INSERT INTO tb_sys_operation_log (username,operation_tablename,operation_time, operation_type, sql_statement,ipaddr,old_values,new_values,operation_pid)
    VALUES (current_user,TG_TABLE_NAME,now(), operation_type, full_sql_statement,inet_client_addr(),old_column_data,new_column_data,pg_backend_pid());
    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

创建 PostgreSQL 触发器的函数，该函数会记录数据库操作（插入、更新、删除）到名为 “tb_sys_operation_log” 的系统操作日志表中。该函数会捕获相关信息，例如操作类型、SQL 语句、用户、IP 地址以及受操作影响的旧值和新值，还包括进程 ID。

3. 创建触发器：最后，我们需要创建一个触发器，将触发器函数与要监视的数据库表关联起来。以下是一个示例的触发器创建语句：

CREATE TRIGGER log_operation_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW
EXECUTE FUNCTION sys_log_operation();

在上面的代码中，将"your_table"替换为实际要监视的数据库表的名称。这个触发器将在每次插入、更新或删除记录时调用触发器函数，并将相关信息插入到日志表中。

这个命令将在每次对 “your_table” 表进行插入、更新或删除操作之后触发名为 “sys_log_trigger” 的触发器，并执行 “sys_log_operation” 函数来记录操作信息。

请确保 “tb_sys_operation_log” 表存在，并且具有存储函数信息所需的必要列。此外，对于大型表来说，以 JSONB 形式捕获旧值和新值可能会消耗较多内存。

过程中其他问题

1.1. pg_stat_activity表记录sql的长度截取问题

		SELECT query INTO full_sql_statement
    FROM pg_stat_activity
    WHERE pg_stat_activity.pid = pg_backend_pid();

其中pg_stat_activity表中sql的记录信息，会发生截取长度的问题，其长度取决于postgresql.conf配置文件中的track_activity_query_size,其默认值为1024,将配置文件中的该值打开注释，设置值并重启数据库。

结论

通过使用 PostgreSQL 触发器记录数据库操作的 SQL 语句，我们可以方便地追踪和监控应用程序对数据库的操作。这个解决方案提供了一种简单而有效的方法来记录和审计 SQL 语句，有助于调试和排查问题。

这个命令将在每次对 “your_table” 表进行插入、更新或删除操作之后触发名为 “sys_log_trigger” 的触发器，并执行 “sys_log_operation” 函数来记录操作信息。

请确保 “tb_sys_operation_log” 表存在，并且具有存储函数信息所需的必要列。此外，对于大型表来说，以 JSONB 形式捕获旧值和新值可能会消耗较多内存。