Why does this multiple "DO" block script work in DBeaver but fail in pgAdmin?

Question

I have created a script to be run on a Postgres DB, and it works perfectly when run in DBeaver as long as Autocommit is turned on, but when the same script is run in pgAdmin with Autocommit turned on, it fails.

I've boiled down the script to the following example, which also works in DBeaver but fails in pgAdmin under the described conditions:

DO $$ 
BEGIN
    ROLLBACK;
    RAISE INFO 'Autocommit is ON.';
END $$;

DO $$ 
BEGIN
    ROLLBACK;
    RAISE INFO 'Autocommit is still ON.';
END $$;

When it fails in pgAdmin, the following error shows:

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inline_code_block line 3 at ROLLBACK 

SQL state: 2D000

This indicates to me it isn't really running in Autocommit mode, but the setting is definitely turned on each time I try it. What's more, without changing any settings or how I'm running the script, if I only have one of the above DO blocks, it works in both DBeaver and pgAdmin. So for some reason executing the script in pgAdmin is only allowing a single DO block per script while using Autocommit and transaction control within those DO blocks? I can't seem to find any documentation on this being the case, though it is a fairly specific issue.

I also tried executing the example script in DBeaver using both the "execute SQL script" button and the "execute SQL query" button (after selecting the whole script for the latter) and it works in both cases, so it seems to be something pgAdmin specific (or that doesn't affect DBeaver at least).

Unfortunately, I need to be able to control transactions within the DO block to enable processing of data in batches in a loop and be able to make progress on the data processing without the whole script finishing, so it can fail and be continued later, so I don't see an alternative to doing COMMITs within the DO blocks.

Answer 1

It must be that when pgAdmin executes several commands at once, it wraps them in an explicit transaction. That causes the error you observe.

I couldn't find a way to tell pgAdmin not to do that, so you can either use a different interactive client or write the job as a single DO statement:

DO $$ 
BEGIN
    ROLLBACK;
    RAISE INFO 'Autocommit is ON.';

    ROLLBACK;
    RAISE INFO 'Autocommit is still ON.';
END $$;