Re: PostgreSql - access modified rows in prepare transaction command

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