COMMIT, ROLLBACK, and Autocommit: PostgreSQL Transactions for SQL Server DBAs
If you have ever been handed a SQL Server maintenance script by a developer, you have probably seen this move. They open a query window, type BEGIN TRANSACTION, run a couple of UPDATE statements, eyeball a SELECT to make sure they did not just empty the warehouse, and then, only when they are happy, highlight COMMIT and press F5. If something looks wrong, they run ROLLBACK instead and pretend it never happened.
That workflow is so common that a lot of scripts arrive with the COMMIT and ROLLBACK lines already commented out at the bottom, like a safety catch:
|
1 2 |
-- COMMIT TRANSACTION; -- ROLLBACK TRANSACTION; |
It is a reasonable-looking habit in SSMS, where the session holds the transaction open between executions. The trouble starts when that same script is carried over to PostgreSQL, where the safety catch quietly stops working. Not with an error. It just does not do what you expect. This post is about why, and what to do instead, aimed at people who know SQL Server well and are now staring at a psql prompt or a pgAdmin Query Tool wondering where their comfortable habits went.[psql vs pgAdmin]
The SQL Server Pattern You Have Seen
Here is the scenario for the whole post: a simple warehouse. We move stock of one product from one warehouse to another. Decrement the source, increment the destination, look at the result, decide.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
USE [Warehouse]; GO BEGIN TRANSACTION; DECLARE @product_id int = 1001; DECLARE @from_wh int = 1; DECLARE @to_wh int = 2; DECLARE @qty int = 50; UPDATE [dbo].[inventory] SET [quantity] = [quantity] - @qty WHERE [warehouse_id] = @from_wh AND [product_id] = @product_id; UPDATE [dbo].[inventory] SET [quantity] = [quantity] + @qty WHERE [warehouse_id] = @to_wh AND [product_id] = @product_id; /* Eyeball the result, then run ONE of the lines below by hand. */ SELECT [warehouse_id], [product_id], [quantity] FROM [dbo].[inventory] WHERE [product_id] = @product_id AND [warehouse_id] IN (@from_wh, @to_wh); -- COMMIT TRANSACTION; -- ROLLBACK TRANSACTION; |
You run everything down to the SELECT. SSMS shows you the two rows in the grid. The transaction is still open, holding its locks, waiting for someone to decide. You commit or roll back by hand. This works because SSMS keeps the session and the open transaction alive between executions, and because the result of that SELECT comes straight back to the grid. It also carries an obvious hazard: forget to finish the transaction and you have left a blocking chain behind you.
Both of those assumptions change in PostgreSQL.
Where the Pattern Breaks Down
Three things catch you out when you carry this pattern across. One is a real difference in defaults, one is behavior the two engines actually share but the interactive habit hides, and one is stricter on the PostgreSQL side.
1. Autocommit is the default
In SQL Server you are always inside an implicit transaction that you commit explicitly or that commits per statement depending on settings, but the mental model is “I control the transaction”. In PostgreSQL, the client runs in autocommit mode by default: every statement that is not wrapped in an explicit BEGIN is its own transaction and commits the moment it succeeds.
psql exposes this as a variable, AUTOCOMMIT, which is on by default.[1] pgAdmin’s Query Tool has its own autocommit toggle in the toolbar. An explicit BEGIN still opens a real transaction block that stays open until you COMMIT or ROLLBACK, so the interactive “decide later” approach can work, but only when you are genuinely interactive and willing to hold locks while you think.
2. Disconnecting rolls back an open transaction
This is the one that quietly breaks the pattern. If you take that same script, leave the COMMIT commented out, and run the whole file non-interactively:
|
1 |
psql -h myserver -d warehouse -f transfer.sql |
then psql reaches the end of the file with the transaction still open, the connection closes, and the backend rolls the transaction back. Your two UPDATE statements run, the SELECT prints, everything looks plausible, and then nothing persists. This part is not actually unique to PostgreSQL: run the same uncommitted script through sqlcmd and SQL Server rolls its transaction back on disconnect too. What lulls people is the interactive habit. In an SSMS window the session stays alive between executions, so the transaction waits, holding its locks, until a human finishes it by hand. Move that same script into a file and run it unattended and that waiting period is gone in both engines. The PostgreSQL transactions tutorial[2] states the PostgreSQL rule plainly: a transaction that has not been committed when the session ends is discarded.
So the commented-out COMMIT line is not a safe default when the script runs unattended. It is a guaranteed rollback, in PostgreSQL and SQL Server alike.
3. Any error aborts the entire transaction
This difference deserves its own warning. In SQL Server, without SET XACT_ABORT ON, a run-of-the-mill error (a constraint violation, a divide by zero) does not necessarily kill the transaction. The batch can keep going and you can still commit.
PostgreSQL does not work that way. The moment a statement inside a transaction block raises an error, the whole transaction enters an aborted state. Every subsequent statement fails with the same message until you roll back:
|
1 |
ERROR: current transaction is aborted, commands ignored until end of transaction block |
There is no per-statement forgiveness. You either handle the error (in plpgsql, with a BEGIN ... EXCEPTION block, which uses an internal savepoint) or you roll back and start over. This is stricter than SQL Server, and honestly it is safer, but it surprises people who expect the batch to limp along.
The Naive Translation, and Why It Disappoints
The instinct is to paste the SQL Server shape almost verbatim:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
BEGIN; UPDATE inventory SET quantity = quantity - 50 WHERE warehouse_id = 1 AND product_id = 1001; UPDATE inventory SET quantity = quantity + 50 WHERE warehouse_id = 2 AND product_id = 1001; SELECT warehouse_id, product_id, quantity FROM inventory WHERE product_id = 1001 AND warehouse_id IN (1, 2); -- COMMIT; -- ROLLBACK; |
Run interactively in psql, statement by statement, this behaves like SSMS: the explicit BEGIN opens a transaction block that, per the PostgreSQL BEGIN documentation[3], stays open until you give an explicit COMMIT or ROLLBACK, so you commit or roll back when ready. Run as a file, it does the work and throws it away at disconnect. And neither version gives you any narration if you wrap the logic in a function or a DO block later, because a DO block cannot return a result set at all. There is no grid to look at.
What I actually want is a script that does the work, tells me exactly what it did, and does not keep anything unless I say so, whether I run it interactively or from a file. PostgreSQL has a clean idiom for that.
The Better Pattern: A DO Block With a Dry-Run Flag
A single DO statement is its own transaction.[4] It commits if it succeeds and rolls back if it raises. We can use that to build a dry run: do everything, print a running commentary with RAISE NOTICE, and then deliberately raise an error at the end so the whole thing rolls back. The notices still reach the client, because messages are delivered as they are raised, independently of whether the transaction later commits.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
DO $$ DECLARE /* Edit these. in_dry_run = true reports only and rolls back. Set it to false to actually apply the transfer. */ in_dry_run constant boolean := true; in_product_id constant int := 1001; in_from_wh constant int := 1; in_to_wh constant int := 2; in_qty constant int := 50; v_available int; v_rows int; BEGIN /* Validate: exactly one source row, and enough stock. */ BEGIN SELECT quantity INTO STRICT v_available FROM inventory WHERE warehouse_id = in_from_wh AND product_id = in_product_id; EXCEPTION WHEN no_data_found THEN RAISE EXCEPTION 'No inventory row for product % in warehouse %.', in_product_id, in_from_wh; END; IF v_available < in_qty THEN RAISE EXCEPTION 'Insufficient stock in warehouse %: have %, need %.', in_from_wh, v_available, in_qty; END IF; /* Debit the source. */ UPDATE inventory SET quantity = quantity - in_qty WHERE warehouse_id = in_from_wh AND product_id = in_product_id; GET DIAGNOSTICS v_rows = ROW_COUNT; RAISE NOTICE 'Debited % unit(s) from warehouse % (% row updated).', in_qty, in_from_wh, v_rows; /* Credit the destination, creating the row if it does not exist. */ INSERT INTO inventory (warehouse_id, product_id, quantity) VALUES (in_to_wh, in_product_id, in_qty) ON CONFLICT (warehouse_id, product_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity; GET DIAGNOSTICS v_rows = ROW_COUNT; RAISE NOTICE 'Credited % unit(s) to warehouse % (% row affected).', in_qty, in_to_wh, v_rows; RAISE NOTICE 'Transfer of % unit(s) of product % from warehouse % to % complete.', in_qty, in_product_id, in_from_wh, in_to_wh; /* The safety catch, PostgreSQL style. */ IF in_dry_run THEN RAISE EXCEPTION 'DRY RUN complete - all work above has been ROLLED BACK. ' 'Set in_dry_run := false and re-run to apply.'; END IF; END $$ LANGUAGE plpgsql; |
Run it as-is and you get the full story, then a clean rollback:
|
1 2 3 4 |
NOTICE: Debited 50 unit(s) from warehouse 1 (1 row updated). NOTICE: Credited 50 unit(s) to warehouse 2 (1 row affected). NOTICE: Transfer of 50 unit(s) of product 1001 from warehouse 1 to 2 complete. ERROR: DRY RUN complete - all work above has been ROLLED BACK. Set in_dry_run := false and re-run to apply. |
That ERROR line at the end is intentional. It is the rollback firing. Nothing was kept. When you are satisfied, you change one word, in_dry_run := false, run it again, and this time the block completes normally and autocommit persists the change. No commented-out COMMIT, no held-open transaction, and it behaves identically whether you run it interactively or from a file.
Getting Meaningful Diagnostics
The narration is half the point, so it is worth knowing the tools. PostgreSQL gives you direct equivalents for the SQL Server diagnostics you already use.
RAISE, the PRINT and THROW you already know
RAISE has severity levels. The ones below EXCEPTION are informational and do not stop execution; EXCEPTION aborts and rolls back. Whether a given level reaches your screen depends on the client_min_messages setting, which defaults to notice.
| Level | Stops execution? | Rough SQL Server analog |
|---|---|---|
| DEBUG / LOG | No | (server log only) |
| INFO | No | PRINT (always sent to client) |
| NOTICE | No | |
| WARNING | No | PRINT, with a warning flavor |
| EXCEPTION | Yes, rolls back | RAISERROR / THROW |
The % placeholders in a RAISE message are filled positionally by the arguments that follow, which is why RAISE NOTICE 'Debited % unit(s) ...', in_qty prints the value. Full details are in the Errors and Messages[5] documentation.
GET DIAGNOSTICS, the @@ROWCOUNT replacement
There is no @@ROWCOUNT in plpgsql. Instead you ask for the row count explicitly right after the statement:
|
1 |
GET DIAGNOSTICS v_rows = ROW_COUNT; |
There is also a boolean FOUND variable that is set by many statement types (a SELECT INTO that found a row, an UPDATE that touched at least one row, and so on), handy for quick checks. And SELECT ... INTO STRICT enforces exactly one row, raising NO_DATA_FOUND or TOO_MANY_ROWS if it does not get it, which is how the validation block above guards against a missing product without silently grabbing the wrong row. The statements chapter[6] covers GET DIAGNOSTICS, FOUND, and the STRICT behavior.
Where the output actually shows up
This trips people up, so it is worth stating plainly.
| Client | Where NOTICE / INFO messages appear |
|---|---|
| psql | Printed inline, interleaved with results, as NOTICE: ... lines |
| pgAdmin Query Tool | In the Messages tab, not the Data Output grid |
If you run the DO block in pgAdmin and stare at the Data Output tab waiting for rows, you will conclude nothing happened. Look at the Messages tab instead. A DO block never produces a result grid; all of its visible output is messages.
When You Genuinely Need COMMIT Inside the Logic
A DO block cannot issue its own COMMIT or ROLLBACK; it lives or dies with the surrounding transaction. If you need to commit in stages, for example a long batch that should checkpoint its progress, use a stored procedure instead. Since version 11, a PROCEDURE invoked with CALL can manage transactions internally:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE PROCEDURE warehouse.transfer_stock( p_product_id int, p_from_wh int, p_to_wh int, p_qty int ) LANGUAGE plpgsql AS $$ BEGIN UPDATE inventory SET quantity = quantity - p_qty WHERE warehouse_id = p_from_wh AND product_id = p_product_id; INSERT INTO inventory (warehouse_id, product_id, quantity) VALUES (p_to_wh, p_product_id, p_qty) ON CONFLICT (warehouse_id, product_id) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity; COMMIT; END $$; |
The rules and limits of in-procedure transaction control are documented under Transaction Management[7]. For one-off maintenance work, though, the dry-run DO block is usually all you need, and it keeps the whole operation atomic.
The Translation Cheat Sheet
If you keep one thing from this post, make it this table.
| SQL Server | PostgreSQL |
|---|---|
| Session stays in an open transaction between executions | Autocommit by default; explicit BEGIN opens a block, but disconnect rolls it back |
| Leftover open transaction holds locks until noticed | Open transaction is discarded when the session ends |
| Error may leave the transaction committable | Any error aborts the whole transaction until ROLLBACK |
| @@ROWCOUNT | GET DIAGNOSTICS v = ROW_COUNT |
| RAISE NOTICE (or RAISE INFO) | |
| RAISERROR / THROW | RAISE EXCEPTION |
| SET XACT_ABORT ON | effectively always on inside a transaction block |
| Run to the SELECT, then COMMIT or ROLLBACK by hand | Dry-run flag that RAISEs to roll back, then flip the flag to apply |
| Results land in the grid | DO block emits messages only (Messages tab in pgAdmin) |
| Nested BEGIN TRAN / @@TRANCOUNT | SAVEPOINT for partial rollback |
Wrapping Up
The hardest part of moving maintenance habits from SQL Server to PostgreSQL was not the syntax. It was letting go of the assumption that a transaction will sit open in the session, waiting patiently for a decision. PostgreSQL would rather you be explicit: do the work inside one atomic unit, narrate it with messages, and decide up front whether this run is a rehearsal or the real thing. The dry-run flag gives you the same decide-at-the-end safety as the commented-out COMMIT line, without ever leaving a transaction open to block anyone.
As it happens, you do not have to wait until you reach PostgreSQL to write scripts this way. You can get the same self-contained behavior in SQL Server with a debug flag, SET XACT_ABORT ON, and a single batch that commits or rolls back on its own, no open transaction left hanging. That is how I prefer to do it in SQL Server, and it is the subject of the next post: Self-Contained Transaction Scripts in SQL Server: the @debug_only Pattern (available 2026-06-17).
If you have your own pattern for safe, self-documenting maintenance scripts in PostgreSQL, I would like to hear it. You can find me on Bluesky and LinkedIn.
Appendix: psql and pgAdmin Are Not the Same Experience
If you are coming from SQL Server, it helps to know that the two PostgreSQL clients you are most likely to reach for behave differently around transactions, even though the server rules underneath them are identical. The server always runs in autocommit mode unless you open an explicit transaction with BEGIN; what changes between the clients is how statements are sent and whether the client adds a commit of its own.
psql is the command-line client, the closest relative to sqlcmd. It is an interactive prompt: you type a statement, end it with a semicolon, and it is sent and executed right then. Then you type the next one. It has an AUTOCOMMIT setting that is on by default, so each standalone statement commits the instant it succeeds. The moment you type BEGIN;, you open a transaction block, and every statement after it runs inside that block until you type COMMIT; or ROLLBACK;. That is the part that feels like SSMS with an explicit BEGIN TRAN: the transaction stays open across the statements you run one at a time, and nothing is permanent until you decide.
pgAdmin is the graphical client, the closer analog to SSMS. The difference is that you usually select a block of SQL and run the whole thing in one execution rather than statement by statement. Its Query Tool has an Auto commit option, on by default, so a single run executes the block and commits at the end. That means you do not automatically get a paused, open transaction to inspect the way you do when you step through statements in psql. To get that behavior in pgAdmin you either include explicit BEGIN and COMMIT commands in the script you run, or you turn Auto commit off, after which each run leaves the transaction open and you commit or roll back with the toolbar buttons.[8]
One more wrinkle worth knowing: when you send several semicolon-separated statements as a single execution (common in pgAdmin, and possible in psql too), the server treats that whole string as one transaction unless you include explicit BEGIN and COMMIT commands inside it. So a failure partway through rolls the entire send back.
| Client | Family | How statements are sent | Default commit behavior | How to hold a transaction open |
|---|---|---|---|---|
psql |
Command line, like sqlcmd |
One at a time, as you finish each with ; |
AUTOCOMMIT on; each statement commits on success |
Type BEGIN;, run statements, then COMMIT; or ROLLBACK; |
| pgAdmin Query Tool | Graphical, like SSMS | Usually a whole block in one run | Auto commit on; the run commits at the end | Add explicit BEGIN/COMMIT, or turn Auto commit off and use the commit and rollback buttons |
A practical takeaway: the scripts in this post behave the same way in both clients, because they rely on explicit transaction control (BEGIN, or a DO block) rather than on whatever the client’s autocommit happens to be set to. That is exactly why writing the logic to be self-contained, rather than leaning on a client’s interactive behavior, is the safer habit.
References
- psql: AUTOCOMMIT and client behavior – the AUTOCOMMIT variable and how psql sends statements ↩
- PostgreSQL Documentation: Transactions – how transactions begin, commit, and what happens when a session ends with one open ↩
- BEGIN, COMMIT, and ROLLBACK command references ↩
- DO – executing an anonymous code block ↩
- PL/pgSQL: Errors and Messages – RAISE, severity levels, and message formatting ↩
- PL/pgSQL: Basic Statements – SELECT INTO STRICT, GET DIAGNOSTICS, and the FOUND variable ↩
- PL/pgSQL: Transaction Management – committing inside procedures called with CALL ↩
- pgAdmin: Query Tool – the Auto commit option and how the Query Tool executes statements ↩