Database semaphore rows are leaked when the controller's DB session breaks, with no recovery path (manual SQL is the only fix)

[zelig81]

Pre-requisites

• I have double-checked my configuration
• I have searched existing issues
• I am using the latest release at time of filing

Version

v4.1.1

What happened

Follow-on from 16771. While the workflow-controller's PostgreSQL session was wedged, workflows using database semaphores failed at admission with Failed to acquire the synchronization lock. session proxy is closed. Some of them had already been recorded as lock holders, and those rows were never released.

After the controller was restarted and the database was fully healthy, sync_state still contained held = true rows belonging to workflows that had been Failed for hours. A semaphore with a limit of 1 was blocked outright — new workflows queued behind a holder that no longer existed — and the only fix was to DELETE the rows by hand.

Why nothing recovers it

Three mechanisms could plausibly clean this up, and all three miss it:

1. Release on workflow deletion — releaseAllWorkflowLocks is guarded on the workflow's synchronization status:

func (wfc *WorkflowController) releaseAllWorkflowLocks(ctx context.Context, obj any) {
    ...
    if wf.Status.Synchronization != nil {
        wfc.syncManager.ReleaseAll(ctx, wf)
    }
}

A workflow that died while the session was broken has status.synchronization == nil, so ReleaseAll is never called — not on a manual kubectl delete, and not when the TTL GC deletes it days later.

2. Controller restart — the rows are in the database and survive it. Restarting fixes the session; it does nothing about state already written.

3. Inactive-controller expiry — cannot fire, because the controller column is an empty string on every row we have, in every environment we checked. Every row therefore appears to belong to the single '' controller, which is heartbeating normally, so no row is ever considered abandoned. (Consequently, guidance that suggests cleaning up with DELETE FROM sync_state WHERE controller = '<dead-controller>' matches nothing.)

Impact

The damage scales with the semaphore limit, and only the limit-1 case is visible:

Limit	One leaked holder causes	How it surfaces
1	total blockage	workflows pile up Pending; a concurrencyPolicy: Forbid cron stops scheduling entirely
N	permanent loss of 1/N capacity	it doesn't — throughput just degrades

This is the dangerous part. In our case one semaphore with a limit of 10 lost 4 slots and kept running at 60% for hours, looking completely healthy: workflows still ran, the queue still drained, and any "pending too long" alert self-cleared. We only found it by auditing the table after fixing the obvious limit-1 blockage.

What you expected to happen

A lock held by a workflow that is no longer running should eventually be released — either by reconciling holders against live workflows, or by not gating release on a status field that is absent precisely in the failure case that causes the leak.

How to reproduce it

1. Configure synchronization.postgresql and a semaphore with a small limit.
2. While workflows hold that semaphore, make the database unreachable long enough to wedge the session (see 16771).
3. Restore the database and restart the controller.
4. SELECT name, workflowkey, held FROM sync_state WHERE held = true still lists workflows that are Failed. Deleting those workflow objects does not release them.

Suggested fix

Either reconcile held rows against live workflows on controller startup, or make release independent of wf.Status.Synchronization — the workflow key is already in the row, so ownership can be re-derived without it.

Separately, populating the controller column would make the existing inactive-controller expiry usable as a backstop.