On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver <
adrian.klaver@aklaver.com> 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 <
samuelmarks@gmail.com> > <mailto:
samuelmarks@gmail.com>> 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;
> > ```
