1

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?

3
  • you can't. do is a single statement. Mar 21 '20 at 18:03
  • are there any workarounds?
    – Zeruno
    Mar 21 '20 at 18:06
  • @a_horse_with_no_name I am getting a "statement aborted" message when this block fails. Would you know if there is a timer related to it for long-running statements?
    – Zeruno
    Mar 22 '20 at 20:45
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.