Thread: 2PC w/ dblink

We have an application that is using dblink from within trigger
procedures to send data to other postgres databases.   Its implementing
a highly application specific sort of data replication.

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?

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.

On 11/11/10 5:17 AM, Vick Khera wrote:
> 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.


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.