Add support for PREPARE TRANSACTION (2PC)

[momilo]

(apologies for using bug type, but could not select other types)

Context

Postgres officially supports PREPARE TRANSACTION 'foobar' finishing statement, along the (more commonly used) COMMIT and ROLLBACK. It allows for the preparation of the current transaction for later commitment via a 2PC process (COMMIT PREPARED).

It would be great if jackc/pgx could support the above, via an additional method on its Tx interface (e.g. PrepareTx(), to differentiate from Prepare which is used for prepared statements).

Potential solutions

The implementation should be fairly simple (on both the standard and pool connections), almost mirroring the current Commit implementation, with the only change in the actual instruction submitted, and an additional argument (transaction name) being passed.

Currently available workarounds

It is possible to achieve a somewhat similar functionality by doing the following:

tx, err := dbPool.Begin(ctx)
tx.Exec(ctx, <do things here>)
tx.Exec(ctx, `PREPARE TRANSACTION 'my-transaction';`)
tx.Conn().Close(ctx)

The problem (apart from the above not being very elegant and not marking tx.closed = True) - it is not possible to do tx.c.Release(), releasing the connection to the pool. Hence, when using database connection pools, each transaction will effectively close a connection (most likely inviting the pool to re-establish it).

Steps forward

I would be happy to prepare a PR implementing the above, if the maintainers would be interested. This would be very helpful and unblock a number of large projects I and my colleagues are contributing towards. Thank you in advance for your kind help.

[jackc]

In the existing system, what happens if after calling PREPARE TRANSACTION you call Commit() or Rollback(). I would expect those to be no-ops. Would that do what you need?

If not, I'm open to adding explicit support. But I'm not sure about adding PrepareTx to the Tx interface. pgx uses savepoints to simulate nested transactions. What happens when PrepareTx is called on a nested transaction?

[momilo]

Hi, I have tested your suggestion and can confirm that it works:

tx.Exec(ctx, `PREPARE TRANSACTION 'something';`)
tx.Commit()

The Commit returns nil error (DB technically returns a warning "no transaction in progress"). Most importantly, it does correctly release the connection back to the pool (i.e. there remains a required connection after "PREPARE TRANSACTION" invocation, but after the de-facto no-op Commit() invocation it is released and is correctly marked as idle in stats).

Given the above, I believe we can close this comment and there is no need to add the extra functionality to the library, if you do not want to expose it explicitly.

(As a side note - thank you very much for your responsiveness!)