Re: One transaction and several processes

On 07/18/2018 05:31 PM, Valery Kuzmin wrote:
> Hi,
>
> Can multiple processes participate in a single transaction's execution?
> I need to do the following actions sequence:
> 1. Calling service begins transaction. At this step transaction
> manager generate new XID.
> 2. Calling service updates some data.
> 3. Need to detach a transaction from a service context before other
> service is called.
> 4. Called service resume transaction using XID.
> 5. Called service updates its data.
> 6. Calling service gets control, resume transaction using XID and does commit.
> How can the third and fourth step be implemented?
>

It's not very clear what you mean by "detaching" (and more importantly
why you want this at all). PostgreSQL certainly does not support handing
over regular XID to some other connection, or even keeping it active
after the connection closes.

There are two approaches how you might achieve something similar:

1) proxy - Instead of communicating directly with the database, the
services may communicate with a proxy that handles the actual database
connection and communicates with the connection.

2) 2PC - Both services may do their own "private" transaction, but
confirm it using a two-phase commit. This is more expensive than plain
transactions and may not have exactly the same behavior, but it does
provide the durability guarantees etc.

FWIW this question is for pgsql-general rather than pgsql-hackers.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services