Say you are writing some Python code, code that used to talk to MySQL as a backend. Say you are using autocommit, because you believe transactions are for academic weenies.

Now say you want to execute a set of updates to the database. And you don’t know which ones that have been executed before. In Python/MySQL land, you just execute each one in turn with a simple try/catch, so you can continue past failures such as duplicate keys, etc.

Now, suppose that you convert to Postgres as your backend. Now, you catch the exception, and continue on. The very next statement that you execute gives you this:

ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

Now what do you do? Well, Google tells me the problem is not unique to Python, and sent some poor Ruby-on-Rails dev to the madhouse. He sent himself there because this error comes on the SQL statement AFTER the errored statement. I tried just close()ing the cursor, and creating a new one. No dice. Hmm….

Well, the solution for those dying to know is to abort() at the connection level, and then get yourself a new cursor() to continue on. Apparently this is a Postgres thing, not a driver or a Python thing, and I must say that it is brain dead STUPID. There, I said it. Why in the name of all that is good and holy am I required to hand rollback() an autocommit transaction? That has to be one of the stupidest things I have ever heard of.

Do I need some sort of magic ‘autorollback’ transaction? Pixie dust? What? I always assumed (perhaps totally incorrectly, but almost every other database on the planet is on my side here) that autocommit meant each statement was totally separate, and NOT dependent upon the success/failure state of the previous? Am I wrong?

Anyhoo, if you are using Postgres and autocommit, remember to rollback on any exception, as silly as it sounds, it does indeed work. This concludes this evenings rant.

Popularity: 31%