Stop rollback of anonymous DO code block?

Question

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?

Answer 1

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;

10