Perform action before exception call in Postgres trigger function

Question

Postgres 8.4 here. Imagine this code snippet from Postgres doc:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;
    IF NEW.salary IS NULL THEN
        RAISE EXCEPTION '% cannot have null salary', NEW.empname;
    END IF;

    -- Who works for us when she must pay for it?
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
    END IF;

    -- Remember who changed the payroll when
    NEW.last_date := current_timestamp;
    NEW.last_user := current_user;
    RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

If we want to do something like logging in a custom table these exceptions:

-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
    INSERT INTO my_log_table ('User didn't supplied empname')
    RAISE EXCEPTION 'empname cannot be null';
END IF;

It won't work because anything we put before a RAISE EXCEPTION call is undone by the rollback RAISE EXCEPTION implies, i.e. the my_log_table row we create will be deleted as soon as RAISE EXCEPTION is called.

What's is the best way to accomplish something like this? Maybe catching our custom exception?

Turning off rollback @ TRIGGER is not an option, I need it.

What you'd really want is a subtransaction (something roughly equivalent to Oracle's `pragma autonomous`. Unfortunately, this is still just a proposal, and isn't implemented yet (Postgres 9.3). You could check out this [post](http://raghavt.blogspot.com.au/2012/05/autonomous-transaction-in-postgresql-91.html) detailing a common workaround though. — Mureinik, Sep 03 '14 at 19:30
This could work, although I'm feeling in this particular situation It'll be like using a sledgehammer to crack a nut :) — jpp1jpp1, Sep 03 '14 at 20:11
When you add a long quote from *any* source, please also add a link to the source. I added the link to the Postgres 8.4 manual. — Erwin Brandstetter, Sep 03 '14 at 20:21

score 8 · Accepted Answer · edited May 23 '17 at 10:33

You can trap errors / catch exceptions.

In the EXCEPTION block you can do anything else, like INSERT into another table. Afterwards you could re-raise the exception to propagate out, but that would roll back the whole transaction including the INSERT to the log table (unless the exception is wrapped and caught in an outer function).

You could:

use tricks like a dblink call to emulate an autonomous transaction, which isn't undone, when the wrapping transaction is rolled back. Related:
RAISE a NOTICE or WARNING additionally, which also isn't undone by ROOLBACK.
RAISE a different EXCEPTION with your own text.

Alternatively, you can just cancel the row that triggered the trigger function and not raise an exception. Everything else in the transaction goes through normally.

Assuming this is a trigger ON UPDATE and you have another table with identical structure to write failed INSERTs to:

CREATE OR REPLACE FUNCTION emp_stamp()
  RETURNS trigger AS
$func$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
         RAISE EXCEPTION 'empname cannot be null';
    END IF;

    IF ...

    RETURN NEW;    -- regular end

EXCEPTION WHEN others THEN  -- or be more specific
    INSERT INTO log_tbl VALUES (NEW.*); -- identical table structure
    RETURN NULL;   -- cancel row
END
$func$ LANGUAGE plpgsql;

Note that NEW contains the state of the row right before the exception occurred, including previous successful statements in the same function.

Trigger:

CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

I'll end up using something like this, I lose the exception which got catched and showed at app level, but at least the plpgsql function that fires the trigger can return a false value to the app (as it detects no rows where updated -FOUND-) I found other more complicated solutions don't worth the trouble — jpp1jpp1, Sep 04 '14 at 08:38

score 1 · Answer 2 · answered Sep 03 '14 at 19:52

1

You have two options actually.

You can log into the postgresql log by using some level on raise Check the manual
Log the error on your application, not in Db transaction. For example, negative salary should not be checked in trigger function like this. Or the exception should be handled on the insert invocations.

answered Sep 03 '14 at 19:52

Aret

475
3
9

1. In fact what I'm trying to preserve is not a log_add (it was only an example), but something different (an insertion in another table). – jpp1jpp1 Sep 03 '14 at 20:06
2. I'm aware of that, but doing it at application level would be slower as I'd have to check the conditions with more DB queries. I was just wondering if postgresql had a way to do this within TRIGGERS – jpp1jpp1 Sep 03 '14 at 20:08

Perform action before exception call in Postgres trigger function

2 Answers2

Linked

Related