Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: Delete/Replace Bug in Functions?
Date: Mon, 21 Oct 2002 15:31:19 -0700
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210211531.19436.josh@agliodbs.com>

Folks,

I seem to be experiencing a rather elusive bug in 7.2.3, and I wanted to ta=
lk=20
it over before submitting it to pgsql-bugs, to see if anyone else has=20
experienced anything similar.

1. I have  a function (fn_save_order) which is a long and complex data=20
verification and dependant data updating procedure.

2. One of the pieces of dependant data that fn_save_order creates is 3 reco=
rds=20
in order_spec.   If fn_save_order is saving an existing order record, first=
=20
it deletes the 3 exisiting records in order_spec and replaces them with new=
=20
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 =3D v_usq;
	END IF;
	WHILE numbers[spec_loop] is not null LOOP

		v_offset :=3D spec_loop - 1;

		SELECT job_type INTO current_type
		FROM job_types
		WHERE in_use =3D TRUE
		ORDER BY job_type LIMIT 1 OFFSET v_offset;

		INSERT INTO order_spec ( job_type, order_usq, no_needed, start_time,=20
end_time, pay_rate, bill_rate )
		VALUES ( current_type, new_usq, numbers[spec_loop], sttimes[spec_loop],=
=20
endtimes[spec_loop],
			pays[spec_loop], bills[spec_loop] );
		spec_loop :=3D spec_loop + 1;

	END LOOP;

3. This all works fine, *unless* the 3 records being deleted are identical =
to=20
the 3 records replacing them aside from the table's SERIAL index.  Then, fo=
r=20
some reason, Postgres does not seem to delete the exisiting records but=20
rather keeps both the old and the new records, doubling the number of=20
order_spec records to 6.   I tailed the log, and the delete statement is=20
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=
=20
seems to have "cured" the problem, but I'm worried that it's an indication =
of=20
a more serious MVCC issue with deleting and inserting records within a=20
function.

Thoughts?   Regrettably, the function and table spec is extremely dense; I'=
m=20
trying to set up a test case.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco