On 11/11/10 5:17 AM, Vick Khera wrote:
On Wed, Nov 10, 2010 at 12:39 PM, John R Pierce<pierce@xxxxxxxxxxxx> wrote:
My developers are complaining about the lack of support for 2 phase commit
in this scenario. Can we get any mileage on PREPARE TRANSACTION in a
dblink sort of environment like this?
Yes, that's an ideal case for this. We use it outside of dblink with
two direct connections to two databases. Just be sure you have some
monitoring that will alert you to prepared transactions that are
lingering for a long time. Also, if you have a pending prepared
transaction on a host with a newly inserted row, and you retry
inserting that row from another connection, you will get an immediate
"statement timeout" error. This is a bit confusing at first but once
you know what the cause is, it is easy to work with.
I guess my question really is, can a pl/pgsql trigger procedure that in
turn is using dblink to talk to another database use BEGIN, PREPARE
TRANSACTION, and COMMIT PREPARED a transaction over the dblink ?
afaik, this code currently isn't using a remote transaction at all, its
just doing simple INSERT or UPDATE on the remote database.
and how does that interact with the parent transaction on the local
server? I'm pretty sure our trigger can't exactly do the PREPARE
TRANSACTION from within the trigger procedure.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
