* postgres2: default the filemeta CREATE TABLE when createTable is unset
An empty createTable rendered through fmt.Sprintf produced
%!(EXTRA string=filemeta), which Postgres rejected with a syntax error at
init. Fall back to a working template so a minimal config bootstraps.
* mysql2: default the filemeta CREATE TABLE when createTable is unset
Same empty-template failure the postgres2 path had: an unset createTable
rendered to %!(EXTRA string=filemeta) and MySQL rejected it at init.
Fall back to a working template.
Concurrent bucket deletion across multiple filer replicas races on the
per-bucket DROP TABLE. The first replica drops the table; the rest hit
an undefined-table error (postgres 42P01, mysql 1051) which propagates
out of DeleteFolderChildren and panics the filer. On restart the same
pending DROP re-runs and the filer crash-loops.
Make the drop idempotent. Same defect class in all SQL backends, so fix
postgres, postgres2, mysql, mysql2, and sqlite together.
* mysql: keep S3 list order byte-lexicographic regardless of name column collation
ORDER BY name and the name > ? pagination predicate follow the column
collation, so a case-insensitive filemeta.name (e.g. utf8mb3_general_ci)
returns S3 keys out of byte order and breaks clients that merge two sorted
listings.
Detect the live name collation at startup; only when it isn't binary, wrap
the list comparison, prefix, and ORDER BY in BINARY name so order and
pagination stay consistent. Correctly configured utf8mb4_bin tables keep
their indexed range scan unchanged, and the operator gets a warning to
convert the column.
* postgres: keep S3 list order byte-lexicographic regardless of name column collation
ORDER BY name and the name > $n pagination predicate follow the column or
database collation, so a locale-aware filemeta.name (e.g. the en_US.UTF-8
database default) returns S3 keys out of byte order and breaks clients that
merge two sorted listings.
Detect the live name collation at startup; only when it isn't byte-ordered,
wrap the list comparison, prefix, and ORDER BY in COLLATE "C" so order and
pagination stay consistent. A byte-ordered (C/POSIX/C.UTF-8) column keeps its
indexed range scan unchanged, and the operator gets a warning to declare the
column COLLATE "C".
* fix(filer/postgres): default to ON CONFLICT upsert to keep tx alive
A KvPut from the inode-index secondary write could fail with 23505
(duplicate key) inside a rename's transaction, after which the next
statement returned 25P02 and rename surfaced to FUSE as EIO. Default
the postgres upsert query when enableUpsert=true so INSERTs are
idempotent; the enableUpsert=false escape hatch is preserved for
non-PG-compatible backends.
* fix(filer/mysql): default to ON DUPLICATE KEY UPDATE upsert
Same shape as the postgres default: when enableUpsert=true but no
upsertQuery is configured, install a sensible default so the
inode-index KvPut does not waste a duplicate-key roundtrip on every
entry write. Uses the VALUES() form so the default works on MariaDB
and MySQL >=5.7; the MySQL 8.0.19 row-alias form is left to explicit
config.
* fix(filer): default enableUpsert=true for sql stores
The default-template fallback only kicks in when enableUpsert=true,
so minimal configs that omit the flag entirely were still exposed.
Default it on for postgres/postgres2/mysql/mysql2; an explicit false
in filer.toml still wins because SetDefault only fills absent keys.
* fix(filer): don't disable the SQL idle connection pool when unconfigured
The mysql/mysql2/postgres stores called SetMaxIdleConns(maxIdle)
unconditionally, so an unset connection_max_idle (0) actively kept zero
idle connections - every query opened and closed a fresh connection
instead of reusing the pool.
Only apply the value when it's set; otherwise leave database/sql's
default idle pool of 2 in place.
* comments: shorten idle-pool note
* fix(filer): default the SQL idle pool via config, keep explicit 0 honored
Apply the idle-pool default at the config layer with SetDefault instead of
guarding the SetMaxIdleConns call. An absent connection_max_idle now reads
back as 2 (pool stays on), while an explicit 0 flows through to
SetMaxIdleConns(0) so operators can still disable idle pooling on purpose.
* fix(filer/postgres): use pgx v5 API for PgBouncer simple protocol
In pgx/v5 the `prefer_simple_protocol` DSN parameter was removed, so
appending it to the connection string caused PgBouncer/PostgreSQL to
reject it as an unknown startup parameter:
FATAL: unsupported startup parameter: prefer_simple_protocol (SQLSTATE 08P01)
Parse the DSN with pgx.ParseConfig and, when pgbouncer_compatible is
set, configure DefaultQueryExecMode = QueryExecModeSimpleProtocol and
disable the statement/description caches. Register the config via
stdlib.RegisterConnConfig before sql.Open.
Fixes#9005
* refactor(filer/postgres): extract shared OpenPGXDB helper with cleanup
Extract the pgx v5 ParseConfig/RegisterConnConfig/sql.Open/Ping logic
into a shared postgres.OpenPGXDB helper used by both postgres and
postgres2 filer stores, eliminating ~60 lines of duplication.
The helper also unregisters the conn config via stdlib.UnregisterConnConfig
on every failure path (sql.Open error, Ping error) so we do not leak
entries in stdlib's global connection config map when initialization
fails.
* refactor(filer/postgres): use stdlib.OpenDB to avoid conn config leak
Switch OpenPGXDB from RegisterConnConfig + sql.Open("pgx", connStr) to
stdlib.OpenDB(*connConfig). The former leaks an entry in stdlib's global
conn config map on every successful initialization; stdlib.OpenDB takes
the config directly and keeps no global registration.
Addresses CodeRabbit review feedback on #9010.
Chris Lu
chrislu
John W Higgins
Chris Lu
LazyDBA247-Anyvision