Difference between revisions of "SQL MERGE Patch Status"

From PostgreSQL wiki
Jump to: navigation, search
m (Overview)
m (Bugs)
Line 13: Line 13:
  
 
== Bugs ==
 
== Bugs ==
* Incorrect wording on ERROR when using subselect in the SET clause of MERGE UPDATE:
+
* Incorrect wording on ERROR when using subselect in the SET clause of MERGE UPDATE (Reported by PeterG)
 
Unrecognized node type 114
 
Unrecognized node type 114
 
[https://www.postgresql.org/message-id/CAH2-WznPcu-7gDhWB-AG9BMamZ0sVKrwrHyoy%3DGLYy4-z01Emg%40mail.gmail.com]
 
[https://www.postgresql.org/message-id/CAH2-WznPcu-7gDhWB-AG9BMamZ0sVKrwrHyoy%3DGLYy4-z01Emg%40mail.gmail.com]
  
* Incorrect wording on ERROR when using a whole row reference in the SET clause of MERGE UPDATE
+
* 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
 
ERROR:  XX000: variable not found in subplan target lists
 
[https://www.postgresql.org/message-id/CAH2-WznPcu-7gDhWB-AG9BMamZ0sVKrwrHyoy%3DGLYy4-z01Emg%40mail.gmail.com]
 
[https://www.postgresql.org/message-id/CAH2-WznPcu-7gDhWB-AG9BMamZ0sVKrwrHyoy%3DGLYy4-z01Emg%40mail.gmail.com]

Revision as of 12:31, 30 January 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]

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]

Minor

  • Mention DO NOTHING is an extension to the SQL Standard in docs

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]

Unsupported features

  • Partitioning (WIP)
  • INSERT OVERIDING
  • Tab completion
  • 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 (as yet untested)
  • CTE support - not part of standard - would throw syntax ERROR (as yet untested)

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