One of many dumb things I did recently…

I know that many people are reluctant to admit they made some stupid mistakes (like accidentally dropping a table in production). Some of these mistakes are very visible to the rest of the world, like the infamous Amazon S3 rm *, but raise the hands those who never ever did anything like this?! That’s why I always try to share my “oops,” in hope that it will make many people feel better.

Now, here is my recent story.

I spent two workdays trying to figure out why my stored procedure failed on commit. It was failing with an error “can’t commit while a subtransaction is running.” I am looking at the code, and there is no subtransaction! And I was trying to figure it out for two days! The use case was a classic one for “why we need to commit inside a stored procedure: we have a loop for a hundred million rows, and we want to commit, say, every 100K or 0.5M.

I read and re-read all related PostgreSQL documentation, all mailing lists, and all blog posts I could find, and I still could not figure out how my example differs from other people’s examples which work.

Out of total desperation, I started to remove parts of the code, hoping that, at some point, I would remove something which prevented it from working.

Guess what? I am down to i:=i+1, and it still returns the same error!

And then, finally, I realized what the problem was! I had exception handling!!! Which implicitly creates a checkpoint!!! Which I theoretically knew but, for some reason, I didn’t associate it with a subtransaction.

Now I knew what was wrong, and I knew that the solution was to create a block inside the loop and put the exception handling there. Great.

Doing this, sitting and waiting for commit messages, and nothing happens. I know that the commit message should appear within 20 min, and I am already waiting for two hours. Since this is happening not in production but in my sandbox, I let it run… until it hits “out of disk space.”

Next thing I realized – when I was putting back into my code all of the meaningful pieces I removed, I accidentally deleted i:=i+1.

😂 😂 😂

2 Comments

Filed under Development and testing, SQL

2 responses to “One of many dumb things I did recently…

  1. bithead2k

    My very first C program (in Turbo C, no less) was called “deltree”. It was an attempt to delete files in multiple directories, which was something DOS 2.1 couldn’t do.
    While walking the directory tree, I forgot that DOS would return ‘.’ and ‘..’ as directory entries. My programs walked all the way back to the root and proceeded to delete random files all over the main drive! I caught it in about 5 seconds, but it was already too late. It deleted itself, the source code, random files in the Windows directory, half the C compiler….
    fdisk, format, reinstall.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s