Node中PostgreSQL更新语句无生效，但原生环境正常问题排查

场景还原 #

你使用Node.js v8.9.4 + pg模块v7.4.3时碰到了一个奇怪的问题：执行UPDATE语句时，Node返回的rowCount始终为0，但直接在PostgreSQL原生客户端（比如psql）跑相同语句，每次都能更新3行数据。测试用的SELECT * from test.new_orders能正常拿到目标数据，说明数据库连接和基础查询是没问题的。

你的原UPDATE语句：

UPDATE test.new_orders 
SET properties = properties || '{"statusid":1,"updatedAt":"2018-5-24 14:51:43"}' 
WHERE to_timestamp((properties ->> 'updatedAt'),'YYYY-MM-DD HH24:MI:SS.US') <= now() - INTERVAL '20 seconds' 
AND properties ->> 'partitionid' = '6';

Node中的执行代码：

getUpdatableOrdersAndUpdateStatuses(partitionid,orderProperties,interval) { 
  return new Promise((resolve, reject) => { 
    let query = `UPDATE test.new_orders SET properties = properties || '${orderProperties}' WHERE to_timestamp((properties ->> 'updatedAt'),'YYYY-MM-DD HH24:MI:SS.US') <= now() - INTERVAL '${interval}' AND properties ->> 'partitionid' = '${partitionid}';`; 
    // test query = 'select * from test.new_orders' 
    console.log(query); 
    SQL.query(query, (err, res) => { 
      if (err) { 
        console.log(err); 
        return reject(err); 
      } 
      console.log(res); 
      return resolve(true); 
    }) 
  }); 
}

目标数据样例：

orderid properties  
5281 {"tarifid": "1", "statusid": 1, "createdAt": "2018-05-24T13:40:57.544955", "updatedAt": "2018-5-24 14:46:49", "partitionid": "6"}  
15 {"tarifid": "1", "statusid": 1, "createdAt": "2018-05-24T14:38:57.609023", "updatedAt": "2018-5-24 14:46:49", "partitionid": "6"}  
152 {"tarifid": "1", "statusid": 1, "createdAt": "2018-05-24T14:39:11.655951", "updatedAt": "2018-5-24 14:46:49", "partitionid": "6"}

问题根源 #

核心问题是时区不匹配：

• 你的updatedAt字段存储的是Asia/Yerevan时区的时间字符串
• 在PostgreSQL原生客户端中，你的会话时区大概率已经设置为Asia/Yerevan，所以to_timestamp转换后的时间和now()（服务器当前时区时间）在同一维度，比较逻辑正常，能匹配到行
• 但Node的pg模块连接数据库时，默认可能使用UTC时区（或者和你原生会话不同的时区），导致now()返回的时间和转换后的updatedAt时间不在同一个时区，比较结果永远为false，自然没有行被更新

解决办法 #

在to_timestamp转换后加上AT TIME ZONE 'Asia/Yerevan'，强制把转换后的时间对齐到目标时区，确保和now()的比较是同维度的。

修改后的UPDATE语句：

UPDATE test.new_orders 
SET properties = properties || '{"statusid":1,"updatedAt":"2018-5-24 14:51:43"}' 
WHERE to_timestamp((properties ->> 'updatedAt'),'YYYY-MM-DD HH24:MI:SS.US') AT TIME ZONE 'Asia/Yerevan' <= now() - INTERVAL '20 seconds' 
AND properties ->> 'partitionid' = '6';

对应的Node代码也要更新：

let query = `UPDATE test.new_orders SET properties = properties || '${orderProperties}' WHERE to_timestamp((properties ->> 'updatedAt'),'YYYY-MM-DD HH24:MI:SS.US') AT TIME ZONE 'Asia/Yerevan' <= now() - INTERVAL '${interval}' AND properties ->> 'partitionid' = '${partitionid}';`;

额外建议：使用参数化查询

另外，你当前的代码用字符串拼接生成SQL，存在SQL注入风险，建议改用pg模块支持的参数化查询：

getUpdatableOrdersAndUpdateStatuses(partitionid, orderProperties, interval) { 
  return new Promise((resolve, reject) => { 
    const query = `
      UPDATE test.new_orders 
      SET properties = properties || $1 
      WHERE to_timestamp((properties ->> 'updatedAt'),'YYYY-MM-DD HH24:MI:SS.US') AT TIME ZONE 'Asia/Yerevan' <= now() - INTERVAL $2 
      AND properties ->> 'partitionid' = $3;
    `; 
    SQL.query(query, [orderProperties, interval, partitionid], (err, res) => { 
      if (err) { 
        console.error(err); 
        return reject(err); 
      } 
      console.log(res); 
      return resolve(true); 
    }); 
  }); 
}

这样不仅更安全，还能避免字符串拼接带来的格式错误问题。

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