Folks,
I seem to be experiencing a rather elusive bug in 7.2.3, and I wanted to talk
it over before submitting it to pgsql-bugs, to see if anyone else has
experienced anything similar.
1. I have a function (fn_save_order) which is a long and complex data
verification and dependant data updating procedure.
2. One of the pieces of dependant data that fn_save_order creates is 3 records
in order_spec. If fn_save_order is saving an existing order record, first
it deletes the 3 exisiting records in order_spec and replaces them with new
ones. Here's the relevant code:IF v_usq > 0 THEN -- Note: v_usq is > 0 for all existing records DELETE FROM
order_specWHERE order_usq = v_usq;END IF;WHILE numbers[spec_loop] is not null LOOP
v_offset := spec_loop - 1;
SELECT job_type INTO current_type FROM job_types WHERE in_use = TRUE ORDER BY job_type LIMIT 1 OFFSET
v_offset;
INSERT INTO order_spec ( job_type, order_usq, no_needed, start_time,
end_time, pay_rate, bill_rate ) VALUES ( current_type, new_usq, numbers[spec_loop], sttimes[spec_loop],
endtimes[spec_loop], pays[spec_loop], bills[spec_loop] ); spec_loop := spec_loop + 1;
END LOOP;
3. This all works fine, *unless* the 3 records being deleted are identical to
the 3 records replacing them aside from the table's SERIAL index. Then, for
some reason, Postgres does not seem to delete the exisiting records but
rather keeps both the old and the new records, doubling the number of
order_spec records to 6. I tailed the log, and the delete statement is
getting passed to the parser ... it just seems to be ignored.
4. Establishing a unique index on several of the data columns of order_spec
seems to have "cured" the problem, but I'm worried that it's an indication of
a more serious MVCC issue with deleting and inserting records within a
function.
Thoughts? Regrettably, the function and table spec is extremely dense; I'm
trying to set up a test case.
--
-Josh BerkusAglio Database SolutionsSan Francisco