通过Java进程输入输出流调试Psql块变量的方法

问题背景 #

通过Java进程逐条向psql输入流写入PostgreSQL DO块语句，目前仅在DO块以$$闭合后，才能从标准输出获取RAISE INFO的输出。需要在设置dynamic_sql变量后，无需等待块执行完成，就能实时输出变量值。尝试过psql的\echo元命令，但在DO块内无效，且仍需等待整个块执行完毕才输出。

可行解决方案 #

方案1：使用pg_notify发送即时通知 #

PostgreSQL的NOTIFY机制可以在DO块执行过程中，将变量值作为消息推送给客户端，且psql会即时输出该通知，无需等待块执行完成。

步骤：

1. 启动psql时开启无缓冲输出
   调用psql时添加--unbuffered参数，避免psql缓冲输出内容：

   psql -U your_username -d your_database --unbuffered
   

2. 在Java进程中先执行监听命令
   向psql输入流写入：

   LISTEN debug_channel;
   SET CLIENT_MIN_MESSAGES TO INFO;
   

   LISTEN用于订阅指定的通知通道，SET CLIENT_MIN_MESSAGES确保INFO级别的消息能被输出。

3. 修改DO块代码，添加pg_notify
   在设置dynamic_sql后，调用pg_notify发送变量值：

   do $$
   declare x integer:= 12;
   declare y integer := x + 4;
   DECLARE table_name text := 'foodmart' ;
   DECLARE dynamic_sql text;
   begin
    dynamic_sql := 'select employee_id from ' || table_name || ' where employee_name = ''Mark'';';
    -- 发送变量值到通知通道
    perform pg_notify('debug_channel', dynamic_sql);
    raise info '%', dynamic_sql;
    execute dynamic_sql;
   end
   $$;
   

   当DO块执行到perform pg_notify时，psql会立刻输出类似如下内容到标准输出，Java进程可即时读取：

   Asynchronous notification "debug_channel" with payload "select employee_id from foodmart where employee_name = 'Mark';
   

方案2：拆分逻辑，使用psql元命令调试变量 #

如果无需复杂的PL/pgSQL逻辑，可将变量声明和输出拆分为独立的psql元命令，每条命令执行后即时输出结果：

\set x 12
\set y :x + 4
\set table_name 'foodmart'
\set dynamic_sql 'select employee_id from ' || :table_name || ' where employee_name = ''Mark'''
-- 即时输出变量值
\echo :dynamic_sql
-- 执行动态SQL
PREPARE stmt AS :dynamic_sql;
EXECUTE stmt;
DEALLOCATE stmt;

这种方式下，\echo会在执行后立刻输出dynamic_sql的值，无需等待后续语句执行。

关键说明 #

• DO块作为整体事务执行，默认情况下RAISE INFO的输出会被psql缓冲，直到块执行完成才flush到标准输出，--unbuffered参数可关闭该缓冲。
• pg_notify是唯一能在PL/pgSQL块执行过程中即时向客户端发送消息的原生机制，适合复杂逻辑的实时调试。

内容的提问来源于stack exchange，提问作者Ravi Kapoor