MergeIntoPolyfill

Features:

• provide a DSL to express postgres MERGE queries
• depending on the current postgres version,
  • >= 15 runs the query as a single MERGE query
  • < 15 will run a more involved plan in a transaction:
    • select query to find the matches specified
    • one insert/update/delete query for each match
  • both ways have the same effect, but the MERGE query is more performant, since the parameters have to be sent only once.

Polyfill caveats

• It is very slow! In load tests with a high QPS, The polyfill was 100x slower than the merge into equivalent.
• The source table must have an id column
• When using the polyfill on partitioned tables, the partition columns must not be updated in a way that places the row outside of the partition it's in. This library won't check to ensure this is the case!

Examples

The following is code from the test:

Repo.insert(%Book{title: "Book 2", year: 1999})
Repo.insert(%Book{title: "Book 10", year: 2007})
Repo.insert(%Book{title: "Book 3", year: 2000})

source_query =
  from(gs in fragment("generate_series(1, 10)"),
    select: %{
      id: gs + 0,
      title: fragment("concat(?::text, ?)", ^"Book ", gs),
      year: gs + 2000
    }
  )

merge_into(Book, as(:target).title == as(:source).title, source_query) do
  matched?() and as(:source).year >= 2008 ->
    update([:year])

  matched?() and as(:target).title == ^"Book 2" ->
    delete()

  matched?() ->
    update(title: fragment("concat(?, ' (', ?, ')')", as(:target).title, as(:source).year))

  not matched?() ->
    insert([:title, :year])
end
|> Repo.transaction()

For Postgres 15 and newer, it will run this query:

MERGE INTO "books" AS b0 
USING (SELECT 
  sf0 + 0 AS "id", 
  concat($1::text, sf0) AS "title", 
  sf0 + 2000 AS "year" 
  FROM generate_series(1, 10) AS sf0
) AS s1 
ON b0."title" = s1."title" 
WHEN MATCHED AND s1."year" >= 2008 
  THEN UPDATE SET "year" = s1."year" 
WHEN MATCHED AND b0."title" = $2 
  THEN DELETE 
WHEN MATCHED AND TRUE 
  THEN UPDATE SET "title" = concat(b0."title", ' (', s1."year", ')') 
WHEN NOT MATCHED AND TRUE 
  THEN INSERT ("title", "year") VALUES (s1."title", s1."year");
-- ["Book ", "Book 2"]

For anything older than Postgres 15, these queries will be executed:

begin;

SELECT jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_build_object('target_id', b0."id", 'source_id', s1."id")::jsonb, $1::text[], to_jsonb(NOT (b0."id" IS NULL) AND (s1."year" >= 2008)::boolean))::jsonb, $2::text[], to_jsonb(NOT (b0."id" IS NULL) AND (b0."title" = $3)::boolean))::jsonb, $4::text[], to_jsonb(NOT (b0."id" IS NULL) AND TRUE::boolean))::jsonb, $5::text[], to_jsonb((b0."id" IS NULL) AND TRUE::boolean)) FROM "books" AS b0 RIGHT OUTER JOIN (SELECT sf0 + 0 AS "id", concat($6::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 ON b0."title" = s1."title";
-- [["0"], ["1"], "Book 2", ["2"], ["3"], "Book "]

UPDATE "books" AS b0 SET "year" = s1."year" FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 WHERE (b0."title" = s1."title") AND (b0."id" = ANY($2));
-- ["Book ", [455]]

DELETE FROM "books" AS b0 WHERE (b0."id" = ANY($1));
-- [[454]]

UPDATE "books" AS b0 SET "title" = concat(b0."title", ' (', s1."year", ')') FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 WHERE (b0."title" = s1."title") AND (b0."id" = ANY($2));
-- ["Book ", [456]]

INSERT INTO "books" ("title","year") (SELECT s0."title", s0."year" FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s0 WHERE (s0."id" = ANY($2)));
-- ["Book ", [5, 7, 4, 9, 8, 6, 1]]

commit;

Setup

1. Add the dependency to your mix.exs:

   {:merge_into_polyfill, "~> 0.1"}

2. Add MergeIntoPolyfill.CheckVersion to your application supervisor, directly after the ecto repo is started:

   children = [
     # ...
     MyApp.Repo, 
     {MergeIntoPolyfill.CheckVersion, MyApp.Repo}
     # ...
   ]

   Alternatively, you can set the desired builder every time you are using the merge_into macro:

   merge_into(Schema, as(:source).id == as(:target).id, values, builder: MergeIntoPolyfill.Builders.MergeInto) do
     # ...
   end

   or by updating the env that is set by MergeIntoPolyfill.CheckVersion:

   Application.put_env(:merge_into_polyfill, MergeIntoPolyfill.CheckVersion, builder: MergeIntoPolyfill.Builders.Polyfill)

Done! The MERGE INTO polyfill is now ready to be used.