PostgreSQL查询运行时更新无响应，求pg_sleep替代方案

你遇到的核心问题是PostgreSQL的快照隔离机制：当你执行单个SELECT查询（哪怕里面嵌套了pg_sleep）时，整个查询会基于执行开始时的数据库快照返回结果，pg_sleep只是延长了查询的执行时长，但不会刷新这个快照。所以在这10秒内对users表的更新，根本不会被当前查询感知到。

1. 在数据库函数中用循环轮询（每次查询获取新快照） #

如果要在数据库层面实现“持续检测更新”，可以用PL/pgSQL写一个循环函数，每次执行查询后短暂休眠——这样每次查询都会使用新的数据库快照，能捕捉到期间的更新：

CREATE OR REPLACE FUNCTION check_users_updates()
RETURNS SETOF users AS $$
BEGIN
  -- 模拟10秒检测时长，每秒查询一次
  FOR i IN 1..10 LOOP  
    RETURN QUERY SELECT * FROM users WHERE looking = true;
    PERFORM pg_sleep(1);
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

调用这个函数时，每一轮循环的SELECT都会读取最新的已提交数据，你在期间执行的UPDATE操作会被实时捕捉到。

2. 使用PostgreSQL的LISTEN/NOTIFY异步通知机制 #

这是更高效的方案，不需要被动轮询，当数据库发生指定更新时会主动通知应用：

步骤1：创建更新触发通知的触发器 #

先写一个触发器函数，当users表的looking字段被更新为true时发送通知：

CREATE OR REPLACE FUNCTION notify_user_update()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.looking = true THEN
    -- 发送通知，携带更新的用户ID
    PERFORM pg_notify('user_looking_update', NEW.id::text);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给users表绑定触发器
CREATE TRIGGER trigger_user_looking_update
AFTER UPDATE OF looking ON users
FOR EACH ROW EXECUTE FUNCTION notify_user_update();

步骤2：在应用层监听通知 #

以你用到的Node.js pg库为例，代码示例如下：

// 建立连接后监听指定通知频道
db.connect((err, client) => {
  if (err) throw err;
  client.query('LISTEN user_looking_update');
  
  // 监听通知事件
  client.on('notification', (msg) => {
    const updatedUserId = msg.payload;
    // 立即查询该用户的最新数据
    db.one('SELECT * FROM users WHERE id = $1', [updatedUserId])
      .then(user => console.log('捕捉到更新用户:', user))
      .catch(err => console.error(err));
  });
});

这样当你执行UPDATE users SET looking=true WHERE id=1;时，应用会立刻收到通知并获取最新数据，完全不需要休眠等待。

3. 在应用层实现轮询 #

如果不想在数据库层面做复杂操作，也可以把逻辑放在应用（比如Node.js）里，每隔一段时间主动查询一次：

async function checkUsersContinuously() {
  const endTime = Date.now() + 10000; // 持续检测10秒
  while (Date.now() < endTime) {
    const targetUsers = await db.many('SELECT * FROM users WHERE looking=true');
    console.log('当前looking=true的用户:', targetUsers);
    await new Promise(resolve => setTimeout(resolve, 1000)); // 每秒查询一次
  }
}
checkUsersContinuously();

这种方式更灵活，所有逻辑都在应用层，每次查询都会获取数据库的最新状态。

内容的提问来源于stack exchange，提问作者A.B.