PostgreSql - access modified rows in prepare command

0
votes

I have the problem to access modified data (updates and inserts) in a prepared statement before a commit/rollback. For example consider the following block:

BEGIN;    
do some update;   
do some insert;    
PREPARE 'transaction1';

After executing the 'prepare' command (and before executing the 'commit' command), I neeed to fetch the data modified by the transaction 'transaction1'. I can fetch the rows with the old values that are modified by the transaction (using the xmax field), howewer I need also the values that the transaction will write into these rows when it commits.

Postgres needs to store these values somewhere in order to commit the transaction when it is required, so my question is: how can I access these values?

2
postgresqltransactions
Is that supposed to be a PREPARE ... AS statement, or a PREPARE TRANSACTION ... statement? I'm guessing the second, but then I can't see what part the update/inserts are supposed to be playing. - Richard Huxton
Is a PREPARE TRANSACTION statement. The updates/inserts do some modifications to data and I need to know these modifications before the call to the commit/rollback. This answer your question? - Pierpaolo Cincilla
No - you can always see changes you make in your own transaction. Can you provide an actual example of what you want to do and what goes wrong? - Richard Huxton
Consider for instance the following block:BEGIN; update tableA set columnA=10 where id=0; PREPARE transactionA now in order to decide if commit or rollback the transactionA I need to know the new value of columnA in the row updated by transactionA. E.g. if transactionA update tableA.columnA to a value <= 10 then commit transactionA else rollback transactionA. The problem is that values updated by the transactionA are not visible before transactionA is committed. How can I fetch them? Thanks for your help - Pierpaolo Cincilla

2 Answers

1
votes

Thank you Richard, you are right. I can't fetch modification of a "prepared" transaction. The work around that I have done is to fetch values midified by the transaction before call the PREPARE statement.

    BEGIN;
    execute my_stored_procedure;
    fetch modified rows;
    PREPARE TRANSACTION my_stored_procedure;'

Pierpaolo

0
votes

OK - I think your problem is that PREPARE TRANSACTION doesn't do what you think it does.

You PREPARE as the last thing in your transaction and that saves it so you can commit later. It's commonly used in cases where you need to update multiple databases simultaneously. You would PREPARE transactions on both and then COMMIT PREPARED on both at the same time (as far as you can). That reduces the window when a problem can occur and let one database get out of step with the other.

=> BEGIN;
BEGIN
richardh=> SELECT * FROM foo;
 app_id | rule_id
--------+---------
      1 |      10
      1 |      20
      2 |      10
(3 rows)

=> INSERT INTO foo VALUES (3,30);
INSERT 0 1
=> PREPARE TRANSACTION 'abc';
PREPARE TRANSACTION
=> SELECT * FROM foo;
 app_id | rule_id
--------+---------
      1 |      10
      1 |      20
      2 |      10
(3 rows)

=> COMMIT PREPARED 'abc';
COMMIT PREPARED
=> SELECT * FROM foo;
 app_id | rule_id
--------+---------
      1 |      10
      1 |      20
      2 |      10
      3 |      30
(4 rows)

You might want to post another question describing the problem you are trying to solve, there might be a different approach.