
douglasshuang
High Performance PostgreSQL for Rails: Off-by-one bug in `SCRUB_BATCHES` procedure (page 65)
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
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!
1
Also Liked

douglasshuang
My pleasure, Thank you!
1
Popular Pragmatic Bookshelf topics

Python Testing With Pytest - Chapter 2, warnings for “unregistered custom marks”
While running the smoke tests in Chapter 2, I get these...
New

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

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

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

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

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

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

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

Book: Programming Phoenix LiveView, page 142 (157/378), file lib/pento_web/live/product_live/form_component.ex, in the function below:
d...
New

@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

Hello Devtalk World!
Please let us know a little about who you are and where you’re from :nerd_face:
New

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

Learn different ways of writing concurrent code in Elixir and increase your application's performance, without sacrificing scalability or...
New

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

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

Inside our android webview app, we are trying to paste the copied content from another app eg (notes) using navigator.clipboard.readtext ...
New

zig/http.zig at 7cf2cbb33ef34c1d211135f56d30fe23b6cacd42 · ziglang/zig.
General-purpose programming language and toolchain for maintaini...
New

A Brief Review of the Minisforum V3 AMD Tablet.
Update: I have created an awesome-minisforum-v3 GitHub repository to list information fo...
New

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
Categories:
Sub Categories:
Popular Portals
- /elixir
- /rust
- /wasm
- /ruby
- /erlang
- /phoenix
- /keyboards
- /rails
- /js
- /python
- /security
- /go
- /swift
- /vim
- /clojure
- /emacs
- /haskell
- /java
- /onivim
- /typescript
- /svelte
- /kotlin
- /crystal
- /c-plus-plus
- /tailwind
- /gleam
- /react
- /ocaml
- /flutter
- /elm
- /vscode
- /ash
- /html
- /opensuse
- /centos
- /php
- /deepseek
- /zig
- /scala
- /sublime-text
- /textmate
- /lisp
- /nixos
- /debian
- /react-native
- /agda
- /kubuntu
- /arch-linux
- /django
- /revery
- /ubuntu
- /manjaro
- /spring
- /nodejs
- /diversity
- /lua
- /julia
- /c
- /slackware
- /markdown