3 ms·

Author here. That's a great point bhahn, I just updated my gist to properly handle the case that you just outlined: https://gist.github.com/aanari/349c7d97ed50

by aanari 12y ago

Author here. That's a great point bhahn, I just updated my gist to properly handle the case that you just outlined: https://gist.github.com/aanari/349c7d97ed50c6f69930#file-batch_at_will_commander-sql https://gist.github.com/aanari/349c7d97ed50c6f69930#file-bat... By creating a separate function for the locking and updating of rows, we ensure that the `BEGIN/END` transaction is handled per iteration rather than at the very end, so we only lock rows while they are being processed. Since Postgres does not support nested transaction blocks, calling a defined function from within an anonymous function block seemed to be the easiest and clearest path to achieve this.

bhahn 12y ago
https://gist.github.com/aanari/349c7d97ed50c6f69930#file-batch_at_will_commander-sql-L22-L28 https://gist.github.com/aanari/349c7d97ed50c6f69930#file-bat... The anonymous block is implicitly called in a transaction, so all the calls to batch_at_will will be executed in the parent transaction; there's no way around this (except for using db_link but that's pretty smelly imo). The only way to batch update while only locking rows in the batch is to run the loop outside of postgres like OP suggested.