PostgreSQL transaction rollback not functioning as expected during nested transactions
I'm converting an old project and Hey everyone, I'm running into an issue that's driving me crazy... I'm attempting to set up I tried several approaches but none seem to work. I'm experiencing an scenario where a nested transaction in PostgreSQL is not rolling back as expected. I have a function that performs multiple database operations within a transaction block. However, when I encounter an behavior in an inner transaction, it seems to only rollback the inner transaction and not the outer one. I am using PostgreSQL version 13.3 and here is a simplified version of my code: ```sql CREATE OR REPLACE FUNCTION perform_operations() RETURNS void AS $$ DECLARE result1 INTEGER; result2 INTEGER; begin BEGIN; -- Start outer transaction -- First operation INSERT INTO users (username) VALUES ('test_user'); result1 := 1; BEGIN; -- Start inner transaction -- Second operation that might unexpected result INSERT INTO orders (user_id, amount) VALUES (result1, 100); -- Simulate an behavior RAISE EXCEPTION 'Simulated behavior'; COMMIT; -- Inner transaction commit EXCEPTION WHEN OTHERS THEN ROLLBACK; -- Only rolls back inner END; COMMIT; -- Outer transaction commit END; $$ LANGUAGE plpgsql; ``` When I call `SELECT perform_operations();`, I expect both the order insert and user insert to be rolled back due to the simulated behavior. However, the user insert continues in the database, which is not the behavior I intended. I have also checked the database logs and there are no indications of issues there. I've tried using savepoints for better control over the transaction, but that doesn't seem to solve the question either. Adjusting the transaction isolation level didn't yield any different results. Is there a known way to enforce that an outer transaction will rollback regardless of inner transaction outcomes? Any insights would be greatly appreciated! Thanks in advance! For context: I'm using Sql on Windows. Any ideas what could be causing this? I'm working on a REST API that needs to handle this. Any suggestions would be helpful. The project is a REST API built with Sql. I'm developing on CentOS with Sql. Any ideas how to fix this? I'm using Sql LTS in this project. Any advice would be much appreciated.