pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete/Replace Bug in Functions?
3+ messages / 2 participants
[nested] [flat]
* Delete/Replace Bug in Functions?
@ 2002-10-21 22:31 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2002-10-21 22:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete/Replace Bug in Functions?
@ 2002-10-21 22:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2002-10-21 22:44 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> 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;
> ...
> 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] );
Um, might v_usq be different from new_usq?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete/Replace Bug in Functions?
@ 2002-10-21 22:50 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-10-21 22:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom,
> > 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] );
>
> Um, might v_usq be different from new_usq?
No, actually; there's a declaration earlier that assigns them the same value
for existing records. Plus "new_usq" is the link to the orders record, so
if they were different I wouldn't ever see the extra records.
I just tried to set up a test case for this issue, based on much simpler
schema. Unfortunately, the bug is not reproduceable in this simple case.
Any suggestions on how to reproduce it without dumping you an enitre copy of
my *confidential* database design?
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-10-21 22:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-21 22:31 Delete/Replace Bug in Functions? Josh Berkus <josh@agliodbs.com>
2002-10-21 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-21 22:50 ` Josh Berkus <josh@agliodbs.com>
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