pg_regress Test Suite Specification

Overview

This specification describes the addition of a pg_regress-compatible test suite to pg_durable using the standard PostgreSQL regression testing framework. This aligns pg_durable with PostgreSQL community practices and enables testing against any PostgreSQL installation without Docker overhead.

Background

pg_regress is PostgreSQL’s official regression test driver: - Ships with PostgreSQL core (src/test/regress/) - Runs .sql test files and compares output against expected .out files - Industry standard for testing PostgreSQL extensions - Used by virtually all mature PostgreSQL extensions (PostGIS, pg_stat_statements, pgvector, etc.) - Familiar to PostgreSQL developers, DBAs, and contributors - Well-integrated with PostgreSQL build systems and package managers

Why pg_regress matters for pg_durable:

Community expectations - PostgreSQL extension users expect make installcheck to work
Development workflow - Test against installed PostgreSQL without Docker overhead
Fork integration - Required for integration into PostgreSQL distributions (Azure DB for PostgreSQL, Citus, AWS RDS, etc.)
Package maintainers - Linux distros (Debian, RHEL) use pg_regress for validation
Contributor friendly - Lower barrier to entry for PostgreSQL developers
Production testing - Test against actual production PostgreSQL installations

Current pg_durable testing: - Custom E2E framework in tests/e2e/ using Docker and shell scripts - Tests use polling loops with variable timing - Non-deterministic output (UUIDs, timestamps, variable wait times) - Not compatible with pg_regress’s output comparison model - Requires Docker, longer feedback cycles - Unfamiliar to most PostgreSQL developers

Goals

Industry standard testing - Use the same testing approach as mature PostgreSQL extensions
Faster development feedback - Test against local PostgreSQL without Docker
Enable fork integration - Allow pg_durable to be tested in PostgreSQL distributions
Lower contributor barrier - Familiar testing approach for PostgreSQL developers
Maintain existing E2E tests - Keep comprehensive tests for complex scenarios
Provide simple, deterministic tests - Cover core DSL functionality with reproducible output

Non-Goals

Replace existing E2E test suite (both will coexist)
Test every edge case with pg_regress (use E2E for complex scenarios)
Test background worker internals (focus on SQL API surface)

Design

Dual Test Suite Approach

pg_regress suite (sql/, expected/): - Simple, deterministic tests - Core DSL functionality - Fast feedback (no Docker) - Standard PostgreSQL integration - Runs against any PostgreSQL installation - Familiar to PostgreSQL developers - Quick iteration during development

E2E suite (tests/e2e/): - Complex scenarios (keep existing) - HTTP calls, external dependencies - Race conditions, cancellation - Background worker behavior - Full integration testing with Docker - Comprehensive scenario testing

Directory Structure

sql/                  # pg_regress input test files
├── 00_init.sql
├── simple.sql
├── sequence.sql
├── parallel.sql
├── conditional.sql
└── variables.sql
expected/             # Expected output files (generated)
├── 00_init.out
├── simple.out
├── sequence.out
└── ...
Makefile              # PGXS configuration (at repo root)

New Helper Function: df.await_instance()

Signature: sql df.await_instance( instance_id TEXT, timeout_seconds INT DEFAULT 30 ) RETURNS TEXT

Behavior: - Polls instance status until completed/failed/cancelled - Returns final status as text: 'completed', 'failed', or 'cancelled' - Raises exception on timeout - Encapsulates non-deterministic polling logic

Default timeout: 30 seconds - Matches current E2E tests (300 attempts × 0.1s = 30s) - Long enough for simple tests on slow CI systems - Short enough to fail fast on real issues

Implementation location: src/dsl.rs

Example usage: ```sql – Start instance SELECT df.start(‘SELECT 42’, ‘test’) AS instance_id \gset

– Wait for completion (deterministic timeout) SELECT df.await_instance(:‘instance_id’);

– Output: ‘completed’ ```

Making Tests Deterministic

Problems with current E2E tests:

Non-deterministic timing: sql -- ❌ Variable number of iterations LOOP SELECT s INTO status FROM df.status(rec.instance_id) s; EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 300; PERFORM pg_sleep(0.1); attempts := attempts + 1; END LOOP;
Variable instance IDs in output: sql -- ❌ UUID changes every run RAISE NOTICE 'Testing % variant: %', rec.variant, rec.instance_id; -- Output: Testing operator variant: f47ac10b-58cc-4372-a567-0e02b2c3d479
Timestamps in tables: sql -- ❌ Non-deterministic timestamps CREATE TABLE test_log (id SERIAL, step INT, ts TIMESTAMP DEFAULT now());

Solutions:

Use df.await_instance() instead of polling loops
Remove timestamps from test tables
Avoid RAISE NOTICE with instance IDs
Order all SELECT results explicitly

Test Conversion List

Phase 1: Core DSL (Priority 1)

Convert these tests from tests/e2e/sql/ to pg_regress format:

E2E Test	pg_regress Name	Description
`01_simple_sql.sql`	`simple.sql`	Basic SQL execution, `df.sql()`
`02_sequence.sql`	`sequence.sql`	Sequential execution (`~>`, `df.seq()`)
`03_variables.sql`	`variables.sql`	Variable binding (`\|=>`, `df.as()`)
`04_parallel_join.sql`	`parallel.sql`	Parallel execution (`&`, `df.join()`)
`05_conditional_true.sql` + `06_conditional_false.sql`	`conditional.sql`	Conditional logic (`df.if()`)

Coverage: Basic DSL operators and functions

Phase 2: Advanced DSL (Priority 2)

E2E Test	pg_regress Name	Description
`24_loop_break.sql`	`loops.sql`	Loop execution (`df.loop()`, break conditions)
`23_transactions.sql`	`transactions.sql`	Transaction semantics
`10_explain.sql`	`explain.sql`	Execution plan inspection

Coverage: Advanced features with deterministic behavior

Phase 3: Management API (Priority 3)

New Test	pg_regress Name	Description
N/A	`management.sql`	Status, cancel, cleanup operations
N/A	`labels.sql`	Label-based queries

Coverage: Management and observability

Excluded from pg_regress

These tests remain E2E-only (non-deterministic or complex):

E2E Test	Reason
`07_sleep.sql`	Timing-dependent
`08_loop_cancel.sql`	Race conditions, background worker timing
`09_monitoring.sql`	Real-time metrics, variable timing
`11-16_scenario_*.sql`	Complex multi-table scenarios
`17_race.sql`	Explicitly tests race conditions
`18_http.sql`	External HTTP dependencies
`19_github_api.sql`	External API dependencies
`21_signals.sql`	Complex timing, external signal sending
`22_cross_connection.sql`	Multi-connection complexity
`25_extension_creation_security.sql`	Security-specific, complex

Example Conversion

Before (E2E format)

File: tests/e2e/sql/02_sequence.sql

DROP TABLE IF EXISTS test_sequence_log;
CREATE TABLE test_sequence_log (id SERIAL, step INT, variant TEXT, ts TIMESTAMP DEFAULT now());

CREATE TEMP TABLE _test_state (instance_id TEXT, variant TEXT);

INSERT INTO _test_state SELECT df.start(
    'INSERT INTO test_sequence_log (step, variant) VALUES (1, ''op'')'
    ~> 'INSERT INTO test_sequence_log (step, variant) VALUES (2, ''op'')'
    ~> 'INSERT INTO test_sequence_log (step, variant) VALUES (3, ''op'')',
    'test-sequence-op'
), 'operator';

DO $$
DECLARE
    rec RECORD;
    status TEXT;
    attempts INT;
BEGIN
    FOR rec IN SELECT instance_id, variant FROM _test_state LOOP
        RAISE NOTICE 'Testing % variant: %', rec.variant, rec.instance_id;
        attempts := 0;

        LOOP
            SELECT s INTO status FROM df.status(rec.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 [%]: status = %', rec.variant, status;
        END IF;
    END LOOP;
END $$;

SELECT step, variant FROM test_sequence_log ORDER BY id;
DROP TABLE _test_state;
DROP TABLE test_sequence_log;
SELECT 'TEST PASSED' AS result;

After (pg_regress format)

File: sql/sequence.sql

-- Test sequential execution using ~> operator and df.seq() function
DROP TABLE IF EXISTS test_sequence_log;
CREATE TABLE test_sequence_log (id SERIAL, step INT, variant TEXT);

-- Test A: Using ~> operator
SELECT df.start(
    'INSERT INTO test_sequence_log (step, variant) VALUES (1, ''op'')'
    ~> 'INSERT INTO test_sequence_log (step, variant) VALUES (2, ''op'')'
    ~> 'INSERT INTO test_sequence_log (step, variant) VALUES (3, ''op'')',
    'test-sequence-op'
) AS instance_id \gset

SELECT df.await_instance(:'instance_id');

-- Test B: Using df.seq() function
SELECT df.start(
    df.seq(
        df.seq(
            'INSERT INTO test_sequence_log (step, variant) VALUES (1, ''fn'')',
            'INSERT INTO test_sequence_log (step, variant) VALUES (2, ''fn'')'
        ),
        'INSERT INTO test_sequence_log (step, variant) VALUES (3, ''fn'')'
    ),
    'test-sequence-fn'
) AS instance_id \gset

SELECT df.await_instance(:'instance_id');

-- Verify results (deterministic output)
SELECT step, variant FROM test_sequence_log ORDER BY id;

-- Cleanup
DROP TABLE test_sequence_log;

File: expected/sequence.out

DROP TABLE
CREATE TABLE
 await_instance 
----------------
 completed
(1 row)

 await_instance 
----------------
 completed
(1 row)

 step | variant 
------+---------
    1 | op
    2 | op
    3 | op
    1 | fn
    2 | fn
    3 | fn
(6 rows)

DROP TABLE

Implementation Plan

Step 1: Add df.await_instance()

File: src/dsl.rs

Add new function: ```rust

[pg_extern(schema = “df”)]

fn await_instance( instance_id: &str, timeout_seconds: default!(i32, 30), ) -> Result<String, Box

Step 2: Create pg_regress Directory Structure

Test files live at the repo root: bash mkdir -p sql expected

Step 3: PGXS Configuration in Root Makefile

The root Makefile includes PGXS at the bottom:

# pg_regress configuration for pg_durable
EXTENSION = pg_durable
DATA = pg_durable--1.0.sql

# Test files (in order)
REGRESS = simple sequence variables parallel conditional loops transactions explain management labels

# PostgreSQL configuration
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Step 4: Convert Phase 1 Tests

Convert 5 core DSL tests to pg_regress format
Run tests: make installcheck
Capture expected output
Verify reproducibility

Step 5: Update Documentation

Update test/regress/README.md noting files are at repo root
Update main README.md with pg_regress instructions
Update docs/TESTING.md with dual approach

Step 6: Add CI Integration

Update .github/workflows/ci.yml: yaml - name: Run pg_regress tests run: | PG_CONFIG=$(cargo pgrx info pg-config pg17) make installcheck

Success Criteria

[ ] df.await_instance() function implemented and tested
[ ] 5 Phase 1 tests converted and passing
[ ] Expected output files generated and committed
[ ] make installcheck works against running PostgreSQL instance
[ ] E2E tests still pass (no regression)
[ ] Documentation updated
[ ] CI runs both test suites

Alternative Approaches Considered

Output Normalization

Some extensions add custom normalization frameworks (sed-like substitution rules) to handle non-deterministic output like UUIDs or timing values.

Example: ```

normalize.rules

s/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/INSTANCE_ID/g s/\d+.\d+ ms/N ms/g ```

Why we’re not using this approach:

Not standard pg_regress - Requires custom Rust code and build infrastructure
Harder debugging - Test failures show normalized output, not actual values
Fork complexity - PostgreSQL forks wouldn’t recognize this approach
Unnecessary - Making tests truly deterministic (via df.await_instance(), removing timestamps, avoiding UUID output) is simpler and more maintainable

When normalization might be useful: - If testing EXPLAIN output with variable costs/timing - If PostgreSQL version differences cause output variations - For these cases, use PostgreSQL’s built-in alternate expected files (test.out, test_1.out) instead

Future Considerations

Performance: pg_regress tests should complete in <10 seconds total
Isolation: Each test should clean up its own tables
Parallel execution: pg_regress supports parallel test execution, but our tests need sequential execution due to shared background worker
Version compatibility: Test across PostgreSQL 14, 15, 16, 17

Open Questions

Should df.await_instance() return just status, or include additional metadata (execution time, node count)?
- Answer: Return only status for simplicity. Use df.result() for metadata if needed.
Should we support make check (in-tree build) or only make installcheck (installed extension)?
- Answer: Both. pg_regress supports both modes.
What should happen if background worker is not running?
- Answer: df.await_instance() should timeout with clear error message.

References

PostgreSQL pg_regress documentation: https://www.postgresql.org/docs/current/regress.html
pgrx testing guide: https://github.com/pgcentralfoundation/pgrx/blob/develop/TESTING.md
Example pg_regress extension: https://github.com/citusdata/citus/tree/main/src/test/regress

PGXN

PostgreSQL Extension Network

Contents