Thread: Making a result of transaction visible to everyone, saving the ability for a rollback

I need to execute a rather complex plpgsql function on a production
server, the result of which is hard to test by myself.

I want to make the result of a transaction, in which this function is
executed, visible to the outer clients, but to have the ability of
making a rollback in case one of the clients says something went wrong.
Is it possible to do that?

Igor Katson wrote:
> I need to execute a rather complex plpgsql function on a production
> server, the result of which is hard to test by myself.
>
> I want to make the result of a transaction, in which this function is
> executed, visible to the outer clients, but to have the ability of
> making a rollback in case one of the clients says something went wrong.
> Is it possible to do that?

No.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Wed, Feb 11, 2009 at 12:58:53PM +0300, Igor Katson wrote:
> I want to make the result of a transaction, in which this function is
> executed, visible to the outer clients, but to have the ability of
> making a rollback in case one of the clients says something went wrong.
> Is it possible to do that?

You can't use ROLLBACK to accomplish that; transactional semantics are
an all or nothing affair.  For example, if another transaction writes
results from this intermediate transaction back into the database how
does the database know what you really want to get rid of when you
rollback.

A possible solution would be to perform a backup or use point in
time recovery to be able to get the whole database back to the state
beforehand.


  Sam