Re: Autocommit, isolation level, and vacuum behavior

On 2008-09-10 16:46, Jack Orenstein wrote:

> Application requirement. We need to do something for each row retrieved from BIG
> and the something is expensive. We do the scan slowly (30 second sleep inside
> the loop) to amortize the cost.

Then do the processing in separate transactions like this (in pseudocode):

$last_id = -1;
do {
begin transaction;
$result = select * from bigtable
where id>$last_id
and processed=false
order by id limit 1;
if ( empty($result) ) {
rollback;
break;
}
do_something_expensive_with($result[0]);
update bigtable set processed=true where id=$result[0][id];
commit;
sleep 30;
} while (true);

Always avoid long running transactions. This is recommended for any
transactional database.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh