On Wed, Nov 10, 2010 at 12:39 PM, John R Pierce <pierce@hogranch.com> 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.