pgx v5 beta 5 available for testing

[jackc]

I'm pleased to announce the first beta release of pgx v5 is available.

The code is in branch v5-dev. There are a significant number of changes. Check out the changelog to see what's new.

Try it with:

go get github.com/jackc/pgx/v5@v5.0.0-beta.1

If no major issues are reported I plan to release v5.0.0 in September.

---

There have only been minor changes since v5.0.0-alpha.5.

• Renamed pgxpool.NewConfig() to NewWithConfig()
• Cancelling a context that caused the pgxpool to start a establishing a connection will not interrupt it.
• Added pgxpool.Pool.Reset() (and prerequisite puddle update)
• Port sslpassword support in pgconn from pgx v4
• Documentation updates
• Miscellaneous bug fixes

[drakkan]

Hello, I just tested beta1 with SFTPGo by replacing lib/pq with pgx, all tests pass, but I'm a little worried about the increase in binary size. I'm using pgx as database/sql driver:

• current binary size (go 1.19 and lib/pq 1.10.6): 36389448 bytes
• v5.0.0-alpha.1: 38133192 bytes
• v5.0.0-alpha.2: 38141384 bytes
• v5.0.0-alpha.3: 39964072 bytes
• v5.0.0-alpha.4: 40070792 bytes
• v5.0.0-beta.1: 40136328 bytes

can we at least have a binary size increase as with alpha.1/.2? I'm using pgx as database/sql driver since SFTPGo also supports MySQL/MariaDB and SQLite with the same code, so no pgx specific features are used.

Just for reference here are my changes:

diff --git a/go.mod b/go.mod
index 1c46e2f..3a1ae65 100644
--- a/go.mod
+++ b/go.mod
@@ -34,10 +34,10 @@ require (
        github.com/hashicorp/go-hclog v1.2.2
        github.com/hashicorp/go-plugin v1.4.4
        github.com/hashicorp/go-retryablehttp v0.7.1
+       github.com/jackc/pgx/v5 v5.0.0-beta.1
        github.com/jlaffaye/ftp v0.0.0-20201112195030-9aae4d151126
        github.com/klauspost/compress v1.15.9
        github.com/lestrrat-go/jwx v1.2.25
-       github.com/lib/pq v1.10.6
        github.com/lithammer/shortuuid/v3 v3.0.7
        github.com/mattn/go-sqlite3 v1.14.14
        github.com/mhale/smtpd v0.8.0
@@ -46,7 +46,7 @@ require (
        github.com/pires/go-proxyproto v0.6.2
        github.com/pkg/sftp v1.13.5
        github.com/pquerna/otp v1.3.0
-       github.com/prometheus/client_golang v1.12.2
+       github.com/prometheus/client_golang v1.13.0
        github.com/robfig/cron/v3 v3.0.1
        github.com/rs/cors v1.8.3-0.20220619195839-da52b0701de5
        github.com/rs/xid v1.4.0
@@ -114,6 +114,8 @@ require (
        github.com/hashicorp/hcl v1.0.0 // indirect
        github.com/hashicorp/yamux v0.1.1 // indirect
        github.com/inconshreveable/mousetrap v1.0.0 // indirect
+       github.com/jackc/pgpassfile v1.0.0 // indirect
+       github.com/jackc/pgservicefile v0.0.0-20200714003250-2b9c44734f2b // indirect
        github.com/jmespath/go-jmespath v0.4.0 // indirect
        github.com/klauspost/cpuid/v2 v2.1.0 // indirect
        github.com/kr/fs v0.1.0 // indirect

git diff internal/dataprovider/pgsql.go
diff --git a/internal/dataprovider/pgsql.go b/internal/dataprovider/pgsql.go
index 8bdad15..9618e9c 100644
--- a/internal/dataprovider/pgsql.go
+++ b/internal/dataprovider/pgsql.go
@@ -26,8 +26,8 @@ import (
        "strings"
        "time"
 
-       // we import lib/pq here to be able to disable PostgreSQL support using a build tag
-       _ "github.com/lib/pq"
+       // we import pgx here to be able to disable PostgreSQL support using a build tag
+       _ "github.com/jackc/pgx/v5/stdlib"
 
        "github.com/drakkan/sftpgo/v2/internal/logger"
        "github.com/drakkan/sftpgo/v2/internal/version"
@@ -189,7 +189,7 @@ func init() {
 
 func initializePGSQLProvider() error {
        var err error
-       dbHandle, err := sql.Open("postgres", getPGSQLConnectionString(false))
+       dbHandle, err := sql.Open("pgx", getPGSQLConnectionString(false))
        if err == nil {
                providerLog(logger.LevelDebug, "postgres database handle created, connection string: %#v, pool size: %v",
                        getPGSQLConnectionString(true), config.PoolSize)

Thanks for your hard work, really appreciated!

[jackc]

Glad it worked!

I've looked into the size increase and have found where it is occurring, but its not obvious to me what can or should be done about it.

It is the combination of a few things.

PostgreSQL supports a simple and an extended protocol with which to execute queries and the extended protocol has several ways it can be used. pgx supports multiple query execution modes pgx.QueryExecMode for various use cases.

In pgx's normal mode of operation it uses the extended protocol and gets the statement description before executing a statement. This means it knows the type of every parameter. This enables one of pgx's most convenient features: you can add support for encoding and decoding 3rd party types without modifying those types. e.g. https://github.com/shopspring/decimal can be used with directly without a wrapper type. This is only possible because it knows the underlying PostgreSQL type.

However, pgx also supports the simple protocol which never knows the PostgreSQL parameter types (and one of the extended protocol modes lacks parameter type knowledge as well). These modes are necessary for people behind connection pools such as PgBouncer that do not support prepared statements. In these modes, pgx needs some way of determining what type to encode the parameter into. Thus: https://pkg.go.dev/github.com/jackc/pgx/v5@v5.0.0-beta.1/pgtype#Map.RegisterDefaultPgType. It takes a Go value and associates it with the name of a PostgreSQL type. e.g.

m.RegisterDefaultPgType(int32(0), "int4")

This builds a map that is used to to decide how to encode parameters.

pgx also supports arrays. PostgreSQL arrays are extremely flexible. They support multidimensional arrays, arbitrary lower bounds, and NULL values. However, that is inconvenient for applications that don't need it (which is presumably the overwhelming majority). So pgx provides generic types Array[T] for complete PostgreSQL compatibility and FlatArray[T] for what an application probably really wants. Then for both of those there is the question of do you want the element to be a T or *T. pgx works with both.

pgx needs to register all those generic types for the execution modes that lack known parameter types. So it does this for each type:

func registerDefaultPgTypeVariants[T any](m *Map, name string) {
	arrayName := "_" + name

	var value T
	m.RegisterDefaultPgType(value, name)  // T
	m.RegisterDefaultPgType(&value, name) // *T

	var sliceT []T
	m.RegisterDefaultPgType(sliceT, arrayName)  // []T
	m.RegisterDefaultPgType(&sliceT, arrayName) // *[]T

	var slicePtrT []*T
	m.RegisterDefaultPgType(slicePtrT, arrayName)  // []*T
	m.RegisterDefaultPgType(&slicePtrT, arrayName) // *[]*T

	var arrayOfT Array[T]
	m.RegisterDefaultPgType(arrayOfT, arrayName)  // Array[T]
	m.RegisterDefaultPgType(&arrayOfT, arrayName) // *Array[T]

	var arrayOfPtrT Array[*T]
	m.RegisterDefaultPgType(arrayOfPtrT, arrayName)  // Array[*T]
	m.RegisterDefaultPgType(&arrayOfPtrT, arrayName) // *Array[*T]

	var flatArrayOfT FlatArray[T]
	m.RegisterDefaultPgType(flatArrayOfT, arrayName)  // FlatArray[T]
	m.RegisterDefaultPgType(&flatArrayOfT, arrayName) // *FlatArray[T]

	var flatArrayOfPtrT FlatArray[*T]
	m.RegisterDefaultPgType(flatArrayOfPtrT, arrayName)  // FlatArray[*T]
	m.RegisterDefaultPgType(&flatArrayOfPtrT, arrayName) // *FlatArray[*T]
}

This means that a lot of code is generated for each type pgx registers (59 at the present) regardless of if the application actually uses it. Removing these saves 2.3 MB (about 1.9 MB of which is array support).

pgx tries real hard to have the same behavior regardless of the query execution mode and succeeds in almost all cases. The only way I see to save that 2.3 MB is to move this code to a different package and require the application to perform some additional step if it planned to use one of those non-default query execution modes. It would probably be less than 10 additional lines of code for the affected applications, but it bothers me for it to not "just work" out of the box. But 2.3 MB wasted for most users bothers me to... I don't know...

[drakkan]

@jackc thanks for your detailed explanation and hard work.

I want to add a little more context.
Some SFTPGo users consider it "heavy" or "light" based on its binary size.
If I add features that require a new third party library and then the binary size increases, I get comments like this

you are adding too many features, I don't want them, SFTPGo is getting very "heavy"

Obviously the binary size does not necessarily affect the runtime speed, however to avoid comments like this I pay attention to the binary size.

The latest versions of Go (apart from 1.19) have greatly reduced the size of the SFTPGo binary, so the developers of Go seem to be paying attention too.

lib/pq is barely maintained, I'm preparing the switch to pgx from a while and I think it is inevitable in the future.

For example I added SQLState to lib/pq, so cockroach-go no longer need to import lib/pq and this avoids its inclusion in the SFTPGo binary once I'll switch to pgx.

I think that pgx should "just work" out of box. What do you think about adding a build tag (pgx<something>) to avoid the inclusion of these additional types and features?

I mean something like this

https://github.com/drakkan/sftpgo/blob/main/internal/dataprovider/pgsql_disabled.go#L15
https://github.com/drakkan/sftpgo/blob/main/internal/dataprovider/pgsql.go#L15

so users that don't need these features can decide to add this build tag and reduce the final binary size.
Do you think it will complicate the pgx code base too much?
Maybe this way we can have a binary size comparable to lib/pq.

This method works fine in SFTPGo, default build:

go build -trimpath -ldflags "-s -w -X github.com/drakkan/sftpgo/v2/internal/version.commit=`git describe --always --dirty` -X github.com/drakkan/sftpgo/v2/internal/version.date=`date -u +%FT%TZ`" -o sftpgo
ls -la sftpgo
-rwxr-xr-x 1 nicola nicola 36405960  7 ago 08.00 sftpgo

minimal build:

go build -trimpath -tags nogcs,nos3,noportable,nobolt,nomysql,nopgsql,nosqlite,nometrics,noazblob -ldflags "-s -w -X github.com/drakkan/sftpgo/v2/internal/internal/version.commit=`git describe --always --dirty` -X github.com/drakkan/sftpgo/v2/internal/version.date=`date -u +%FT%TZ`" -o sftpgo
ls -la sftpgo
-rwxr-xr-x 1 nicola nicola 23363584  7 ago 08.01 sftpgo

thanks!

[jackc]

@drakkan Build tags seem to work. just pushed 8256ab1 which adds the nopgxregisterdefaulttypes tag.

[drakkan]

@drakkan Build tags seem to work. just pushed 8256ab1 which adds the nopgxregisterdefaulttypes tag.

Thanks! I confirm that it works:

• lib/pq, sftpgo binary size: 36546720
• pgx no build tag, size: 40285376
• pgx with the above build tag, size: 38499552

we still have about 2MB more than lib/pq but it is better than before

[jackc]

Just tagged v5.0.0-beta.2.

Here are the changes since v5.0.0-beta.1:

• Fix build on Windows.
• Add build tag to eliminate default type registration code to reduce binary size.
• CopyFrom now works with string values that need to be converted to binary format.
• Fail-safe timeout for background pool connections does not override connect_timeout.
• connect_timeout applies to each connect attempt individually when multiple hosts are specified instead of the entire process. This now matches PostgreSQL libpq behavior.

[drakkan]

@drakkan Build tags seem to work. just pushed 8256ab1 which adds the nopgxregisterdefaulttypes tag.

@jackc I did a quick test using pgx v4, the sftpgo binary size is 38536672 so the new build tag offers no improvements over v4

[cristaloleg]

BTW, are there huge performance wins between v4 and v5 ?

[vikstrous2]

FYI: We ran into this bug introduced in v5: #1281

[jameshartig]

The LazyConnect option has been removed. Pools always lazily connect.

Can there be an optional method that blocks until the createIdleResources finishes? There could be a generic method Ready(context.Context) that blocks until a channel internally is closed or the context is cancelled.

We don't want to start our application accepting requests until our pool has at least MinConns.

[jackc]

BTW, are there huge performance wins between v4 and v5 ?

@cristaloleg There may be minor changes one way or another but performance should be similar in most cases.