>> The concurrency rules are very simple: >> If a MATCHED row is concurrently updated/deleted >> 1. We run EvalPlanQual >> 2. If the updated row is gone EPQ returns NULL slot or EPQ returns a >> row with NULL values, then >> { >> if NOT MATCHED action exists, then raise ERROR >> else continue to next row >> } >> else >> re-check all MATCHED AND conditions and execute the first action >> whose WHEN Condition evaluates to TRUE >> >> >> This means MERGE will work just fine for "normal" UPDATEs, but it will >> often fail (deterministically) in concurrent tests with mixed >> insert/deletes or UPDATEs that touch the PK, as requested. > > > can be nice to have part about differences between MERGE and INSERT ON > CONFLICT DO
We've agreed not to attempt to make it do anything like INSERT ON CONFLICT, so we don't need to discuss that here anymore.
My note was not against MERGE or INSERT ON CONFLICT. If I understand to this topic, I agree so these commands should be implemented separately. But if we use two commands with some intersection, there can be nice to have documentation about recommended use cases. Probably it will be very often question.
Regards
Pavel
MERGE can be semantically equivalent to an UPDATE join or a DELETE join, and in those cases, MERGE behaves the same. It handles much more complex cases also.
MERGE as submitted here follows all MVCC rules similar to an UPDATE join. If it hits a problem with concurent activity it throws ERROR: could not serialize access due to concurrent update to make sure there is no ambiguity (as described directly above).
As we discussed earlier, removing some of those ERRORs and making it do something useful instead may be possible later.
-- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
