fix(db): PostgreSQL `idle in transaction` — Elasticsearch HTTP calls inside @WrapInTransaction in ESContentletAPIImpl.delete(), deleteVersion(), archive()

Description

ESContentletAPIImpl.delete(), deleteVersion(), and archive() are annotated @WrapInTransaction and call indexAPI.removeContentFromIndex() — an Elasticsearch HTTP call — inside the transaction boundary. While Elasticsearch is processing (or unresponsive), the PostgreSQL connection remains open in idle in transaction state, holding row-level locks on the affected identifier and contentlet rows.

Confirmed in production

Observed in pg_stat_activity on ctt_grupo_qa_2310_db (2026-03-02):

pid:            10038
state:          idle in transaction
stuck_duration: 00:13:38
wait_event:     ClientRead
last_query:     select * from identifier where id = $1

wait_event: ClientRead means PostgreSQL returned the query result and is waiting for the Java client to send the next SQL command. The Java thread is blocked on the ES HTTP call — not on PostgreSQL — while the transaction remains open.

Root Cause — Call chain

ESContentletAPIImpl.delete()             ← @WrapInTransaction → BEGIN
  contentFactory.delete(contentletsVersion)
    ESContentFactoryImpl.delete()
      [per contentlet: DB deletes]
      identifierAPI.find(c.getIdentifier())   ← SELECT * FROM identifier
      [identsDeleted check — may skip identifierAPI.delete()]
  indexAPI.removeContentFromIndex(contentlet)  ← ES HTTP call ← BLOCKS HERE
                                               ← transaction still open
                                               ← pg_stat last query = select * from identifier

Why select * from identifier is the last visible query

Inside ESContentFactoryImpl.delete() (lines 628–641), identifierAPI.find() runs SELECT * FROM identifier. If the identifier was already tracked in identsDeleted, identifierAPI.delete() is skipped — no further SQL is sent on this connection. Control returns to ESContentletAPIImpl.delete(), which then calls removeContentFromIndex() while still inside the @WrapInTransaction scope.

Affected methods

Method	Location	ES call line
delete(List, User, boolean, boolean)	ESContentletAPIImpl.java:3541	Line 3581
deleteVersion()	ESContentletAPIImpl.java:3591	Line 3618
archive()	ESContentletAPIImpl.java:3631	(similar pattern)

Impact

	coop-prod permanent orphans	This issue
Connection state	idle (no transaction)	idle in transaction
Permanently leaked?	Yes	No — released when ES call completes
Holds PostgreSQL locks?	No	Yes — row locks on identifier, contentlet, multi_tree
Cascade risk	Pool starvation	Cascading lock waits on same rows
Duration	Hours until restart	Duration of ES call (observed: 13+ min)

During the lock hold window:

• Other transactions attempting to DELETE or UPDATE the same contentlet/identifier rows will block
• Blocked transactions hold their own connections, amplifying pool pressure
• In high-throughput environments (bulk delete, re-publish), this can cascade across many operations

Source of Truth and Eventual Consistency

The database is the authoritative source of truth for all content state. The Elasticsearch index is a derived, eventually-consistent projection of that state. This distinction matters for determining the correct ordering of operations.

Why the current ordering is wrong for delete operations

The current code removes content from ES inside the database transaction — before the DB deletion has committed and become visible to other connections:

BEGIN
  DELETE from contentlet, identifier, etc.   ← not yet visible to other connections
  removeContentFromIndex()                   ← ES updated NOW: content gone from search
COMMIT                                       ← DB deletion now visible

This means content disappears from search results before the database confirms the deletion. If the transaction then rolls back (error after the ES call), the DB record is restored but the ES index entry is gone — a split-brain state that requires a full reindex to repair.

The correct eventual consistency model for deletes

For delete and archive operations, removing the ES entry after the DB transaction commits is the correct behavior:

BEGIN
  DELETE from contentlet, identifier, etc.
COMMIT                                       ← DB deletion now authoritative
removeContentFromIndex()                     ← ES updated: now consistent with DB

The brief window between COMMIT and ES removal is acceptable: during that window, ES may return a stale result, but any code following that result to fetch from DB will find the record is gone — a soft, recoverable miss. The DB is the truth; ES is catching up. This is preferable to the current ordering, where ES says content is gone but the DB has not yet confirmed it (and may roll back).

Contrast with add/update operations

For add and update operations, the same post-commit principle applies but for different reasons: content should not appear in search until the DB record has committed. Indexing inside the transaction risks ES showing content that the DB may still roll back. Both directions of the problem have the same root fix — ES operations should always follow a committed DB state, never precede it.

Implementation risk: nested @WrapInTransaction

The fix is not a simple drop-in replacement. Care is required because delete() is often called from within another @WrapInTransaction method (workflow steps, bulk operations, publish handlers). In that context:

• A HibernateUtil.addCommitListener() registered inside delete() will not fire until the outermost transaction commits — potentially much later, after other operations in the outer transaction that may have their own ES expectations.
• Splitting the method (DB-only @WrapInTransaction inner method + ES call in a non-transactional outer method) solves the standalone case but the inner method will still join the outer transaction when called from a nested context, so the ES call still happens mid-outer-transaction.

An audit of all call sites is required before implementing to identify nested-transaction callers and determine whether any code between the delete() call and the outer commit relies on ES being updated at that specific point.

Acceptance Criteria

• Audit all call sites of delete(), deleteVersion(), and archive() for nested @WrapInTransaction callers and any post-call ES verification within the same transaction scope
• ES removeContentFromIndex() is called after the relevant DB transaction has committed — not inside it
• If the DB transaction rolls back, ES is not modified (no split-brain on rollback)
• Content is correctly removed from ES index after delete/archive operations complete
• No idle in transaction connections held during ES HTTP calls
• Integration tests verify ES consistency after delete/archive — including rollback scenarios

Additional Context

• #34831 — ExperimentsAPIImpl.listActive() DB connection leak (background thread, no @CloseDBIfOpened)
• #34832 — Add socketTimeout to PostgreSQL JDBC URL (bounds how long JDBC can block indefinitely on socket I/O)
• Identified via pg_stat_activity analysis on ctt_grupo_qa_2310_db, 2026-03-02

Start with ESContentletAPIImpl.java at delete(), deleteVersion(), and archive(), then inspect ESContentFactoryImpl.java lines 628–641 and audit all callers for nested @WrapInTransaction scopes. Trace when removeContentFromIndex() runs relative to commit, including rollback paths and any post-call ES checks. Done means ES removal occurs only after commit, no transaction remains open during the HTTP call, and integration coverage verifies delete/archive consistency and rollback behavior.