E2E Testing Guide for pg_durable

This guide explains how to set up and run end-to-end tests for pg_durable.

Prerequisites

  1. Docker - Required to run the test container ```bash

    Verify Docker is installed and running

    docker –version docker ps ```

  2. Rust toolchain - For building the extension ```bash

    Install via rustup if needed

    curl –proto ‘=https’ –tlsv1.2 -sSf https://sh.rustup.rs | sh ```

Quick Start

From the project root:

# Run all E2E tests
./scripts/test-e2e-local.sh

That’s it! The script will: 1. Build and install the extension into the local pgrx PostgreSQL 2. Start PostgreSQL in the required phase configuration 3. Wait for the background worker to be ready 4. Run the matching SQL test files 5. Report results by phase 6. Stop PostgreSQL unless --keep was requested

What Gets Tested

The test suite is organized into 23 files. Files 01–09 open with SET SESSION AUTHORIZATION df_e2e_user so the test logic runs as a non-privileged user. Files 10–16 run as postgres throughout and use inline SET SESSION AUTHORIZATION where needed.

Setup & Special

File Description
00_setup_playground.sql Shared test infrastructure — creates playground.* tables and helper functions (not a test)
00_requires_shared_preload.sql Verifies that the extension requires shared_preload_libraries; runs in the no-preload phase when selected by filename

Non-Privileged Tests (runs as df_e2e_user)

File Description
01_core_primitives.sql df.sql(), ~> sequence, df.join() / &, df.sleep(), df.join3(), df.race() / |
02_conditionals.sql df.if() / ?> / !> true & false branches, condition_node validation, df.if_rows()
03_loops.sql df.loop() / @> with df.cancel(), df.break() / ^?> with while-condition
04_variables_and_results.sql |=> / df.as(), df.setvar() / df.getvar() / {var} templates, dot-notation ($name.col), $name.* expansion, result-name validation
05_monitoring_and_explain.sql df.list_instances(), df.instance_info(), df.status(), df.result(), df.explain() dry-run and live modes
06_http_and_ssrf.sql HTTP allow-list enforcement, SSRF protection (metadata endpoints, localhost, file://, bare IPs); requires --features http
07_signals.sql df.signal() — send signals to a running workflow from within the polling loop
08_scenarios.sql End-to-end workflow scenarios using playground.* tables (ETL, parallel counts, conditional load, order processing, three-step)
09_graph_and_validation.sql df.explain() graph reuse, invalid node_type rejection
51_node_composite_pk.sql df.nodes composite PRIMARY KEY (instance_id, id) — schema contract (legacy nodes_instance_node_key UNIQUE promoted to the PK) and multi-node workflow regression under instance_id-scoped node-status updates and df.result() (issue #129)

Superuser Tests (runs as postgres)

File Description
10_connection_limits.sql pg_durable.max_user_connections defaults — concurrent workflows
11_cross_connection.sql df.signal() / df.cancel() / df.status() via dblink from a separate backend; transaction commit/rollback semantics
12_extension_lifecycle.sql BGW init after CREATE EXTENSION, schema cleanup after DROP, security: non-superuser block, pre-existing schema block
13_user_isolation.sql Superuser-only queries, two-user table isolation, SET ROLE, SECURITY DEFINER, dropped-role error
14_database.sql Wrong-database CREATE EXTENSION rejection; df.start(query, label, database) multi-database routing
15_rls.sql RLS on df.instances / df.nodes / df.vars — per-user visibility, cross-user cancel/signal denied, column-level UPDATE, superuser bypass, per-user variable isolation
16_heartbeat.sql Worker heartbeat liveness — df._worker_epoch.last_seen_at advances over time
52_node_id_collision_across_instances.sql Cross-instance node-ID collision — two instances own the same 8-hex node id; asserts composite-PK coexistence, that (instance_id, id) addresses exactly one row, df.result() is instance-scoped, and a scoped update_node_status-style UPDATE affects exactly one row (issue #129)

Build-Phase Specific

File Phase Description
44_connection_limit_backpressure.sql connlimit-backpressure Backpressure: 4 workflows complete when max_user_connections=2
45_connection_limit_timeout.sql connlimit-timeout Timeout error after execution_acquire_timeout expires
46_connection_limit_startup_validation.sql connlimit-startup BGW refuses to start with invalid GUC value
66_new_transaction_launch_limit.sql new-start-limit transaction_mode => 'new' launch admission cap, timeout, and cleanup
47_http_dsl_disabled.sql http-disabled df.http() unavailable when built without --features http
48_http_allow_all.sql http-allow-all All HTTP destinations allowed when built with --features http-allow-all

Test Structure

pg_durable/
├── scripts/
│   └── test-e2e-local.sh     # Local test runner
└── tests/
    └── e2e/
        └── sql/
            ├── 00_setup_playground.sql      # Shared infrastructure (run first)
            ├── 00_requires_shared_preload.sql
            ├── 01_core_primitives.sql
            ├── 02_conditionals.sql
            ├── ...                          # 03–16 feature test files
            ├── 44_connection_limit_backpressure.sql
            ├── 45_connection_limit_timeout.sql
            ├── 46_connection_limit_startup_validation.sql
            ├── 47_http_dsl_disabled.sql
            ├── 48_http_allow_all.sql
            ├── 49_quoted_role_names.sql
            ├── 50_metrics_grants.sql
            ├── 51_node_composite_pk.sql
            └── 52_node_id_collision_across_instances.sql

Writing New Tests

Create a new .sql file in tests/e2e/sql/:

-- Test: Description of what this tests
-- Expected: What should happen

\set ON_ERROR_STOP on

-- Setup (optional)
DROP TABLE IF EXISTS test_mytable;
CREATE TABLE test_mytable (...);

-- Start the durable function
SELECT df.start(...) AS instance_id \gset

-- Wait for completion
DO $$
DECLARE
    status TEXT;
    attempts INT := 0;
BEGIN
    LOOP
        SELECT s INTO status FROM df.status(:'instance_id') s;
        EXIT WHEN lower(status) IN ('completed', 'failed', 'cancelled') OR attempts > 300;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) != 'completed' THEN
        RAISE EXCEPTION 'TEST FAILED: expected completed, got %', status;
    END IF;
END $$;

-- Verify results
DO $$
BEGIN
    -- Your assertions here
    IF (some condition fails) THEN
        RAISE EXCEPTION 'TEST FAILED: reason';
    END IF;
END $$;

-- Cleanup
DROP TABLE test_mytable;

-- Report success
SELECT 'TEST PASSED: my_test_name' AS result;

Debugging Failed Tests

View PostgreSQL logs

# Keep PostgreSQL running after the test run
./scripts/test-e2e-local.sh --keep

# Then inspect the background worker and server logs
tail -f ~/.pgrx/17.log

Run single test manually

# Run one consolidated file locally
./scripts/test-e2e-local.sh 01_core_primitives --verbose --keep

# Or run a specific phase-scoped test
./scripts/test-e2e-local.sh --http-disabled 47_http_dsl_disabled --verbose --keep

# Connect to the kept server
~/.pgrx/17.*/pgrx-install/bin/psql -h localhost -p 28817 -d postgres

Connect to the running local test server

If you kept PostgreSQL running during or after a test run:

# In another terminal while tests are running
~/.pgrx/17.*/pgrx-install/bin/psql -h localhost -p 28817 -d postgres

CI Integration

Add to your CI pipeline:

./scripts/test-e2e-local.sh

Makefile Targets

make test        # Run all tests (unit + E2E)
make test-e2e    # Run only E2E tests
make test-unit   # Run only pgrx unit tests

Troubleshooting

Docker not running

Error: Cannot connect to the Docker daemon

→ This guide does not use Docker for the local E2E workflow. Use ./scripts/test-e2e-local.sh instead.

PostgreSQL not initialized

Error: pgrx PostgreSQL 17 not installed

→ Run cargo pgrx init

Tests timeout

TEST FAILED: status = pending

→ Background worker may not be starting. Check logs: bash tail -f ~/.pgrx/17.log

Build or install fails

Error: cargo pgrx install failed

→ Run cargo build --features pg17 and then retry the test runner