8

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.

Improve this question
3
  • 1
    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 detailing a common workaround though. Commented Sep 3, 2014 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 :) Commented Sep 3, 2014 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. Commented Sep 3, 2014 at 20:21

2 Answers 2

Reset to default
10

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:

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
  LANGUAGE plpgsql 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$;

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 FUNCTION emp_stamp();
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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
If the exception occurs in the context of a psql script with ON_ERROR_STOP, would the fact that it is handled prevent psql execution from stopping?
@Rafs: Yes, the error is trapped and does not propagate out. psql never learns about it.
Thanks. I want the error to be raised for psql to stop, but I also want to log the error in the same table or another table. RAISE EXCEPTION simply rolls back the logging INSERT on the one hand, and RETURN NEW after RAISE EXCEPTION won't be reached on the other hand. I guess I have to detect the error in some other way...
@Rafs: You can log the error in the exception block. To make it stick, use a PROCEDURE instead of the function and COMMIT. Ask a new question with details if you get stuck.
1

You have two options actually.

  1. You can log into the postgresql log by using some level on raise Check the manual
  2. 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.
Improve this answer

2 Comments

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).
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.