Thread: Problems handling errors in PL/pgSQL
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
> 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: > I'm not sure if this is what you're looking for, but in 7.1 you can do something like: INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval); GET DIAGNOSTICS rows = ROW_COUNT; -- do something based onrows -- See "24.2.5.4. Obtaining other results status" at http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/plpgsql-des cription.html. Hope this helps, Joe
Joe, > I'm not sure if this is what you're looking for, but in 7.1 you can > do > something like: > > INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval); > GET DIAGNOSTICS rows = ROW_COUNT; > -- do something based on rows -- There's several other ways I can check, as well. However, I was hoping for some way to avoid adding anything to a couple of dozen functions which are already debugged. -Josh P.S. Does anyone yet have full documentation on GET DIAGNOSICS? Roberto? ______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
"Josh Berkus" <josh@agliodbs.com> writes:
> 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.
This would clearly be a bug, but I cannot replicate the problem:
regression=# SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');
ERROR: <unnamed> referential integrity violation - key referenced from order_details not found in orders
regression=#
regards, tom lane
"Josh Berkus" <josh@agliodbs.com> writes:
> Always good to have you folks test something. This does appear to be a
> bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres. When I run it
> through command-line PSQL, an error is returned; for some reason, KPSQL
> returns the return value for the function and no error.
But how would KPSQL know what value the function might have returned?
Something fishy here ... did you check the postmaster log to see whether
an error is really being reported or not?
regards, tom lane
Tom,
> This would clearly be a bug, but I cannot replicate the problem:
>
> regression=# SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE,
> TRUE, FALSE}');
> ERROR: <unnamed> referential integrity violation - key referenced
> from order_details not found in orders
> regression=#
Always good to have you folks test something. This does appear to be a
bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres. When I run it
through command-line PSQL, an error is returned; for some reason, KPSQL
returns the return value for the function and no error.
Grazie!
-Josh Berkus
______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
Tom, > But how would KPSQL know what value the function might have returned? > Something fishy here ... did you check the postmaster log to see > whether > an error is really being reported or not? <sheepish grin> Actually, I haven't been able to get the postgresql log to work since I compiled RC2. I keep meaning to take it up with PGSQL Inc. but not getting around to it ... -Josh Berkus ______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