pgadmin-org / pgadmin-org/pgadmin4

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)

Open
#8,387 4 comments 1 reaction 1 assignee View on GitHub

@khushboovashi is already working on this.

Since Jan 29, 2025.

Feature
Dominant language
Python
Stars
3.8k
Forks
891
Avg merge
4d 7h
Merged PRs (30d)
8

Description

Please note that security bugs or issues should be reported to security@pgadmin.org.

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 $$;
  1. 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:

Image

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

Image

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.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.