Based on the feedback on my previous patch, I've separated only the LIMIT part into its own feature. This version plays nicely with inheritance. The intended use is splitting up big UPDATEs and DELETEs into batches more easily and efficiently.
Before looking into the patch, I'd like to know the use cases in more details.
There are two common use cases I can think of:
1)
I've just added a column to an existing table, and it is all NULL. I've changed the code to populate that column appropriately for new or updated rows, but I need to back fill the existing rows. I have a (slow) method to compute the new value. (I've not yet changed the code to depend on that column being populated)
The obvious solution is:
update the_table set new_col=populate_new_col(whatever) where new_col is null.
But this will bloat the table because vacuum cannot intervene, and will take a very long time. The first row to be update will remain locked until the last row gets updated, which is not acceptable. And if something goes wrong before the commit, you've lost all the work.
With the limit clause, you can just do this:
update the_table set new_col=populate_new_col(whatever) where new_col is null limit 50000;
In a loop with appropriate vacuuming and throttling.
2)
I've introduced or re-designed partitioning, and need to migrate rows to the appropriate partitions without long lived row locks.
