step_created entity row and event are written non-transactionally — a crash between them wedges the run permanently

[ShadowWalker2014]

step_created entity row and event are written non-transactionally — a crash between them wedges the run permanently

Summary

In @workflow/world-postgres, events.create() writes a step as two separate statements with no transaction:

1. workflow_steps — the entity row (the exactly-once ownership claim)
2. workflow_events — the step_created row (replay's source of truth)

A crash between them leaves an entity row with no step_created event. That state is unrecoverable: the run stays running with a pending step and replays forever without progressing.

You already recognise and repair this exact partial write on the hook_created path — the step_created path has no equivalent branch.

Why it deadlocks

The two sources of truth disagree, and each side is behaving correctly:

• Replay rebuilds from the event log. No step_created → it concludes the step was never created and re-issues it as a lazy step_started (one carrying stepName + input) — @workflow/core dist/runtime/step-executor.js:255.
• The lazy-start exactly-once gate reads the entity row, finds the orphan, concludes a concurrent worker won the create, and throws — dist/storage.js:526:

if (lazyStepStart && validatedStep) {
  throw new EntityConflictError(`Step "${data.correlationId}" already created`);
}

• step-executor.js:152-163 maps EntityConflictError to { type: 'skipped' }, and the aggregate loop re-replays with no backoff.

So the log says "never created, run it", the row says "already claimed, skip it", and every replay re-derives the same standoff. There is no path out — no retry, no timeout, no reaper in the library, and the run never reaches a terminal state.

Your own precedent

dist/storage.js:1042, on the hook path:

Idempotency: if the existing hook is the same (runId, hookId) we are trying to create, this is either a duplicate / replayed processing of the same hook_created (not a real conflict), or an orphaned hook row from a prior crashed attempt (the hook INSERT below landed but the events INSERT below didn't — these writes are not in one transaction). Distinguish by checking whether the hook_created event actually exists in the event log … missing → orphaned hook row (crash between hook INSERT and events INSERT): skip the hook insert … and fall through to the events INSERT below, completing the partial write.

That's the correct handling. step_created (storage.js:811) and the lazy-start claim (storage.js:857) both use bare onConflictDoNothing() and never check whether the creation event exists.

Production evidence

Self-hosted world-postgres, ~12M journal inserts/day.

• 15 step entity rows with no step_created event accumulated over 11 days (~1.4/day). Same DB: 0 orphaned hooks (your fix works) and 0 orphaned waits.
• One instance wedged a live customer session for 7 hours. The workflow body re-executed ~500×/sec the entire time, pegging a worker thread. Only manual DB intervention cleared it.
• On the wedged run, the step had a workflow_steps row and the event log simply stopped at the previous step's step_completed — no step_created for it, ever.

The window is not small in practice. From pg_stat_statements on that database:

statement	mean	max
insert into workflow_events	12–364 ms	94.6 s
insert into workflow_steps	87 ms	129.1 s

Under load the gap between the two inserts can exceed a minute, so any process termination in that window (deploy, OOM, SIGKILL, connection loss) can produce this.

Suggested fix

Either would work:

1. Make the two writes atomic — wrap the entity insert and its creation event in one transaction. This is the real fix and would cover step_created, wait_created, and attr_set, which all share the shape.
2. Port the hook branch to steps — on a zero-row onConflictDoNothing(), check whether the step_created event exists. Present → genuine concurrent create, throw EntityConflictError as today. Missing → orphaned partial write, complete it instead of throwing.

Note wait_created and attr_set are written the same way and are covered by the same partial index (workflow_events_entity_creation_unique), so (1) is the durable answer.

Workaround we're running

We wrap world.events in our composed world (no patch to your package) and, on that specific conflict, re-issue the missing step_created through your own create() using the payload the intercepted lazy step_started already carries. The original error is still rethrown — the gate keys off the entity row, so that call can't proceed — and the next replay sees the event, emits a plain step_started, and runs the step.

We deliberately did not delete the orphan row: proving a row is abandoned requires an age threshold larger than the max insert latency above, which means minutes of user-visible stall, and losing that race inverts the partial write into an event with no row.

Environment

• @workflow/world-postgres 5.0.0-beta.20
• @workflow/core 5.0.0-beta.25
• Self-hosted Postgres world, graphile-worker queue, Node 22

[ShadowWalker2014]

Cross-referencing two earlier reports in the same family, both closed:

• @workflow/world-postgres can insert duplicate step_created events under concurrent workers #2039 — world-postgres can insert duplicate step_created events under concurrent workers
• @workflow/world-postgres: late concurrent step_started can land after step_completed in the event log (non-atomic entity UPDATE + event INSERT) #2331 — late concurrent step_started can land after step_completed in the event log (non-atomic entity UPDATE + event INSERT)

Same underlying shape each time: an entity write and its event write are separate statements, so any interleaving or interruption between them leaves the entity table and the event log disagreeing. #2331 is the closest — it names the non-atomicity explicitly, for the UPDATE + INSERT pair rather than the INSERT + INSERT pair here.

That's four instances now across steps and hooks, fixed one symptom at a time. Suggested fix (1) — one transaction around the entity write and its creation event — would close the whole family rather than the next symptom, and would also cover wait_created and attr_set, which share the shape and the workflow_events_entity_creation_unique index but have no reported instances yet.

Happy to test a patch against our workload if useful — we run a self-hosted postgres world at ~12M journal inserts/day and can reproduce the wedge deterministically by deleting a step_created row.

[muZk]

Two more instances of this shape, both on the UPDATE + INSERT pair rather than the INSERT + INSERT one — and they contradict the "0 orphaned waits" line in the report above, so I think they strengthen the case for your suggested fix (1) over another per-event-type patch.

What we saw

Two long-lived bounded control-loop workflows (tick → sleep → repeat, ~60 and ~6 iterations) each wedged permanently, weeks apart, on the same underlying divergence: the entity row reached completed, but the matching completion event never landed in workflow_events.

run	entity table	event log	missing
A (tick loop)	19 waits, 19th completed	19 wait_created, 18 wait_completed	the 19th wait_completed
B (sweep loop)	2 steps, both completed	2 step_started, 1 step_completed	the 2nd step_completed

Confirmed by anti-join — a workflow_waits / workflow_steps row in a terminal state with no corresponding event for its correlation id. Zero orphans of the step_created shape in the same database, so this is the completion-side variant, not the one reported above.

The symptom is different, and that matters for the workaround

Your wedge produces a conflict error on replay, which is what gives your composed-world wrapper something to intercept and a payload to re-issue from. Ours produces no error at all:

• Replay rebuilds from the event log, sees a wait/step it started but never saw finish, and re-suspends.
• handleSuspension computes minTimeoutSeconds from the wait items as Math.max(1000, resumeAt - now). resumeAt is long past, so it floors to 1.
• The queue delivery gets a clean 200 {"timeoutSeconds": 1} and reschedules.

So the run live-loops on a successful delivery path forever, and nothing anywhere throws. I verified this by replaying the pending wake-up job by hand and reading the response body. One of these ran for 25 days; the other for 2. Because both runs stayed running, our own supervisor treated them as healthy and never started a replacement lineage — the outage was silent apart from a lifetime-bound alert.

There is no conflict to hook, so the interception workaround in the report above has nothing to key off. A repair would have to be an out-of-band sweeper that appends the missing completion event — and that is materially riskier than your step_created repair, because the entity-creation partial unique index (workflow_events_entity_creation_unique) does not cover completion events, so a false positive writes a duplicate step_completed instead of being rejected. Given the 94.6 s / 129.1 s max insert latencies quoted above, the safe age threshold for such a sweeper is large.

We are not doing that. Our two wedged runs were idempotent control loops, so cancelling them and letting the supervisor start fresh lineages was correct and cheap — recovery took under 60 s once identified. But that only works because these particular runs were disposable. It is not available to anyone whose wedged run represents real in-flight work, which is the case in the report above.

Why fix (1)

wait_completed and step_completed are both still a bare await drizzle.update(...) followed by a separate event INSERT — in 4.1.1 (0 transactions in storage.js), in 4.3.1 (1, for step_started), and in 5.0.0-beta.28 (3, for run_started / step_started / hook_received). #2331 named this exact non-atomic UPDATE + INSERT pair and was closed after making one event type atomic; the completion events it described are still non-atomic three releases later.

Counting the report above, that is now six instances across step_created, hook_created, step_started, step_completed, wait_completed, and duplicate step_created (#2039). Each has been addressed as its own symptom. Wrapping events.create()'s entity mutation and event insert in one transaction closes the whole family at once, including the two here, and removes the need for every self-hosted operator to build a different bespoke repair for whichever variant they hit.

Environment

• @workflow/world-postgres 4.1.1 (local patch to dist/streamer.js only — storage.js unmodified, tx_count = 0 verified on the deployed image)
• @workflow/core 4.2.4, workflow 4.2.4
• graphile-worker 0.16.6, Node 24, self-hosted Postgres world

Edited to correct the package version: the storage.js observations are from @workflow/world-postgres 4.1.1, not 4.2.4 (that is the workflow SDK version in the same project).

[olgen]

Another instance of the wait_completed UPDATE + INSERT variant described by @muZk above, on current versions, with a failure mode that goes beyond a silent wedge.

Versions

• @workflow/world-postgres 5.0.0-beta.40 (the wait_completed branch is still await drizzle.update(waits) followed by a separate event INSERT; same on main today)
• workflow / @workflow/core 5.0.0-beta.47
• self-hosted Postgres world, graphile-worker, Node 24, steps served by a separate deployment

What we saw (2026-09-11)

Two runs of a small sleep(15 min) → step workflow, created on 2026-09-08, ended up with workflow_waits.status = completed but no wait_completed event in workflow_events (only run_created, run_started, wait_created). From then on, every invocation's elapsed-wait pass called events.create(wait_completed), world-postgres answered EntityConflictError("already completed"), the runtime swallowed it, still saw a pending elapsed wait and enqueued a continuation with delaySeconds: 1 and a fresh :a<attempt> idempotency key.

The part that is new compared to the report above: this did not stay a quiet 1 s loop.

• Each time our step server was unavailable, graphile retried the failed delivery and the successful sibling deliveries had already enqueued their own next continuation, so the chains fanned out. We ended at ~27 parallel chains per run, waitContinuation.attempt > 26 000.
• Together the two runs produced ~1 700 /.well-known/workflow/v1/flow requests per minute. The step-server pods hit the 2 GB heap limit and OOM-crashed roughly every 5 minutes for hours (20 restarts on one pod), which in turn made every other workflow in the system fail with fetch failed and back off.
• All worker concurrency slots were permanently held by these two runs. The pod logs looked like "reminder workflows are failing", the actual cause was two three-day-old runs nobody was looking at.

So for anyone triaging: if the steps deployment is OOM-looping and the queue is "clogged", check graphile_worker.jobs payloads for a large waitContinuation.attempt before assuming the workload that shows up in the logs is at fault.

Detection

select w.run_id, w.wait_id
from workflow.workflow_waits w
where w.status = 'completed'
  and not exists (
    select 1 from workflow.workflow_events e
    where e.run_id = w.run_id and e.type = 'wait_completed'
      and w.wait_id = w.run_id || '-' || e.correlation_id);

What we did

Cancelled both runs by hand (status → cancelled, deleted their queue jobs); flow rate dropped from ~1 700/min to single digits within a minute and the OOM restarts stopped.

Then patched dist/storage.js locally (Bun patch), in line with fix (1) proposed above:

• the waits-row UPDATE and the event INSERT run in one drizzle.transaction (read committed, like the existing run_started / step_started / hook_received branches), so a failed insert rolls the row back to waiting;
• a row that is already completed and has no wait_completed event is treated as an orphan of the old path and gets the missing event written instead of a conflict, so an already-wedged run heals on its next replay; a genuine duplicate (row and event present) still raises EntityConflictError.

Regression test: a BEFORE INSERT trigger on workflow_events fails the wait_completed insert once, then asserts the row is still waiting and that the retry completes row + event. Fails on the unpatched file. Patch and test are in WeFix-Social/kindergeld.plus#1015 if useful as a reference; happy to turn it into an upstream PR if maintainers want that shape.

Related: the same non-transactional pattern on run creation (run row → slot marker → run_created) is #3656.