+972 52-548-6969
My WTF of The Day
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].
Comments
Comment preview