INSERT ... ON CONFLICT UPDATE and logical decoding - Mailing list pgsql-hackers

From Peter Geoghegan
Subject INSERT ... ON CONFLICT UPDATE and logical decoding
Date
Msg-id CAM3SWZTD4MXS=RM+wZa9U9ZnLWu1OzhQWkFeY-gk=eBFxnHOvA@mail.gmail.com
Whole thread Raw
Responses Re: INSERT ... ON CONFLICT UPDATE and logical decoding  (Andres Freund <andres@2ndquadrant.com>)
List pgsql-hackers
Andres pointed out that the INSERT ... ON CONFLICT UPDATE patch
doesn't work well with logical decoding. Basically, as things stand,
there is no means of determining that an INSERT had an ON CONFLICT
UPDATE clause from logical decoding. It isn't obvious that this
matters - after all, the relevant WAL records are generally consistent
with there being a vanilla INSERT (or vanilla UPDATE). If there was no
possibility of concurrent conflicts, that might actually be the end of
it, but of course there is.

I guess that the best way of fixing this is exposing to output plugins
that a "super deletion" is a
REORDER_BUFFER_CHANGE_INTERNAL_SUPERDELETE.  This is kind of an
internal ReorderBufferChangeType constant, because it means that the
output plugin should probably just omit the tuple just inserted and
now deleted. I tend to think that it would be best if the fact that a
speculative insertion was a speculative insertion is not exposed. We
just formalize that a REORDER_BUFFER_CHANGE_INTERNAL_SUPERDELETE is a
variant of REORDER_BUFFER_CHANGE_DELETE...it's implicit that things
like triggers are not fired here (which may or may not matter,
depending on the use case, I suppose).

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. 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...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.

Thoughts? Can't say that I've given conflict resolution for
multi-master systems a great deal of thought before now, so I might be
quite off the mark here.

-- 
Peter Geoghegan



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: array_push is just stupid ...
Next
From: Greg Stark
Date:
Subject: Re: Allow "snapshot too old" error, to prevent bloat