On Wed, Feb 8, 2023 at 5:08 AM Aleksander Alekseev <aleksan...@timescale.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