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)
@khushboovashi is already working on this.
Since Jan 29, 2025.
- 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:
- In pgAdmin, open the Query tool
- Ensure "Auto commit?" option is enabled in the "Execute options" next to the "Execute script" button
- Run the following script:
DO $$
BEGIN
ROLLBACK;
RAISE INFO 'Autocommit is ON.';
END $$;
DO $$
BEGIN
ROLLBACK;
RAISE INFO 'Autocommit is still ON.';
END $$;
- 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.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.