Re: Add ON CONFLICT DO RETURN clause

Wolfgang Walther <walther(at)technowledgy(dot)de> writes:

> 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?

There was a patch for ON CONFLICT DO SELECT submitted a while back, but
the author abandoned it. I hven't read either that patch that or yours,
so I don't know how they compare, but you might want to have a look at
it:

https://commitfest.postgresql.org/16/1241/

> Best
>
> Wolfgang

- ilmari