Logging in error states in Postgres transactions

Question

I'm currently working on rebuilding some SQL Server stored procedures in Postgres, but I have the following hangup

CREATE OR REPLACE PROCEDURE public.math_proc ()
LANGUAGE plpgsql
AS $$
BEGIN
    CREATE TEMP TABLE log_table(event TEXT) ON COMMIT DROP;
    INSERT INTO log_table VALUES ('Doing some addition');
    SELECT 1 + 0;
    INSERT INTO log_table VALUES ('Doing some multiplication');
    SELECT 1 * 0;
    INSERT INTO log_table VALUES ('Doing some division');
    SELECT 1 / 0;
    INSERT INTO log_table VALUES ('Done doing math!');
    SELECT json_agg(log_table);
EXCEPTION
    WHEN OTHERS THEN
        SELECT json_agg(log_table);
        ROLLBACK;
END;
$$;
CALL public.math_proc ();

When an error occurs in the procedure above, the transaction rollbacks prior to entering the exception block. This means that log_table is wiped, removing any evidence of the logged statements.

In SQL Server, the corresponding code would allow me to see the contents of log_table, prior to rolling back the error state.

How do I persist this info? Postgres doesn't have autonomous transactions, and I'm a bit uncomfortable using third party extensions like pg_background to get this functionality. Logging to a text file or using copy is hacky and slow. Using DB_Link is also rather slow, and also introduces a ton of overhead. Are there alternatives here?

Answer 1

Postgresql doesn't (currently) support sub-transactions (or autonomous transactions in Oracle-speak (Don't know what MS-SQL calls it)), so using something like pg_background or dblink appear to be the current best options.

For whatever it's worth, having recently been needing to solve this problem myself I came up with https://github.com/gsiems/pg-util_log (which can use either pg_background or dblink).