4 ms·
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 =
by silvestrov 3y ago
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 to make small-ish batch updates and avoid locking all rows in the table.
- idorosen 3y agoYou can already achieve this with CTEs.
- orthoxerox 3y agoCan you really run updates on CTEs that are simple projections and selections? TIL.
- williamdclt 3y agoNo, you run a SELECT … LIMIT in a CTE, and the main query is an UPDATE … JOIN CTE ON mytable.id = CTE.id
- ElongatedMusket 3y agoBut using a Common Table Expression is a workaround, kind of like using a paper towel as a plate. Just give us a plate...
- williamdclt 3y agoCompletely agree, I’d like a LIMIT on UPDATE too
- skrebbel 3y agoHow?
- CraigJPerry 3y agoWould you not just be able to do the CTE as you would a correlated sub query? Something like: WITH batch AS ( SELECT id FROM user_profile WHERE followers_count IS NULL LIMIT 10000 ) UPDATE user_profile SET followers_count = 0 FROM batch WHERE user_profile.id = batch.id But with the difference that if you didn’t want to round-trip to the application for each batch you could now make this a recursive CTE?
- layer8 3y agoDo you specifically need a CTE, or wouldn't a nested SELECT also work?
- samus 3y agoThis would also require adding the ORDER BY clause, as there might be applications where the order in which they are updated matters.
- enormousness 3y agoIs not any single query in Postgres a transaction? I don't think individual rows would be visible outside that transaction until all are updated.
- samus 3y agoThe 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.
- ahoka 3y agoThe ”solution” for this is also SELECT FOR UPDATE … LIMIT 1000.
- sgarland 3y agoThis is one area where MySQL is ahead. UPDATE and DELETE can both use LIMIT.