Thread: PostgreSql - access modified rows in prepare transaction command

Hello,

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

BEGIN;    
do some update;   
do some insert;    
PREPARE TRANSACTION '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? Thank you in advance.

Pierpaolo Cincilla




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSql-access-modified-rows-in-prepare-transaction-command-tp5745926.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 20.02.2013 12:48, pierpaolo.cincilla wrote:
> I have the problem to access modified data (updates and inserts) in a
> prepare transaction statement before a commit/rollback. For example consider
> the following block:
>
> BEGIN;
> do some update;
> do some insert;
> PREPARE TRANSACTION '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? Thank you in advance.

In short, you can't. PostgreSQL stores the values in the tables, but 
they are invisible to other transactions until the prepared transaction 
is committed. From this point of view, a prepared transaction behaves 
the same as a transaction that's still in-progress in another backend.

If you explain a bit more what you're trying to accomplish, someone can 
probably suggest a better solution.

- Heikki

Thank you Heikki for your reply. As you suggest, I will explain better what
I'm trying to accomplish. 

What I'm writing a ditributed two-phase-commit termination protocol that
work in this manner:

1) Each site has a replica of the database. A site A perform a transaction
t1 and prepare it (PREPARE TRANSACTION 't1'). Then it atomic broadcast a
certification request for the transaction t1 *along with its writeset*
(values updated by t1) to other sites. 

2) When a site receive the certification request for transaction t1 does the
certification (check that there are no concurrent conflicting transactions).
If the certification succeed then 
2a) if the transaction is local (i.e. originated at that site) it commit the
transaction (COMMMIT PREPARED 't1').
2b) If the transaction is remote (i.e. prepared at another site) *it apply
locally the writeset of transaction t1* to reflect modifications to its
local replica of the database (UPDATE command).

The problem is that if I can't fetch the writeset of a transaction in phase
1 (before the commit request) then when I certify the transaction at another
site I can't apply the updates performed by the remote transaction right
away but I have to wait the originating site to commit the transaction and
send back its writeset (now visible). This will be very bad because it adds
an extra round to the algorithm.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSql-access-modified-rows-in-prepare-transaction-command-tp5745926p5745930.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wednesday, February 20, 2013 5:10 PM pierpaolo.cincilla wrote:
> Thank you Heikki for your reply. As you suggest, I will explain better
> what
> I'm trying to accomplish.
> 
> What I'm writing a ditributed two-phase-commit termination protocol
> that
> work in this manner:
> 
> 1) Each site has a replica of the database. A site A perform a
> transaction
> t1 and prepare it (PREPARE TRANSACTION 't1'). Then it atomic broadcast
> a
> certification request for the transaction t1 *along with its writeset*
> (values updated by t1) to other sites.
> 
> 2) When a site receive the certification request for transaction t1
> does the
> certification (check that there are no concurrent conflicting
> transactions).
> If the certification succeed then
> 2a) if the transaction is local (i.e. originated at that site) it
> commit the
> transaction (COMMMIT PREPARED 't1').
> 2b) If the transaction is remote (i.e. prepared at another site) *it
> apply
> locally the writeset of transaction t1* to reflect modifications to its
> local replica of the database (UPDATE command).
> 
> The problem is that if I can't fetch the writeset of a transaction in
> phase
> 1 (before the commit request) then when I certify the transaction at
> another
> site I can't apply the updates performed by the remote transaction
> right
> away but I have to wait the originating site to commit the transaction
> and
> send back its writeset (now visible). This will be very bad because it
> adds
> an extra round to the algorithm.

I think the one possible way to get the transaction data at the point you
need will be through
WAL, but that will also not be straightforward, you need to decode and find
in WAL corresponding data.

With Regards,
Amit Kapila.

On 20.02.2013 13:39, pierpaolo.cincilla wrote:
> Thank you Heikki for your reply. As you suggest, I will explain better what
> I'm trying to accomplish.
>
> What I'm writing a ditributed two-phase-commit termination protocol that
> work in this manner:
>
> 1) Each site has a replica of the database. A site A perform a transaction
> t1 and prepare it (PREPARE TRANSACTION 't1'). Then it atomic broadcast a
> certification request for the transaction t1 *along with its writeset*
> (values updated by t1) to other sites.
>
> 2) When a site receive the certification request for transaction t1 does the
> certification (check that there are no concurrent conflicting transactions).
> If the certification succeed then
> 2a) if the transaction is local (i.e. originated at that site) it commit the
> transaction (COMMMIT PREPARED 't1').
> 2b) If the transaction is remote (i.e. prepared at another site) *it apply
> locally the writeset of transaction t1* to reflect modifications to its
> local replica of the database (UPDATE command).

The usual way to keep two identical databases in sync using two-phase 
commit is to just run all the statements in both databases. That assumes 
that the statements always produce identical results in both databases, 
though.

> The problem is that if I can't fetch the writeset of a transaction in phase
> 1 (before the commit request) then when I certify the transaction at another
> site I can't apply the updates performed by the remote transaction right
> away but I have to wait the originating site to commit the transaction and
> send back its writeset (now visible). This will be very bad because it adds
> an extra round to the algorithm.

You could fetch the "writeset" in the same connection just before 
calling PREPARE TRANSACTION. While the transaction is still active, the 
changes are visible to itself.

Aside from any extra round-trips, the bigger reason you can't commit 
first and then fetch the writeset is that you can't roll back the 
transaction anymore, if the writeset can't be applied on the other node. 
If you could live with that, and the problem is just the latency, then 
you don't need two-phase commit to begin with.

- Heikki