Thread

1. Strange behavior of transcations

   Hui Jackson <jackhts4@gmail.com> — 2022-06-05T11:10:01Z

   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
   ]);
   }
   

2. Re: Strange behavior of transcations

   David G. Johnston <david.g.johnston@gmail.com> — 2022-06-05T14:29:27Z

   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.
   

3. Re: Strange behavior of transcations

   Erik Brandsberg <erik@heimdalldata.com> — 2022-06-05T14:37:23Z

   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 Sun, Jun 5, 2022, 10:29 AM David G. Johnston <david.g.johnston@gmail.com>
   wrote:
   
   > 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.
   >
   >