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

telemachus
Python Testing With Pytest - Chapter 2, warnings for “unregistered custom marks” While running the smoke tests in Chapter 2, I get these...
New
jesse050717
Title: Web Development with Clojure, Third Edition, pg 116 Hi - I just started chapter 5 and I am stuck on page 116 while trying to star...
New
jamis
The following is cross-posted from the original Ray Tracer Challenge forum, from a post by garfieldnate. I’m cross-posting it so that the...
New
HarryDeveloper
Hi @venkats, It has been mentioned in the description of ‘Supervisory Job’ title that 2 things as mentioned below result in the same eff...
New
nicoatridge
Hi, I have just acquired Michael Fazio’s “Kotlin and Android Development” to learn about game programming for Android. I have a game in p...
New
s2k
Hi all, currently I wonder how the Tailwind colours work (or don’t work). For example, in app/views/layouts/application.html.erb I have...
New
kolossal
Hi, I need some help, I’m new to rust and was learning through your book. but I got stuck at the last stage of distribution. Whenever I t...
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
jwandekoken
Book: Programming Phoenix LiveView, page 142 (157/378), file lib/pento_web/live/product_live/form_component.ex, in the function below: d...
New
dachristenson
@mfazio23 Android Studio will not accept anything I do when trying to use the Transformations class, as described on pp. 140-141. Googl...
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
AstonJ
I’ve been hearing quite a lot of comments relating to the sound of a keyboard, with one of the most desirable of these called ‘thock’, he...
New
PragmaticBookshelf
Learn different ways of writing concurrent code in Elixir and increase your application's performance, without sacrificing scalability or...
New
First poster: joeb
The File System Access API with Origin Private File System. WebKit supports new API that makes it possible for web apps to create, open,...
New
AstonJ
Was just curious to see if any were around, found this one: I got 51/100: Not sure if it was meant to buy I am sure at times the b...
New
New
husaindevelop
Inside our android webview app, we are trying to paste the copied content from another app eg (notes) using navigator.clipboard.readtext ...
New
First poster: bot
zig/http.zig at 7cf2cbb33ef34c1d211135f56d30fe23b6cacd42 · ziglang/zig. General-purpose programming language and toolchain for maintaini...
New
CommunityNews
A Brief Review of the Minisforum V3 AMD Tablet. Update: I have created an awesome-minisforum-v3 GitHub repository to list information fo...
New
AstonJ
If you’re getting errors like this: psql: error: connection to server on socket “/tmp/.s.PGSQL.5432” failed: No such file or directory ...
New

Sub Categories: