0

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?

1 Answer 1

0

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.