My WTF of The Day
time to read 7 min | 1347 words

The following tidbit caused quite a bit of problem, and a loss of a day for two people. I'm working on legacy system right now, and part of this work is refactoring stuff without breaking the behavior. The process is quite big, but we managed quite fine until we suddenly started to get wildly different results. We traced is a dozen times, and eventually we narrowed it down to code that looked a bit like this (PL/SQL):

declare

cursor offline_sales_cursor as

      select product_id, customer_id, price

      from offline_sales

 

for sale in offline_sales_cursor

begin

      insert into sales(product_id, customer_id, price)

      {sale.product_id, sale.customer_id, sale.price);

 

      exception

      when DUP_VAL_ON_INDEX

            update sales

                  set price = sale.price

            where product_id = sale.product_id

                  and customer_id = sale.customer_id;

end;

end loop;

 

commit;

For the purpose of the discussion, sales has a primary key on product_id and customer_id (yes, it's bad design, but it's not a real db) and foreign keys to the products and customers tables. Can you guess what the problem was?

It turns out that we had an invalid customer id in the offline_sales table, which meant that this would run for a little bit, get a foreign key error and exit the loop and then commit part of the changes! When I refactored this bit, I changed it to two statements, one for updating and one for inserting, so the failure wasn't writing anything. I looked at the code several times, and only caught this by accident (the code base has commits spread all over the place). Urgh! Urgh! Urgh! [Gnashing of teeth].

Tweet Share Share 0 comments
Tags: