PGSQL Trigger Function Write Exception to Log Table

Question

Background

I'm new to PostgreSQL and I'm having some issues with this trigger function, which I've obviously simplified considerably below. I could ask to help fix the query, but I think I can handle that, and what I'm more concerned about is that I have a lot of functions like this and I need a way to be able to have visibility into why it's failing, and which ones are failing.

Question

How can I catch exceptions that happen within this function and write them to some kind of log table so I can review and fix each one? Ideally I'd like to write the sql statement that failed to the log table as well so I can see specifically what went wrong. I've seen a few examples of similar things, but they don't seem to fit my scenario.

CREATE OR REPLACE FUNCTION my_func() RETURNS TRIGGER AS $$
   BEGIN
        IF (TG_OP = 'INSERT') THEN

            INSERT INTO my_table(...)
            SELECT ...
            FROM table_1 t1
            JOIN table_2 t2 ON t1.id = t2.id               
            ON CONFLICT (id)
            DO UPDATE
            field1 = EXCLUDED.field1;

        ELSIF(TG_OP = 'UPDATE') THEN

            UPDATE my_table 
            SET ...
            FROM table_1 t1
            JOIN table_2 t2 ON t1.id = t2.id
            WHERE id = NEW.id;

         ELSIF (TG_OP = 'DELETE') THEN
            DELETE FROM my_table WHERE id= OLD.id;
        END IF;
      RETURN NULL;
   END;
$$ LANGUAGE plpgsql;

1. After catching the exception and logging it, do you want to rethrow? 2. What version of PG? — 404
– 404, Commented Nov 27, 2018 at 16:42
You cannot get the triggering statement, but you can catch an exception in a BEGIN ... EXCEPTION ... END block. — Laurenz Albe
– Laurenz Albe, Commented Nov 27, 2018 at 16:45
It's versison 10.5. I think I want it to just fail silently and log the result and I can monitor and fix issues as they arise. It's currently failing silently, but I have no visibility, which is the problem. — hyphen
– hyphen, Commented Nov 27, 2018 at 16:46
@LaurenzAlbe What about the actual sql within the trigger? Like, if it failed on an update, could I get the statement that executed within the update part of my trigger? One thing that makes it hard to triage is I have to replace NEW.myfield with actual values...there must be a better way, but the way I'm doing it is cumbersome. — hyphen
– hyphen, Commented Nov 27, 2018 at 16:49
You cannot get the failing query. What you can get is information about the error and trigger related information. — Laurenz Albe
– Laurenz Albe, Commented Nov 27, 2018 at 16:56

404 · Accepted Answer · 2018-11-27 18:13:28Z

Here's an example, using a normal function rather than a trigger, though it's really the same thing in as far as how to log:

Table to store errors:

CREATE TABLE errors (id SERIAL, sql_state TEXT, message TEXT, detail TEXT, hint TEXT, context TEXT);

Function which does work and has the exception handling/logging:

CREATE OR REPLACE FUNCTION my_func()
    RETURNS VOID AS
$BODY$
DECLARE
    _sql_state TEXT;
    _message TEXT;
    _detail TEXT;
    _hint TEXT;
    _context TEXT;
BEGIN
    PERFORM 1 / 0;
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            _sql_state := RETURNED_SQLSTATE,
            _message := MESSAGE_TEXT,
            _detail := PG_EXCEPTION_DETAIL,
            _hint := PG_EXCEPTION_HINT,
            _context := PG_EXCEPTION_CONTEXT;

        INSERT INTO errors (sql_state, message, detail, hint, context)
        VALUES (_sql_state, _message, _detail, _hint, _context);
END
$BODY$
    LANGUAGE plpgsql;

After calling the function, the errors table contains:

See https://rextester.com/BQPG27732

Context shows a call stack of sorts. You could add more error-related fields of course, I only chose a handful of those available in GET STACKED DIAGNOSTICS

Collectives™ on Stack Overflow

PGSQL Trigger Function Write Exception to Log Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related