Tuesday, May 29, 2007

Small Post About Transactions or "Why, Postgres, Why??"

Anyone who knows RDBMSs knows the importance of transactions. It's often good to use them even if you're running a few administrative DML commands to fix a bug. In Oracle, you're pretty much forced to use them (unless you do something silly like run all queries from JDBC with autocommit enabled). Postgres has good transaction support, but you must explicitly "begin transaction" from psql (command line app).

Occasionally I run into this very frustrating issue. If you get an error, ANY error, Postgres automatically brings your transaction into an error state - not even rolled back - in which your transaction is dead, kaput.

So, today I was running a big delete followed by two big inserts. Beforehand I ran BEGIN TRANSACTION in case of boo-boos. After a while the inserts finished. Let's check out the results before committing.......

select * from securityid limit 100 order by random();
ERROR: syntax error at or near "order"
LINE 1: select * from securityid limit 100 order by random();

Whoopsie, I reversed the position of the limit and order by clauses. Let's fix that right up...

select * from securityid order by random() limit 100;
ERROR: current transaction is aborted, commands ignored until end of transaction block

NOOOOOOO!!! At this point nothing but rollback will work. Why, Postgres, why? It was just a typo - a syntax error! Sigh...