The paged_operations plugin adds paged_update and paged_delete dataset methods. These behave similarly to the default update and delete dataset methods, except that the update or deletion is done in potentially multiple queries (by default, affecting 1000 rows per query). For a large table, this prevents the change from locking the table for a long period of time.
Because the point of this is to prevent locking tables for long periods of time, the separate queries are not contained in a transaction, which means if a later query fails, earlier queries will still be committed. You could prevent this by using a transaction manually, but that defeats the purpose of using these methods.
Examples:
Album.where{name <= 'M'}.paged_update(updated_at: Sequel::CURRENT_TIMESTAMP) # SELECT id FROM albums WHERE (name <= 'M') ORDER BY id LIMIT 1 OFFSET 1000 # UPDATE albums SET updated_at = CURRENT_TIMESTAMP WHERE ((name <= 'M') AND ("id" < 1002)) # SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 1002)) ORDER BY id LIMIT 1 OFFSET 1000 # UPDATE albums SET updated_at = CURRENT_TIMESTAMP WHERE ((name <= 'M') AND ("id" < 2002) AND (id >= 1002)) # ... # SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 10002)) ORDER BY id LIMIT 1 OFFSET 1000 # UPDATE albums SET updated_at = CURRENT_TIMESTAMP WHERE ((name <= 'M') AND (id >= 10002)) Album.where{name > 'M'}.paged_delete # SELECT id FROM albums WHERE (name > 'M') ORDER BY id LIMIT 1 OFFSET 1000 # DELETE FROM albums WHERE ((name > 'M') AND (id < 1002)) # SELECT id FROM albums WHERE (name > 'M') ORDER BY id LIMIT 1 OFFSET 1000 # DELETE FROM albums WHERE ((name > 'M') AND (id < 2002)) # ... # SELECT id FROM albums WHERE (name > 'M') ORDER BY id LIMIT 1 OFFSET 1000 # DELETE FROM albums WHERE (name > 'M')
The plugin also adds a paged_datasets method that will yield separate datasets limited in size that in total handle all rows in the receiver:
Album.where{name > 'M'}.paged_datasets{|ds| puts ds.sql} # Runs: SELECT id FROM albums WHERE (name <= 'M') ORDER BY id LIMIT 1 OFFSET 1000 # Prints: SELECT * FROM albums WHERE ((name <= 'M') AND ("id" < 1002)) # Runs: SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 1002)) ORDER BY id LIMIT 1 OFFSET 1000 # Prints: SELECT * FROM albums WHERE ((name <= 'M') AND ("id" < 2002) AND (id >= 1002)) # ... # Runs: SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 10002)) ORDER BY id LIMIT 1 OFFSET 1000 # Prints: SELECT * FROM albums WHERE ((name <= 'M') AND (id >= 10002))
To set the number of rows per page, pass a :rows_per_page option:
Album.where{name <= 'M'}.paged_update({x: Sequel[:x] + 1}, rows_per_page: 4) # SELECT id FROM albums WHERE (name <= 'M') ORDER BY id LIMIT 1 OFFSET 4 # UPDATE albums SET x = x + 1 WHERE ((name <= 'M') AND ("id" < 5)) # SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 5)) ORDER BY id LIMIT 1 OFFSET 4 # UPDATE albums SET x = x + 1 WHERE ((name <= 'M') AND ("id" < 9) AND (id >= 5)) # ... # SELECT id FROM albums WHERE ((name <= 'M') AND (id >= 12345)) ORDER BY id LIMIT 1 OFFSET 4 # UPDATE albums SET x = x + 1 WHERE ((name <= 'M') AND (id >= 12345))
You should avoid using paged_update or paged_datasets with updates that modify the primary key, as such usage is not supported by this plugin.
This plugin only supports models with scalar primary keys.
Usage:
# Make all model subclasses support paged update/delete/datasets # (called before loading subclasses) Sequel::Model.plugin :paged_operations # Make the Album class support paged update/delete/datasets Album.plugin :paged_operations