0

Is it possible to make third step in PostgreSQL?

  1. Make changes X to DB.
  2. Make changes Y to DB that are based on changes X.
  3. Rollback changes X.

If yes, please describe.

alex23
  • 353
  • 2
  • 13
  • 1
    This would be possible if PostgreSQL could autonomously commit a subtransaction, or if it had autonomous transactions with the ability to perform dirty reads on another transaction. Neither is supported (by any DB, as far as I know), so you can't do this. RDBMSes are practically designed to *stop* you doing this. Why would you want to? What's the actual underlying problem you are attempting to solve here? – Craig Ringer Jun 04 '14 at 16:34

1 Answers1

4

No. Committing changes Y will commit also changes X. Think about it: If X was rolled back then relational consistency rules might break. Regardless of database engine.

Peter M. - stands for Monica
  • 1,102
  • 12
  • 20
  • Thanks for the answer. Changes X and Y doesn't cross each other (different tables), Y just uses values of updated rows in calculations. Well, then I think I will save X changes to some variables, and then use it in Y changes. – alex23 Jun 04 '14 at 16:38
  • But when X is rolled back, values from X which Y used will become invalid, or worse, will point to other unrelated data in X, and how you will distinguish between valid link to X in some Y rows and invalid in others? Of course it depends on kind of X values used in Y. If it is just some summary or average, it might be OK. But if you want to store in Y pointers to X and then deleting them, you are asking for problems. – Peter M. - stands for Monica Jun 04 '14 at 16:54
  • Yes, I understand that. Y doesn't save any pointers to X. I just implemented what I want in my PHP application - first I run X changes, then I SELECT what I need for Y, then I rollback X changes, then I use selected data for Y changes. – alex23 Jun 04 '14 at 17:10