0

I know that you do the BEGIN; command, then all commands after that can be rolled back, as long as you haven't executed COMMIT; yet. My question is, is there a command to roll back just one command and not ALL commands after the BEGIN? I'm using Postgres by the way.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
an honest observer
  • 9
  • 2

1 Answers1

2

Read https://www.postgresql.org/docs/14/tutorial-transactions.html about SAVEPOINT and ROLLBACK TO SAVEPOINT.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Note that each savepoint consumes some resources so don't just blindly issue one before every statement in a framework or anything like that. – Richard Huxton Mar 14 '22 at 09:49
  • Having to create a savepoint manually for each transaction seems like a tedious process. I'm guessing there's no command that undos the transactions one by one like ctrl+z without having to create a savepoint for each rollback – an honest observer May 01 '22 at 13:07
  • No, there is not. Suppose you wanted to rollback a transaction in which you did DELETE of a million rows. The database would have to secretly store those data indefinitely, just in case you might want to Ctrl-Z the DELETE someday. – Bill Karwin May 01 '22 at 15:16
  • There is some discussion of adding a feature for _temporal tables_ to PostgreSQL in some future version. A code patch exists, but it is not committed. See https://blog.dbi-services.com/temporal-tables-for-postgresql-15/ – Bill Karwin May 01 '22 at 15:17