Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL internals: Things to know about update statements

patrick.engineering

Post reply on HN
Print view View original

31–40 of 61 posts

Re: PostgreSQL internals: Things to know about update statements

#31

Post by layer8 »

idorosen wrote: post #20
silvestrov wrote: post #18

One thing I'd really like PostgreSQL to add is LIMIT on update statement as this makes batching easier. E.g. UPDATE user_profile SET followers_count = 0 WHERE followers_count IS NULL LIMIT 10000 I don't care which rows gets updated, only that no more than 10000 rows get updated. After each UPDATE I will COMMIT and then repeat as long as the UPDATE returns a positive number of rows updated. This makes it much easier t…

You can already achieve this with CTEs.

Do you specifically need a CTE, or wouldn't a nested SELECT also work?

Re: PostgreSQL internals: Things to know about update statements

#32

Post by dathinab »

> 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 a situation will fail (in any SQL database) and needs to be retired. (Through in some unusual setups the db might be able to translate retry the transaction by itself, through at the cost of a ton of drawbacks)

Anyway reading the postgres documentation is always a good idea, it's not perfect but pretty good.

Re: PostgreSQL internals: Things to know about update statements

#33

Post by layer8 »

koolba wrote: post #5
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…

This is transaction 101. You simply lock the row via a SELECT … FOR UPDATE when you read the value. Any application that actually cares about consistency of its data must be doing this. Or just do it one step so the update increments the value in place. That way you have an implicit lock.

However, SELECT FOR UPDATE doesn't lock not-yet-existing rows in PostgreSQL, like it does in MySQL with gap locks. If for example, the transaction performs either an UPDATE or an INSERT based on whether the SELECT FOR UPDATE found or didn’t find a row, then two concurrent executions of that transaction can run into a conflict or duplicate INSERT in PostgreSQL, whereas in MySQL the second execution would block on the SELECT FOR UPDATE of the first execution even when there are no matching rows (but the first execution then will/might create some).

Re: PostgreSQL internals: Things to know about update statements

#34

Post by dathinab »

arthurcolle wrote: post #9
koolba wrote: post #5

Earlier quoted context omitted.

This is transaction 101. You simply lock the row via a SELECT … FOR UPDATE when you read the value. Any application that actually cares about consistency of its data must be doing this. Or just do it one step so the update increments the value in place. That way you have an implicit lock.

I haven't used this, but I see its in Oracle and MySQL - does this exist in Postgresql or is this just the same as wrapping in a transaction where you're selecting on the row by ID or something? Just curious

it does exist and I think it was even added to the SQL standard in some update

what FOR UPDATE does is (simplified) to lock a mutex for each row the select statement returns which are released once the transaction commits

this has the effect that parallel running transactions have to wait until the new computed value is visible before reading it and in turn there is no problem with lost updates

just to be clear this is a very simplified explanation in many ways

Re: PostgreSQL internals: Things to know about update statements

#35

Post by OPA100 »

Database locking can be a bit surprising at times. I recently stumbled over CREATE TABLE IF NOT EXISTS causing client timeouts - you might expect this to be near-instantaneous if the table exists, and it usually is, until it's not... because it always acquires an exclusive table lock, so it's blocked even by readers.

Re: PostgreSQL internals: Things to know about update statements

#37

Post by layer8 »

remram wrote: post #36

> This behavior may well be interpreted as a violation of the SQL standard. This effectively changed the tone from an informational article to a hit piece. This is a huge accusation which is plain wrong.

It’s not a hit piece, it’s just someone who had the wrong conception of the guarantees (not) provided by transactions, and hasn’t fully come around yet.

Re: PostgreSQL internals: Things to know about update statements

#38

Post by layer8 »

OPA100 wrote: post #35

Database locking can be a bit surprising at times. I recently stumbled over CREATE TABLE IF NOT EXISTS causing client timeouts - you might expect this to be near-instantaneous if the table exists, and it usually is, until it's not... because it always acquires an exclusive table lock, so it's blocked even by readers.

I wouldn’t classify that as “database locking”, but as “idiosyncrasy of a particular database system”. It’s a QoI issue.

Re: PostgreSQL internals: Things to know about update statements

#39

Post by samus »

enormousness wrote: post #28
samus wrote: post #24

Earlier quoted context omitted.

This would also require adding the ORDER BY clause, as there might be applications where the order in which they are updated matters.

Is not any single query in Postgres a transaction? I don't think individual rows would be visible outside that transaction until all are updated.

The question is which rows would be updated by a single execution of a UPDATE...LIMIT query. The order of result rows of a SELECT query is undefined in Postgres unless you add an ORDER BY clause. It is natural to assume that an UPDATE...LIMIT would be similarly affected.

Re: PostgreSQL internals: Things to know about update statements

#40

Post by fweimer »

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 documented clearly). But even with that taken care of, you still had to think carefully about non-transactional side effects (such as sending mail) when writing application logic. A neat side effect was that you could restart the PostgreSQL server without impacting running applications (and today, you could probably even kexec a new kernel before the application timeout kicks in).

I suspect with the current preferences to avoid exceptions, writing for automated retry becomes quite a bit harder. And I couldn't really get automated transaction retry to work for SQLite while still caching statement handles, treating them as prepared statements.

Post reply on HN