Re: dblink: rollback transaction

John,

The example I provided was for illustrational purposes only :) The
problem that I am trying to solve is more complex. Basically, I am
trying to propagate remote data from remote tables and install it in the
local tables. I do this operation in a loop as follows:

For j IN all_tables LOOP
    1. Bring remote data from remote_tables[j] (using dblink)
    2. Insert received data in local_tables[j]
    3. Delete data from remote_table[j] (using dblink)
END LOOP

Suppose I successfully ran the first loop iteration, but the second
iteration caused step 2 to through a "duplicate key" exception. This
will cause the effects of both loop iterations to roll back. However,
only local operations (step 2), but not the remote operations (step 3)
are rolled back. This causes the data brought and installed into the
first table to be deleted locally (i.e. step 2 of the first iteration is
rolled back), but not restored remotely (i.e. step 3 of the first
iteration is NOT rolled back). Therefore, I lose data for the first
table completely both in the local and remote locations.

Is there any way to roll back a remote dblink Xaction? Does anyone have
a better solution for my problem?

Thanks.

Oleg


-----Original Message-----
From: John Sidney-Woollett [mailto:johnsw@wardbrook.com]
Sent: Thursday, February 05, 2004 12:49 AM
To: Oleg Lebedev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dblink: rollback transaction


Oleg Lebedev said:
> Is there a way to rollback a dblink transaction?
> Say, I delete some data from the remote database, but I don't want
> this to be visible untill the data is inserted in the current
> database. And if the insertion of data in the current database throws
> an error, I want to rollback the dblink transaction, which should
> restore data in its original remote location.

What about doing the insert first, and then issuing the delete via the
dblink? If the insert fails and the transaction rolls back then the
deletion in the remote database will never have been done.

John Sidney-Woollett

*************************************
This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*************************************