Trapping errors from pl/perl (trigger) functions

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.