User: Password:
|
|
Subscribe / Log in / New account

UPSERT can be simulated client-side

UPSERT can be simulated client-side

Posted Jun 12, 2014 2:41 UTC (Thu) by sbishop (guest, #33061)
In reply to: UPSERT can be simulated client-side by andresfreund
Parent article: PGCon 2014: Clustering and VODKA

Would you mind helping me understand why dskoll's code wouldn't work? I know why it would be a problem on SQL Server, which is where all my experience is at, but my understanding of PostgreSQL's approach to MVCC tells me that the transaction ought to see a consistent view of the database within one transaction. (In other words, I don't know why the linked-to code loops.)


(Log in to post comments)

UPSERT can be simulated client-side

Posted Jun 12, 2014 6:10 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]

Just imagine what happens if two of these transactions are executing at the same time. You'll end up with two copies of data, since each transaction will see the same view initially.

In practice, you'll likely get a constraint violation error in one of the transactions. That's actually the way UPSERT is usually implemented in Postgres right now.

Ironically, MSSQL does not have this problem because it locks tables, so a second transaction will be waiting until the first transaction is finished.

UPSERT can be simulated client-side

Posted Jun 13, 2014 2:36 UTC (Fri) by sbishop (guest, #33061) [Link]

Thank you, Cyberax. That makes sense.

UPSERT can be simulated client-side

Posted Jun 13, 2014 8:09 UTC (Fri) by intgr (subscriber, #39733) [Link]

> Ironically, MSSQL does not have this problem because it locks tables, so a second transaction will be waiting until the first transaction is finished

Citation needed. This article contradicts your claim: http://www.mssqltips.com/sqlservertip/3074/use-caution-wi...

> MERGE *looks* like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently. This means that you could end up with race conditions or primary key conflicts when multiple users are attempting to fire the same MERGE statement concurrently

MSSQL and MVCC

Posted Jun 14, 2014 13:59 UTC (Sat) by oldtomas (guest, #72579) [Link]

According to the wikipedia page on MVCC [1] MS SQL supports MVCC since SQL Server 2005. So they were late at the party (as often) but they seem to be there.

OTOH, given the installations I've seen in the wild (not the real showcases, mind you), most DBAs and application writers seem overly conservative and prefer to not know the feature exists.


Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds