public inbox for [email protected]  

help / color / mirror / Atom feed

From: Samuel Marks <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: How do I upsert depending on a second table?
Date: Wed, 24 Sep 2025 12:02:42 -0500
Message-ID: <CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
	<CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com>
	<CAMfPbcbuww3geDUFce+eQ2DEtg1n=qHQ3N=2LdZYdJ55cUUHBA@mail.gmail.com>
	<CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
	<[email protected]>

On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver <[email protected]>
wrote:
>
> On 9/23/25 23:44, Juan Rodrigo Alejandro Burgos Mella wrote:
> > The insert works because there is no data in the repo table that
> > conflicts with the entered full name.
>
> Except this part:
>
> SELECT 1/COUNT(*)
> FROM org
> WHERE name = 'org0'
>    AND owner = 'wrong user';
>
> will cause a divide by 0 error and abort the transaction preventing the
> INSERT from happening.
>
> Example:
>
> test=# begin ;
> BEGIN
> test=*# select 1/0;
> ERROR:  division by zero
> test=!# select 1;
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block
> test=!# rollback ;
> ROLLBACK
>
>
>

Yes but it's meant to divide by zero. That cancels the whole transaction
stopping it from going through. It being a transaction lets me guarantee
that at point of update or insert [upsert] the org owner matches the
requestor.

I would preference a single statement (one semicolon) solution; but for now
at least this works 🤷

> >
> > El mar, 23 sept 2025, 23:19, Samuel Marks <[email protected]
> > <mailto:[email protected]>> escribió:
> >
> >     Ok so you're thinking I give up on putting it all in one query and
> >     instead use a transaction? - Is that the recommended way?
> >
> >     ```sql
> >     TRUNCATE repo, org;
> >     INSERT INTO org(name, owner) VALUES ('org0', 'user0');
> >     ```
> >
> >     ```sql
> >     START TRANSACTION READ WRITE;
> >
> >     SELECT 1/COUNT(*)
> >     FROM org
> >     WHERE name = 'org0'
> >        AND owner = 'wrong user';
> >
> >     INSERT INTO repo (id, full_name, org)
> >     VALUES (0, 'org0/name0 by wrong user', 'org0')
> >     ON CONFLICT (full_name) DO UPDATE
> >          SET full_name = EXCLUDED.full_name,
> >              org       = EXCLUDED.org
> >     RETURNING id;
> >
> >     COMMIT;
> >     ```

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: How do I upsert depending on a second table?
  In-Reply-To: <CAMfPbcZeOZCdosvokifS=Kj6seakGFNvM6vM3v8FQNho7v_7Og@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox