Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nuAIo-0003px-SA for pgsql-sql@arkaria.postgresql.org; Thu, 26 May 2022 10:01:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nuAIm-0006nd-5X for pgsql-sql@arkaria.postgresql.org; Thu, 26 May 2022 10:01:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nuAIl-0006nU-Po for pgsql-sql@lists.postgresql.org; Thu, 26 May 2022 10:01:07 +0000 Received: from mail-lj1-x22c.google.com ([2a00:1450:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nuAIh-0006Ft-C9 for pgsql-sql@lists.postgresql.org; Thu, 26 May 2022 10:01:07 +0000 Received: by mail-lj1-x22c.google.com with SMTP id s20so1207491ljd.10 for ; Thu, 26 May 2022 03:01:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=hq+CJfO7ueMD4CzayCE3zylROtpWfeoBRfKdOBruYxM=; b=hSHSMQyhKprOvO3LnQNewb4zx/UXgg4o3mYfl8PtEM/CV5m4TLGI+C0PSqtJbsJHZM DW84IbLXQY9w10TMwQ08P98Bk/7yzx3PpOmW2/qmAcXjeqdVybgwLfx2Ps3wPCVCJUew +Iu4sw+PdqqdFXBOY0rUqMB80rQX3aaFEzuP8QyORdaXpv3porHNRDas/FtNacx/4E05 qZl6qHELylrQlkcr+JCpOwZIaj7gyXp47hanJgXSPFV4tpPpff+6K1IAZCq+ct6eFVa7 9Rmx4LqPW9XjKf6rp5HO6URKv+Z+WF7E5NgaGhsJDjb3jUFfmoqqXc6IHk8KqpeGWP9K 85+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=hq+CJfO7ueMD4CzayCE3zylROtpWfeoBRfKdOBruYxM=; b=cmLHVdl9xj+7SV1uARJdqfM87S5nm7Y5ofJ/v455ZO3Tt3qPCiH8/cbstI99t6P9Ff K4ECIJytCCrg6x07kvWBnkTQNaBV22GoR2baN3ii8YoUBPbhTClnNSkIUO8LxJAGW3EV Tk4v9gYnstJ4BE9YJ6+uMjtw1C3T4YIrSLN4LOIENXQ1mQgvJvaIhtbjyqNlhzfSSJHy OHQFBcwrx5nDnCLeW+scK/aFmySOSxGw/LYcLXFaOB6xCtGO5Fchg0fHLnP6U6lHJYQ2 XjSKhkNiMh76D0kfb8sYt0EHOk6FafVVuJqsF75dmqEuFYHUlcqnJAWWi4dEK/598Lz7 Yb6Q== X-Gm-Message-State: AOAM532lCWUlejJ82oy1FBb6+UiRDNpXOyc6d8Rc9QVKoT2mYrSu1Isq 7vVoptwArBQtbCp5bxtZFMx5LWR/1yvO8WLMj3A= X-Google-Smtp-Source: ABdhPJzurASXnddEwzbcjCNptrtPZyNTChIGA7+rUPazjA9IEu64/tIYGAZda3ioEoEqipQmHQit+bOdcF9IhB/dZak= X-Received: by 2002:a05:651c:230c:b0:250:6459:d6d4 with SMTP id bi12-20020a05651c230c00b002506459d6d4mr14549918ljb.271.1653559261375; Thu, 26 May 2022 03:01:01 -0700 (PDT) MIME-Version: 1.0 References: <20220525233556.GA9042@depesz.com> In-Reply-To: <20220525233556.GA9042@depesz.com> From: Hui Jackson Date: Thu, 26 May 2022 18:00:49 +0800 Message-ID: Subject: Re: Using Transaction with if-else in prepared statement To: depesz@depesz.com, science@misuse.org Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000063b2e005dfe7449b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000063b2e005dfe7449b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Concluded both methods shared: 1. one query per each call for using prepared statement await pgPool.query('BEGIN;'); const sender =3D (await pgPool.query('SELECT coin from app_user where id = =3D $1;',[senderId])).rows[0]; if(sender.coin >=3D amount){ await pgPool.query('UPDATE app_user set coin =3D coin - $1 where id =3D $= 2;',[ coin, senderId]); await pgPool.query('UPDATE app_user set coin =3D coin + $1 where id =3D $= 2;',[ coin, receiverId]); await pgPool.query('INSERT INTO coin_history(sender_id, receiver_id, amount) VALUES ($1, $2, $3)',[senderId, receiverId, coin]); await pgPool.query('COMMIT;'); }else{ await pgPool.query('END;'); } 2. Use constraint CREATE TABLE app_user( id integer, coin numeric CHECK (coin >=3D 0) ); hubert depesz lubaczewski =E6=96=BC 2022=E5=B9=B45=E6= =9C=8826=E6=97=A5=E9=80=B1=E5=9B=9B =E4=B8=8A=E5=8D=887:35=E5=AF=AB=E9=81= =93=EF=BC=9A > On Wed, May 25, 2022 at 07:12:49PM +0800, Hui Jackson wrote: > > I am trying to make transaction in nodejs > > The logic will be check if sufficient coin in sender's amount, if > > sufficient then do transaction. > > I am new to postgres, not sure if this is a right way to do so, if you > have > > a better solution, please let me know, thank you. > > I would do i by issuing *only* > INSERT INTO coin_history(sender_id, receiver_id, amount) VALUES > and then changing coin data with triggers plus adding constraints that > app_user.coin can never be < 0. > > depesz > --00000000000063b2e005dfe7449b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Concluded both methods shared:
1.=C2= =A0 one query per each call for using prepared statement
await pgPool.query('BEGIN;');
const sender =3D (await= pgPool.query('SELECT coin from app_user where id =3D $1;',[senderId])).rows[0];
if(sender.co= in >=3D amount){
=C2=A0 await pgPool.query('UPDATE app= _user set coin =3D coin - $1 where id =3D $2;',[coin, = senderId]);
=C2=A0 = await pgPool.query('UPDATE app_user set coin =3D coin + $1 where id =3D $2;',[coin, receiverId]);
=C2=A0 await p= gPool.query('INSERT INTO coin_history(sender_id, recei= ver_id, amount) VALUES ($1, $2, $3)',[senderId, receiv= erId, coin]);
=C2=A0 await pgPool.query('COMMIT;');
}else{
<= div style=3D"color:rgb(212,212,212);font-family:Consolas,"Courier New&= quot;,monospace;font-size:14px;white-space:pre;background-color:rgb(30,30,3= 0)">=C2=A0 await pgPool.query('END;');
}
2. Use constraint
CREATE TABLE app_user(
    id integer,
    coin numeric <=
span style=3D"box-sizing:border-box;font-weight:bolder;color:rgb(71,71,71)"=
>CHECK (coin >=3D 0)
);

hubert depesz lubaczewski <depesz@depesz.com> =E6=96=BC 2022=E5=B9=B45=E6=9C= =8826=E6=97=A5=E9=80=B1=E5=9B=9B =E4=B8=8A=E5=8D=887:35=E5=AF=AB=E9=81=93= =EF=BC=9A
On Wed= , May 25, 2022 at 07:12:49PM +0800, Hui Jackson wrote:
> I am trying to make transaction in nodejs
> The logic will be check if sufficient coin in sender's amount, if<= br> > sufficient then do transaction.
> I am new to postgres, not sure if this is a right way to do so, if you= have
> a better solution, please let me know, thank you.

I would do i by issuing *only*
INSERT INTO coin_history(sender_id, receiver_id, amount) VALUES
and then changing coin data with triggers plus adding constraints that
app_user.coin can never be < 0.

depesz
--00000000000063b2e005dfe7449b--