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 [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
