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

Hi,

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.

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

I'm pretty much dead set against exposing anything like this output
plugins. The output plugin shouldn't care that a insertion was a
upsert. For one they won't get it right since they will be really
infrequent, for another it'll be hard to replay such an event on another
node.

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

An output plugin can't just go back and call a tuple that was already
sent to a client back.

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

I can't see that working. How such a event - that obviously causes
unique conflicts! - be replayed on another node?

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

> 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. IIUC we can actually otherwise end with a,
theoretically, arbitrarily large chain of insert/super deletions.

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

I don't think conflict resolution actually plays a role here. This is
about consistency inside a single system, not consistency across
systems.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services