Re: Add ON CONFLICT DO RETURN clause

Peter Geoghegan:
> On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
> <walther(at)technowledgy(dot)de> wrote:
>> The attached patch adds a DO RETURN clause to be able to do this:
>>
>> INSERT INTO x (id) VALUES (1)
>> ON CONFLICT DO RETURN
>> RETURNING created_at;
>>
>> Much simpler. This will either insert or do nothing - but in both cases
>> return a row.
>
> How can you tell which it was, though?

I guess I can't reliably. But isn't that the same in the ON UPDATE case?

In the use cases I had so far, I didn't need to know.

> I don't see why this statement should ever perform steps for any row
> that are equivalent to DO NOTHING processing -- it should at least
> lock each and every affected row, if only to conclusively determine
> that there really must be a conflict.
>
> In general ON CONFLICT DO UPDATE allows the user to add a WHERE clause
> to back out of updating a row based on an arbitrary predicate. DO
> NOTHING has no such WHERE clause. So DO NOTHING quite literally does
> nothing for any rows that had conflicts, unlike DO UPDATE, which will
> at the very least lock the row (with or without an explicit WHERE
> clause).
>
> The READ COMMITTED behavior for DO NOTHING is a bit iffy, even
> compared to DO UPDATE, but the advantages in bulk loading scenarios
> can be decisive. Or at least they were before we had MERGE.

Agreed - it needs to lock the row. I don't think I fully understood what
"nothing" in DO NOTHING extended to.

I guess I want DO RETURN to behave more like a DO SELECT, so with the
same semantics as selecting the row?

Best

Wolfgang