PostgreSQL Question posted in
The official documentation can be found here.

Is the MERGE command in PostgreSQL atomic or does it need some "love" like the SQL Server version?

Coming from SQL Server where I know that the MERGE command needs some extra care like locking hints to be an atomic operation, I now take my fist steps with PostgreSQL. I now came to a point where I would like to use a MERGE command in PostgreSQL, knowing the problematic in SQL Server, I did a quick research and looking at the docs to see if the MERGE command in PostgreSQL is atomic by design or if something similar like on SQL Server is needed.

If it makes any difference the command will not be used directly but within a stored procedure. Currently using PostgreSQL 15.

Well I did not find an answer, only some blogs that mention something in the flow of text but nothing concrete.

Clarification

"Atomic was a bit short what I meant was ACID, so let me rephrase MERGE in not ACID. MERGE acts like multiple statements run one after another and it is not guaranteed that every operation of this single command is made against the same version of the data. Because other statements can modify them in the meantime. A single UPDATE statement (without sub selects of course) is ACID because as soon as the statement is starting to execute the underlying data will not be changed by other statements until the UPDATE statement is finished.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on the link @Adrian Klaver has provided Transaction Isolation I found the answer I was looking for.

    Based on this documentation MERGE statement in PostgreSQL is also not ACID. They stated clearly that rows could be modified between the SELECT phase and the INSERT/UPDATE/DELETE phase which can lead to unwanted results if the INSERT, UPDATE and DELETE parts also involves conditions.

    So in short you will have to be extra careful when using MERGE and choose the right isolation level based on the use case just like in MS SQL Server.


  2. 0

    If you run MERGE on the default READ COMMITTED isolation level, concurrent data modifications can lead to transaction anomalies like "lost update". The statement will always be atomic in the sense that it either completes successfully or has no effect.

    Note that that is not substantially different from an UPDATE: see for example this article. And you can have things like that happen with a single UPDATE that modifies several rows.

    The only reliable way to avoid anomalies is to use a higher transaction isolation level: SERIALIZABLE if you need absolute certainty, but usually REPEATABLE READ, which prevents lost updates from concurrent data modifications, is good enough.

    The alternative is to lock everything in sight, which is usually undesirable.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search