SQL MERGE Patch Status

Previous History

SQL MERGE

Overview

Patch for SQL Standard MERGE statement has been 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

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.

• EXPLAIN ANALYZE reports number of rows inserted incorrectly, not taking into account rows skipped by NOT MATCHED WHEN AND conditions (calculation error, reports number of rows processed - updates - deletes) (Reported by PeterG)

• 3 Assertion failures reported by sqlsmith (Reported by Andreas Seltenreich)

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_SubLink))", File: "clauses.c", Line: 440) MERGE INTO public.brin_test as target_0 USING pg_catalog.pg_database as ref_0

 left join pg_catalog.pg_user_mapping as sample_0 tablesample system (2.3)
 on (pg_catalog.mul_d_interval(
       cast(pg_catalog.pi() as float8),
       cast(case when sample_0.umoptions is not NULL then (select write_lag from pg_catalog.pg_stat_replication limit 1 offset 2)
            else (select write_lag from pg_catalog.pg_stat_replication limit 1 offset 2)
            end
          as "interval")) = (select intervalcol from public.brintest limit 1 offset 2)
       )

ON target_0.a = ref_0.encoding WHEN NOT MATCHED AND cast(null as "timestamp") < cast(null as date)

  THEN INSERT VALUES ( 62, 6)

WHEN NOT MATCHED

 AND false
  THEN DO NOTHING;

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_SubLink))", File: "prepunion.c", Line: 2246) MERGE INTO public.onek2 as target_0 USING public.prt1 as ref_0

 inner join public.tenk1 as ref_1
 on ((select t from public.btree_tall_tbl limit 1 offset 63)
        is not NULL)

ON target_0.stringu1 = ref_1.stringu1 WHEN NOT MATCHED THEN DO NOTHING;

-- TRAP: FailedAssertion("!(!((((const Node*)(node))->type) == T_Query))", File: "var.c", Line: 248) MERGE INTO public.clstr_tst_inh as target_0 USING pg_catalog.pg_statio_sys_tables as ref_0

 left join public.rule_and_refint_t3 as ref_1
 on (((ref_0.heap_blks_hit is not NULL)
       or (((select f1 from public.path_tbl limit 1 offset 5)
              >= (select thepath from public.shighway limit 1 offset 33)
             )
         or (cast(null as tsvector) <> cast(null as tsvector))))
     and (ref_0.toast_blks_read is not NULL))

ON target_0.d = ref_1.data WHEN NOT MATCHED

 AND cast(null as int2) = pg_catalog.lastval()
  THEN DO NOTHING;

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 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. MERGE actions can be conditional, so conditions must be re-evaluated on the latest row.

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

If MERGE attempts an INSERT and a unique index is present and the new row is a duplicate then a uniqueness violation is raised. MERGE does not attempt to avoid the ERROR by attempting an UPDATE. It woud be possible to avoid the errors by using speculative inserts but that has been argued against by some. (*)

The full guarantee of always either insert or update that is available with INSERT ON CONFLICT UPDATE is not always possible because of the conditional rules of the MERGE statement, so we would be able to make only one attempt at UPDATE if INSERT fails or INSERT if UPDATE fails. This is to ensure consistent behavior of the command.

(*) It is understood that other DBMS throw errors in these case (fact check needed). Some DBMS cope with this by routing such errors to an Error Table that is created on first error.

Planned for PG11

• 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
• RLS - Requested not to be supported for PG11, on basis of risk - would throw ERROR on table with POLICYs (already blocked)
• get_query_def() in ruleutils.c

Not planned for PG11

• Whole row vars in MERGE UPDATE targetlists
• Subselects in WHEN AND conditions (supported by Oracle and SQLServer, but non-standard)
• INSERT OVERRIDING
• 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