From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Problems handling errors in PL/pgSQL
Date: Mon, 23 Apr 2001 12:48:57 -0700
Message-ID: <web-43127@davinci.ethosmedia.com> (raw)
Folks,
I've run up against a problematic limitation of PL/pgSQL's
error-handling ability which could force me to re-write about 25 custom
functions. I'm hoping that you folks can show me a way around the
situation.
THE PROBLEM:
PL/pgSQL handles errors though "Implied Transactions", where the entire
function is a transaction and rolls back in the event that an error is
encountered. There is no way to declare a transaction within a PL/pgSQL
function, nor can one issue a ROLLBACK or COMMIT statement within a
function. As such, I have designed all of my data-modifiaction funcitons
to take advantage of this functionality, packaging all updates within a
single function.
However, not all types of errors are so trapped. The most problematic
un-trapped error is referential integrity: if an INSERT or UPDATE fails
because of a referential integrity violation, the PL/pgSQL function will
still see the statement as a success and not error out. Example:
Postgres ver. 7.1 RC2
CREATE TABLE "order_details" (
"order_detail_id" integer DEFAULT nextval('order_details_order_detail__seq'::text)
NOT NULL,
"order_usq" integer NOT NULL REFERENCES orders(usq),
"detail_id" integer NOT NULL,
"detail_req" boolean,
Constraint "order_details_pkey" Primary Key ("order_detail_id")
);
CREATE FUNCTION "fn_save_order_details" (integer,integer[],boolean[])
RETURNS integer AS '
DECLARE
v_order ALIAS for $1;
arr_details ALIAS for $2;
arr_req ALIAS for $3;
arr_loop INT2;
detail_no INT4;
detail_r BOOLEAN;
BEGIN
DELETE FROM order_details
WHERE order_usq = v_order;
arr_loop := 1;
WHILE arr_details[arr_loop] LOOP
detail_no := arr_details[arr_loop];
detail_r := COALESCE(arr_req[arr_loop], FALSE);
INSERT INTO order_details ( order_usq, detail_id, detail_req )
VALUES ( v_order, detail_no, detail_r );
arr_loop := arr_loop + 1;
END LOOP;
RETURN arr_loop - 1;
END;
' LANGUAGE 'plpgsql';
SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');
---------
3
... thus supposedly reporting success: 3 order_details were saved.
However, it turns out that order 7703 has been deleted. Thus, the three
INSERTS we ran on order_details failed due to lack of referential
integrity; no records were saved. Yet the function did not error.
THE QUESTIONS:
1. Based on the above, it seems I have to go back and add data
validation and RAISE ERROR statements to all of my functions that do
INSERTS or UPDATES to tables with referential integrity triggers. Is
there a way around this?
2. Is there a plan to fix this kind of deficiency in Postgres
function/procedure error handling?
Thanks so much for your suggestions,
-Josh
______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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Problems handling errors in PL/pgSQL
In-Reply-To: <web-43127@davinci.ethosmedia.com>
References: <web-43127@davinci.ethosmedia.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