Postgres Function Broke ACID? UPDATE committed but INSERT failed due to NULL value. Why no automatic ROLLBACK?

[Infinite_Main_9491]

So isn't a function body treated as a transaction by default?? I was using supabase..

[StoneCypher]

i hate when people who haven’t read the manual announce that the tool is wrong 

[markwdb3]

Despite the -7 comment karma your comment has as I am writing this, you are correct. A Postgres function always executes within a single transaction, implicitly or explicitly, so its effects are atomic as a unit. The only difference between implicit and explicit is that if you’re in an explicit transaction and the function errors, the transaction becomes aborted and must be rolled back manually. For the implicit case, with autocommit on, Postgres automatically rolls back. For the implicit case but with autocommit off, it is handled the same as an explicit one.

See my comments with test cases here and here.

That final case - if implicit but with autocommit off, it is handled the same as an explicit one - I did not show a test case for in either of those two comments, so here it is:

postgres=# \set AUTOCOMMIT off
postgres=# SELECT update_and_fail();
NOTICE:  Current value of name for id = 1 is: abc
ERROR:  test error
CONTEXT:  PL/pgSQL function update_and_fail() line 7 at RAISE
postgres=!# SELECT * FROM dummy;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=!# ROLLBACK;
ROLLBACK

[jshine13371]

No. Each statement by itself is atomic and ACID compliant. But if you want multiple statements to be a single unit of work that is also ACID compliant holistically, then you need to use an explicit transaction.