Intermittent Connection Error in CI: `Expected password response, got message type 88`

[pyramation]

I've observed a rare and intermittent error during CI runs on GitHub Actions:

Expected password response, got message type 88

This appears to occur when the test suite or app attempts to connect to PostgreSQL before it's fully ready to accept connections, causing the client to send a termination ('X') message instead of a proper authentication response.

It does not happen consistently, only sporadically — likely due to timing/race conditions during service startup in the CI environment.

Probable Cause:

• PostgreSQL service is still initializing when the connection is attempted.
• The client receives an incomplete or unexpected response and closes the connection, triggering the error.

Suggested Fix:

• Add a retry or readiness check using pg_isready or a custom wait loop before running tests or migrations.
• Optionally configure a healthcheck for PostgreSQL in docker-compose.yml and wait for it to report healthy status before continuing.

[pyramation]

Intermittent expected password response, got message type 88 (08P01) during tests with pgsql-test

---

Description

When running tests with pgsql-test (locally or in CI), we intermittently hit the following fatal Postgres protocol error:

error: expected password response, got message type 88
    at Parser.parseErrorMessage (pg-protocol/src/parser.ts:369:69)
    ...
  severity: 'FATAL',
  code: '08P01',
  file: 'auth.c',
  line: '689',
  routine: 'recv_password_packet'

Message type 88 corresponds to 'X' (Terminate). This means the server is expecting a password during the authentication handshake, but instead receives a termination packet from the client.

This happens randomly, not deterministically, and is most common when tests run in parallel.

---

Environment

• Node.js v22.13.1
• pg 8.16.3 (via pgsql-test@2.6.2)
• pg-protocol@1.10.3
• MacOS (local) and CI runners
• Using pgsql-test to spin up ephemeral databases per test suite

---

Reproduction

1. Run pnpm test in a workspace package (e.g. @launchql/inflection).
2. Jest spins up ephemeral databases (db-...) via pgsql-test.
3. During afterAll teardown, pools are closed and the database is dropped.
4. Intermittently, one or more Jest workers throw the error above.

---

Observations

• The error happens during authentication (before password exchange completes).

• Logs show pg.Pool clients attempting to connect after teardown has started.

• Teardown closes pools and immediately drops the DB:

  • pool.end() closes sockets
  • DROP DATABASE runs

• Any clients still in-flight (during startup/auth) receive a Terminate mid-handshake → server raises 08P01.

---

Root Cause

This is a race condition between:

1. Jest workers / pgsql-test trying to establish new connections.
2. Teardown closing pools and dropping the database.

Connections can arrive just as teardown begins, leading to protocol mismatch.

---

Impact

• Flaky test runs (intermittent failures).
• CI pipelines failing nondeterministically.
• Developers rerunning tests multiple times to get a clean pass.

---

Proposed Solutions

Short-term mitigations

• Run DB-creating tests serially (jest --runInBand or maxWorkers=1).
• Add artificial delay before DROP DATABASE to allow pending auth to finish.
• Increase connectionTimeoutMillis to avoid premature aborts mid-auth.

Longer-term fixes

1. Teardown gating

   • Track pending pool.connect() calls.
   • Block new connections once teardown starts.
   • Wait for all pending connects to settle before pool.end() and DROP DATABASE.

2. Single pool per temp DB

   • Avoid creating multiple pools per ephemeral DB.
   • Reuse a shared pool per role (postgres, app_user) during test lifetime.

3. Advisory lock around DB lifecycle

   • Use pg_advisory_lock() in the admin connection when creating/dropping DBs.
   • Ensures only one worker at a time manipulates ephemeral DB state.

4. Safer cleanup in pgsql-test

   • Wrap afterEach rollback/cleanup in try/catch (ignore if connection is already closed).
   • Ensure pool.end() is only called once after afterAll.

---

Next Steps

• Decide whether pgsql-test should implement teardown gating + advisory locking internally.
• Add a config option to run teardown serially or with a safety delay.
• Update docs to recommend --runInBand for packages creating/dropping DBs in parallel.

[pyramation]

PG authentication, teardown gating, and test stability

Overview
This document explains the intermittent Postgres protocol error 08P01 (expected password response, got message type 88/‘X’ Terminate) we observed during parallel tests, the fixes implemented in packages/pgsql-test, and guidance for correct usage with related utilities (pg-cache and teardownPgPools). It also outlines best practices to avoid regressions.

Problem summary

• Symptom: Random CI/local failures with error code 08P01 during authentication: server expects password but receives a Terminate packet.
• Root cause: A race between new client connections starting authentication and test teardown closing pools and dropping the database. If teardown terminates sockets or drops the DB mid-handshake, the server sees a protocol mismatch and raises 08P01.

What changed in pgsql-test

• Teardown gating in PgTestConnector
  • A shuttingDown flag blocks creation of new clients after teardown begins.
  • All in-flight client connect() promises are tracked (pendingConnects).
  • closeAll() now:
    • begins teardown (sets shuttingDown = true),
    • awaits pending connect handshakes to settle,
    • closes clients, disposes pools, and drops tracked databases,
    • resets state at the end (clears pendingConnects, sets shuttingDown = false) so subsequent tests can create clients again.
• Client lifecycle hardening
  • PgTestClient now stores its connect promise and close() awaits the handshake before ending the connection to avoid sending Terminate mid-auth.
• Teardown sequence
  • getConnections() teardown calls manager.beginTeardown() before teardownPgPools() and manager.closeAll() so gating is active as soon as teardown starts.

Why this fixes 08P01

• The failure mode required a new auth handshake to overlap with teardown. Gating prevents new handshakes once teardown starts.
• Awaiting pending connects ensures any already-started handshakes finish before we end pools or drop databases.
• Await-before-end in PgTestClient.close() avoids “Terminate during auth” on the client side.

About “cannot create client while shutting down”

• This error is expected if code tries to create clients during teardown. That’s precisely the unsafe window we’re protecting against.
• After closeAll() completes, PgTestConnector resets shuttingDown=false, so subsequent beforeEach/beforeAll can create clients normally.
• Seeing this error outside of teardown indicates a test or helper is racing teardown; the call site should be moved earlier (e.g., into beforeAll) or delayed until after teardown completes in the next test cycle.

Interaction with pg-cache

• pgsql-test itself does not rely on pg-cache for connection gating. However, some tests and utilities use teardownPgPools from pg-cache to ensure pools are closed.
• Using teardownPgPools remains valid and is part of the recommended teardown sequence. In pgsql-test, we call manager.beginTeardown() first, then teardownPgPools(), and finally manager.closeAll()—this ensures no new clients can be created while pools are shutting down.

Guidance for using teardownPgPools

• Always initiate teardown gating (manager.beginTeardown()) before calling teardownPgPools() if you are orchestrating teardown yourself. In pgsql-test’s standard flow, connect.ts does this for you.
• Avoid creating new Pool or Client instances in afterAll/afterEach; if you must perform final queries, perform them before starting teardown.
• If you maintain custom pools, make sure they are ended exactly once and do not auto-recreate after teardown begins.

Audit notes on current usages

• getConnections() pattern in pgsql-test and graphile-test wraps setup/teardown consistently and now engages gating at teardown start.
• Tests that manually use pg-cache and teardownPgPools should follow the sequence:
  1. beginTeardown()
  2. teardownPgPools()
  3. closeAll()
• The “cannot create client while shutting down” error would indicate a call to getConnections() or new client creation during teardown; after the recent fix, CI showed this only when the gate wasn’t reset—now resolved by resetting after cleanup.

Concrete audit findings (file references)

• Core fixture uses pg-cache teardown in cleanup:
  • /packages/core/test-utils/CoreDeployTestFixture.ts: cleanup() awaits teardownPgPools(); this path does not create PgTestConnector clients directly, so gating is not required here.
• Graphile test utilities compose pgsql-test teardown correctly:
  • /packages/graphile-test/src/get-connections.ts: createConnectionsBase() returns dbTeardown from pgsql-test and calls it after gqlContext.teardown(); no client creation occurs after teardown begins.
• CLI tests close pools after fixture teardown:
  • /packages/cli/tests/deploy.test.ts: afterAll awaits fixture.cleanup() then calls teardownPgPools(); no new clients created during teardown.
  • /packages/cli/tests/tags.test.ts: same pattern—fixture.cleanup() followed by teardownPgPools().
  • /packages/cli/tests/cli-deploy-fixture.test.ts: top-level afterAll calls teardownPgPools(); per-suite afterAll calls fixture.cleanup(); ordering prevents post-teardown client creation.
• pgsql-test orchestrates safe teardown sequencing:
  • /packages/pgsql-test/src/connect.ts: teardown calls manager.beginTeardown() → teardownPgPools() → manager.closeAll(), enabling gating before pool shutdown and awaiting pending connect handshakes.
• pg-cache lifecycle:
  • /packages/pg-cache/src/lru.ts: PgPoolCacheManager manages Pool instances and teardownPgPools() disposes them; it does not introduce new clients during shutdown.

Summary of risks checked

• No tests or utilities were found creating clients after teardown begins.
• Places that rely only on teardownPgPools() (without pgsql-test gating) are not also creating PgTestConnector clients, so they remain safe.
• Composition layers (graphile-test) defer to pgsql-test teardown, which engages gating early and prevents the 08P01 race.

Actionable guidance linked to files above

• If you manage both PgTestConnector and pg-cache in custom fixtures, use the sequence:
  1. manager.beginTeardown()
  2. teardownPgPools()
  3. manager.closeAll()
• Keep client creation in beforeAll/beforeEach; avoid creating clients or pools within or racing with afterAll/teardown.

Best practices and recommendations

• Keep client creation in beforeAll/beforeEach and teardown in afterAll/afterEach; do not mix creation inside teardown blocks.
• Do not open new connections once teardown begins. If a test needs extra work at the end, finish it before teardown starts.
• If reusing pools, be sure they do not spawn new clients after teardown begins.
• Prefer the provided getConnections() helper which handles gating and teardown sequence for you.

Troubleshooting checklist

• Seeing 08P01 again?
  • Check if any code opens a client after teardown begins.
  • Ensure closeAll() is awaited before starting the next test’s setup in your custom fixtures.
• Seeing “PgTestConnector is shutting down; no new clients allowed”?
  • Confirm the call is not inside or racing with afterAll/teardown.
  • If it happens at the next test’s beforeEach, ensure the previous teardown finished (await promises).
• Pool leaks or double-end warnings?
  • Ensure teardownPgPools() and closeAll() are each called once per test environment.
  • Verify no background process is constructing pools after teardown starts.

Possible future improvements

• Optional config flag to disable gating or increase safety delays for special scenarios.
• Docs/examples showing custom fixtures that combine pg-cache with PgTestConnector gating explicitly.
• Additional diagnostics around who attempts to create clients during teardown (stack traces guarded by a debug flag).

Conclusion
With teardown gating, pending connect tracking, and orderly close semantics, the intermittent 08P01 authentication protocol errors are resolved. The system now blocks unsafe new connections during teardown, waits for in-flight handshakes, and resets state so subsequent tests proceed normally. Follow the guidance above when using pg-cache and teardownPgPools to maintain stability.