[Bug] cross-process compact/kline-insert locks are keyed on the data dir, so processes sharing one database get no protection #173

Summary

Both cross-process file locks that guard database-scoped resources are keyed on the data directory, not on the database:

• orm/compact_process_lock.go → compactProcessLockRoot() = <dataDir>/locks/compact
• orm/kline_insert_lock.go → klineInsertLockRoot() = <dataDir>/locks/kline_insert

Two processes serialize only if they resolve the same BanDataDir. Two processes with different data dirs sharing one database take their leases in different files — no contention, no protection. This is the same disjoint-key class as the compaction bug fixed in e056909 (there the two arms keyed on different strings; here they key on different directories). The lock is real; it is keyed on something that is not the shared resource. The shared resource is the database, so the key must be the database.

When it bites

Nothing in the configuration model ties BanDataDir to database.url, so plausible setups hit it without any exotic tooling:

• a spider (its own data dir) and a trading/backtest process (another data dir) pointed at one QuestDB;
• two deployments sharing a central QuestDB on the same host;
• an amplifier: config.getEnvPath normalizes with filepath.Abs but no symlink/.. canonicalization, so even the same data dir given as a relative path from different cwds yields different lock files.

The exclusive arm is armed by the spider and web/dev (AutoCompact), plus the spider's PurgeKlineUn — so the dangerous interleaving is "long-lived compactor + any second process with a different data dir on the same DB".

Impact

The failure modes are the ones the compaction lock exists to prevent, two of them silent: a fatal [-105] table does not exist panic; GetKlineRange/GetKlineRanges returning 0,0/empty (indistinguishable from "no data for this pair"); destroyed post-snapshot writes. For the kline-insert lock it is uncoordinated concurrent same-pair ingest.

Measured on a live QuestDB with a synthetic WAL table (8 readers, INSERT-only re-dirty, deadline-retry compactor, readers holding only the process file lock):

Proposed fix

Key both lock roots on the database identity: parse the URL with the same pgxpool.ParseConfig(normalizeDatabaseURL(...)) the pool uses, hash lower(host):port/dbname (the path must not leak credentials), root the locks in a per-machine location outside any data dir (os.UserCacheDir(), os.TempDir() fallback), and fall back to the current data-dir root whenever the identity cannot be resolved — never to "no lock". PR follows.

Known limitations, stated up front: a single-bot setup (one data dir, one DB) is unaffected today and stays unaffected; file locks cannot coordinate across machines (true before and after — the auto-started local QuestDB is the primary model); changing the lock location creates a one-time unprotected window during a rolling upgrade where an old and a new process briefly use different roots.