Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent

On Wed, Feb 8, 2023 at 5:08 AM Aleksander Alekseev
<aleksander(at)timescale(dot)com> wrote:
> > To me it's a pretty fundamental violation of how heap visibility works.
>
> I don't think this has much to do with heap visibility. It's true that
> generally a command doesn't see its own tuples. This is done in order
> to avoid the Halloween problem which however can't happen in this
> particular case.
>
> Other than that the heap doesn't care about the visibility, it merely
> stores the tuples. The visibility is determined by xmin/xmax, the
> isolation level, etc.

I think that in a green field situation we would probably make READ
COMMITTED updates throw cardinality violations in the same way as ON
CONFLICT DO UPDATE, while not changing anything about ON CONFLICT DO
NOTHING. We made a deliberate trade-off with the design of DO NOTHING,
which won't lock conflicting rows, and so won't dirty any heap pages
that it doesn't insert on to.

I don't buy your argument about DO UPDATE needing to be brought into
line with DO NOTHING. In any case I'm pretty sure that Tom's remarks
in 2016 about a behavioral inconsistencies (which you cited) actually
called for making DO NOTHING more like DO UPDATE -- not the other way
around.

To me it seems as if allowing the same command to update the same row
more than once is just not desirable in general. It doesn't seem
necessary to bring low level arguments about cmin/cmax into it, nor
does it seem necessary to talk about things like the Halloween
problem. To me the best argument is also the simplest: who would want
us to allow it, and for what purpose?

I suppose that we might theoretically prefer to throw a cardinality
violation for DO NOTHING, but I don't see a way to do that without
locking rows and dirtying heap pages. If somebody were to argue that
we should make DO NOTHING lock rows and throw similar errors now then
I'd also disagree with them, but to a much lesser degree. I don't
think that this patch is a good idea.

--
Peter Geoghegan