PostgreSQL | How to ensure you're in a transaction
Transactions are only useful when you actually use them, otherwise it's too late when you realize you've forgotten the
WHERE clause and would really, really like to be able to roll back the changes - as Robert Treat found out recently.
If you're lucky conventional backups or
PITR will help you avoid total disaster, but apart from the delay in extracting the necessary data from the backups and reading it in, Murphy's Law dicates that exactly the data you need won't be in the backup. Far better is to remember to start a transaction in the first place, putting recovery a blood-pressure friendly
psql has two surprisingly little-used functions which help ensure you're inside a transaction when you need to be.
I've written about this before: psql has a prompt formatting option %x which displays an asterisk * whenever a transaction is in process. Set it with e.g.
\set PROMPT1 '%/%R%x%# ' and train yourself to be very nervous about carrying out anything other than simple
SELECTs when that little asterisk is not visible:
test=> \set PROMPT1 '%/%R%x%# ' test=> BEGIN; BEGIN test=*> DELETE FROM important_table; DELETE 24566321 test=*> ROLLBACK; ROLLBACK test=> \echo Phew, that was close! Phew, that was close!
(See the psql manual for further details)
Of course this still requires you to remember to issue the
BEGIN instruction, because PostgreSQL's default setting is for
AUTOCOMMIT to be on. Issuing
\set AUTOCOMMIT off, especially in combination with the prompt setting above, provides a useful safety net:
test=> \set AUTOCOMMIT off test=> DELETE FROM important_table; DELETE 24566321 test=*> \echo Ooops! Ooops! test=*> ROLLBACK; ROLLBACK
The risk here of course is closing psql before committing the transaction, implicitly rolling back any changes you wanted to make, but the asterisk in the prompt serves as a useful visual reminder.
\set PROMPT1 '%/%R%x%# ' and
\set AUTOCOMMIT off in your
.psqlrc file to make them permanent settings.