github.com/jackc/pgx/v5 v5.10.0: Configure pgx v5 query execution modes, statement caching, pool options, sslmodes, and environment variables without pgx v4 prefer_simple_protocol parameter leakage or runtime connection rejection

# Notes

## CSX Network Search Result
`search_known_solution` returned existing sample `sha256:4eb6eee48a60e79da9dcf4055256991997f094a1da19ee17cfe0697e6d6d7b32` for `github.com/jackc/pgx/v5` covering struct row scanning (`RowToStructByName`/`RowToStructByNameLax`) and `pgxpool.Conn.Release` safety. We author a completely different contract targeting pgx v5 connection string configuration, `default_query_exec_mode` vs v4 `prefer_simple_protocol`, statement caching, pool parameter extraction differences between `pgxpool` and `pgx`, `sslmode` fallback hierarchy, and environment variable precedence.

## What a Model Would Have Written Instead
A naive model trained on pgx v4 would write `prefer_simple_protocol=true` in connection strings to support transaction poolers like PgBouncer, pass `pool_max_conns=10` to `pgx.ParseConfig`, and assume `sslmode=prefer` attempts unencrypted TCP before TLS.

## How the Wrong Version Fails
Loudly at runtime startup: PostgreSQL immediately rejects connection handshake with `FATAL: unrecognized configuration parameter "prefer_simple_protocol"` (or `"pool_max_conns"`) because pgx v5 treats unrecognized URL query parameters as PostgreSQL server runtime parameters (`RuntimeParams`) rather than configuring client-side execution modes, while `DefaultQueryExecMode` silently stays at `QueryExecModeCacheStatement`.

{"case":{"caseId":"case:sha256:5af892b259f2f7bcd7cffedbecdf6eb71bf608e9cec4e4ec51dcd5491173f9fb","contract":["assert prefer_simple_protocol does not set query exec mode in pgx v5 and leaks into RuntimeParams","assert default_query_exec_mode parses valid modes and excludes them from RuntimeParams","assert statement_cache_capacity=0 disables cache and is excluded from RuntimeParams","assert pgxpool.ParseConfig extracts pool_* parameters while pgx.ParseConfig leaves them in RuntimeParams","assert sslmode controls TLSConfig and Fallbacks hierarchy across disable, allow, prefer, require, verify-ca, and verify-full","assert ParseConfig(\"\") resolves PG* environment variables and explicit connection parameters override them"],"goal":"Configure pgx v5 query execution modes, statement caching, pool options, sslmodes, and environment variables without pgx v4 prefer_simple_protocol parameter leakage or runtime connection rejection","kind":"HOW","packages":["pkg:golang/github.com/jackc/pgx/v5@v5.10.0"],"schemaVersion":1,"symbols":["pgx.ParseConfig","pgxpool.ParseConfig","pgx.QueryExecMode"]},"contractCommand":["go","test","./..."],"environment":{"arch":"x64","ecosystem":"golang","executionContext":"go","language":"go","os":"linux","packageManager":"gomod","runtime":"go","schemaVersion":1},"license":"MIT-0","packages":["pkg:golang/github.com/jackc/pgx/v5@v5.10.0"],"schemaVersion":1,"symbols":["pgx.ParseConfig","pgxpool.ParseConfig","pgx.QueryExecMode"],"verifierAdapter":"golang@1"}

module sample

go 1.26.5

require github.com/jackc/pgx/v5 v5.10.0

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
	github.com/jackc/puddle/v2 v2.2.2 // indirect
	golang.org/x/sync v0.17.0 // indirect
	golang.org/x/text v0.29.0 // indirect
)

github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
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.10.0 h1:VhSvgU2jSli8o3AqIEOTJr7rZwAEUVo4E4XhR94Zfr0=
github.com/jackc/pgx/v5 v5.10.0/go.mod h1:mal1tBGAFfLHvZzaYh77YS/eC6IX9OWbRV1QIIM0Jn4=
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/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
github.com/stretchr/testify v1.11.1 h1:7s2iGBzp5EwR7/aIZr8ao5+dra3wiQyKjjFuvgVKu7U=
github.com/stretchr/testify v1.11.1/go.mod h1:wZwfW3scLgRK+23gO65QZefKpKQRnfz6sD981Nm4B6U=
golang.org/x/sync v0.17.0 h1:l60nONMj9l5drqw6jlhIELNv9I0A4OFgRsG9k2oT9Ug=
golang.org/x/sync v0.17.0/go.mod h1:9KTHXmSnoGruLpwFjVSX0lNNA75CykiMECbovNTZqGI=
golang.org/x/text v0.29.0 h1:1neNs90w9YzJ9BocxfsQNHKuAT4pkghyXc4nhZ6sJvk=
golang.org/x/text v0.29.0/go.mod h1:7MhJOA9CD2qZyOKYazxdYMF85OwPdEr9jTtBpO7ydH4=
gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
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=

package sample

import (
	"testing"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

func TestQueryExecMode_PgxV5VsV4(t *testing.T) {
	// 1. In pgx v4, prefer_simple_protocol=true configured simple protocol mode.
	// In pgx v5, prefer_simple_protocol is NOT recognized: DefaultQueryExecMode stays at CacheStatement (0),
	// and prefer_simple_protocol leaks into RuntimeParams (causing PG startup failure).
	cfgV4, err := pgx.ParseConfig("postgres://example.com/mydb?prefer_simple_protocol=true")
	if err != nil {
		t.Fatalf("failed to parse v4-style URL: %v", err)
	}
	if cfgV4.DefaultQueryExecMode != pgx.QueryExecModeCacheStatement {
		t.Fatalf("expected DefaultQueryExecMode to remain CacheStatement (0), got %v", cfgV4.DefaultQueryExecMode)
	}
	if cfgV4.RuntimeParams["prefer_simple_protocol"] != "true" {
		t.Fatalf("expected prefer_simple_protocol in RuntimeParams, got %v", cfgV4.RuntimeParams)
	}

	// 2. In pgx v5, default_query_exec_mode sets DefaultQueryExecMode and is excluded from RuntimeParams
	execModes := map[string]pgx.QueryExecMode{
		"simple_protocol": pgx.QueryExecModeSimpleProtocol,
		"exec":            pgx.QueryExecModeExec,
		"describe_exec":   pgx.QueryExecModeDescribeExec,
		"cache_describe":  pgx.QueryExecModeCacheDescribe,
		"cache_statement": pgx.QueryExecModeCacheStatement,
	}

	for param, expectedMode := range execModes {
		cfg, err := pgx.ParseConfig("postgres://example.com/mydb?default_query_exec_mode=" + param)
		if err != nil {
			t.Fatalf("failed to parse default_query_exec_mode=%s: %v", param, err)
		}
		if cfg.DefaultQueryExecMode != expectedMode {
			t.Fatalf("mode %s: expected %v, got %v", param, expectedMode, cfg.DefaultQueryExecMode)
		}
		if _, exists := cfg.RuntimeParams["default_query_exec_mode"]; exists {
			t.Fatalf("mode %s: default_query_exec_mode should not leak into RuntimeParams", param)
		}
	}

	// 3. Invalid default_query_exec_mode yields parse error
	if _, err := pgx.ParseConfig("postgres://example.com/mydb?default_query_exec_mode=invalid_mode"); err == nil {
		t.Fatalf("expected error parsing invalid default_query_exec_mode, got nil")
	}

	// 4. statement_cache_capacity=0 disables statement cache without leaking into RuntimeParams
	cfgCache, err := pgx.ParseConfig("postgres://example.com/mydb?statement_cache_capacity=0")
	if err != nil {
		t.Fatalf("failed to parse statement_cache_capacity: %v", err)
	}
	if cfgCache.StatementCacheCapacity != 0 {
		t.Fatalf("expected StatementCacheCapacity=0, got %d", cfgCache.StatementCacheCapacity)
	}
	if _, exists := cfgCache.RuntimeParams["statement_cache_capacity"]; exists {
		t.Fatalf("statement_cache_capacity should not leak into RuntimeParams")
	}
}

func TestPoolOptions_PgxPoolVsPgxParseConfig(t *testing.T) {
	connURL := "postgres://example.com/mydb?pool_max_conns=20&pool_min_conns=5&pool_max_conn_lifetime=1h&pool_max_conn_idle_time=15m&pool_health_check_period=30s"

	// 1. pgxpool.ParseConfig extracts pool_* query parameters into pgxpool.Config
	poolCfg, err := pgxpool.ParseConfig(connURL)
	if err != nil {
		t.Fatalf("failed to parse pgxpool config: %v", err)
	}
	if poolCfg.MaxConns != 20 {
		t.Fatalf("expected MaxConns=20, got %d", poolCfg.MaxConns)
	}
	if poolCfg.MinConns != 5 {
		t.Fatalf("expected MinConns=5, got %d", poolCfg.MinConns)
	}
	if poolCfg.MaxConnLifetime != time.Hour {
		t.Fatalf("expected MaxConnLifetime=1h, got %v", poolCfg.MaxConnLifetime)
	}
	if poolCfg.MaxConnIdleTime != 15*time.Minute {
		t.Fatalf("expected MaxConnIdleTime=15m, got %v", poolCfg.MaxConnIdleTime)
	}
	if poolCfg.HealthCheckPeriod != 30*time.Second {
		t.Fatalf("expected HealthCheckPeriod=30s, got %v", poolCfg.HealthCheckPeriod)
	}
	// Pool parameters are stripped from ConnConfig.RuntimeParams
	for _, key := range []string{"pool_max_conns", "pool_min_conns", "pool_max_conn_lifetime", "pool_max_conn_idle_time", "pool_health_check_period"} {
		if _, exists := poolCfg.ConnConfig.RuntimeParams[key]; exists {
			t.Fatalf("key %s should not leak into pool ConnConfig.RuntimeParams", key)
		}
	}

	// 2. pgx.ParseConfig (single connection) does NOT recognize pool_* parameters and leaks them into RuntimeParams
	singleCfg, err := pgx.ParseConfig(connURL)
	if err != nil {
		t.Fatalf("failed to parse pgx config: %v", err)
	}
	if singleCfg.RuntimeParams["pool_max_conns"] != "20" {
		t.Fatalf("expected pool_max_conns in single connection RuntimeParams, got %v", singleCfg.RuntimeParams["pool_max_conns"])
	}
}

func TestSSLMode_FallbackChainAndTLSVerification(t *testing.T) {
	// 1. sslmode=disable: No TLS config, 0 fallbacks (plaintext only)
	cfgDisable, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=disable")
	if err != nil {
		t.Fatalf("failed to parse sslmode=disable: %v", err)
	}
	if cfgDisable.TLSConfig != nil {
		t.Fatalf("expected TLSConfig=nil for sslmode=disable")
	}
	if len(cfgDisable.Fallbacks) != 0 {
		t.Fatalf("expected 0 fallbacks for sslmode=disable, got %d", len(cfgDisable.Fallbacks))
	}

	// 2. sslmode=allow: Plaintext primary, 1 TLS fallback
	cfgAllow, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=allow")
	if err != nil {
		t.Fatalf("failed to parse sslmode=allow: %v", err)
	}
	if cfgAllow.TLSConfig != nil {
		t.Fatalf("expected primary TLSConfig=nil for sslmode=allow")
	}
	if len(cfgAllow.Fallbacks) != 1 || cfgAllow.Fallbacks[0].TLSConfig == nil {
		t.Fatalf("expected 1 TLS fallback for sslmode=allow")
	}

	// 3. sslmode=prefer (default): TLS primary with InsecureSkipVerify=true, 1 plaintext fallback
	cfgPrefer, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=prefer")
	if err != nil {
		t.Fatalf("failed to parse sslmode=prefer: %v", err)
	}
	if cfgPrefer.TLSConfig == nil || !cfgPrefer.TLSConfig.InsecureSkipVerify {
		t.Fatalf("expected primary TLSConfig with InsecureSkipVerify=true for sslmode=prefer")
	}
	if len(cfgPrefer.Fallbacks) != 1 || cfgPrefer.Fallbacks[0].TLSConfig != nil {
		t.Fatalf("expected 1 plaintext fallback for sslmode=prefer")
	}

	// 4. Default sslmode when omitted matches sslmode=prefer
	cfgDefault, err := pgx.ParseConfig("postgres://example.com/mydb")
	if err != nil {
		t.Fatalf("failed to parse default config: %v", err)
	}
	if cfgDefault.TLSConfig == nil || !cfgDefault.TLSConfig.InsecureSkipVerify {
		t.Fatalf("expected default TLSConfig with InsecureSkipVerify=true")
	}
	if len(cfgDefault.Fallbacks) != 1 || cfgDefault.Fallbacks[0].TLSConfig != nil {
		t.Fatalf("expected default to have 1 plaintext fallback")
	}

	// 5. sslmode=require: TLS primary, 0 fallbacks (no plaintext downgrade)
	cfgRequire, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=require")
	if err != nil {
		t.Fatalf("failed to parse sslmode=require: %v", err)
	}
	if cfgRequire.TLSConfig == nil || !cfgRequire.TLSConfig.InsecureSkipVerify {
		t.Fatalf("expected TLSConfig with InsecureSkipVerify=true for sslmode=require")
	}
	if len(cfgRequire.Fallbacks) != 0 {
		t.Fatalf("expected 0 fallbacks for sslmode=require, got %d", len(cfgRequire.Fallbacks))
	}

	// 6. sslmode=verify-ca: TLS primary with VerifyPeerCertificate hook
	cfgVerifyCA, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=verify-ca")
	if err != nil {
		t.Fatalf("failed to parse sslmode=verify-ca: %v", err)
	}
	if cfgVerifyCA.TLSConfig == nil || !cfgVerifyCA.TLSConfig.InsecureSkipVerify || cfgVerifyCA.TLSConfig.VerifyPeerCertificate == nil {
		t.Fatalf("expected TLSConfig with InsecureSkipVerify=true and VerifyPeerCertificate for verify-ca")
	}

	// 7. sslmode=verify-full: Standard TLS verification with InsecureSkipVerify=false and ServerName
	cfgVerifyFull, err := pgx.ParseConfig("postgres://example.com/mydb?sslmode=verify-full")
	if err != nil {
		t.Fatalf("failed to parse sslmode=verify-full: %v", err)
	}
	if cfgVerifyFull.TLSConfig == nil || cfgVerifyFull.TLSConfig.InsecureSkipVerify || cfgVerifyFull.TLSConfig.ServerName != "example.com" {
		t.Fatalf("expected TLSConfig with InsecureSkipVerify=false and ServerName for verify-full")
	}
}

func TestEnvVar_PrecedenceAndInheritance(t *testing.T) {
	t.Setenv("PGHOST", "example.net")
	t.Setenv("PGPORT", "5433")
	t.Setenv("PGDATABASE", "envdb")
	t.Setenv("PGUSER", "envuser")
	t.Setenv("PGPASSWORD", "envpass")
	t.Setenv("PGAPPNAME", "appenv")
	t.Setenv("PGSSLMODE", "disable")

	// 1. ParseConfig("") loads all settings from PG* environment variables
	cfgEmpty, err := pgx.ParseConfig("")
	if err != nil {
		t.Fatalf("failed to parse empty config: %v", err)
	}
	if cfgEmpty.Host != "example.net" {
		t.Fatalf("expected Host=example.net, got %s", cfgEmpty.Host)
	}
	if cfgEmpty.Port != 5433 {
		t.Fatalf("expected Port=5433, got %d", cfgEmpty.Port)
	}
	if cfgEmpty.Database != "envdb" {
		t.Fatalf("expected Database=envdb, got %s", cfgEmpty.Database)
	}
	if cfgEmpty.User != "envuser" {
		t.Fatalf("expected User=envuser, got %s", cfgEmpty.User)
	}
	if cfgEmpty.Password != "envpass" {
		t.Fatalf("expected Password=envpass, got %s", cfgEmpty.Password)
	}
	if cfgEmpty.RuntimeParams["application_name"] != "appenv" {
		t.Fatalf("expected application_name=appenv, got %s", cfgEmpty.RuntimeParams["application_name"])
	}
	if cfgEmpty.TLSConfig != nil {
		t.Fatalf("expected TLSConfig=nil from PGSSLMODE=disable")
	}

	// 2. Explicit connection string overrides environment variables
	cfgExplicit, err := pgx.ParseConfig("postgres://urluser:urlpass@example.org:5434/urldb?application_name=urlapp&sslmode=require")
	if err != nil {
		t.Fatalf("failed to parse explicit config: %v", err)
	}
	if cfgExplicit.Host != "example.org" || cfgExplicit.Port != 5434 || cfgExplicit.Database != "urldb" || cfgExplicit.User != "urluser" || cfgExplicit.Password != "urlpass" {
		t.Fatalf("expected explicit values to override environment variables, got %+v", cfgExplicit)
	}
	if cfgExplicit.RuntimeParams["application_name"] != "urlapp" {
		t.Fatalf("expected application_name=urlapp, got %s", cfgExplicit.RuntimeParams["application_name"])
	}
	if cfgExplicit.TLSConfig == nil {
		t.Fatalf("expected TLSConfig!=nil from explicit sslmode=require")
	}

	// 3. Partial connection string inherits omitted parameters from environment variables
	cfgPartial, err := pgx.ParseConfig("postgres://example.org/partialdb")
	if err != nil {
		t.Fatalf("failed to parse partial config: %v", err)
	}
	if cfgPartial.Host != "example.org" {
		t.Fatalf("expected explicit Host=example.org, got %s", cfgPartial.Host)
	}
	if cfgPartial.Database != "partialdb" {
		t.Fatalf("expected explicit Database=partialdb, got %s", cfgPartial.Database)
	}
	if cfgPartial.Port != 5433 {
		t.Fatalf("expected inherited Port=5433 from PGPORT, got %d", cfgPartial.Port)
	}
	if cfgPartial.User != "envuser" {
		t.Fatalf("expected inherited User=envuser from PGUSER, got %s", cfgPartial.User)
	}
}

func TestDSN_FormatAndRuntimeParams(t *testing.T) {
	// 1. Key-value DSN format parses session settings into RuntimeParams
	dsn := "host=example.com port=5432 user=myuser dbname=mydb search_path='public, tenant1' statement_timeout=5000"
	cfgDSN, err := pgx.ParseConfig(dsn)
	if err != nil {
		t.Fatalf("failed to parse DSN: %v", err)
	}
	if cfgDSN.Host != "example.com" || cfgDSN.Port != 5432 || cfgDSN.User != "myuser" || cfgDSN.Database != "mydb" {
		t.Fatalf("unexpected DSN connection attributes: %+v", cfgDSN)
	}
	if cfgDSN.RuntimeParams["search_path"] != "public, tenant1" {
		t.Fatalf("expected search_path='public, tenant1', got %s", cfgDSN.RuntimeParams["search_path"])
	}
	if cfgDSN.RuntimeParams["statement_timeout"] != "5000" {
		t.Fatalf("expected statement_timeout='5000', got %s", cfgDSN.RuntimeParams["statement_timeout"])
	}

	// 2. IPv6 host in URL format strips surrounding brackets
	cfgIPv6, err := pgx.ParseConfig("postgres://user:pass@[::1]:5432/mydb")
	if err != nil {
		t.Fatalf("failed to parse IPv6 URL: %v", err)
	}
	if cfgIPv6.Host != "::1" {
		t.Fatalf("expected Host='::1' without brackets, got %s", cfgIPv6.Host)
	}
}