Thread: Trapping errors from pl/perl (trigger) functions
Hi, I have a pl/perl trigger function which can give an error, and I would like to catch it in a pl/pgsql function, but I can't seem to trap it. Is it possible to catch errors generated pl/perl functions in a BEGIN ... EXCEPTION WHEN ... END block? Or perhaps in some other way?
On Sat, Jun 30, 2007 at 10:30:32PM +0200, Wiebe Cazemier wrote: > I have a pl/perl trigger function which can give an error, and I would like to > catch it in a pl/pgsql function, but I can't seem to trap it. What have you tried and how did the outcome differ from your expectations? > Is it possible to catch errors generated pl/perl functions in a BEGIN ... > EXCEPTION WHEN ... END block? Or perhaps in some other way? You could use "WHEN internal_error" or "WHEN others". If that doesn't work then please post a simple but complete example that shows what you're trying to do. -- Michael Fuhr
On Sunday 01 July 2007 21:16, Michael Fuhr wrote: > What have you tried and how did the outcome differ from your > expectations? The pl/perl trigger function in question generates an exception by elog(ERROR, "message"). I also tried die(), which didn't make a difference. When I do something on the table which the trigger function prevents, I get a message saying ERROR: blablabla. When such an error is generated by a pl/pgsql trigger function, I can trap the error with WHEN raise_exception. This does not work for the exception generated by the pl/perl function. > You could use "WHEN internal_error" or "WHEN others". If that > doesn't work then please post a simple but complete example that > shows what you're trying to do. Trapping "others" works, even though I think it's kind of klunky. An example: create table test_table ( field integer ); create function test_function() returns trigger as $$ elog(ERROR, "message"); return; $$ LANGUAGE plperl; create trigger test_trigger before insert on test_table for each row execute_procedure test_function(); create function perform_actions() RETURNS VOID as $$ BEGIN BEGIN insert into test_table (field) values (1); EXCEPTION WHEN raise_exception THEN NULL; END; END: $$ language plpgsql; select perform_actions(); The exception generated by the plperl function is not trapped by "WHEN raise_exception", but it is by "WHEN others". Is it a bug that postgres doesn't see pl/perl's error as an exception, or is there a good reason for it?
Wiebe Cazemier <halfgaar@gmx.net> writes: > When I do something on the table which the trigger function prevents, I get a > message saying ERROR: blablabla. When such an error is generated by a pl/pgsql > trigger function, I can trap the error with WHEN raise_exception. This does > not work for the exception generated by the pl/perl function. Why would you expect it to? The raise_exception SQLSTATE applies specifically and solely to the plpgsql RAISE command. The entire point of those identifiers is to match fairly narrow classes of exceptions, not anything thrown by anyone. IMHO the real problem with both RAISE and the plperl elog command is there's no way to specify which SQLSTATE to throw. In the case of the elog command I think you just get a default. regards, tom lane
On Sun, Jul 01, 2007 at 03:50:09PM -0400, Tom Lane wrote: > IMHO the real problem with both RAISE and the plperl elog command > is there's no way to specify which SQLSTATE to throw. In the case > of the elog command I think you just get a default. That default is XX000 (internal_error): test=> create function foo() test-> returns void test-> language plperl test-> as $_$ test$> elog(ERROR, 'test error'); test$> $_$; CREATE FUNCTION test=> \set VERBOSITY verbose test=> select foo(); ERROR: XX000: error from Perl function: test error at line 2. LOCATION: plperl_call_perl_func, plperl.c:1076 The code around plperl.c:1076 is /* XXX need to find a way to assign an errcode here */ ereport(ERROR, (errmsg("error from Perl function: %s", strip_trailing_ws(SvPV(ERRSV, PL_na))))); I don't see any relevant TODO items. Would something like the following be appropriate? * Allow RAISE and its analogues to set SQLSTATE. -- Michael Fuhr
On Sunday 01 July 2007 21:50, Tom Lane wrote: > Why would you expect it to? The raise_exception SQLSTATE applies > specifically and solely to the plpgsql RAISE command. The entire > point of those identifiers is to match fairly narrow classes of > exceptions, not anything thrown by anyone. > > IMHO the real problem with both RAISE and the plperl elog command > is there's no way to specify which SQLSTATE to throw. In the case > of the elog command I think you just get a default. I expected it to, because I told elog what kind of errorlevel to give me, but apparently that does not influence the SQLSTATE. I didn't know it didn't apply to procedures in other languages.