On 9/24/25 10:02, Samuel Marks wrote:
> On Wed, Sep 24, 2025 at 10:13 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> 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.
My reply was to Juan Rodrigo Alejandro Burgos Mella referencing the comment:
"The insert works because there is no data in the repo table that
conflicts with the entered full name. "
I was pointing out that in your second example the INSERT would not
happen as the org table does not have a row:
name owner
org0 wrong_user
So the SELECT 1/COUNT(*) [...] would result in a divide by 0 error and
the transaction would abort. Therefore ON CONFLICT (full_name) DO UPDATE
does not apply as the INSERT never happens.
I should have added previously this only applies for the 'wrong user'
case. For cases where the correct name/owner exists in the org table
then the INSERT and it's ON CONFLICT come into play and what happens
then is dependent on whether there is an existing row in the repo with
the same full_name or not. The issue I see is that the full_name is
UNIQUE across all orgs and I not sure that is good idea. It would seem
to me UNIQUE(org, full_name) would be better.
>
> I would preference a single statement (one semicolon) solution; but for
> now at least this works 🤷
>
--
Adrian Klaver
adrian.klaver@aklaver.com
