From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Delete/Replace Bug in Functions?
Date: Mon, 21 Oct 2002 15:31:19 -0700
Message-ID: <200210211531.19436.josh@agliodbs.com> (raw)
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_spec WHERE 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 Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Delete/Replace Bug in Functions?
In-Reply-To: <200210211531.19436.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox