I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it began. How can I stop this functionality?
You can commit inside the DO, as long as it is not already inside a transaction.
create table foo (z double precision); do language PLPGSQL $$ declare i int; begin for i in 1 .. 12 loop insert into foo values (sqrt(10-i)); commit; end loop; end;$$;
ERROR: cannot take square root of a negative number CONTEXT: SQL statement "insert into foo values (sqrt(10-i))" PL/pgSQL function inline_code_block line 1 at SQL statement
select count(*) from foo;