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

21–30 of 61 posts

Re: PostgreSQL internals: Things to know about update statements

#21

Post by orthoxerox »

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.

Can you really run updates on CTEs that are simple projections and selections? TIL.

Re: PostgreSQL internals: Things to know about update statements

#22

Post by solatic »

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…

I'm convinced that this is due to developers over-relying on ORMs instead of delving into raw SQL.

I'll give another, different but somewhat related example: consider a worker that works on a batch of rows, and wants to update each of the rows in the batch, each row with different data for that row. In raw SQL, this is simple enough with the UPDATE FROM VALUES pattern (see e.g. https://stackoverflow.com/a/18799497 ).

There's no support for this in Prisma, for example: https://www.prisma.io/docs/orm/prisma-client/queries/crud#up... . Most developers would wrap prisma.foo.update in an application-level loop, or possibly wrap all the individual updates in a single prisma.$transaction.

Re: PostgreSQL internals: Things to know about update statements

#23

Post by skrebbel »

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.

How?

Re: PostgreSQL internals: Things to know about update statements

#24

Post by samus »

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…

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

Re: PostgreSQL internals: Things to know about update statements

#25

Post by izietto »

GauntletWizard wrote: post #12
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 don't disagree, but it's so uncommonly used, and I don't know any orm that will do it for you- it'd be easy enough to precompile, but that'd require compiler integration. (Or a hit to every select statement - which probably wouldn't matter for most apps!)

ActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...

Re: PostgreSQL internals: Things to know about update statements

#26

Post by ahoka »

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…

The ”solution” for this is also SELECT FOR UPDATE … LIMIT 1000.

Re: PostgreSQL internals: Things to know about update statements

#27

Post by NeveHanter »

GauntletWizard wrote: post #12
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 don't disagree, but it's so uncommonly used, and I don't know any orm that will do it for you- it'd be easy enough to precompile, but that'd require compiler integration. (Or a hit to every select statement - which probably wouldn't matter for most apps!)

QueryDsl have it, it also has FOR SHARE.

Re: PostgreSQL internals: Things to know about update statements

#28

Post by enormousness »

samus wrote: post #24
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…

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.

Re: PostgreSQL internals: Things to know about update statements

#29

Post by Rapzid »

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…

> The example with follower_count shows how to use the +1 technique to do without locking..

Without explicit pessimistic locking. There are always locks. More guarantees, more locks.

That issue example can be tackled with optimistic concurrency controls depending on the constraints. The issue can be checked out with an UPDATE .. WHERE .. RETURNING ..

Re: PostgreSQL internals: Things to know about update statements

#30

Post by williamdclt »

orthoxerox wrote: post #21
idorosen wrote: post #20

Earlier quoted context omitted.

You can already achieve this with CTEs.

Can you really run updates on CTEs that are simple projections and selections? TIL.

No, you run a SELECT … LIMIT in a CTE, and the main query is an UPDATE … JOIN CTE ON mytable.id = CTE.id
Post reply on HN