使用node.js在Postgres中更新 [英] Upsert in Postgres using node.js
问题描述
我正在尝试使用具有pg扩展名（版本0.5.4）的node.js在postgres数据库中进行插入或更新。
I'm trying to do an insert or update in a postgres database using node.js with pg extension (version 0.5.4).
到目前为止，我已经这段代码：
（...）
So far I have this code: (...)
client.query({
text: "update users set is_active = 0, ip = $1 where id=$2",
values: [ip,id]
}, function(u_err, u_result){
debug(socket_id,"update query result: ",u_result);
debug(socket_id,"update query error: ",u_err);
date_now = new Date();
var month = date_now.getMonth() + 1;
if(!u_err){
client.query({
text: 'insert into users (id,first_name,last_name,is_active,ip,date_joined) values' +
'($1,$2,$3,$4,$5,$6)',
values: [
result.id,
result.first_name,
result.last_name,
1,
ip,
date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds()
]
}, function(i_err, i_result){
debug(socket_id,"insert query result: ",i_result);
debug(socket_id,"insert query error: ",i_err);
});
}
});
问题是，尽管两个查询都有效，但问题总是同时运行而不是只运行插入操作
The problem is that, although both queries work the problem is always running both instead of only running the insert function if the update fails.
代码中的调试功能输出如下：
The debug functions in code output something like:
UPDATE
Object { type="update query result: ", debug_value={...}}
home (linha 56)
Object { type="update query error: ", debug_value=null}
home (linha 56)
Object { type="insert query result: "}
home (linha 56)
Object { type="insert query error: ", debug_value={...}}
插入
Object { type="update query result: ", debug_value={...}}
home (linha 56)
Object { type="update query error: ", debug_value=null}
home (linha 56)
Object { type="insert query result: ", debug_value={...}}
home (linha 56)
Object { type="insert query error: ", debug_value=null}
**编辑**
回答node-postgres开发人员：
ANSWER FROM node-postgres developer:
可以检索受插入和
更新影响的行数。它并未在本机绑定中完全实现，但是
在纯javascript版本中有效。下周或两周内，我将在
内进行处理。同时使用纯JavaScript版本和
在这里查看：
It's possible to retrieve number of rows affected by an insert and update. It's not fully implemented in the native bindings, but does work in the pure javascript version. I'll work on this within the next week or two. In the mean time use pure javascript version and have a look here:
**结束编辑**
任何人都可以帮忙吗？
推荐答案
您的问题的直接答案是使用存储过程进行更新
The immediate answer to your question is to use a stored procedure to do an upsert.
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
类似于pg的模块。
client.query({
text: "SELECT upsert($1, $2, $3, $4, $5, $6)"
values: [ obj.id,
obj.first_name,
obj.last_name,
1,
ip,
date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds()
]
}, function(u_err, u_result){
if(err) // this is a real error, handle it
// otherwise your data is updated or inserted properly
});
当然，这假定您使用的模型对象具有所需的所有值，即使它们没有变化。您必须将它们全部传递给upsert。如果您按照此处显示的方式进行操作，则应该在更新后检查实际的错误对象，以确定它是否由于行已存在或其他原因而失败（这是真正的db错误，
Of course this assumes that you're using some kind of model object that has all the values you need, even if they aren't changing. You have to pass them all into the upsert. If you're stuck doing it the way you've shown here, you should probably check the actual error object after the update to determine if it failed because the row is already there, or for some other reason (which is real db error that needs to be handled).
然后，您必须处理从更新失败到插入完成之间的潜在竞争状况。如果其他函数尝试使用相同的ID插入，则可能会出现问题。交易对此有利。这就是我现在所拥有的。希望对您有所帮助。
Then you've gotta deal with the potential race condition between the time your update failed and the time your insert goes through. If some other function tries to insert with the same id, you've got a problem. Transactions are good for that. That's all I got right now. Hope it helps.
这篇关于使用node.js在Postgres中更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
