douglasshuang

douglasshuang

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

@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)

Marked As Solved

andatki

andatki

Author of High Performance PostgreSQL for Rails

Hi @douglasshuang. Thank you for the detailed bug report. I was able to verify what you’re saying and agree that the last assignment within the loop should not add 1. I took that off as you suggested and the print output matches what you have.

I’m submitting a fix and hopefully it makes it into a future edition of the book. Thanks again for taking the time to notice this and prepare a detailed write-up!

Also Liked

douglasshuang

douglasshuang

My pleasure, Thank you!

Where Next?

Popular Pragmatic Bookshelf topics Top

belgoros
Following the steps described in Chapter 6 of the book, I’m stuck with running the migration as described on page 84: bundle exec sequel...
New
brianokken
Many tasks_proj/tests directories exist in chapters 2, 3, 5 that have tests that use the custom markers smoke and get, which are not decl...
New
edruder
I thought that there might be interest in using the book with Rails 6.1 and Ruby 2.7.2. I’ll note what I needed to do differently here. ...
New
raul
Hi Travis! Thank you for the cool book! :slight_smile: I made a list of issues and thought I could post them chapter by chapter. I’m rev...
New
AleksandrKudashkin
On the page xv there is an instruction to run bin/setup from the main folder. I downloaded the source code today (12/03/21) and can’t see...
New
New
AndyDavis3416
@noelrappin Running the webpack dev server, I receive the following warning: ERROR in tsconfig.json TS18003: No inputs were found in c...
New
jonmac
The allprojects block listed on page 245 produces the following error when syncing gradle: “org.gradle.api.GradleScriptException: A prob...
New
creminology
Skimming ahead, much of the following is explained in Chapter 3, but new readers (like me!) will hit a roadblock in Chapter 2 with their ...
New
mert
AWDWR 7, page 152, page 153: Hello everyone, I’m a little bit lost on the hotwire part. I didn’t fully understand it. On page 152 @rub...
New

Other popular topics Top

New
PragmaticBookshelf
Free and open source software is the default choice for the technologies that run our world, and it’s built and maintained by people like...
New
AstonJ
Or looking forward to? :nerd_face:
498 13326 269
New
brentjanderson
Bought the Moonlander mechanical keyboard. Cherry Brown MX switches. Arms and wrists have been hurting enough that it’s time I did someth...
New
AstonJ
We have a thread about the keyboards we have, but what about nice keyboards we come across that we want? If you have seen any that look n...
New
mafinar
This is going to be a long an frequently posted thread. While talking to a friend of mine who has taken data structure and algorithm cou...
New
sir.laksmana_wenk
I’m able to do the “artistic” part of game-development; character designing/modeling, music, environment modeling, etc. However, I don’t...
New
PragmaticBookshelf
Get the comprehensive, insider information you need for Rails 8 with the new edition of this award-winning classic. Sam Ruby @rubys ...
New
AnfaengerAlex
Hello, I’m a beginner in Android development and I’m facing an issue with my project setup. In my build.gradle.kts file, I have the foll...
New
PragmaticBookshelf
Use advanced functional programming principles, practical Domain-Driven Design techniques, and production-ready Elixir code to build scal...
New

Sub Categories: