Spyced

I wrote a relatively simple multithreaded script to automate some cleanup work in my database. I used psycopg1, because it was conveniently packaged for the version of debian the server had. (And also because psycopg2's bundled pooling mechanism kind of sucks.) My script ran for a couple minutes, and segfaulted. I upgraded to the latest version of psycopg1, to no avail. You'd think that after 20+ "stable" releases this wouldn't be a problem anymore. Sigh. I ran it in gdb to see where it was segfaulting, and sure enough psycopg was dereferencing a null pointer. Unfortunately it was far from obvious how to fix the problem, at least to someone unfamiliar with the code. I bit the bullet and upgraded to psycopg2, which apparently got its first non-beta release earlier this month. For less-sucky pooling I used sqlalchemy's pool module. No more segfaults.

Greg Mullane has an excellent post on updating unique columns . A simple problem, but one that can be troublesome in practice: [T]here is one circumstance when [unique constraints] can be a real pain: swapping the values of the rows. In other words, a transaction in which the column values start unique, and end unique, but may not be so in the middle. Read his article -- I wouldn't have thought of his "reversing the polarity" method. Clever! But my first thought when I read this was, "Aha, Greg missed one." Surely the easiest way is to simply create a deferrable constraint (where you can elect to have the constraint only checked at the end of the transaction, instead of at the end of each statement)! So I gave it a try: => CREATE TABLE foo ( i int CONSTRAINT foo_pk PRIMARY KEY DEFERRABLE ); ERROR: misplaced DEFERRABLE clause At first I thought this indicated a syntax error, but my syntax was correct. After some g...