PostgreSQL internals: Things to know about update statements • Hacker News Board

Re: PostgreSQL internals: Things to know about update statements

#51

Post by senderista » Sat, Dec 30, 2023, 4:43 PM UTC

fweimer wrote: post #40

GauntletWizard wrote: post #2

#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate pr…

If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transactions, and doing that can be quite hard. Many years ago, I wrote some helper code to retry transactions on transient failures for PostgreSQL (which was unnecessarily hard at the time because the error codes that are eligible for transaction retry were not docum…

That's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.

Re: PostgreSQL internals: Things to know about update statements

#52

Post by masto » Sat, Dec 30, 2023, 5:04 PM UTC

hithereperson90 wrote: post #44

dathinab wrote: post #32

> This behavior may well be interpreted as a violation of the SQL standard. no it may not this is fully standard compatible behavior and even expected behavior for _any_ SQL database implementing a read committed transaction insulation level also it's a problem which on a theoretically level is not solvable with how SQL works, hence why on stricter isolation levels like serializable committing the transaction in such…

[flagged]

Please don't unleash AI comment bot spam on Hacker News.

Re: PostgreSQL internals: Things to know about update statements

#53

Post by rvdginste » Sat, Dec 30, 2023, 5:44 PM UTC

I find the following article from some time ago more informative. It contains a very good explanation of isolation levels and how they are implemented in postgresql.

https://news.ycombinator.com/item?id=38684447

Re: PostgreSQL internals: Things to know about update statements

#54

Post by Horffupolde » Sat, Dec 30, 2023, 5:54 PM UTC

Grownups don’t use UPDATE.

Re: PostgreSQL internals: Things to know about update statements

#55

Post by fweimer » Sat, Dec 30, 2023, 6:16 PM UTC

senderista wrote: post #51

fweimer wrote: post #40

Earlier quoted context omitted.

If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transactions, and doing that can be quite hard. Many years ago, I wrote some helper code to retry transactions on transient failures for PostgreSQL (which was unnecessarily hard at the time because the error codes that are eligible for transaction retry were not docum…

That's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.

I don't think you can have automated retry because if the application logic lives outside the database and is not subject to its transaction processing (which is the common programming pattern today, I think), the database has to arrange for a re-run of that logic if any of the observed database state changes (such as the followers_count returned from the SELECT statement in the example). This means that the code implementing the application code has to be ready to execute multiple times without ill effects, and application programmers need to be aware of that.

Re: PostgreSQL internals: Things to know about update statements

#56

Post by [deleted] » Sat, Dec 30, 2023, 6:35 PM UTC

pmontra wrote: post #13

chrisandchris wrote: post #6

Earlier quoted context omitted.

While I agree, it's a pretty bad example for demonstrating this issue, because an error on "follower_count" will probably not cause any harm (youtube example). Also, the value can be recalculated at all times (maybe not feasable). Any other example that comes to my mind will not depend on the "state of the apps memory" for a sensitive value, because that would be bad design too. Then the sensitive value must be the a…

The example with follower_count shows how to use the +1 technique to do without locking. That's OK IMHO. It's not possible to use that technique when updating other type of data. Example select * from issues where id = 123; -- do something in app update issues set status = 'done' where id = 123; There you can lose a status update if two people happen to work on the same issue at the same time with two different ideas…

[deleted]

Re: PostgreSQL internals: Things to know about update statements

#57

Post by Rapzid » Sat, Dec 30, 2023, 7:58 PM UTC

Rapzid wrote: post #8

wisty wrote: post #7

Earlier quoted context omitted.

8 bytes of new data but 8x28 = 224 bytes = 200 data + 8 date + 16 for id. The 28 is a factor (×).

I should have used more precise language; I mean the bytes written to disk don't work out with such a simple formula. Missed the footnote which discusses this, but there are even more things to consider such as how many rows are being updated at once and etc.

To add to this for anyone reading later..

The OS doesn't write bytes to the storage; it writes blocks. For modern drives the sector size is 4k, and the block io size is likely to be 4k unless utilizing 512b emulation.

So, the OS will always send 4k minimum writes to the storage device. In this case updating just the 8bytes would still result in a 4k write; at least to the wal then again to update the page plus index updates and etc.

Re: PostgreSQL internals: Things to know about update statements

#58

Post by rowls66 » Sat, Dec 30, 2023, 8:40 PM UTC

ComodoHacker wrote: post #16

rowls66 wrote: post #3

In the Lost Updates section, a more straight forward solution is to use the FOR UPDATE clause in the first select statement. This locks the record and prevents concurrent updates.

When you're incrementing by using UPDATE ... SET value = value + 1, the database holds the locks for the minimum time needed. Everything else is less efficient. In more complex scenarios, FOR UPDATE is the solution.

Not sure what you mean by "the database holds locks for the minimum time needed." Locks are always held until the transaction commits.

Re: PostgreSQL internals: Things to know about update statements

#59

Post by sgarland » Sat, Dec 30, 2023, 9:20 PM UTC

solatic wrote: post #48

sgarland wrote: post #42

Earlier quoted context omitted.

I think maybe with Django, you could use a QuerySet with an F expression to do this? [0] Agreed that it's trivial in pure SQL. As a counter though, having worked at places using an ORM (mostly Django), and also where everything was raw SQL, the latter winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later. I lost any respect for Prisma when I learned it doesn't do JOINs in the DB…

> raw SQL... winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later. I won't dispute this, I'll just point out that (a) premature optimization is the root of all evil, (b) because it's raw SQL, it's easier to reason about and fix. Inefficient queries aren't really a problem in the early days when your whole dataset doesn't even add up to a gigabyte.

The queries aren't necessarily a problem early on (although I've seen OFFSET/LIMIT used for pagination, which is just... no), but schema is sticky. Terrible decisions made early on only become harder to fix as the data set grows.

Re: PostgreSQL internals: Things to know about update statements

#60

Post by senderista » Sat, Dec 30, 2023, 10:15 PM UTC

fweimer wrote: post #55

senderista wrote: post #51

Earlier quoted context omitted.

That's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.

I don't think you can have automated retry because if the application logic lives outside the database and is not subject to its transaction processing (which is the common programming pattern today, I think), the database has to arrange for a re-run of that logic if any of the observed database state changes (such as the followers_count returned from the SELECT statement in the example). This means that the code imp…

That's true, that's why the client API should allow you to submit a callable object of some sort (e.g. a C++ lambda) that is automatically wrapped in a DB transaction and allows you to register commit and abort handlers as I described. Here's such an API that I'm currently working on: https://senderista.github.io/atomik-website/.