Difference between revisions of "SQL MERGE Patch Status"

From PostgreSQL wiki
Jump to: navigation, search
m (Major)
(Major)
Line 38: Line 38:
  
 
Explanatory text added to concurrency chapter by request of Stephen Frost at Dev meeting. Summary here:
 
Explanatory text added to concurrency chapter by request of Stephen Frost at Dev meeting. Summary here:
 +
 +
The SQl Standard says "The extent to which an SQL-implementation may disallow independent changes that are not significant is
 +
implementation-defined.", SQL-2016 Foundation, p.1178, 4)
 +
"not significant" is undefined.
 +
The following concurrency rules are included within v14.
  
 
If MERGE attempts an INSERT and a unique index exists then a
 
If MERGE attempts an INSERT and a unique index exists then a
 
uniqueness violation is raised.  MERGE does not attempt to avoid the ERROR by
 
uniqueness violation is raised.  MERGE does not attempt to avoid the ERROR by
attempting an UPDATE, even though we could do if we wanted it to.
+
attempting an UPDATE, though we could choose to do that if we wished.
  
 
If MERGE attempts an UPDATE or DELETE and the row is concurrently updated,
 
If MERGE attempts an UPDATE or DELETE and the row is concurrently updated,
Line 48: Line 53:
  
 
If MERGE attempts an UPDATE or DELETE and the row is concurrently deleted
 
If MERGE attempts an UPDATE or DELETE and the row is concurrently deleted
we currently throw an ERROR. We think it is possible and desirable to attempt
+
we currently throw an ERROR. We agree it is possible and desirable to attempt
 
an INSERT in this case, but haven't yet worked out how.
 
an INSERT in this case, but haven't yet worked out how.
  

Revision as of 18:23, 3 February 2018

Previous History

SQL MERGE

Overview

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

Bugs

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

Minor

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

Major

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

Explanatory text added to concurrency chapter by request of Stephen Frost at Dev meeting. Summary here:

The SQl Standard says "The extent to which an SQL-implementation may disallow independent changes that are not significant is implementation-defined.", SQL-2016 Foundation, p.1178, 4) "not significant" is undefined. The following concurrency rules are included within v14.

If MERGE attempts an INSERT and a unique index exists then a uniqueness violation is raised. MERGE does not attempt to avoid the ERROR by attempting an UPDATE, though we could choose to do that if we wished.

If MERGE attempts an UPDATE or DELETE and the row is concurrently updated, then MERGE will behave the same as the UPDATE or DELETE commands and perform its action on the latest version of the row, using standard EvalPlanQual.

If MERGE attempts an UPDATE or DELETE and the row is concurrently deleted we currently throw an ERROR. We agree it is possible and desirable to attempt an INSERT in this case, but haven't yet worked out how.

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
  • INSERT OVERRIDING
  • 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