Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3NJn0356681 for ; Mon, 23 Apr 2001 15:49:00 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 43127 for ; Mon, 23 Apr 2001 12:48:57 -0700 From: "Josh Berkus" Subject: Problems handling errors in PL/pgSQL To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 23 Apr 2001 12:48:57 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/357 X-Sequence-Number: 2715 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