Re: Add ON CONFLICT DO RETURN clause

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

--
Peter Geoghegan