Site Search: SiteSearchJobImpl holds one pooled DB connection in an open transaction for the entire crawl, so the audit insert fails on long runs

Problem Statement

SiteSearchJobImpl.run() opens a database transaction — and therefore leases a pooled JDBC connection — before the crawl begins, and holds it until after the crawl finishes:

Line	SiteSearchJobImpl.java	Effect
164	HibernateUtil.startTransaction()	Leases a Hikari connection and sets autoCommit=false
166	prepareJob(jobContext) → findRecentAudits() (line 296)	First SELECT — the Postgres transaction actually begins
182	publisherAPI.publish(config, status)	The entire crawl: bundlers, then the search-engine push
220	siteSearchAuditAPI.save(audit)	The audit INSERT, on that same hours-old connection
223	Logger.error(this, "can't save audit data", ex)	Failure swallowed — job still reports "Finished"
227	HibernateUtil.closeSession()	Commits and finally releases the connection

On a large site the crawl runs for hours. The connection is not idle throughout — the bundlers query heavily — but it goes quiet in stretches, most obviously during ESSiteSearchPublisher.process(), which reads the bundled files back off disk and pushes documents into the search engine with essentially no SQL traffic. If any quiet stretch exceeds the idle timeout of a stateful network device between dotCMS and Postgres, the TCP flow is silently evicted and the socket is dead by the time the audit INSERT runs.

Nothing in the stack can recover from this:

• HikariCP never validates or retires an in-use connection. maxLifetime and connectionTestQuery apply on checkout or while idle in the pool; keepaliveTime is never configured (SystemEnvDataSourceStrategy). A connection held for hours is never health-checked.
• SiteSearchAuditAPIImpl.save() is @WrapInTransaction, but that cannot help here — per WrapInTransactionInterceptor, a nested call "just execute[s] inside the existing transaction". Because line 164 already opened one, save() joins the stale transaction instead of leasing a fresh, validated connection.
• DotConnect performs no revalidation or retry.
• pgjdbc's tcpKeepAlive defaults to false and is not set anywhere in the codebase, so the socket emits no packets at all while quiet.

Reported symptom (large site, ~31.9K files / 8.6K pages / 10.6K urlmaps; run spanned ~8h35m from fire time to failure):

WARN  pool.ProxyConnection: jdbc/dotCMSPool - Connection org.postgresql.jdbc.PgConnection@… marked as broken
      because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at com.dotmarketing.common.db.DotConnect.loadResult(DotConnect.java:334)
    at com.dotmarketing.sitesearch.business.SiteSearchAuditFactoryImpl.save(SiteSearchAuditFactoryImpl.java:40)
    at com.dotmarketing.sitesearch.business.SiteSearchAuditAPIImpl.save(SiteSearchAuditAPIImpl.java:17)
    at com.dotcms.publishing.job.SiteSearchJobImpl.run(SiteSearchJobImpl.java:187)
    …
Caused by: java.net.SocketException: Connection timed out

INFO  pool.ProxyLeakTask: Previously reported leaked connection org.postgresql.jdbc.PgConnection@… on thread
      dotCMSQuartz_Worker-N was returned to the pool (unleaked)
ERROR job.SiteSearchJobImpl: can't save audit data
com.dotmarketing.exception.DotDataException: An I/O error occurred while sending to the backend.
  "SQL": ["insert into sitesearch_audit (job_id,job_name,fire_date,incremental,start_date,end_date,host_list,
           all_hosts, lang_list,path,path_include,files_count,pages_count,urlmaps_count,index_name)
           values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"]
INFO  job.SiteSearchJobImpl: Job Finished

Note the failure is SocketException: Connection timed out (ETIMEDOUT), not Connection reset — TCP retransmissions expired against a black hole. That is the signature of a stateful device dropping the flow without sending RST, and it rules out a Postgres-side kill such as idle_in_transaction_session_timeout, which produces a FATAL: terminating connection… / reset instead. In the reported environment idle_in_transaction_session_timeout was at its default (disabled) and no connection pooler was in the path.

The ProxyLeakTask warning is a symptom, not the cause — it is leakDetectionThreshold (default 300000 ms) reporting a connection held far beyond five minutes.

Impact

1. The audit row is lost, silently. The catch at line 223 logs and continues, so the job reports success. This surfaces only if someone reads the logs closely.
2. Incremental indexing degrades to a full rebuild. The audit row is the checkpoint: an incremental crawl requires !recentAudits.isEmpty() and anchors its delta at recentAudits.get(0).getFireDate() (line 332). With no row, the next run re-crawls from the previous surviving audit or rebuilds entirely — which on a site this size is another multi-hour run, making recurrence more likely, not less.
3. Database-wide autovacuum impact. A transaction held open for hours pins the xmin horizon, suppressing dead-tuple cleanup across the entire database, not just Site Search tables. On a busy instance this drives table and index bloat affecting unrelated workloads.

Why a transaction here buys nothing

The job's only database write is the single-row audit INSERT. prepareJob() mutates Quartz's in-memory JobDataMap (persisted by Quartz on its own connection); the bundlers are read-only on the Site Search path; the crawl's actual output goes to the search engine, which is not covered by a Postgres transaction and cannot be rolled back. So the long transaction provides no atomicity over anything that matters, while carrying every cost above.

Bundler read-only status was verified: no raw SQL, no DotConnect, no HibernateUtil in FileAssetBundler, URLMapBundler, or HTMLPageAsContentBundler. The one DB write present — FileAssetBundler:260 pushedAssetUtil.savePushedAssetForAllEnv(...) — is gated by config.shouldManageDependencies(), which returns isStatic(); SiteSearchConfig never sets it, so Site Search takes the non-static branch. (One caveat: URLMapBundler and HTMLPageAsContentBundler call getHTML(), which executes user-authored Velocity — a template or plugin viewtool could in principle write.)

This is the same defect class as #34833 (long non-transactional work inside @WrapInTransaction leaving Postgres idle in transaction), in a different feature.

Steps to Reproduce

The mechanism is a held connection, so it does not require an eight-hour wait to demonstrate.

Fast synthetic reproduction

1. Run dotCMS against Postgres.
2. Schedule a Site Search job over enough content that the crawl runs for at least a few minutes.
3. While the crawl is running, confirm the held connection:

   SELECT pid, state, wait_event, xact_start, now() - xact_start AS held
   FROM pg_stat_activity
   WHERE application_name LIKE '%dotCMS%' AND state = 'idle in transaction';
   

   Expect a session whose xact_start dates to the beginning of the crawl, with wait_event = ClientRead.
4. Kill that specific backend mid-crawl to simulate the network drop:
   SELECT pg_terminate_backend(<pid>);
5. Let the crawl finish. The audit INSERT fails, ERROR job.SiteSearchJobImpl: can't save audit data is logged, and the job still reports "Job Finished".
6. Confirm no row was written: SELECT * FROM sitesearch_audit WHERE job_id = '<job-id>';
7. Run the job again — it performs a full rebuild rather than an incremental update.

Organic reproduction

A large site whose crawl runs for hours, with any stateful firewall or NAT between dotCMS and Postgres whose idle TCP timeout (commonly 30–60 minutes by default) is shorter than the crawl's quiet stretches.

Acceptance Criteria

• The Site Search job no longer holds a pooled connection, or an open Postgres transaction, across the crawl. Removing HibernateUtil.startTransaction() at SiteSearchJobImpl:164 is sufficient: save() is already @WrapInTransaction and will open its own short transaction on a fresh, pool-validated connection.
• The audit INSERT succeeds on a long-running crawl where the connection would previously have gone stale (verifiable with the synthetic repro above).
• pg_stat_activity shows no dotCMS session idle in transaction for the duration of a Site Search crawl.
• A failed audit save is surfaced in the job's status/result rather than only written to the log — the job must not report "Finished" as though nothing went wrong.
• Incremental indexing continues to work across consecutive runs (audit row present, delta anchored on it, no unintended full rebuild).
• The expectation that the crawl phase is read-only against Postgres is recorded, so a future change setting setStatic(true) on a SiteSearchConfig does not silently reintroduce per-asset writes inside the crawl.
• Consider auditing the other Quartz jobs that use the same pattern (ContentImportThread, DeleteOldClickstreams, CleanUnDeletedUsersJob, IdentifierDateJob, DeleteUserJob) — each calls HibernateUtil.startTransaction() at job start and may hold a connection across long work.

dotCMS Version

Reported on 26.07.13-01 (Current Release / dotEvergreen), self-hosted on Postgres over SSL.

Confirmed present in current main (verified at be94fd0a7f): SiteSearchJobImpl:164 / :220 / :227, HibernateUtil:1216, SystemEnvDataSourceStrategy leak threshold default 300000, and no tcpKeepAlive/keepaliveTime anywhere in the source tree.

Not a regression — git log -S places HibernateUtil.startTransaction() in this method since at least the 2016 source reorganisation (7f5a06e84b). Recent Site Search work (#36983, #36360) only shifted line numbers.

Severity

Medium - Some functionality impacted

Indexing itself completes correctly; the loss is the audit record and, consequently, incremental indexing. The database-wide autovacuum impact of the long-held transaction raises this above cosmetic.

Known workaround

Enable TCP keepalives so the socket never goes silent long enough to be evicted:

• Append tcpKeepAlive=true to the JDBC URL (DB_BASE_URL).
• On the dotCMS host, lower net.ipv4.tcp_keepalive_time below the network device's idle timeout — e.g. 600, with tcp_keepalive_intvl=60 and tcp_keepalive_probes=5. The Linux default of 7200s is longer than most firewall timeouts, which is why the socket dies silently today.

This is a mitigation, not a fix: it keeps the socket alive but leaves the multi-hour open transaction — and its autovacuum impact — in place.

Links

• Freshdesk ticket #39033
• #34833 — same defect class (long non-transactional work inside @WrapInTransaction causing Postgres idle in transaction)
• #36706 — the other way the same sitesearch_audit row fails to persist (path varchar(500) overflow), reported from the same environment; both are silently absorbed by the identical catch at SiteSearchJobImpl:223