PLPGSQL scripts with multiple top-level DO blocks and transaction control do not work in pgAdmin but do in other clients over Postgres DB (error 2D000)

[harveyadcock]

Please note that security bugs or issues should be reported to [email protected].

Describe the bug

A script using multiple top-level DO blocks that uses transaction control within the DO blocks, which works in DBeaver over the same Postgres database, does not work when run in pgAdmin with the same settings (as far as I can tell). Autocommit is turned on in both clients.

To Reproduce

Steps to reproduce the behavior:

1. In pgAdmin, open the Query tool
2. Ensure "Auto commit?" option is enabled in the "Execute options" next to the "Execute script" button
3. Run the following script:

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

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

4. See the error:

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

SQL state: 2D000

When removing the 2nd DO block from the above code, the script runs fine in pgAdmin using the same settings.

Expected behavior

The script to run to completion, outputting the 2 RAISE INFO lines to the Messages console. This behaviour is seen when running the same script with Autocommit turned on in DBeaver, whether selecting the whole script and running via the "Execute SQL query" button, or just by pressing the "Execute SQL script" button.

Error message

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

SQL state: 2D000

Screenshots

Here is the same script running successfully in DBeaver using the "Execute SQL script" button:

And here is the same script running successfully in DBeaver using the "Execute SQL query" button after selecting the entire script:

Desktop (please complete the following information):

• OS: Windows 11
• Version: Version 10.0.22631 Build 22631
• Mode: Desktop
• Browser (if running in server mode): N/A
• Package type: [e.g. RPM, DEB, Python, Container, etc.]

Additional context

This looks to me like pgAdmin is doing something weird in how it's running the entire script with Autocommit vs how it would run individual queries. Unfortunately the 3rd party support team who will run my scripts use pgAdmin by default so I cannot just work around the issue by using some other SQL client.

[khushboovashi]

Hi,

This behaviour is because pgAdmin runs the query in a single transaction, whereas dbeaver runs as separate queries in different transactions. So, if you run these queries one by one in pgAdmin too, they will be executed successfully.

If you run the same query with AutoCommit OFF in dbever or in pgAdmin, it will fail at both places.
As it is by design, I am closing this ticket; if you still want this feature in pgAdmin, feel free to create a feature request.

Thanks,
Khushboo

[harveyadcock]

Hi,

This behaviour is because pgAdmin runs the query in a single transaction, whereas dbeaver runs as separate queries in different transactions. So, if you run these queries one by one in pgAdmin too, they will be executed successfully.

If you run the same query with AutoCommit OFF in dbever or in pgAdmin, it will fail at both places. As it is by design, I am closing this ticket; if you still want this feature in pgAdmin, feel free to create a feature request.

Thanks, Khushboo

Hi Khushboo,

To me this does not seem like expected behaviour though - pgAdmin is wrapping the script in a transaction block with no way to turn such behaviour off. In my view, this largely negates the purpose of Autocommit mode, which is supposed to automatically commit after each statement. If the user wanted to wrap the whole script in a transaction, they can do that. This implementation means users are unable to run scripts that should work fine and do in most other tools, because a transaction has been constructed around the entire script unnecessarily and without explanation to the user.

I would request that this be reconsidered, as the implementation as it stands is unable to run SQL scripts that Postgres would allow without such transaction wrapping. Perhaps splitting “Autocommit” mode into 2 if the existing functionality is really wanted - “Autocommit after each statement” (the standard Autocommit functionality) or “Autocommit on completion” (the current pgAdmin functionality) or something. As it stands, the current implementation is inconsistent with other SQL clients and makes certain scripts fail to run.

Best regards,
Harvey