[systems]

Well MS SQL Merge statement is not very good, and I personally avoid it, and most places I worked in recommend to avoid it, except in the simplest scenarios

From the docs "At scale, MERGE may introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production."

I dont know if its better in PQSQL , but they took their time, so maybe it is

[dspillett]

> Well MS SQL Merge statement is not very good

That is rather an understatement. IIRC unless you are careful with transaction isolation levels and other options, a single MERGE statement can still deadlock against itself.

See https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-... and the older page it links to, mongst many references to the problems you can encounter.

Though things are a fair amount better than they used to be, back in 2008 when MERGE was first introduced, with most of the persistent buggy behaviours pertaining to specific features that not everyone uses, I still completely avoid it.

[andy_ppp]

Postgres’ history usually suggests they don’t ship broken database features which is why most of us reach for it as a first option when choosing a database. The MSSQL warning sounds bad enough that I’d never use this feature!

[zozbot234]

Well, the Postgres docs about MERGE include a similar warning: "When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see [Concurrency control / Transaction isolation] for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable." https://www.postgresql.org/docs/current/sql-merge.html

[KajMagnus]

That's not a warning, instead, it's how it should work, and how one would want it to work, i.e. that the transaction isolation rules apply. Lack of this, would have warranted a warning.

[cogman10]

Agreed. The amount of deadlocks merged causes in MS SQL is pretty insane.

We tried to use it for "upsert" type capabilities and even that would cause weird deadlocks.

Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

[piaste]

> Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

INSERT... ON CONFLICT is awesome, but it has some limitations.

The one I ran into most commonly is that it can only handle exactly one unique constraint on the target table. So if you have both a PK and another unique index, you need to choose which one gets the simple 'on conflict' and which one gets a hacky workaround (locks/transactions, triggers, exception handling, etc.)

If I'm reading the MERGE docs right, you can handle that case:

   WHEN MATCHED AND old.pkey = new.pkey THEN UPDATE SET value = new.value
   WHEN MATCHED AND old.col1 = new.col1 AND old.col2 = new.col2 THEN UPDATE SET reps = reps + 1
   WHEN NOT MATCHED THEN INSERT [...]

[jtc331]

MERGE does not have locking built in in the way that ON CONFLICT does, so it handles more cases and fewer cases all at the same time.

[mwigdahl]

Agreed, another vote here from painful experience to avoid MERGE in SQL Server like the plague.

[int_19h]

Is that a consequence of MSSQL using row locks rather than MVCC by default? Or is it still the same even when you enable snapshot transactions?