sqldb/v2: sync features with tapd's sqldb package
What changed, and why it matters
This commit is a routine feature-sync between two internal database packages. It adds new configuration knobs, improves retry logic, updates a Postgres driver, and adds helper functions for SQL type conversions. There is no direct evidence in the commit that this fixes an active security vulnerability; it appears to be a code-maintenance and compatibility change.
Treat as a normal maintenance commit. Review the pgx/v5 5.7.4 release notes separately to confirm whether any security advisories are addressed by the upgrade, and verify that the new `RequireSSL` option is documented and defaults safely in consuming applications.
Security signals we found
Dependency update: pgx/v5 driver upgraded from 5.5.4 to 5.7.4 (may include upstream fixes, but no specific CVE is referenced in the commit)
New `RequireSSL` Postgres config flag added (security-relevant option, but default behavior is not shown to change)
Retry logic expanded to handle deadlock errors in addition to serialization errors (resilience improvement)
Postgres test fixture upgraded from version 11 to 15 (test infrastructure only)
Evidence from the diff
The patch synchronizes sqldb/v2 with tapd’s sqldb package. Notable changes include: expanded PostgresConfig with connection-pool settings and an SSL requirement flag; new SQL error types (ErrDeadlockError, ErrSchemaError) and retry logic that handles serialization/deadlock errors; updated pgx/v5 from 5.5.4 to 5.7.4 and related dependency bumps; new SQL null-type conversion helpers; and a Postgres test fixture upgraded from version 11 to 15. The commit message frames this as a feature parity effort, not a security fix.
Changed components
sqldb/v2/config.gosqldb/v2/postgres.gosqldb/v2/sqlerrors.gosqldb/v2/interfaces.gosqldb/v2/sqlutils.gosqldb/v2/sqlite.gosqldb/v2/go.modsqldb/v2/go.sumsqldb/v2/postgres_fixture.gosqldb/v2/postgres_test.goInspect captured patch +293 / −59
diff --git a/sqldb/v2/config.go b/sqldb/v2/config.go
index 61abbb6..b7516c0 100644
--- a/sqldb/v2/config.go
+++ b/sqldb/v2/config.go
@@ -14,8 +14,16 @@ const (
// time.
defaultMaxConns = 25
- // connIdleLifetime is the amount of time a connection can be idle.
- connIdleLifetime = 5 * time.Minute
+ // defaultMaxIdleConns is the number of permitted idle connections.
+ defaultMaxIdleConns = 6
+
+ // defaultConnMaxIdleTime is the amount of time a connection can be
+ // idle before it is closed.
+ defaultConnMaxIdleTime = 5 * time.Minute
+
+ // defaultConnMaxLifetime is the maximum amount of time a connection can
+ // be reused for before it is closed.
+ defaultConnMaxLifetime = 10 * time.Minute
)
// SqliteConfig holds all the config arguments needed to interact with our
@@ -65,11 +73,15 @@ func (p *SqliteConfig) Validate() error {
//
//nolint:ll
type PostgresConfig struct {
- Dsn string `long:"dsn" description:"Database connection string."`
- Timeout time.Duration `long:"timeout" description:"Database connection timeout. Set to zero to disable."`
- MaxConnections int `long:"maxconnections" description:"The maximum number of open connections to the database. Set to zero for unlimited."`
- SkipMigrations bool `long:"skipmigrations" description:"Skip applying migrations on startup."`
- QueryConfig `group:"query" namespace:"query"`
+ Dsn string `long:"dsn" description:"Database connection string."`
+ Timeout time.Duration `long:"timeout" description:"Database connection timeout. Set to zero to disable."`
+ MaxOpenConnections int `long:"maxconnections" description:"Max open connections to keep alive to the database server. Set to zero for unlimited."`
+ MaxIdleConnections int `long:"maxidleconnections" description:"Max number of idle connections to keep in the connection pool. Set to zero for unlimited."`
+ ConnMaxLifetime time.Duration `long:"connmaxlifetime" description:"Max amount of time a connection can be reused for before it is closed. Valid time units are {s, m, h}."`
+ ConnMaxIdleTime time.Duration `long:"connmaxidletime" description:"Max amount of time a connection can be idle for before it is closed. Valid time units are {s, m, h}."`
+ RequireSSL bool `long:"requiressl" description:"Whether to require using SSL (mode: require) when connecting to the server."`
+ SkipMigrations bool `long:"skipmigrations" description:"Skip applying migrations on startup."`
+ QueryConfig `group:"query" namespace:"query"`
}
// Validate checks that the PostgresConfig values are valid.
diff --git a/sqldb/v2/go.mod b/sqldb/v2/go.mod
index 56747ef..7e987ca 100644
--- a/sqldb/v2/go.mod
+++ b/sqldb/v2/go.mod
@@ -6,7 +6,7 @@ require (
github.com/golang-migrate/migrate/v4 v4.19.0
github.com/jackc/pgconn v1.14.3
github.com/jackc/pgerrcode v0.0.0-20240316143900-6e2875d9b438
- github.com/jackc/pgx/v5 v5.5.4
+ github.com/jackc/pgx/v5 v5.7.4
github.com/lightningnetwork/lnd/fn/v2 v2.0.8
github.com/ory/dockertest/v3 v3.10.0
github.com/pmezard/go-difflib v1.0.0
@@ -16,6 +16,7 @@ require (
)
require (
+ dario.cat/mergo v1.0.2 // indirect
github.com/Azure/go-ansiterm v0.0.0-20230124172434-306776ec8161 // indirect
github.com/Microsoft/go-winio v0.6.2 // indirect
github.com/Nvveen/Gotty v0.0.0-20120604004816-cd527374f1e5 // indirect
@@ -24,26 +25,25 @@ require (
github.com/containerd/continuity v0.3.0 // indirect
github.com/containerd/errdefs v1.0.0 // indirect
github.com/containerd/errdefs/pkg v0.3.0 // indirect
- github.com/docker/cli v20.10.17+incompatible // indirect
+ github.com/docker/cli v28.1.1+incompatible // indirect
github.com/docker/docker v28.3.3+incompatible // indirect
github.com/docker/go-connections v0.5.0 // indirect
github.com/docker/go-units v0.5.0 // indirect
github.com/dustin/go-humanize v1.0.1 // indirect
+ github.com/go-viper/mapstructure/v2 v2.3.0 // indirect
github.com/gogo/protobuf v1.3.2 // indirect
github.com/google/go-cmp v0.7.0 // indirect
github.com/google/shlex v0.0.0-20191202100458-e7afc7fbc510 // indirect
github.com/google/uuid v1.6.0 // indirect
github.com/hashicorp/errwrap v1.1.0 // indirect
github.com/hashicorp/go-multierror v1.1.1 // indirect
- github.com/imdario/mergo v0.3.12 // indirect
github.com/jackc/chunkreader/v2 v2.0.1 // indirect
github.com/jackc/pgio v1.0.0 // indirect
github.com/jackc/pgpassfile v1.0.0 // indirect
github.com/jackc/pgproto3/v2 v2.3.3 // indirect
- github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
- github.com/jackc/puddle/v2 v2.2.1 // indirect
+ github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
+ github.com/jackc/puddle/v2 v2.2.2 // indirect
github.com/mattn/go-isatty v0.0.20 // indirect
- github.com/mitchellh/mapstructure v1.4.1 // indirect
github.com/moby/docker-image-spec v1.3.1 // indirect
github.com/moby/sys/user v0.3.0 // indirect
github.com/moby/term v0.5.0 // indirect
@@ -64,9 +64,8 @@ require (
golang.org/x/sync v0.15.0 // indirect
golang.org/x/sys v0.34.0 // indirect
golang.org/x/text v0.24.0 // indirect
- gopkg.in/yaml.v2 v2.3.0 // indirect
gopkg.in/yaml.v3 v3.0.1 // indirect
- modernc.org/libc v1.66.3 // indirect
+ modernc.org/libc v1.66.3 // indirect; indirectv
modernc.org/mathutil v1.7.1 // indirect
modernc.org/memory v1.11.0 // indirect
)
diff --git a/sqldb/v2/go.sum b/sqldb/v2/go.sum
index 3153a6f..113570a 100644
--- a/sqldb/v2/go.sum
+++ b/sqldb/v2/go.sum
@@ -1,3 +1,5 @@
+dario.cat/mergo v1.0.2 h1:85+piFYR1tMbRrLcDwR18y4UKJ3aH1Tbzi24VRW1TK8=
+dario.cat/mergo v1.0.2/go.mod h1:E/hbnu0NxMFBjpMIE34DRGLWqDy0g5FuKDhCb31ngxA=
github.com/Azure/go-ansiterm v0.0.0-20230124172434-306776ec8161 h1:L/gRVlceqvL25UVaW/CKtUDjefjrs0SPonmDGUVOYP0=
github.com/Azure/go-ansiterm v0.0.0-20230124172434-306776ec8161/go.mod h1:xomTg63KZ2rFqZQzSB4Vz2SUXa1BpHTVz9L5PTmPC4E=
github.com/Microsoft/go-winio v0.6.2 h1:F2VQgta7ecxGYO8k3ZZz3RS8fVIXVxONVUPlNERoyfY=
@@ -25,8 +27,8 @@ github.com/dhui/dktest v0.4.5 h1:uUfYBIVREmj/Rw6MvgmqNAYzTiKOHJak+enB5Di73MM=
github.com/dhui/dktest v0.4.5/go.mod h1:tmcyeHDKagvlDrz7gDKq4UAJOLIfVZYkfD5OnHDwcCo=
github.com/distribution/reference v0.6.0 h1:0IXCQ5g4/QMHHkarYzh5l+u8T3t73zM5QvfrDyIgxBk=
github.com/distribution/reference v0.6.0/go.mod h1:BbU0aIcezP1/5jX/8MP0YiH4SdvB5Y4f/wlDRiLyi3E=
-github.com/docker/cli v20.10.17+incompatible h1:eO2KS7ZFeov5UJeaDmIs1NFEDRf32PaqRpvoEkKBy5M=
-github.com/docker/cli v20.10.17+incompatible/go.mod h1:JLrzqnKDaYBop7H2jaqPtU4hHvMKP+vjCwu2uszcLI8=
+github.com/docker/cli v28.1.1+incompatible h1:eyUemzeI45DY7eDPuwUcmDyDj1pM98oD5MdSpiItp8k=
+github.com/docker/cli v28.1.1+incompatible/go.mod h1:JLrzqnKDaYBop7H2jaqPtU4hHvMKP+vjCwu2uszcLI8=
github.com/docker/docker v28.3.3+incompatible h1:Dypm25kh4rmk49v1eiVbsAtpAsYURjYkaKubwuBdxEI=
github.com/docker/docker v28.3.3+incompatible/go.mod h1:eEKB0N0r5NX/I1kEveEz05bcu8tLC/8azJZsviup8Sk=
github.com/docker/go-connections v0.5.0 h1:USnMq7hx7gwdVZq1L49hLXaFtUdTADjXGp+uj1Br63c=
@@ -43,6 +45,8 @@ github.com/go-logr/stdr v1.2.2 h1:hSWxHoqTgW2S2qGc0LTAI563KZ5YKYRhT3MFKZMbjag=
github.com/go-logr/stdr v1.2.2/go.mod h1:mMo/vtBO5dYbehREoey6XUKy/eSumjCCveDpRre4VKE=
github.com/go-sql-driver/mysql v1.6.0 h1:BCTh4TKNUYmOmMUcQ3IipzF5prigylS7XXjEkfCHuOE=
github.com/go-sql-driver/mysql v1.6.0/go.mod h1:DCzpHaOWr8IXmIStZouvnhqoel9Qv2LBy8hT2VhHyBg=
+github.com/go-viper/mapstructure/v2 v2.3.0 h1:27XbWsHIqhbdR5TIC911OfYvgSaW93HM+dX7970Q7jk=
+github.com/go-viper/mapstructure/v2 v2.3.0/go.mod h1:oJDH3BJKyqBA2TXFhDsKDGDTlndYOZ6rGS0BRZIxGhM=
github.com/gogo/protobuf v1.3.2 h1:Ov1cvc58UF3b5XjBnZv7+opcTcQFZebYjWzi34vdm4Q=
github.com/gogo/protobuf v1.3.2/go.mod h1:P1XiOD3dCwIKUDQYPy72D8LYyHL2YPYrpS2s69NZV8Q=
github.com/google/go-cmp v0.7.0 h1:wk8382ETsv4JYUZwIsn6YpYiWiBsYLSJiTsyBybVuN8=
@@ -58,8 +62,6 @@ github.com/hashicorp/errwrap v1.1.0 h1:OxrOeh75EUXMY8TBjag2fzXGZ40LB6IKw45YeGUDY
github.com/hashicorp/errwrap v1.1.0/go.mod h1:YH+1FKiLXxHSkmPseP+kNlulaMuP3n2brvKWEqk/Jc4=
github.com/hashicorp/go-multierror v1.1.1 h1:H5DkEtf6CXdFp0N0Em5UCwQpXMWke8IA0+lD48awMYo=
github.com/hashicorp/go-multierror v1.1.1/go.mod h1:iw975J/qwKPdAO1clOe2L8331t/9/fmwbPZ6JB6eMoM=
-github.com/imdario/mergo v0.3.12 h1:b6R2BslTbIEToALKP7LxUvijTsNI9TAe80pLWN2g/HU=
-github.com/imdario/mergo v0.3.12/go.mod h1:jmQim1M+e3UYxmgPu/WyfjB3N3VflVyUjjjwH0dnCYA=
github.com/jackc/chunkreader/v2 v2.0.0/go.mod h1:odVSm741yZoC3dpHEUXIqA9tQRhFrgOHwnPIn9lDKlk=
github.com/jackc/chunkreader/v2 v2.0.1 h1:i+RDz65UE+mmpjTfyz0MoVTnzeYxroil2G82ki7MGG8=
github.com/jackc/chunkreader/v2 v2.0.1/go.mod h1:odVSm741yZoC3dpHEUXIqA9tQRhFrgOHwnPIn9lDKlk=
@@ -75,12 +77,12 @@ github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsI
github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
github.com/jackc/pgproto3/v2 v2.3.3 h1:1HLSx5H+tXR9pW3in3zaztoEwQYRC9SQaYUHjTSUOag=
github.com/jackc/pgproto3/v2 v2.3.3/go.mod h1:WfJCnwN3HIg9Ish/j3sgWXnAfK8A9Y0bwXYU5xKaEdA=
-github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk=
-github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
-github.com/jackc/pgx/v5 v5.5.4 h1:Xp2aQS8uXButQdnCMWNmvx6UysWQQC+u1EoizjguY+8=
-github.com/jackc/pgx/v5 v5.5.4/go.mod h1:ez9gk+OAat140fv9ErkZDYFWmXLfV+++K0uAOiwgm1A=
-github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
-github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
+github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo=
+github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
+github.com/jackc/pgx/v5 v5.7.4 h1:9wKznZrhWa2QiHL+NjTSPP6yjl3451BX3imWDnokYlg=
+github.com/jackc/pgx/v5 v5.7.4/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ=
+github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo=
+github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
github.com/kisielk/errcheck v1.5.0/go.mod h1:pFxgyoBC7bSaBwPgfKdkLd5X25qrDl4LWUI2bnpBCr8=
github.com/kisielk/gotool v1.0.0/go.mod h1:XhKaO+MFFWcvkIS/tQcRk01m1F5IRFswLeQ+oQHNcck=
github.com/kr/pretty v0.3.0 h1:WgNl7dwNpEZ6jJ9k1snq4pZsg7DOEN8hP9Xw0Tsjwk0=
@@ -95,8 +97,6 @@ github.com/lightningnetwork/lnd/fn/v2 v2.0.8 h1:r2SLz7gZYQPVc3IZhU82M66guz3Zk2oY
github.com/lightningnetwork/lnd/fn/v2 v2.0.8/go.mod h1:TOzwrhjB/Azw1V7aa8t21ufcQmdsQOQMDtxVOQWNl8s=
github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWEY=
github.com/mattn/go-isatty v0.0.20/go.mod h1:W+V8PltTTMOvKvAeJH7IuucS94S2C6jfK/D7dTCTo3Y=
-github.com/mitchellh/mapstructure v1.4.1 h1:CpVNEelQCZBooIPDn+AR3NpivK/TIKU8bDxdASFVQag=
-github.com/mitchellh/mapstructure v1.4.1/go.mod h1:bFUtVrKA4DC2yAKiSyO/QUcy7e+RRV2QTWOzhPopBRo=
github.com/moby/docker-image-spec v1.3.1 h1:jMKff3w6PgbfSa69GfNg+zN/XLhfXJGnEx3Nl2EsFP0=
github.com/moby/docker-image-spec v1.3.1/go.mod h1:eKmb5VW8vQEh/BAr2yvVNvuiJuY6UIocYsFu/DxxRpo=
github.com/moby/sys/user v0.3.0 h1:9ni5DlcW5an3SvRSx4MouotOygvzaXbaSrc/wGDFWPo=
@@ -197,8 +197,6 @@ gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8
gopkg.in/check.v1 v1.0.0-20201130134442-10cb98267c6c h1:Hei/4ADfdWqJk1ZMxUNpqntNwaWcugrBjAiHlqqRiVk=
gopkg.in/check.v1 v1.0.0-20201130134442-10cb98267c6c/go.mod h1:JHkPIbrfpd72SG/EVd6muEfDQjcINNoR0C8j2r3qZ4Q=
gopkg.in/yaml.v2 v2.2.2/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
-gopkg.in/yaml.v2 v2.3.0 h1:clyUAQHOM3G0M3f5vQj7LuJrETvjVot3Z5el9nffUtU=
-gopkg.in/yaml.v2 v2.3.0/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
diff --git a/sqldb/v2/interfaces.go b/sqldb/v2/interfaces.go
index 4c9102f..d894a16 100644
--- a/sqldb/v2/interfaces.go
+++ b/sqldb/v2/interfaces.go
@@ -6,7 +6,6 @@ import (
"fmt"
"math"
"math/rand"
- prand "math/rand"
"time"
)
@@ -22,12 +21,16 @@ const (
// repetition.
DefaultNumTxRetries = 20
- // DefaultRetryDelay is the default delay between retries. This will be
- // used to generate a random delay between 0 and this value.
- DefaultRetryDelay = time.Millisecond * 50
+ // DefaultInitialRetryDelay is the default initial delay between
+ // retries. This will be used to generate a random delay between -50%
+ // and +50% of this value, so 20 to 60 milliseconds. The retry will be
+ // doubled after each attempt until we reach DefaultMaxRetryDelay. We
+ // start with a random value to avoid multiple goroutines that are
+ // created at the same time to effectively retry at the same time.
+ DefaultInitialRetryDelay = time.Millisecond * 40
// DefaultMaxRetryDelay is the default maximum delay between retries.
- DefaultMaxRetryDelay = time.Second
+ DefaultMaxRetryDelay = time.Second * 3
)
// BackendType is an enum that represents the type of database backend we're
@@ -140,23 +143,25 @@ type BatchedQuerier interface {
// executor. This can be used to do things like retry a transaction due to an
// error a certain amount of times.
type txExecutorOptions struct {
- numRetries int
- retryDelay time.Duration
+ numRetries int
+ initialRetryDelay time.Duration
+ maxRetryDelay time.Duration
}
// defaultTxExecutorOptions returns the default options for the transaction
// executor.
func defaultTxExecutorOptions() *txExecutorOptions {
return &txExecutorOptions{
- numRetries: DefaultNumTxRetries,
- retryDelay: DefaultRetryDelay,
+ numRetries: DefaultNumTxRetries,
+ initialRetryDelay: DefaultInitialRetryDelay,
+ maxRetryDelay: DefaultMaxRetryDelay,
}
}
// randRetryDelay returns a random retry delay between 0 and the configured max
// delay.
func (t *txExecutorOptions) randRetryDelay() time.Duration {
- return time.Duration(prand.Int63n(int64(t.retryDelay))) //nolint:gosec
+ return time.Duration(rand.Int63n(int64(t.maxRetryDelay))) //nolint:gosec
}
// TxExecutorOption is a functional option that allows us to pass in optional
@@ -175,7 +180,7 @@ func WithTxRetries(numRetries int) TxExecutorOption {
// to wait before a transaction is retried.
func WithTxRetryDelay(delay time.Duration) TxExecutorOption {
return func(o *txExecutorOptions) {
- o.retryDelay = delay
+ o.initialRetryDelay = delay
}
}
@@ -270,11 +275,12 @@ type OnBackoff func(retry int, delay time.Duration)
// retries is exceeded.
func ExecuteSQLTransactionWithRetry(ctx context.Context, makeTx MakeTx,
rollbackTx RollbackTx, txBody TxBody, onBackoff OnBackoff,
- numRetries int) error {
+ opts *txExecutorOptions) error {
waitBeforeRetry := func(attemptNumber int) bool {
retryDelay := randRetryDelay(
- DefaultRetryDelay, DefaultMaxRetryDelay, attemptNumber,
+ opts.initialRetryDelay, opts.maxRetryDelay,
+ attemptNumber,
)
onBackoff(attemptNumber, retryDelay)
@@ -291,14 +297,14 @@ func ExecuteSQLTransactionWithRetry(ctx context.Context, makeTx MakeTx,
}
}
- for i := 0; i < numRetries; i++ {
+ for i := 0; i < opts.numRetries; i++ {
tx, err := makeTx()
if err != nil {
dbErr := MapSQLError(err)
log.Tracef("Failed to makeTx: err=%v, dbErr=%v", err,
dbErr)
- if IsSerializationError(dbErr) {
+ if IsSerializationOrDeadlockError(dbErr) {
// Nothing to roll back here, since we haven't
// even get a transaction yet. We'll just wait
// and try again.
@@ -327,7 +333,7 @@ func ExecuteSQLTransactionWithRetry(ctx context.Context, makeTx MakeTx,
}
dbErr := MapSQLError(bodyErr)
- if IsSerializationError(dbErr) {
+ if IsSerializationOrDeadlockError(dbErr) {
if waitBeforeRetry(i) {
continue
}
@@ -348,7 +354,7 @@ func ExecuteSQLTransactionWithRetry(ctx context.Context, makeTx MakeTx,
}
dbErr := MapSQLError(commitErr)
- if IsSerializationError(dbErr) {
+ if IsSerializationOrDeadlockError(dbErr) {
if waitBeforeRetry(i) {
continue
}
@@ -405,8 +411,7 @@ func (t *TransactionExecutor[Q]) ExecTx(ctx context.Context,
}
return ExecuteSQLTransactionWithRetry(
- ctx, makeTx, rollbackTx, execTxBody, onBackoff,
- t.opts.numRetries,
+ ctx, makeTx, rollbackTx, execTxBody, onBackoff, t.opts,
)
}
diff --git a/sqldb/v2/postgres.go b/sqldb/v2/postgres.go
index 8dcbdad..3b0255a 100644
--- a/sqldb/v2/postgres.go
+++ b/sqldb/v2/postgres.go
@@ -9,7 +9,7 @@ import (
"time"
pgx_migrate "github.com/golang-migrate/migrate/v4/database/pgx/v5"
- _ "github.com/golang-migrate/migrate/v4/source/file" // Read migrations from files. // nolint:ll
+ _ "github.com/golang-migrate/migrate/v4/source/file"
_ "github.com/jackc/pgx/v5"
"github.com/lightningnetwork/lnd/fn/v2"
)
@@ -33,6 +33,7 @@ var (
// avoid replacing words which just have the word "TIMESTAMP" in
// them.
"TIMESTAMP": " TIMESTAMP WITHOUT TIME ZONE",
+ "UNHEX": "DECODE",
}
// Make sure PostgresStore implements the MigrationExecutor interface.
@@ -122,13 +123,29 @@ func NewPostgresStore(cfg *PostgresConfig) (*PostgresStore, error) {
}
maxConns := defaultMaxConns
- if cfg.MaxConnections > 0 {
- maxConns = cfg.MaxConnections
+ if cfg.MaxOpenConnections > 0 {
+ maxConns = cfg.MaxOpenConnections
+ }
+
+ maxIdleConns := defaultMaxIdleConns
+ if cfg.MaxIdleConnections > 0 {
+ maxIdleConns = cfg.MaxIdleConnections
+ }
+
+ connMaxLifetime := defaultConnMaxLifetime
+ if cfg.ConnMaxLifetime > 0 {
+ connMaxLifetime = cfg.ConnMaxLifetime
+ }
+
+ connMaxIdleTime := defaultConnMaxIdleTime
+ if cfg.ConnMaxIdleTime > 0 {
+ connMaxIdleTime = cfg.ConnMaxIdleTime
}
db.SetMaxOpenConns(maxConns)
- db.SetMaxIdleConns(maxConns)
- db.SetConnMaxLifetime(connIdleLifetime)
+ db.SetMaxIdleConns(maxIdleConns)
+ db.SetConnMaxLifetime(connMaxLifetime)
+ db.SetConnMaxIdleTime(connMaxIdleTime)
return &PostgresStore{
cfg: cfg,
diff --git a/sqldb/v2/postgres_fixture.go b/sqldb/v2/postgres_fixture.go
index 7a4a86f..03c9a17 100644
--- a/sqldb/v2/postgres_fixture.go
+++ b/sqldb/v2/postgres_fixture.go
@@ -23,7 +23,7 @@ const (
testPgUser = "test"
testPgPass = "test"
testPgDBName = "test"
- PostgresTag = "11"
+ PostgresTag = "15"
)
// TestPgFixture is a test fixture that starts a Postgres 11 instance in a
@@ -37,7 +37,7 @@ type TestPgFixture struct {
}
// NewTestPgFixture constructs a new TestPgFixture starting up a docker
-// container running Postgres 11. The started container will expire in after
+// container running Postgres 15. The started container will expire in after
// the passed duration.
func NewTestPgFixture(t testing.TB, expiry time.Duration) *TestPgFixture {
// Use a sensible default on Windows (tcp/http) and linux/osx (socket)
@@ -124,6 +124,10 @@ func (f *TestPgFixture) TearDown(t testing.TB) {
require.NoError(t, err, "Could not purge resource")
}
+func (f *TestPgFixture) DB() *sql.DB {
+ return f.db
+}
+
// RandomDBName generates a random database name.
func RandomDBName(t testing.TB) string {
randBytes := make([]byte, 8)
diff --git a/sqldb/v2/postgres_test.go b/sqldb/v2/postgres_test.go
index 6f94699..6557512 100644
--- a/sqldb/v2/postgres_test.go
+++ b/sqldb/v2/postgres_test.go
@@ -1,5 +1,4 @@
//go:build test_db_postgres
-// +build test_db_postgres
package sqldb
diff --git a/sqldb/v2/sqlerrors.go b/sqldb/v2/sqlerrors.go
index 5972991..9f9151c 100644
--- a/sqldb/v2/sqlerrors.go
+++ b/sqldb/v2/sqlerrors.go
@@ -95,6 +95,27 @@ func parseSqliteError(sqliteErr *sqlite.Error) error {
DBError: sqliteErr,
}
+ // A write operation could not continue because of a conflict within the
+ // same database connection.
+ case sqlite3.SQLITE_LOCKED, sqlite3.SQLITE_BUSY_SNAPSHOT:
+ return &ErrDeadlockError{
+ DbError: sqliteErr,
+ }
+
+ // Generic error, need to parse the message further.
+ case sqlite3.SQLITE_ERROR:
+ errMsg := sqliteErr.Error()
+
+ switch {
+ case strings.Contains(errMsg, "no such table"):
+ return &ErrSchemaError{
+ DbError: sqliteErr,
+ }
+
+ default:
+ return fmt.Errorf("unknown sqlite error: %w", sqliteErr)
+ }
+
default:
return fmt.Errorf("unknown sqlite error: %w", sqliteErr)
}
@@ -130,6 +151,12 @@ func parsePostgresError(pqErr *pgconn.PgError) error {
DBError: pqErr,
}
+ // Handle schema error.
+ case pgerrcode.UndefinedColumn, pgerrcode.UndefinedTable:
+ return &ErrSchemaError{
+ DbError: pqErr,
+ }
+
default:
return fmt.Errorf("unknown postgres error: %w", pqErr)
}
@@ -168,3 +195,53 @@ func IsSerializationError(err error) bool {
var serializationError *ErrSerializationError
return errors.As(err, &serializationError)
}
+
+// ErrDeadlockError is an error type which represents a database agnostic
+// error where transactions have led to cyclic dependencies in lock acquisition.
+type ErrDeadlockError struct {
+ DbError error
+}
+
+// Unwrap returns the wrapped error.
+func (e ErrDeadlockError) Unwrap() error {
+ return e.DbError
+}
+
+// Error returns the error message.
+func (e ErrDeadlockError) Error() string {
+ return e.DbError.Error()
+}
+
+// IsDeadlockError returns true if the given error is a deadlock error.
+func IsDeadlockError(err error) bool {
+ var deadlockError *ErrDeadlockError
+ return errors.As(err, &deadlockError)
+}
+
+// IsSerializationOrDeadlockError returns true if the given error is either a
+// deadlock error or a serialization error.
+func IsSerializationOrDeadlockError(err error) bool {
+ return IsDeadlockError(err) || IsSerializationError(err)
+}
+
+// ErrSchemaError is an error type which represents a database agnostic error
+// that the schema of the database is incorrect for the given query.
+type ErrSchemaError struct {
+ DbError error
+}
+
+// Unwrap returns the wrapped error.
+func (e ErrSchemaError) Unwrap() error {
+ return e.DbError
+}
+
+// Error returns the error message.
+func (e ErrSchemaError) Error() string {
+ return e.DbError.Error()
+}
+
+// IsSchemaError returns true if the given error is a schema error.
+func IsSchemaError(err error) bool {
+ var schemaError *ErrSchemaError
+ return errors.As(err, &schemaError)
+}
diff --git a/sqldb/v2/sqlite.go b/sqldb/v2/sqlite.go
index 4775afc..f258e78 100644
--- a/sqldb/v2/sqlite.go
+++ b/sqldb/v2/sqlite.go
@@ -5,14 +5,14 @@ package sqldb
import (
"database/sql"
"fmt"
- "github.com/golang-migrate/migrate/v4"
- "github.com/lightningnetwork/lnd/fn/v2"
"net/url"
"path/filepath"
"testing"
"time"
+ "github.com/golang-migrate/migrate/v4"
sqlite_migrate "github.com/golang-migrate/migrate/v4/database/sqlite"
+ "github.com/lightningnetwork/lnd/fn/v2"
"github.com/stretchr/testify/require"
_ "modernc.org/sqlite" // Register relevant drivers.
)
@@ -141,7 +141,7 @@ func NewSqliteStore(cfg *SqliteConfig, dbPath string) (*SqliteStore, error) {
db.SetMaxOpenConns(defaultMaxConns)
db.SetMaxIdleConns(defaultMaxConns)
- db.SetConnMaxLifetime(connIdleLifetime)
+ db.SetConnMaxLifetime(defaultConnMaxLifetime)
s := &SqliteStore{
Config: cfg,
@@ -336,6 +336,29 @@ func NewTestSqliteDB(t testing.TB, sets []MigrationSet) *SqliteStore {
return sqlDB
}
+// NewTestSqliteDBFromPath is a helper function that creates a SQLite database
+// for testing from a given database file path.
+func NewTestSqliteDBFromPath(t *testing.T, dbPath string,
+ sets []MigrationSet) *SqliteStore {
+
+ t.Helper()
+
+ t.Logf("Creating new SQLite DB for testing, using DB path %s", dbPath)
+
+ sqlDB, err := NewSqliteStore(&SqliteConfig{
+ SkipMigrations: false,
+ }, dbPath)
+ require.NoError(t, err)
+
+ require.NoError(t, ApplyAllMigrations(sqlDB, sets))
+
+ t.Cleanup(func() {
+ require.NoError(t, sqlDB.DB.Close())
+ })
+
+ return sqlDB
+}
+
// NewTestSqliteDBWithVersion is a helper function that creates an SQLite
// database for testing and migrates it to the given version.
func NewTestSqliteDBWithVersion(t *testing.T, set MigrationSet,
diff --git a/sqldb/v2/sqlutils.go b/sqldb/v2/sqlutils.go
index ce99eb6..c4bcd0e 100644
--- a/sqldb/v2/sqlutils.go
+++ b/sqldb/v2/sqlutils.go
@@ -4,9 +4,16 @@ import (
"database/sql"
"time"
+ "github.com/lightningnetwork/lnd/fn/v2"
"golang.org/x/exp/constraints"
)
+var (
+ // MaxValidSQLTime is the maximum valid time that can be rendered as a
+ // time string and can be used for comparisons in SQL.
+ MaxValidSQLTime = time.Date(9999, 12, 31, 23, 59, 59, 999999, time.UTC)
+)
+
// NoOpReset is a no-op function that can be used as a default
// reset function ExecTx calls.
var NoOpReset = func() {}
@@ -35,6 +42,29 @@ func SQLInt32[T constraints.Integer](num T) sql.NullInt32 {
}
}
+// SQLPtrInt32 turns a pointer to a numerical integer type into the NullInt32
+// that sql/sqlc uses.
+func SQLPtrInt32[T constraints.Integer](num *T) sql.NullInt32 {
+ if num == nil {
+ return sql.NullInt32{}
+ }
+ return sql.NullInt32{
+ Int32: int32(*num),
+ Valid: true,
+ }
+}
+
+// SqlOptInt32 turns an option of a numerical integer type into the NullInt32
+// that sql/sqlc uses when an integer field can be permitted to be NULL.
+func SqlOptInt32[T constraints.Integer](num fn.Option[T]) sql.NullInt32 {
+ return fn.MapOptionZ(num, func(num T) sql.NullInt32 {
+ return sql.NullInt32{
+ Int32: int32(num),
+ Valid: true,
+ }
+ })
+}
+
// SQLInt64 turns a numerical integer type into the NullInt64 that sql/sqlc
// uses when an integer field can be permitted to be NULL.
//
@@ -47,6 +77,27 @@ func SQLInt64[T constraints.Integer](num T) sql.NullInt64 {
}
}
+// SQLPtrInt64 turns a pointer to a numerical integer type into the NullInt64
+// that sql/sqlc uses.
+func SQLPtrInt64[T constraints.Integer](num *T) sql.NullInt64 {
+ if num == nil {
+ return sql.NullInt64{}
+ }
+ return sql.NullInt64{
+ Int64: int64(*num),
+ Valid: true,
+ }
+}
+
+// SqlBool turns a boolean into the NullBool that sql/sqlc uses when a boolean
+// field can be permitted to be NULL.
+func SqlBool(b bool) sql.NullBool {
+ return sql.NullBool{
+ Bool: b,
+ Valid: true,
+ }
+}
+
// SQLStr turns a string into the NullString that sql/sqlc uses when a string
// can be permitted to be NULL.
//
@@ -84,9 +135,58 @@ func SQLTime(t time.Time) sql.NullTime {
}
}
+// ExtractSqlInt64 turns a NullInt64 into a numerical type. This can be useful
+// when reading directly from the database, as this function handles extracting
+// the inner value from the "option"-like struct.
+func ExtractSqlInt64[T constraints.Integer](num sql.NullInt64) T {
+ return T(num.Int64)
+}
+
+// ExtractSqlInt64Ptr turns a NullInt64 into a pointer to a numerical type.
+func ExtractSqlInt64Ptr[T constraints.Integer](num sql.NullInt64) *T {
+ if !num.Valid {
+ return nil
+ }
+ val := T(num.Int64)
+ return &val
+}
+
+// ExtractSqlInt32 turns a NullInt32 into a numerical type. This can be useful
+// when reading directly from the database, as this function handles extracting
+// the inner value from the "option"-like struct.
+func ExtractSqlInt32[T constraints.Integer](num sql.NullInt32) T {
+ return T(num.Int32)
+}
+
+// ExtractSqlInt32Ptr turns a NullInt32 into a pointer to a numerical type.
+func ExtractSqlInt32Ptr[T constraints.Integer](num sql.NullInt32) *T {
+ if !num.Valid {
+ return nil
+ }
+ val := T(num.Int32)
+ return &val
+}
+
+// ExtractOptSqlInt32 turns a NullInt32 into an option of a numerical type.
+func ExtractOptSqlInt32[T constraints.Integer](num sql.NullInt32) fn.Option[T] {
+ if !num.Valid {
+ return fn.None[T]()
+ }
+
+ result := T(num.Int32)
+ return fn.Some(result)
+}
+
// ExtractSqlInt16 turns a NullInt16 into a numerical type. This can be useful
// when reading directly from the database, as this function handles extracting
// the inner value from the "option"-like struct.
func ExtractSqlInt16[T constraints.Integer](num sql.NullInt16) T {
return T(num.Int16)
}
+
+// ExtractBool turns a NullBool into a boolean. This can be useful when reading
+// directly from the database, as this function handles extracting the inner
+// value from the "option"-like struct.
+func ExtractBool(b sql.NullBool) bool {
+ return b.Bool
+}
Why this scored 17/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.