Re: INSERT ON CONFLICT and RETURNING

On 03.09.2020 19:30, Marko Tiikkaja wrote:
> There's prior art on this: https://commitfest.postgresql.org/15/1241/
>
>
> .m
Ooops:(
Thank you.
I missed it.

But frankly speaking I still didn't find answer for my question in this
thread: what are the dangerous scenarios with ON CONFLICT DO NOTHING/SELECT.
Yes, record is not exclusively locked. But I just want to obtain value
of some column which is not a source of conflict. I do not understand
what can be wrong if some
other transaction changed this column.

And I certainly can't agree with Peter's statement:
> Whereas here, with ON CONFLICT DO SELECT,
> I see a somewhat greater risk, and a much, much smaller benefit. A
> benefit that might actually be indistinguishable from zero.

From my point of view it is quite common use case when we need to
convert some long key to small autogenerated record identifier.
Without UPSERT we have to perform two queries instead of just one . And
even with current implementation of INSERT ON CONFLICT...
we have to either perform extra lookup, either produce new (useless)
tuple version.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company