Translate HQL INSERT ... ON CONFLICT with a field-list target to an upsert

XMLWordPrintableJSON

    • Type: New Feature
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Mutation
    • 3
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Translate HQL insert ... on conflict (<field list>) do nothing and do update set x = excluded.x, ... into an upsert: one update statement per values row, filter as equality on the conflict-target columns, $set per assignment, $setOnInsert for the remaining inserted columns, upsert: true, multi: false.

      A single MongoDB update applies the same operators on the insert path and the matched path, which is what makes set x = excluded.x the supported assignment form: the value it writes on conflict equals the value the insert would have written.

      Covers do nothing and do update set x = excluded.x; single and multi-row values lists; conflict targets and assigned values over plain embeddables (dotted paths and whole embeddables, including one assignment spanning several columns), @Struct embeddables, and arrays as assigned values. Every supported translation was executed against a live MongoDB server and verified against PostgreSQL's behaviour for the equivalent statement, on both the insert and the conflict path.

      Rejections: on constraint <name> throws referencing HIBERNATE-214; expression assignments throw referencing HIBERNATE-215; forms with no MQL mechanism (cross-column and literal assignments, where predicates, no-target do update, array conflict targets) throw bare messages naming the limitation. No-target do nothing keeps throwing until HIBERNATE-219.

      Open design question for this ticket: JDBC row-count semantics count matched documents, so a matched-and-skipped do nothing row would report 1 where PostgreSQL reports 0; the ticket must settle what the emitted statement reports.

      Builds on the AST and JDBC groundwork of HIBERNATE-217.

            Assignee:
            Unassigned
            Reporter:
            Jeffrey Yemin
            None
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: