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

jimmykiang
This test is broken right out of the box… — FAIL: TestAgent (7.82s) agent_test.go:77: Error Trace: agent_test.go:77 agent_test.go:...
New
ianwillie
Hello Brian, I have some problems with running the code in your book. I like the style of the book very much and I have learnt a lot as...
New
curtosis
Running mix deps.get in the sensor_hub directory fails with the following error: ** (Mix) No SSH public keys found in ~/.ssh. An ssh aut...
New
jskubick
I’m running Android Studio “Arctic Fox” 2020.3.1 Patch 2, and I’m embarrassed to admit that I only made it to page 8 before running into ...
New
New
dsmith42
Hey there, I’m enjoying this book and have learned a few things alredayd. However, in Chapter 4 I believe we are meant to see the “>...
New
Henrai
Hi, I’m working on the Chapter 8 of the book. After I add add the point_offset, I’m still able to see acne: In the image above, I re...
New
EdBorn
Title: Agile Web Development with Rails 7: (page 70) I am running windows 11 pro with rails 7.0.3 and ruby 3.1.2p20 (2022-04-12 revision...
New
andreheijstek
After running /bin/setup, the first error was: The foreman' command exists in these Ruby versions: That was easy to fix: gem install fore...
New
SlowburnAZ
Getting an error when installing the dependencies at the start of this chapter: could not compile dependency :exla, "mix compile" failed...
New

Other popular topics Top

Devtalk
Hello Devtalk World! Please let us know a little about who you are and where you’re from :nerd_face:
New
ohm
Which, if any, games do you play? On what platform? I just bought (and completed) Minecraft Dungeons for my Nintendo Switch. Other than ...
New
Exadra37
I am thinking in building or buy a desktop computer for programing, both professionally and on my free time, and my choice of OS is Linux...
New
AstonJ
SpaceVim seems to be gaining in features and popularity and I just wondered how it compares with SpaceMacs in 2020 - anyone have any thou...
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
Exadra37
I am asking for any distro that only has the bare-bones to be able to get a shell in the server and then just install the packages as we ...
New
DevotionGeo
The V Programming Language Simple language for building maintainable programs V is already mentioned couple of times in the forum, but I...
New
PragmaticBookshelf
Create efficient, elegant software tests in pytest, Python's most powerful testing framework. Brian Okken @brianokken Edited by Kat...
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

Sub Categories: