postgresql transaction commit rollback example - Brave Search

postgresql.org › docs › current › tutorial-transactions.html

PostgreSQL: Documentation: 18: 3.4. Transactions

May 14, 2026 - Wally's account ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally'; COMMIT; This example is, of course, oversimplified, but there's a lot of control possible in a transaction block through the use ...

tomasz-gintowt.medium.com › postgresql-transactions-commit-rollback-and-savepoint-da33115c0614

PostgreSQL Transactions: COMMIT, ROLLBACK and SAVEPOINT | by Tomasz Gintowt | Medium

April 23, 2026 - BEGIN; INSERT INTO orders VALUES (1, 'Laptop'); SAVEPOINT step1; INSERT INTO payments VALUES (1, 1000); -- payment failed ROLLBACK TO SAVEPOINT step1; COMMIT; ... If multiple steps depend on each other, always use a transaction.

Videos

Tutorial-43 | Mastering POSTGRESQL TRANSACTION CONTROL: COMMIT, ...

February 2, 2026

postgres procedures transactions control | commit | rollback | ...

September 23, 2024

How to implement Transactions (COMMIT, ROLLBACK, SavePoint) in ...

November 24, 2022

Transactions in #PostgreSQL #COMMIT #ROLLBACK #SQLTransaction ...

October 30, 2020

PostgreSQL Demonstration: Transactions - YouTube

October 27, 2020

Tutorial 52 - Transactions in PostgreSQL. - YouTube

neon.com › postgresql › tutorial › transaction

PostgreSQL Transaction

-- start a transaction BEGIN; -- insert a new row into the accounts table INSERT INTO accounts(name,balance) VALUES('Alice',10000); -- commit the change (or roll it back later) COMMIT; If you want to undo the changes to the database, you can ...

geeksforgeeks.org › postgresql-rollback

PostgreSQL - ROLLBACK - GeeksforGeeks

October 14, 2024 - BEGIN; UPDATE BankStatements SET ... committing the transaction SELECT customer_id, full_name, balance FROM BankStatements; UPDATE BankStatements SET balance = balance + 500 WHERE customer_id = 2; ROLLBACK; -- displaying data ...

github.com › acakojic › postgresql-learning › blob › main › transactions › 1_4_postgresql_rollback_in_transaction.md

postgresql-learning/transactions/1_4_postgresql_rollback_in_transaction.md at main · acakojic/postgresql-learning

... BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; Now, let's improve this with error handling and ROLLBACK.

Author acakojic

medium.com › nerd-for-tech › transaction-in-postgresql-rollback-savepoint-23987dc6c533

Transaction in PostgreSQL | ROLLBACK | SAVEPOINT | by Sohaib Anser | Nerd For Tech | Medium

July 1, 2021 - BEGIN;UPDATE users SET gems = gems — 100 WHERE id=2;UPDATE users SET gems = gems+100 WHERE id=1;COMMIT; Now to get the updated record just write the SELECT statement, and you will see David owns 1100 gems and John has 900 gems.

tutorialspoint.com › postgresql › postgresql_transactions.htm

PostgreSQL - TRANSACTIONS

COMMIT − To save the changes, alternatively you can use END TRANSACTION command. ROLLBACK − To rollback the changes.

stackoverflow.com › questions › 48277519 › how-to-use-commit-and-rollback-in-a-postgresql-function › 48277708

how to use COMMIT and ROLLBACK in a PostgreSQL function - Stack Overflow

You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function. The documentation says:

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK.

Ex negativo, since functions are not procedures that are invoked with CALL, you cannot do that in functions.

The BEGIN that starts a block in PL/pgSQL is different from the SQL statement BEGIN that starts a transaction.

Just remove the COMMIT from your function, and you have the solution: since the whole function is always run inside a single transaction, any error in the third statement will lead to a ROLLBACK that also undoes the first two statements.

Compared to other SQL languages, you should think that Postgres always takes care of the commit/rollback in case of error implicitly when you are inside a transaction.

Here is what the doc is saying:

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

CREATE OR REPLACE FUNCTION TEST1 ()
   RETURNS VOID
   LANGUAGE 'plpgsql'
   AS $$
BEGIN 

    INSERT INTO table1 VALUES (1);

    INSERT INTO table1 VALUES (2);

    INSERT INTO table1 VALUES ('A');
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;$$;

enterprisedb.com › postgres-tutorials › how-work-postgresql-transactions

How to work with PostgreSQL transactions | EDB

As the name suggests, ROLLBACK undoes the changes that were issued in the transaction block before it. postgres=# begin; BEGIN postgres=# delete from test; DELETE 1 postgres=# drop table test; DROP TABLE postgres=# rollback; ROLLBACK postgres=#

Find elsewhere

Google Bing Mojeek

CommandPrompt Inc.

commandprompt.com › education › how-to-use-postgresql-transaction

How to Use PostgreSQL Transaction — CommandPrompt Inc.

June 1, 2023 - Use the following query to check ... but revoke them at the end: BEGIN; UPDATE trxn SET balance = balance - 1500 WHERE id = 2; UPDATE trxn SET balance = balance + 1500 WHERE id = 4; ROLLBACK; This code will subtract the balance ...

educba.com › home › data science › data science tutorials › postgresql tutorial › postgresql transaction

PostgreSQL Transaction | Transaction Statements Used in PostgreSQL

May 11, 2023 - ... INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (2, 'PQR', 'XYZ', 1234567890, 'Pune'); INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (3, 'ABC', 'XYZ', 1234567890, 'Mumbai'); commit; ... ...

Call +917738666252

Address Unit no. 202, Jay Antariksh Bldg, Makwana Road, Marol, Andheri (East),, 400059, Mumbai

stackoverflow.com › questions › 12472318 › can-i-rollback-a-transaction-ive-already-committed-data-loss

database - Can I rollback a transaction I've already committed? (data loss) - Stack Overflow

No, you can't undo, rollback or reverse a commit.

STOP THE DATABASE!

(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).

If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.

You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

Urgent mitigation

Once your database is stopped, you should make a file system level copy of the whole data directory - the folder that contains base, pg_clog, etc. Copy all of it to a new location. Do not do anything to the copy in the new location, it is your only hope of recovering your data if you do not have backups. Make another copy on some removable storage if you can, and then unplug that storage from the computer. Remember, you need absolutely every part of the data directory, including pg_xlog etc. No part is unimportant.

Exactly how to make the copy depends on which operating system you're running. Where the data dir is depends on which OS you're running and how you installed PostgreSQL.

Ways some data could've survived

If you stop your DB quickly enough you might have a hope of recovering some data from the tables. That's because PostgreSQL uses multi-version concurrency control (MVCC) to manage concurrent access to its storage. Sometimes it will write new versions of the rows you update to the table, leaving the old ones in place but marked as "deleted". After a while autovaccum comes along and marks the rows as free space, so they can be overwritten by a later INSERT or UPDATE. Thus, the old versions of the UPDATEd rows might still be lying around, present but inaccessible.

Additionally, Pg writes in two phases. First data is written to the write-ahead log (WAL). Only once it's been written to the WAL and hit disk, it's then copied to the "heap" (the main tables), possibly overwriting old data that was there. The WAL content is copied to the main heap by the bgwriter and by periodic checkpoints. By default checkpoints happen every 5 minutes. If you manage to stop the database before a checkpoint has happened and stopped it by hard-killing it, pulling the plug on the machine, or using pg_ctl in immediate mode you might've captured the data from before the checkpoint happened, so your old data is more likely to still be in the heap.

Now that you have made a complete file-system-level copy of the data dir you can start your database back up if you really need to; the data will still be gone, but you've done what you can to give yourself some hope of maybe recovering it. Given the choice I'd probably keep the DB shut down just to be safe.

Recovery

You may now need to hire an expert in PostgreSQL's innards to assist you in a data recovery attempt. Be prepared to pay a professional for their time, possibly quite a bit of time.

I posted about this on the Pg mailing list, and Виктор Егоров linked to depesz's post on pg_dirtyread, which looks like just what you want, though it doesn't recover TOASTed data so it's of limited utility. Give it a try, if you're lucky it might work.

See: pg_dirtyread on GitHub.

I've removed what I'd written in this section as it's obsoleted by that tool.

See also PostgreSQL row storage fundamentals

Prevention

See my blog entry Preventing PostgreSQL database corruption.

On a semi-related side-note, if you were using two phase commit you could ROLLBACK PREPARED for a transction that was prepared for commit but not fully commited. That's about the closest you get to rolling back an already-committed transaction, and does not apply to your situation.

bytebase.com › blog › guides › postgres rollback explained

Postgres Rollback Explained | Bytebase

September 4, 2025 - BEGIN; -- Step 1: safe operations INSERT INTO employees (name, department) VALUES ('Alice', 'Engineering'); SAVEPOINT sp_batch; -- Step 2: risky operations INSERT INTO employees (name, department) VALUES ('Bob', 'Marketing'); -- Oops, Bob is actually in Sales -- Roll back only the risky step ROLLBACK TO SAVEPOINT sp_batch; -- Step 3: continue with corrected operation INSERT INTO employees (name, department) VALUES ('Bob', 'Sales'); COMMIT; The savepoint stays usable after you roll back to it. One thing to watch: any savepoints you created after it are not just released, they're destroyed and invalidated by the rollback. Some DDL statements (CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE, DROP TABLESPACE) can't run inside transactions at all.

postgresql.org › docs › 8.3 › tutorial-transactions.html

PostgreSQL: Documentation: 8.3: Transactions

February 1, 2021 - Wally's account ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally'; COMMIT; This example is, of course, oversimplified, but there's a lot of control to be had over a transaction block through the ...

postgresql.org › docs › 9.1 › sql-rollback.html

PostgreSQL: Documentation: 9.1: ROLLBACK

October 27, 2016 - ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded. ... Optional key words. They have no effect. Use COMMIT to successfully terminate a transaction.

geeksforgeeks.org › postgresql-transactions

PostgreSQL - Transactions - GeeksforGeeks

August 18, 2024 - BEGIN; UPDATE BankStatements SET ... FROM BankStatements; UPDATE BankStatements SET balance = balance + 500 WHERE customer_id = 2; COMMIT; SELECT customer_id, full_name, balance FROM BankStatements; ... ROLLBACK command is used ...

medium.com › yavar › transactions-in-postgresql-a90b09faa80c

Transactions in PostgreSQL. Typically, a database is a shared… | by Bennison J | YavarTechWorks | Medium

July 6, 2023 - -- transaction with commit BEGIN; -- QUERY 1; -- QUERY 2; -- QUERY 3; COMMIT; -- transaction with rollback BEGIN; -- QUERY 1; -- QUERY 2; -- QUERY 3; ROLLBACK; -- if any of the transaciton query did unwanted thing with database, then run ROLLBACK manually

medium.com › @alxkm › the-complete-guide-to-database-transactions-how-commit-and-rollback-really-work-in-mysql-and-36d1ce81b9eb

The Complete Guide to Database Transactions: How COMMIT and ROLLBACK Really Work in MySQL and PostgreSQL | by Alex Klimenko | Medium

November 2, 2025 - Read on to discover the fascinating world of database transactions — from the bank teller analogy that makes ACID properties click, to real-world e-commerce implementations, chaos testing, and the secret life of your data during COMMIT and ROLLBACK. Ready to dive deep? Let’s explore how transactions really work under the hood, with practical examples in both MySQL and PostgreSQL that you can use in production today…

digitalocean.com › community › tutorials › sql-commit-sql-rollback

SQL Commit And Rollback | DigitalOcean

May 3, 2026 - DDL statements implicitly commit on MySQL and Oracle, so any uncommitted DML before an ALTER TABLE or DROP TABLE becomes permanent and unrollable. Keep DDL and DML in separate transactional units on those engines. Autocommit defaults differ across engines. MySQL and SQL Server default to autocommit on; Oracle SQL*Plus defaults off; PostgreSQL has no server-level autocommit, only a client-side one. SAVEPOINT enables partial rollback inside an open transaction.