Hi, thanks for the PR, are there any performance improvements in benchmarks?

I like the direction this is going, but I am concerned about the change in the CopyFrom() signature. It won't directly break any calling code, but it does break any interfaces defined with Conn, Tx, etc. in mind. e.g.

type DB interface {
	Query(ctx context.Context, sql string, optionsAndArgs ...interface{}) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, optionsAndArgs ...interface{}) pgx.Row
	CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
}

An application or library using interface DB would break.

Would it be sufficient to use the default query exec mode without the ability to override? AFAIK, overriding the mode per query is extremely rare. I only included it on Query, QueryRow, and Exec for completeness and testing.

If it really needs to be controlled per call, perhaps a new optional interface could be created that rowSrc could implement. This interface could have a method to get the query exec mode. A wrapper struct could be created that wrapped the original CopyFromSource and added the new method.

@jackc I really need to override it to avoid the the prepare statement and the cache 😅

We are using pgx in timescale/promscale to ingest metrics and traces, we insert the data using COPY. Basically we have a table per metric, and our COPY operations are always of the same 3 columns.

We want to get rid of the extra roundtrip per copy. In our use case the cache would be a lot of unnecessary memory since we are always sending (time, int64 and float64), which the Type map resolves correctly.

If it really needs to be controlled per call, perhaps a new optional interface could be created that rowSrc could implement. This interface could have a method to get the query exec mode. A wrapper struct could be created that wrapped the original CopyFromSource and added the new method.

I'll try to give ☝️ a shot.

I'm going to try our use case with allowing setting the Exec mode only via ConnConfig.DefaultQueryExecMode.

I reverted the signature changes to CopyFrom.

This is the benchmark comparison. One thing, this is running against a DB listening on localhost, meaning that this does not account for the extra latency of having to go to connect to an external instance:

λ ➜ benchstat no_cache.txt with_cache.txt
name                      old time/op  new time/op  delta
Write5RowsViaCopy-10       152µs ± 4%   122µs ± 3%  -19.56%  (p=0.008 n=5+5)
Write10RowsViaCopy-10      165µs ± 5%   134µs ± 1%  -18.65%  (p=0.008 n=5+5)
Write100RowsViaCopy-10     392µs ± 3%   379µs ± 7%     ~     (p=0.310 n=5+5)
Write1000RowsViaCopy-10   3.53ms ±12%  3.43ms ± 8%     ~     (p=0.841 n=5+5)
Write10000RowsViaCopy-10  14.9ms ± 3%  14.6ms ± 3%     ~     (p=0.222 n=5+5)

LGTM! Thanks!