@effect/sql-pg: after a fatal session error, the same fiber gets the dead connection from the pool again

[Mosnar]

What happens

PgConnectionImpl.fatal calls consumer?.onFatal(error) before this.retire(). onFatal resumes the fiber of the failed statement synchronously, so that fiber keeps running inside fatal, before the PgPool retire hooks have run (they add the session to deadConnections and invalidate the pool item).

The fiber's next statement goes through PgPool.use. The fast path (deadConnections.size === 0) is still taken, so Pool.use hands out the same dead session. The statement fails at once with the same deadWith error and does no I/O, so nothing yields. This repeats for every statement the fiber runs before it yields: error handlers, "mark job failed" writes, zero-delay retries. Only then does fatal reach retire().

Reproduction

Versions: effect 4.0.0-rc.117, @effect/sql-pg 4.0.0-rc.117. Pool of 1 or 2 connections.

import { PgClient } from "@effect/sql-pg"
import { Effect } from "effect"

const program = Effect.gen(function* () {
  const sql = yield* PgClient.PgClient
  // interval has no codec and its binary form is not valid UTF-8:
  // fails with "PgConnection: Failed to decode row"
  yield* Effect.result(sql`SELECT interval '-1 day' AS v`)
  for (let i = 0; i < 6; i++) {
    const r = yield* Effect.result(sql`SELECT 1 AS n, pg_backend_pid() AS pid`)
    console.log(i, r._tag)
  }
})

• Result: all six SELECT 1 statements fail with "Failed to decode row".
• With yield* Effect.sleep(10) after the failing statement, all six succeed on a new backend pid.

Suggested fix

Call this.retire() before consumer?.onFatal(error) in fatal (a one-line move). The retire hooks only add the session to a set and fork Pool.invalidate, so they do not depend on the consumer having been failed first; the lease still held by the failing statement is released after invalidation, so the item is finalized rather than reused. We run this as a local patch on rc.117 with a regression test, and the database suites pass.

Upstream main (after #8354) has the same order in packages/sql/pg/src/PgConnection.ts.