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!

Where Next?

Popular Pragmatic Bookshelf topics Top

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
Alexandr
Hi everyone! There is an error on the page 71 in the book “Programming machine learning from coding to depp learning” P. Perrotta. You c...
New
simonpeter
When I try the command to create a pair of migration files I get an error. user=> (create-migration "guestbook") Execution error (Ill...
New
leba0495
Hello! Thanks for the great book. I was attempting the Trie (chap 17) exercises and for number 4 the solution provided for the autocorre...
New
adamwoolhether
I’m not quite sure what’s going on here, but I’m unable to have to containers successfully complete the Readiness/Liveness checks. I’m im...
New
New
adamwoolhether
Is there any place where we can discuss the solutions to some of the exercises? I can figure most of them out, but am having trouble with...
New
hazardco
On page 78 the following code appears: <%= link_to ‘Destroy’, product, class: ‘hover:underline’, method: :delete, data: { confirm...
New
tkhobbes
After some hassle, I was able to finally run bin/setup, now I have started the rails server but I get this error message right when I vis...
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

AstonJ
A thread that every forum needs! Simply post a link to a track on YouTube (or SoundCloud or Vimeo amongst others!) on a separate line an...
New
dasdom
No chair. I have a standing desk. This post was split into a dedicated thread from our thread about chairs :slight_smile:
New
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
PragmaticBookshelf
Learn different ways of writing concurrent code in Elixir and increase your application's performance, without sacrificing scalability or...
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
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
husaindevelop
Inside our android webview app, we are trying to paste the copied content from another app eg (notes) using navigator.clipboard.readtext ...
New
AstonJ
Curious what kind of results others are getting, I think actually prefer the 7B model to the 32B model, not only is it faster but the qua...
New
Fl4m3Ph03n1x
Background Lately I am in a quest to find a good quality TTS ai generation tool to run locally in order to create audio for some videos I...
New

Sub Categories: