Re: 2PC w/ dblink

On 11/11/10 5:17 AM, Vick Khera wrote:
> On Wed, Nov 10, 2010 at 12:39 PM, John R Pierce<pierce(at)hogranch(dot)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.

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.