Lazy transaction BEGIN

[redbaron]

Is your feature request related to a problem? Please describe.
On a high latency connections every additional roundtrip between client and server is undesirable. Conn.BeginTx starts transaction by Conn.Execing BEGIN command, which internally waits for result. This wait time is a wasteful and app can't do much about it.

Describe the solution you'd like
Delay BEGIN and sendit it together with first query in transaction, essentially making it lazy. Alternatively utilize Pipeline/Batch to send it in BeginTx, but don't wait for it's result until first query in transaction is issued.

Describe alternatives you've considered
App can chose to use Batch and manually roll BEGIN/COMMIT into the batch, but then it loses convenience of Tx API.

[jackc]

👍 for the idea.

But not sure about what interface would make sense or exactly how to implement it. This behavior might need to be opt-in. It might be tricky to transparently implement lazy behavior if the first action is Tx.SendBatch or Tx.LargeObjects for example.

Also, if minimizing round trips are critical, then the commit is just as big a problem. Ideally, there would be a way to bundle the final query and the commit together as well.

[redbaron]

But not sure about what interface would make sense or exactly how to implement it.

BeginTx creates Batch with one command: BEGIN and saves BatchResult in Tx. On the first query attempt it calls BatchResult.Close() and proceeds to executing query as usual.

It might be tricky to transparently implement lazy behavior if the first action is Tx.SendBatch or Tx.LargeObjects for example.

I am not familiar with protocol or pgx internals, why do you think it might be tricky?

This behavior might need to be opt-in.
Less knobs is better. Only user visible change should be that now Query can return errors related to transaction creation. AFAIK correct use of BEGIN never fails I don't think any app will see this change. You have support large userbase so maybe knob is a safer bet.

[redbaron]

Also, if minimizing round trips are critical, then the commit is just as big a problem. Ideally, there would be a way to bundle the final query and the commit together as well.

We can add special query flag like QueryCommit which when present changes behaviour like following:

• query is added to new Batch together with COMMIT. BatchResult is saved in Tx
• batchresult.Exec() is called and result is returned as result to app
• on Tx.Commit() batchresult.Exec() & batchresult.Close() is called and result from Exec it returned to the app

[jackc]

But not sure about what interface would make sense or exactly how to implement it.

BeginTx creates Batch with one command: BEGIN and saves BatchResult in Tx. On the first query attempt it calls BatchResult.Close() and proceeds to executing query as usual.

It might be tricky to transparently implement lazy behavior if the first action is Tx.SendBatch or Tx.LargeObjects for example.

I am not familiar with protocol or pgx internals, why do you think it might be tricky?

Tx.LargeObjects doesn't return an error. The returned object would need to be modified to store the error caused by BeginTx and to return it on any call that can return an error. Tx.SendBatch is tricky because their would already be a batch in progress from the BeginTx. So it would need to append instead of creating a new batch, then modify the results such that the calling code didn't see the first batch item (begin).

There are similar issues with Query and QueryRow, but there is already some infrastructure in place for the inserting of an error into Rows so it probably wouldn't be difficult.

Less knobs is better. Only user visible change should be that now Query can return errors related to transaction creation. AFAIK correct use of BEGIN never fails I don't think any app will see this change. You have support large userbase so maybe knob is a safer bet.

In most cases this is true, but not all. For example:

tx, _ := conn.BeginTx(...)
tx.Conn().Exec(...)

or

tx, _ := conn.BeginTx(...)
conn.Exec(...)

In the current system, both the Exec calls run in the transaction. In this new model neither would. Neither of those examples may be good ideas, but they both work now.

---

If this is to be done, I would picture a new function to start a lazy transaction. The lazy transaction type would panic if the first call was not a Query, QueryRow, or Exec. The function would return a LazyTx that had extra methods like QueryAndCommit, QueryRowAndCommit, and ExecAndCommit to batch the final query and the commit.

To be honest, I'm not sure if it is worth the additional complexity. But since Tx, Rows, and Row are interfaces, it should be possible to entirely implement this outside of pgx.

---

Also, I'm not sure that using a batch (or even pipeline mode) directly should be discounted. Perhaps if there were some more helper methods or functions to make it easier to use. Since you want a Tx, maybe a function that constructs one without actually sending the begin query.

[redbaron]

Tx.LargeObjects doesn't return an error. The returned object would need to be modified to store the error caused by BeginTx and to return it on any call that can return an error.

You get it for free as Tx passes self when constructs LargeObjects.

Tx.SendBatch is tricky because their would already be a batch in progress from the BeginTx

What you described is tricky, but doable without much of a change of BatchResult logic.

tx, _ := conn.BeginTx(...)
tx.Conn().Exec(...)

Conn() is like Query, it waits for BEGIN batch to close

tx, _ := conn.BeginTx(...)
conn.Exec(...)

This can be solved if BEGIN batch was owned by Conn, which any other abstractions above it , like Tx, BatchResult or LargeObjects has access to internally. So instead of Tx owning the BEGIN batch, all the logic described above is moved about closing batch on first command moves to Conn. It will also solve BatchResult tricky case above for "free".

The function would return a LazyTx that had extra methods like QueryAndCommit, QueryRowAndCommit, and ExecAndCommit to batch the final query and the commit.

My idea was to find a way to bring this optimization without major refactoring. Probably not many apps were started development knowing exactly upfront that DB latency can be significant factor in their performance. If it is disovered much later, it would be nice to bring perf improvement with just a config change.

But since Tx, Rows, and Row are interfaces, it should be possible to entirely implement this outside of pgx.

That's an interesting idea. Do you think it will be possible to somehow customize class returns by Conn.BeginTx ?
Something like. a hook on ConnConfig:

OnTxBegin func(tx Tx) (Tx, error)

To intercept and possibly replace any Tx object before it is returned to the app.

If tracking transaction status in Conn is an idea you open to, then to implement it outside of pgx, we'll probably need a callback on Conn to execute any time it is about to send query/command.

[jackc]

Conn() is like Query, it waits for BEGIN batch to close

But a pgx.Conn has a large interface. For example, what if the first thing done in the transaction was something on the underlying pgconn.PgConn? e.g. tx.Conn().PgConn().CopyFrom()

I don't think it is possible to handle all cases, and even what could be handled beyond Query, QueryRow, and Exec could have significant complexity.

---

There is significant underlying complexity to making this behavior transparent. Even Exec, which should be the easiest to transparently bundle with a begin is tricky. An Exec with no arguments is executed with the simple protocol. That means that it is acceptable to send a string with multiple statements. That doesn't work with the extended protocol. That means there would need to be multiple paths just for Exec inside the begin has been queued but not sent path.

I really think the only feasible approach for this is what I described earlier:

If this is to be done, I would picture a new function to start a lazy transaction. The lazy transaction type would panic if the first call was not a Query, QueryRow, or Exec.

Example usage:

tx := txlazytx.BeginTx(conn)
tx.Query(...)

Something like that or helpers around batch or pipeline mode.

I don't think this can be transparently implemented. It would entail a significant increase in internal complexity in pgx and it would entail behavior changes in the edge cases such that it wouldn't be safe to enable for an application all at once.

[timofurrer]

Since you want a Tx, maybe a function that constructs one without actually sending the begin query.

@jackc I came looking for exactly this, so I prototyped that suggestion. Posting the design and measurements before opening a PR, in case you want a different shape.

Motivation. We run a sharded workload where every unit of work opens a short transaction that begins with a fencing token check. Under saturation in our test environment, the client-side idle window between BEGIN and the next statement accounts for a single-digit percentage of total database backend capacity: pure network latency, held while occupying a scarce pooler backend. Folding the check into the BEGIN statement is not an option, because we depend on each command taking its own snapshot as it starts, which a batch gives us and a single folded statement does not.

We can already send [begin, fenceCheck] as one SendBatch on a *pgxpool.Conn. What we cannot do is get a pgx.Tx back afterwards, so the rest of the unit of work loses transaction bookkeeping (ErrTxClosed, safe Rollback after Commit, ErrTxCommitRollback, savepoint nesting). Reimplementing that outside pgx in a correctness-critical path is not something I want to do.

Proposal. Two small exports, no behavior change to existing paths:

// BeginSQL returns the SQL statement that Conn.BeginTx would send for txOptions.
func (txOptions TxOptions) BeginSQL() string

// TxFromCurrentTransaction returns a Tx for the transaction c is already in,
// without sending a begin query. Only TxOptions.CommitQuery is read; the
// isolation level, access mode and deferrable mode must already have been
// established by the begin query the caller sent. Returns an error if c is not
// currently in a transaction.
func (c *Conn) TxFromCurrentTransaction(txOptions TxOptions) (Tx, error)

BeginSQL matters because without it callers have to hand-roll the begin statement and silently drift from whatever beginSQL() does. The guard accepts transaction status 'T' and 'E', so a batch that failed partway still yields a Tx the caller can roll back. Naming is entirely up for grabs.

Call site:

b := &pgx.Batch{}
b.Queue(txOptions.BeginSQL())
b.Queue(fenceCheck, shardID, fenceID).QueryRow(func(row pgx.Row) error { return row.Scan(new(int)) })
batchErr := conn.SendBatch(ctx, b).Close()

tx, err := conn.Conn().TxFromCurrentTransaction(txOptions)

Measurements. Postgres 17, statement cache warm, counting client socket flushes with a wrapping DialFunc. A minimal transaction of begin + fence check + one statement of work + commit:

baseline (hit)               flushes=4
batched  (hit)               flushes=3
baseline (fenced out)        flushes=3
batched  (fenced out)        flushes=3

Also confirmed that begin isolation level read committed queued in a Batch executes fine over the extended protocol, that transaction status is 'T' once BatchResults.Close() returns, and that current_setting('transaction_isolation') inside the resulting Tx reflects the queued begin. go test -run 'Tx|Transaction|Batch' passes against the patch.

On the commit side. You noted earlier that COMMIT is the symmetric problem, and this proposal does not address it. A caller can queue commit in the final batch, but nothing can tell the Tx it is already closed, so the customary defer tx.Rollback() spends the round trip that was just saved. That seems to need its own affordance and I would rather not conflate the two.

Happy to open a PR with tests if the shape looks right to you.

Edit: ended up pushing my prototype code as draft PR in case it helps with the discussion: #2637