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
Running the examples in chapter 5 c under pytest 5.4.1 causes an AttributeError: ‘module’ object has no attribute ‘config’.
In particula...
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
When I try the command to create a pair of migration files I get an error.
user=> (create-migration "guestbook")
Execution error (Ill...
New
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
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
“The ProductLive.Index template calls a helper function, live_component/3, that in turn calls on the modal component. ”
Excerpt From: Br...
New
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
On page 78 the following code appears:
<%= link_to ‘Destroy’, product,
class: ‘hover:underline’,
method: :delete,
data: { confirm...
New
The markup used to display the uploaded image results in a Phoenix.LiveView.HTMLTokenizer.ParseError error.
lib/pento_web/live/product_l...
New
I am using Android Studio Chipmunk | 2021.2.1 Patch 2
Build #AI-212.5712.43.2112.8815526, built on July 10, 2022
Runtime version: 11.0....
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
Please tell us what is your preferred monitor setup for programming(not gaming) and why you have chosen it.
Does your monitor have eye p...
New
Small essay with thoughts on macOS vs. Linux:
I know @Exadra37 is just waiting around the corner to scream at me “I TOLD YOU SO!!!” but I...
New
Oh just spent so much time on this to discover now that RancherOS is in end of life but Rancher is refusing to mark the Github repo as su...
New
Create efficient, elegant software tests in pytest, Python's most powerful testing framework.
Brian Okken @brianokken
Edited by Kat...
New
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
I am trying to crate a game for the Nintendo switch, I wanted to use Java as I am comfortable with that programming language. Can you use...
New
New
Explore the power of Ash Framework by modeling and building the domain for a real-world web application.
Rebecca Le @sevenseacat and ...
New
Hair Salon Games for Girls Fun
Girls Hair Saloon game is mainly developed for kids. This game allows users to select virtual avatars to ...
New
Categories:
Sub Categories:
Popular Portals
- /elixir
- /rust
- /ruby
- /wasm
- /erlang
- /phoenix
- /keyboards
- /python
- /js
- /rails
- /security
- /go
- /swift
- /vim
- /clojure
- /emacs
- /java
- /haskell
- /svelte
- /onivim
- /typescript
- /kotlin
- /c-plus-plus
- /crystal
- /tailwind
- /react
- /gleam
- /ocaml
- /flutter
- /elm
- /vscode
- /ash
- /html
- /opensuse
- /zig
- /centos
- /deepseek
- /php
- /scala
- /lisp
- /react-native
- /sublime-text
- /textmate
- /nixos
- /debian
- /agda
- /django
- /kubuntu
- /deno
- /arch-linux
- /nodejs
- /ubuntu
- /revery
- /spring
- /manjaro
- /lua
- /diversity
- /markdown
- /julia
- /slackware








