postgresql - Why does this multiple "DO" block script work in DBeaver but fail in pgAdmin? - Stack Overflow

admin管理员组

文章数量:1200423

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.

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.

• postgresql
• transactions
• plpgsql
• pgadmin

Share Improve this question Follow edited Jan 22 at 8:07 Harvey Adcock asked Jan 21 at 21:19 Harvey AdcockHarvey Adcock 97111 gold badge77 silver badges1717 bronze badges 4

• 1 Postgres doesn't allow transaction commands inside code blocks. Check the documentation (Notes parte) postgresql.org/docs/current/sql-do.html – Jorge Campos Commented Jan 21 at 21:32
• 1 Make your life easier use psql. – Adrian Klaver Commented Jan 21 at 22:18
• 1 @JorgeCampos this is not true, as seen both in my example working in DBeaver (and the single DO block with a ROLLBACK working in both DBeaver and pgAdmin) and in the documentation: "Transaction control statements are only allowed if DO is executed in its own transaction." So Postgres does allow this. – Harvey Adcock Commented Jan 22 at 8:00
• @AdrianKlaver this does not help with my question. The third party support team that executes the scripts that we write uses pgAdmin, so our scripts have to run using that. The question is why is it different running a script in DBeaver than in pgAdmin. – Harvey Adcock Commented Jan 22 at 8:01

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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 $$;

Not really an answer, just debugging information for your report to pgAdmin. Basically pgAdmin does not parse out the individual DO statements and sends them as a single unit to the server.

When pgAdmin runs the query:

2025-01-22 10:22:31.867 PST [19309] postgres@test DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:22:31.867 PST [19309] postgres@test LOG:  statement: DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is ON.';
    END $$;
    
    DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is still ON.';
    END $$;
    
2025-01-22 10:22:31.867 PST [19309] postgres@test ERROR:  invalid transaction termination
2025-01-22 10:22:31.867 PST [19309] postgres@test CONTEXT:  PL/pgSQL function inline_code_block line 3 at ROLLBACK
2025-01-22 10:22:31.867 PST [19309] postgres@test STATEMENT:  DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is ON.';
    END $$;
    
    DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is still ON.';
    END $$;

When psql runs the query:

2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:23:24.212 PST [20231] postgres@test LOG:  statement: DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is ON.';
    END $$;
2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:23:24.212 PST [20231] postgres@test CONTEXT:  PL/pgSQL function inline_code_block line 3 at ROLLBACK
2025-01-22 10:23:24.212 PST [20231] postgres@test INFO:  Autocommit is ON.
2025-01-22 10:23:24.212 PST [20231] postgres@test CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE
2025-01-22 10:23:24.212 PST [20231] postgres@test STATEMENT:  DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is ON.';
    END $$;
2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:23:24.212 PST [20231] postgres@test LOG:  statement: DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is still ON.';
    END $$;
2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-01-22 10:23:24.212 PST [20231] postgres@test CONTEXT:  PL/pgSQL function inline_code_block line 3 at ROLLBACK
2025-01-22 10:23:24.212 PST [20231] postgres@test INFO:  Autocommit is still ON.
2025-01-22 10:23:24.212 PST [20231] postgres@test CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE
2025-01-22 10:23:24.212 PST [20231] postgres@test STATEMENT:  DO $$ 
    BEGIN
        ROLLBACK;
        RAISE INFO 'Autocommit is still ON.';
    END $$;
2025-01-22 10:23:24.212 PST [20231] postgres@test DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0

本文标签： postgresqlWhy does this multiple quotDOquot block script work in DBeaver but fail in pgAdminStack Overflow