Thread: Add ON CONFLICT DO RETURN clause

When using ON CONFLICT DO NOTHING together with RETURNING, the 
conflicted rows are not returned. Sometimes, this would be useful 
though, for example when generated columns or default values are in play:

CREATE TABLE x (
   id INT PRIMARY KEY,
   created_at TIMESTAMPTZ DEFAULT CURRENT_TIMEMSTAMP
);

To get the created_at timestamp for a certain id **and** at the same 
time create this id in case it does not exist, yet, I can currently do:

INSERT INTO x (id) VALUES (1)
   ON CONFLICT DO UPDATE
   SET id=EXCLUDED.id
   RETURNING created_at;

However that will result in a useless UPDATE of the row.

I could probably add a trigger to prevent the UPDATE in that case. Or I 
could do something in a CTE. Or in multiple statements in plpgsql - this 
is what I currently do in application code.

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.

Thoughts?

Best

Wolfgang

On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@technowledgy.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

Peter Geoghegan:
> On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
> <walther@technowledgy.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

Wolfgang Walther <walther@technowledgy.de> writes:

> Peter Geoghegan:
>> On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
>> <walther@technowledgy.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