0

I know about prepared transaction in Postgres, but seems you can just commit or rollback it later. You cannot even view the transaction's db state before you've committed it. Is any way to save transaction for later use?

What I want to achieve actually is a preview (and correcting) of some changes in db (changes are imports from csv file, so user need to see preview before apply it). I want to make changes, add some changes later, see full state of db and apply it (certainly, commit transaction)

Mikhail Koipish
  • 153
  • 6
  • The usually way to solve this is to import the CSV data into a staging table and then run a SQL query that uses the staging table rather than the real one to "preview" the changes. –  Jul 16 '14 at 11:28
  • This related answer may be of help: http://stackoverflow.com/questions/12502669/how-to-tell-if-record-has-changed-in-postgres/12503305#12503305 (`COPY` to temp staging table and proceed from there ...) – Erwin Brandstetter Jul 16 '14 at 14:07

1 Answers1

1

I cannot find a very good reference in docs, but I have a very strong feeling that the answer is: No, you cannot do that.

It would mean that when you "save" the transaction, the database would basically have to maintain all of its locks in place for an indefinite amount of time. Even if it was possible, it would mean horrible failure modes and trouble on all fronts.

For the pattern that you are describing, I would use two separate transactions. Import to a staging table and show that to user (or import to the main table but mark rows as "unapproved"). If user approves, in another transactions move or update these rows.

You can always end up in a situation where user can simply leave or crash without clicking "OK" or "Cancel". If what you're describing was possible, you would end up with a hung transaction holding all these resources. In my proposed solution you end up with wasteful rows in "staging" table that you may still show to user later or remove.

You may want to read up on persistence saga. This is actually a very simple example of a well known and researched problem.

To make the long story short, this pattern breaks down a long-running process like yours into smaller operations that are applied and persisted in some way in separate transactions. If any of them happens to fail (or does not occur as expected), you have compensating actions that usually undo what the steps executed so far have done (e.g. by throwing away stale/irrelevant data).

Here's a decent introduction:

This concept was formally introduced in the 80s, but is well alive and relevant today.

Mohan Radhakrishnan
  • 3,002
  • 5
  • 28
  • 42
Konrad Garus
  • 53,145
  • 43
  • 157
  • 230