[YSQL] DISCARD ALL takes the catalog version lock and permanently starves auto-analyze

[kai-franz]

Jira Link: DB-23132

Description

DISCARD ALL permanently starves auto-analyze on any cluster that uses connection pooling.

Mechanism

1. YbGetDdlMode() treats T_DiscardStmt (DISCARD ALL / SEQUENCES / TEMP) as a DDL with is_version_increment = false, so the statement enters DDL-transaction mode.
2. Entering DDL mode calls YbMaybeLockMasterCatalogVersion(), which takes ROW_MARK_KEYSHARE on this database's pg_yb_catalog_version row (gated on yb_user_ddls_preempt_auto_analyze, default on).
3. PgTxnManager::GetTxnPriorityRequirement() gives any DDL that is not running on the internal auto-analyze connection kHighestPriority.
4. ANALYZE spawned by auto-analyze takes ROW_MARK_EXCLUSIVE on all rows of pg_yb_catalog_version, at a lower priority. Under fail-on-conflict, CheckPriorityInternal() aborts the lower-priority transaction, which is always the ANALYZE.
5. ANALYZE is excluded from query-layer retries (postgres.c, CMDTAG_ANALYZE) and the service-side backoff grows to ysql_auto_analyze_max_retry_backoff cycles, so the loop never converges.

Because DISCARD ALL never increments the catalog version, it can never actually conflict with the ANALYZE's increment — the lock it takes manufactures a conflict that serves no purpose. Connection poolers issue DISCARD ALL on every connection release (it is pgbouncer's default server_reset_query, and several client-side driver pools do the same on connection close), so on a busy cluster this happens continuously and auto-analyze never completes a single ANALYZE. Table statistics then go stale indefinitely, which in turn degrades cost-based optimizer plans.

The same reasoning applies to every other DDL that never increments the catalog version — notably temp-object DDL (CREATE TEMP TABLE, an ALTER TABLE of a temp relation, an all-temp DROP).

Observed error

The auto-analyze service logs, once per attempt:

failed ANALYZE statement: ANALYZE "public"."t1" with error: ... ERROR:  could not serialize
access due to concurrent update (query layer retries not possible for ANALYZE commands)
DETAIL:  <txn-a> conflicts with higher priority transaction
         (our pri: <n>, their pri: 18446744073709551615): <txn-b> [read committed]

Steps to reproduce

Single node, tserver flags:

--ysql_enable_auto_analyze_service=true
--ysql_enable_auto_analyze=true
--ysql_cluster_level_mutation_persist_interval_ms=1000
--ysql_auto_analyze_threshold=5
--ysql_auto_analyze_scale_factor=0.001

CREATE TABLE t1(id int PRIMARY KEY, v text);
INSERT INTO t1 SELECT g, repeat('x', 20) FROM generate_series(1, 20000) g;

First confirm auto-analyze works: SELECT reltuples FROM pg_class WHERE relname = 't1' reaches 20000 and the tserver log shows no failed ANALYZE statement. Then keep a few sessions issuing DISCARD ALL in a loop:

yes 'DISCARD ALL;' | ysqlsh -X -q

and make a few more mutations to t1. Every subsequent auto-analyze attempt fails with the error above. Reproduced on master (2.31.0.0): with the DISCARD ALL loops as the only concurrent statements — no DML whatsoever — 14 consecutive ANALYZE failures in 90 seconds, versus 0 failures in the same setup without them.

Workaround

Setting the runtime tserver gflag ysql_yb_user_ddls_preempt_auto_analyze=false removes the start-of-DDL lock, so DISCARD ALL can no longer conflict. Verified: 0 auto-analyze failures across 2.15M DISCARD ALL statements. The cost is that genuinely version-incrementing DDLs stop failing fast — they conflict at commit, after doing all their work.

Suggested fix

Only take the catalog-version lock for DDLs that will actually increment the catalog version, i.e. gate YbMaybeLockMasterCatalogVersion() on YB_SYS_CAT_MOD_ASPECT_VERSION_INCREMENT. The check has to be re-evaluated at every DDL nesting level and for every DDL of a transaction block, because a subcommand or a later statement can be the one that increments while the enclosing or earlier statement does not (for example the foreign-key ALTER TABLE that CREATE TABLE ... REFERENCES runs as a subcommand).

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.