High Performance PostgreSQL for Rails: Off-by-one bug in `SCRUB_BATCHES` procedure (page 65)

Pragmatic Bookshelf PragProg Customers
,
1

@andatki

The SCRUB_BATCHES procedure has an off-by-one bug that causes it to skip one row after each batch. Here is output showing the incorrect behavior:

owner@localhost:5432 rideshare_development# CALL SCRUB_BATCHES();
NOTICE:  current_id: 1 - Number of rows updated: 1000
NOTICE:  current_id: 1002 - Number of rows updated: 1000
NOTICE:  current_id: 2003 - Number of rows updated: 1000
NOTICE:  current_id: 3004 - Number of rows updated: 1000
NOTICE:  current_id: 4005 - Number of rows updated: 1000
NOTICE:  current_id: 5006 - Number of rows updated: 1000
NOTICE:  current_id: 6007 - Number of rows updated: 1000
NOTICE:  current_id: 7008 - Number of rows updated: 1000
NOTICE:  current_id: 8009 - Number of rows updated: 1000
NOTICE:  current_id: 9010 - Number of rows updated: 1000
NOTICE:  current_id: 10011 - Number of rows updated: 1000
NOTICE:  current_id: 11012 - Number of rows updated: 1000
NOTICE:  current_id: 12013 - Number of rows updated: 1000
NOTICE:  current_id: 13014 - Number of rows updated: 1000
NOTICE:  current_id: 14015 - Number of rows updated: 1000
NOTICE:  current_id: 15016 - Number of rows updated: 1000
NOTICE:  current_id: 16017 - Number of rows updated: 1000
NOTICE:  current_id: 17018 - Number of rows updated: 1000
NOTICE:  current_id: 18019 - Number of rows updated: 1000
NOTICE:  current_id: 19020 - Number of rows updated: 1000
NOTICE:  current_id: 20021 - Number of rows updated: 190
CALL
owner@localhost:5432 rideshare_development# SELECT id, email FROM users WHERE id IN (1000, 1001, 1002);
  id  |                  email
------+-----------------------------------------
 1000 | 6ddd3da1abbacd649a2853d9@email.com
 1001 | Jenee-Robel-driver-1000@email.com
 1002 | efa11a15129ea1bed93f96@email.com
(3 rows)

The bug is in this assignment statement at the end of the loop body:

current_id := current_id + batch_size + 1;

It should read as follows:

current_id := current_id + batch_size;

Here is the output after the correction:

owner@localhost:5432 rideshare_development# CALL SCRUB_BATCHES();
NOTICE:  current_id: 1 - Number of rows updated: 1000
NOTICE:  current_id: 1001 - Number of rows updated: 1000
NOTICE:  current_id: 2001 - Number of rows updated: 1000
NOTICE:  current_id: 3001 - Number of rows updated: 1000
NOTICE:  current_id: 4001 - Number of rows updated: 1000
NOTICE:  current_id: 5001 - Number of rows updated: 1000
NOTICE:  current_id: 6001 - Number of rows updated: 1000
NOTICE:  current_id: 7001 - Number of rows updated: 1000
NOTICE:  current_id: 8001 - Number of rows updated: 1000
NOTICE:  current_id: 9001 - Number of rows updated: 1000
NOTICE:  current_id: 10001 - Number of rows updated: 1000
NOTICE:  current_id: 11001 - Number of rows updated: 1000
NOTICE:  current_id: 12001 - Number of rows updated: 1000
NOTICE:  current_id: 13001 - Number of rows updated: 1000
NOTICE:  current_id: 14001 - Number of rows updated: 1000
NOTICE:  current_id: 15001 - Number of rows updated: 1000
NOTICE:  current_id: 16001 - Number of rows updated: 1000
NOTICE:  current_id: 17001 - Number of rows updated: 1000
NOTICE:  current_id: 18001 - Number of rows updated: 1000
NOTICE:  current_id: 19001 - Number of rows updated: 1000
NOTICE:  current_id: 20001 - Number of rows updated: 210
CALL
owner@localhost:5432 rideshare_development# SELECT id, email FROM users WHERE id IN (1000, 1001, 1002);
  id  |                  email
------+-----------------------------------------
 1000 | 4b44cfd50568b6a9c54@email.com
 1001 | fc7266ea448226ed25ccdc7f1@email.com
 1002 | 6fdbe4620e31b4464b1b5fd95c64c@email.com
(3 rows)
1 Like