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