Is there anything one cannot do (and then rollback safely) in transaction?

Question

Is there anything one cannot do (and then rollback safely) in transaction?

My transaction is a simple test of a major, disruptive change in a production database. The SQL code wrapped in a transaction consists of statements such as below. The SQL code works in the test database, with a tiny amount of data, where it runs and can be rolled back fine, taking up 0.5 seconds per transaction. I want to measure the performance of the code in production, with several GB of data deleted and then written in the transaction, estimated to take up to 30 min.

ALTER TABLE
UPDATE ...
DROP VIEW ... CASCADE
CREATE OR REPLACE VIEW ...
CREATE MATERIALIZED VIEW ...
CREATE UNIQUE INDEX ... ON <the materialized view just created above> ...
-- I can omit "CONCURRENTLY" if needed:
REFRESH MATERIALIZED VIEW CONCURRENTLY <the materialized view just created above>
CREATE OR REPLACE VIEW ...

Related:

Create database or vacuum are examples of SQL statements that cannot be done in a transaction. But these are exceptions — Frank Heikens, Apr 19 '23 at 17:44
Are you after an exhaustive list of irreversible changes these 5 commands can cause? *"measure the performance of the code in production"* sounds dangerously close to *"test in production"* where you're planning to quietly deploy unvalidated code on a production environment, making sure it can be undone if something goes sideways. If you need to test performance on a larger, prod-sized data set, it might be a good idea to consider setting up an actual performance test db, based on a snapshot of the prod. Introducing a "safe on prod" test category can be a gateway drug. — Zegarek, Apr 22 '23 at 10:16
See: https://stackoverflow.com/a/28251922/939860, https://stackoverflow.com/a/22748516/939860. I would **not** do this on a prod DB. If at all possible, clone the DB and test on the copy. `CREATE DATABASE mydb_test TEMPLATE mydb;` is the fastest way within the same DB cluster - while there are no concurrent sessions. See: https://stackoverflow.com/a/10107602/939860 You still compete for resources when testing on the same server. — Erwin Brandstetter, Apr 23 '23 at 01:35

Erwin Brandstetter · Accepted Answer · 2023-04-26T03:35:30.587

Think twice

If at all possible, run invasive tests on a copy of the productive database, don't take the risk of breaking your productive DB. Even if all goes well, your presence won't go unnoticed. You may slow down or block concurrent activity.
The fastest way to create a copy within the same DB cluster - while there are no concurrent sessions:

CREATE DATABASE mydb_test TEMPLATE mydb;

See:

How to clone a test database from a production one in one single action?

You avoid direct friction, blockage and breakage. But you still compete for resources when testing on the same server. Testing on a different server would avoid that, too ...

About `ROLLBACK`

In Postgres, the vast majority of all DML and DDL commands are fully transactional (can run inside a transaction block) and their effects can be rolled back. Just make sure you don't COMMIT by accident.

Notable exceptions include: (none of these lists are comprehensive)

Things that don't even run in a transaction

There are some commands that cannot run in a transaction context to begin with. So they also cannot be rolled back. If you tried to include any of those in your transaction, Postgres would raise an exception before they even run. So nothing lost except the work that had been done already - and is rolled back.

CREATE DATABASE, DROP DATABASE
CREATE TABLESPACE, DROP TABLESPACE
CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY
REINDEX - when including a partitioned index or table
VACUUM
BEGIN, START TRANSACTION, COMMIT, ROLLBACK - obviously. Those start & end transactions and cannot run inside one. Only raise a WARNING if called in the wrong place.
CALL - if the called procedure includes transaction control commands (see above).
DO - if the code block includes transaction control commands (see above).

Things that are not (or cannot be) rolled back

Those are the ones you really need to be aware of. Like:

Effects of the sequence manipulation functions setval() and nextval(), implicitly used by serial and IDENTITY columns. So expect gaps in serial numbers. See:
- Serial numbers per group of rows for compound key
- How to reset Postgres' primary key sequence when it falls out of sync?
Anything written to log files
Anything returned to clients
Effects of dblink calls (or similar). See:
- Does Postgres support nested or autonomous transactions?

Corner cases

LISTEN, UNLISTEN, and NOTIFY are transactional, but there are some edge cases.
TRUNCATE - the manual:

TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.6 for more details.

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

score 0 · Answer 2 · answered Apr 19 '23 at 18:59

There are a few outlying commands (e.g. "create database" as F.H. says in the comments) but nothing in the "normal run" of things.

However - you probably want to be a little careful of locks. If you are deleting a lot of rows or altering foreign-keys and the like then other transactions can get held up behind your test, waiting to see if it commits or rolls back.