CODEWITHSUNDEEP
postgresql
AntiGMO
AntiGMO

Reputation: 1587

pgsql rollback function or revocation last step

I build a pgsql db, and using JDBC upload data to db. but sometime i upload wrong data. so in pgsql it has some functions or sql statement can allow rollback to last step, without using backup file?

Thanks,

Upvotes: 0

Views: 204

Answers (1)

tobixen
tobixen

Reputation: 4073

If you run things in a transaction, you may end the transaction with either "commit" or "rollback", hence in your script you may end up with an interactive question to the user: "does this seem OK?" and then either do a commit or rollback.

Beware that long-lasting "idle in transaction" may be harmful for the performance, so you should do rollback (or commit) after some timeout. Of course, it would be even better to ask the question to the user before doing any DB operations.

If something is already committed to the database, as far as I know it cannot be rolled back unless you have backups (dunno - perhaps it's theoretically possible to do it based on the WALs ... but probably not). Some years ago I was implementing a system with a "warm standby backup server" that would always be half an hour behind the production database - I could then issue a "distress signal" and the backup server would come online with the old state of the database, i.e. if I'd done some mistake like update users set password=md5('foo' || salt) instead of update users set password=md5('foo' || salt) where username='tobixen'. If the primary database went down, the "warm standby" would roll forward and then come online. I may post additional details about this if there is any interesst for it.

If you're expecting trouble, it's also usually possible to design the database and the scripts in such a manner that it's trivial to do a manual rollback.

Upvotes: 1

Related Questions