Problems handling errors in PL/pgSQL - Mailing list pgsql-sql

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 '
DECLAREv_order ALIAS for $1;arr_details ALIAS for $2;arr_req ALIAS for $3;arr_loop INT2;detail_no INT4;detail_r
BOOLEAN;
BEGINDELETE FROM order_detailsWHERE 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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco