How the pg_durable Worker Coordinates with User Sessions Using the _worker_ready Table

The pg_durable background worker writes a sentinel record to the duroxide._worker_ready table after initialization, while user sessions query this table via is_worker_ready() to verify the worker is active and schema-compatible before executing durable functions.

The Microsoft pg_durable extension embeds the Duroxide runtime as a PostgreSQL background worker. To prevent race conditions between worker startup and user sessions attempting to invoke durable functions like df.start() or df.signal(), the extension implements a lightweight coordination mechanism centered on a single-row sentinel table called _worker_ready.

The Sentinel Table Design

The coordination relies on a table named duroxide._worker_ready that acts as a persistent, queryable flag. This table contains exactly one row (enforced by a PRIMARY KEY constraint on a sentinel boolean column) storing the current schema version and initialization timestamp. Because the table resides in the database catalog, it survives worker restarts and remains visible to all PostgreSQL sessions without requiring inter-process shared memory.

How the Worker Signals Readiness

When the background worker finishes its startup sequence—including loading migrations and registering the schema—it calls write_worker_ready(&mgmt_pool).await in src/worker.rs to publish its ready state.

Creating the Readiness Record

The write_worker_ready function performs three atomic operations:

1. Idempotent table creation using CREATE TABLE IF NOT EXISTS
2. Permission grants allowing PUBLIC to read but not modify the table
3. Upsert of the sentinel row containing the WORKER_SCHEMA_VERSION constant

// src/worker.rs – writes the worker-ready record
async fn write_worker_ready(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS duroxide._worker_ready (
            sentinel        BOOLEAN PRIMARY KEY DEFAULT TRUE,
            CONSTRAINT      only_one_sentinel CHECK (sentinel),
            schema_version  INT NOT NULL,
            initialized_at  TIMESTAMPTZ NOT NULL DEFAULT now()
        )",
    )
    .execute(pool)
    .await?;

    // make the table readable to all sessions
    sqlx::query("GRANT USAGE ON SCHEMA duroxide TO PUBLIC")
        .execute(pool)
        .await?;
    sqlx::query("GRANT SELECT ON duroxide._worker_ready TO PUBLIC")
        .execute(pool)
        .await?;

    // insert or update only when version changes
    sqlx::query(
        "INSERT INTO duroxide._worker_ready (sentinel, schema_version, initialized_at) \
         VALUES (TRUE, $1, now()) \
         ON CONFLICT (sentinel) DO UPDATE SET \
             schema_version = EXCLUDED.schema_version, \
             initialized_at = EXCLUDED.initialized_at \
         WHERE duroxide._worker_ready.schema_version != EXCLUDED.schema_version",
    )
    .bind(crate::WORKER_SCHEMA_VERSION)
    .execute(pool)
    .await?;

    Ok(())
}

The ON CONFLICT clause ensures that the initialized_at timestamp is preserved across worker restarts unless the schema version actually changes, preventing unnecessary updates while allowing version upgrades to refresh the timestamp.

How User Sessions Verify Readiness

Before constructing a Duroxide client, every user-session function calls is_worker_ready() in src/client.rs. This function uses PostgreSQL's SPI (Server Programming Interface) to execute two lightweight catalog queries.

The Readiness Check

// src/client.rs – determines whether the worker is ready
fn is_worker_ready() -> bool {
    // 1. Does the sentinel table exist?
    let table_exists = Spi::get_one::<bool>(
        "SELECT EXISTS(SELECT 1 FROM pg_catalog.pg_tables \
         WHERE schemaname = 'duroxide' AND tablename = '_worker_ready')",
    )
    .ok()
    .flatten()
    .unwrap_or(false);

    if !table_exists {
        return false;
    }

    // 2. Does the row have a sufficient schema_version?
    Spi::get_one_with_args::<bool>(
        "SELECT EXISTS(SELECT 1 FROM duroxide._worker_ready WHERE schema_version >= $1)",
        &[crate::WORKER_SCHEMA_VERSION.into()],
    )
    .ok()
    .flatten()
    .unwrap_or(false)
}

If is_worker_ready() returns false, the client aborts with the error "pg_durable background worker not yet initialized — try again in a moment". This prevents sessions from attempting to connect to a runtime that hasn't finished loading its internal state or migrations.

Coordination Flow and Concurrency

The interaction between the worker and user sessions follows a strict writer-reader pattern that eliminates race conditions:

1. Worker startup – Loads schema, runs migrations, then executes write_worker_ready() to create/verify the sentinel row.
2. Session request – Calls df.start(), df.signal(), or df.cancel(), which internally invokes get_duroxide_client().
3. Readiness verification – get_duroxide_client() calls is_worker_ready(); if the table is missing or the version is stale, it returns an error immediately.
4. Operation execution – Only after confirming the sentinel row exists with a compatible schema version does the session proceed with the durable function call.

Because the worker holds exclusive write access (no INSERT, UPDATE, or DELETE privileges are granted to PUBLIC), concurrent sessions cannot accidentally modify the readiness state. The CHECK (sentinel) constraint guarantees exactly one row, making the existence check deterministic.

Practical Implementation Example

When implementing a custom durable function handler, you rely on the automatic readiness checks built into the pg_durable client API:

use pg_durable::client::start_durable_function;

fn my_orchestrator_handler() {
    // Automatically checks _worker_ready via is_worker_ready() internally
    match start_durable_function("my_orchestrator", "instance-123", "{}") {
        Ok(_) => log!("Orchestration started successfully"),
        Err(e) => log!("Failed to start: {}", e),
    }
}

If the background worker is still initializing or has crashed, the session receives an immediate error rather than hanging or attempting to connect to an uninitialized runtime.

Summary

• The pg_durable worker uses write_worker_ready() in src/worker.rs to create a single-row sentinel table named duroxide._worker_ready after completing startup.
• The table stores the current schema version (WORKER_SCHEMA_VERSION) and initialization timestamp, with read-only access granted to PUBLIC.
• User sessions call is_worker_ready() in src/client.rs before executing durable functions, verifying both table existence and version compatibility via SPI queries.
• This design provides a resilient, race-free coordination mechanism that persists across worker restarts while preventing sessions from accessing uninitialized runtime state.

Frequently Asked Questions

What happens if the pg_durable worker crashes or restarts?

If the worker restarts with the same schema version, the ON CONFLICT clause in write_worker_ready() leaves the existing row unchanged, so sessions continue to see a ready state. If the worker crashes without restarting, subsequent calls to is_worker_ready() continue to return true based on the existing table row until the worker either restarts (refreshing the timestamp if the version changed) or the extension is dropped. This prevents false negatives during brief worker restarts but requires administrative monitoring of the initialized_at timestamp to detect stale ready states.

How does the _worker_ready table handle schema migrations?

The schema_version column in duroxide._worker_ready stores the WORKER_SCHEMA_VERSION constant defined in src/types.rs. When the worker starts after an upgrade that includes schema changes, it detects the version mismatch during the upsert operation and updates both the version number and initialized_at timestamp. Sessions running the new client code check for schema_version >= WORKER_SCHEMA_VERSION, ensuring backward compatibility during rolling upgrades while blocking outdated clients from connecting to newer worker versions.

Why use a database table instead of shared memory or signals for coordination?

Using a catalog table rather than shared memory provides persistence across process restarts and visibility to all PostgreSQL sessions without requiring complex inter-process locking mechanisms. The SPI-based existence check is fast and operates within the session's security context, while the SQL-based approach allows administrators to query duroxide._worker_ready directly for debugging. Additionally, the table-based approach naturally supports privilege management through standard PostgreSQL GRANT statements, ensuring sessions can verify readiness without obtaining elevated permissions.

Can multiple pg_durable workers run concurrently on the same database?

The _worker_ready table design assumes a single worker instance per database. The PRIMARY KEY constraint on the sentinel column prevents multiple workers from inserting conflicting rows, though the last writer would effectively own the readiness signal. In configurations with multiple workers, only the worker that successfully writes the sentinel row will be considered "ready" by client sessions, making this table suitable for singleton worker architectures but requiring additional coordination mechanisms for multi-worker deployments.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}