Correct, he is reading from the connection while writing to it. Unless all the rows have been read, you can't use it to do a write, at the protocol level. Use two connections from the pool for this, one for the read, and the other for the writes.
On Sunday, June 5, 2022, Hui Jackson <jackhts4@gmail.com> wrote:
I am working on node-postgres and there is a strange transactions.
The function aims at locking user's wallet until the refund process is complete, then will update item's has_refund to true.
The problem I am facing is the program return no error, but the database is not updated. Unless I spam the function for multiple times then the value is updated.
If I remove begin and commit, then the function work perfectly.
const refundService = (itemId) =>{
await pgPool.query('BEGIN;');
const users = (await pgPool.query('SELECT * from app_user where $1=ANY(purchase_list);', [ itemId ])).rows;
for(let i = 0; i < users.length; i++){
refund(users[i])
}
await pgPool.query('UPDATE item_lists SET has_refund = $1 where id = $2;', [true, itemId ]);
await pgPool.query('COMMIT;');
}
const refund = (user) =>{
const refund = 10
await pgPool.query('UPDATE app_user SET wallet = wallet + $1', [refund ]);
}
I think you are mis-using your pool. If you want transactions you need checkout a connection from the pool and use it for every command in the transaction. The one-shot query method on the pool is meant for standalone commands. I say think because I’m unsure why you’d get no updates instead of updates but no transaction…
David J.
