Re: INSERT ... ON CONFLICT UPDATE and logical decoding

On Thu, Feb 19, 2015 at 2:11 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2015-02-18 16:35:14 -0800, Peter Geoghegan wrote:
>> Andres pointed out that the INSERT ... ON CONFLICT UPDATE patch
>> doesn't work well with logical decoding.
>
> Just to make that clear: I didn't actually test it, but it ddidn't look
> good.

You didn't have to test it - it wasn't considered properly, but should
have been. Simple as that.

>> Would that be sufficiently flexible for the various logical
>> replication use cases? I guess we are somewhat forced to push that
>> kind of thing into output plugins, because doing so lets them decide
>> how to handle this.
>
> I don't see what benefits that'd bring.

Hmm. Yeah, I did somewhat talk myself out of it as I wrote the e-mail. :-)

>> It's a little unfortunate that this implementation
>> detail is exposed to output plugins, though, which is why I'd be
>> willing to believe that it'd be better to have transaction reassembly
>> normalize the records such that a super deleted tuple was never
>> exposed to output plugins in the first place...
>
> Yes.
>
>> they'd only see a
>> REORDER_BUFFER_CHANGE_INSERT when that was the definitive outcome of
>> an UPSERT, or alternatively a REORDER_BUFFER_CHANGE_UPDATE when that
>> was the definitive outcome. No need for output plugins to consider
>> REORDER_BUFFER_CHANGE_INTERNAL_SUPERDELETE at all.
>
> Yes. It'd be easiest if the only the final insert/update were actually
> WAL logged as full actions.

Well, that implies that we'd actually know that we'd succeed when WAL
logging the speculative heap tuple's insertion. We literally have no
way of knowing if that's the case at that point, though - that's just
the nature of value locking scheme #2's optimistic approach.

> IIUC we can actually otherwise end with a,
> theoretically, arbitrarily large chain of insert/super deletions.

We can end up with an infinite number of insertions and super
deletions, in theory. However, one session is always guaranteed to
make progress, so the lock starvation hazards [1] seem acceptable. You
can say the same thing about the subxact looping pattern too.

I'll look into making it so that a "normalization" process happens
during transaction reassembly, thus relieving logical changeset
plugins from the burden of having to think of ON CONFLICT UPDATE as a
special case at all. I think that's what it will take.

Thanks

[1] https://wiki.postgresql.org/wiki/UPSERT#Theoretical_lock_starvation_hazards
--
Peter Geoghegan