SQL MERGE Patch Status

From PostgreSQL wiki
Revision as of 21:03, 1 February 2018 by Pgeoghegan (talk | contribs) (Bugs)
Jump to: navigation, search

Previous History



Patch for SQL Standard MERGE statement submitted to PostgreSQL core - authored by Simon Riggs and Pavan Deolasee of 2ndQuadrant. Substantial review input from Peter Geoghegan of vmWare.

Current patch is v14 [1]

Next patch v15 due mid-Feb 2018

Open Items


  • Incorrect wording on ERROR when using subselect in the SET clause of MERGE UPDATE (Reported by PeterG)

Unrecognized node type 114 [2]

  • Incorrect wording on ERROR when using a whole row reference in the SET clause of MERGE UPDATE (Reported by PeterG)

ERROR: XX000: variable not found in subplan target lists [3]

Peter Geoghegan disagrees with the current characterization of both of these bugs as "incorrect wording" issues.


  • Mention DO NOTHING is an extension to the SQL Standard in docs (Reported by Simon, fix committed, due in v15)


  • Discussion around concurrency. Should we throw ERROR if concurrent update/delete makes row unavailable after EvalPlanQual?

Unresolved request for change to earlier consensus on how to proceed [4]

PLEASE MAKE ALL REQUESTED CHANGES TO CONCURRENCY IN TERMS OF THE CURRENT PATCH. i.e. state clearly which test needs to change? what is the change in user visible behavior? That allows us to comment on specific issues and to use that test as a target for development.

Unsupported features

  • Partitioning (WIP due v15) - likely to require multiple different plans for various use cases
  • Tab completion (WIP due v15) - likely unable to have full syntax, too complex
  • get_query_def() in ruleutils.c

Not planned for PG11

  • RLS - Requested not to be supported for PG11, on basis of risk - would throw ERROR on table with POLICYs (already blocked)
  • Transition tables for Statement Triggers - would throw ERROR on use against a table with transition tables defined (already blocked)
  • RETURNING - not part of standard - would throw syntax ERROR (blocked at v15)
  • CTE support - not part of standard - would throw syntax ERROR (blocked at v15)

Closed since v13

v14 - 18/1/29

  • Add xref docs between INSERT ON CONFLICT and MERGE
  • Add: New direct self-referencing test case, no bugs found
  • Add: EXPLAIN ANALYZE in the same style as INSERT .. ON CONFLICT
  • Add: Allow Oid system column to be used in WHEN AND conditions
  • Add: Prevent WHEN AND clause from writing data to db, per SQL spec