Problems handling errors in PL/pgSQL
От | Josh Berkus |
---|---|
Тема | Problems handling errors in PL/pgSQL |
Дата | |
Msg-id | web-43127@davinci.ethosmedia.com обсуждение исходный текст |
Ответы |
Re: Problems handling errors in PL/pgSQL
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-sql по дате отправления: