Triggers Baby

Derek Sivers had a new post up on [http://www.onlamp.com/pub/wlg/6956 his O’Rielly blog] about a plpgsql trigger he is going to use to help keep track of inventory for [http://www.cdbaby.com CD Baby]. Couple things struck me as odd in his post. 1) The code path for “IF instock IS NULL” doesn’t seem right in this case. You should never get a null returned, because the SUM()’s will generate a result even if there is no matching rows based on item_id or warehouse_id. (Thats a “gotcha” of sorts with aggregates, in that they always return a row even if there are no matching rows). Now it looks like he has tried to account for this, by attempting to update the values and then checking to make sure something happened else inserting, but tossing in a comment that a value of 0 for instock could mean either no entry or just no stock left would make me feel better. 2) He used single quotes (‘) for quoting rather than dollars ($$). Could it be that he is still using 7.4? If you have any kind of choice at all upgrade to 8.0 asap. Even if your current production site is 7.4, tools like [http://slony.info Slony] make it easier than ever to upgrade without downtime. The reason why I really bring this up is because he is using the ole update then insert trick, which means we have the potential for two seperate transactions to both attempt to insert at the same time, which seems should violate some type of primary key (on item_id and warehouse_id, which he didn’t mention but I would bet exists). If he was using 8.0 he could put that INSERT into a [http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING TRY…CATCH] block and make things a little more robusto. Just couple of quick thoughts… also if you haven’t checked out CD BABY yet give it a swing, there are a number of [http://cdbaby.com/cd/fatpappy good] [http://cdbaby.com/cd/hotmonkey artists] over there and supporting alternative music outlets is always a good thing.