2

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.
    – user1822
    Mar 21, 2020 at 18:03
  • are there any workarounds?
    – Zeruno
    Mar 21, 2020 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, 2020 at 20:45

1 Answer 1

2

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

3
  • This results in: ERROR: invalid transaction termination CONTEXT: PL/pgSQL function inline_code_block line 6 at COMMIT
    – Victor
    Dec 7, 2023 at 23:20
  • @Victor I only get that error I try to do it from inside another transaction block. If you are not in a transaction already, then it works fine for me, in all still-supported versions.
    – jjanes
    Dec 9, 2023 at 2:26
  • you are right. I ran this in PgAdmin. Looks like it wraps everything into a transaction. In console this worked. Thank you
    – Victor
    Dec 16, 2023 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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