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?