PostgreSQL Regression Tests for ParadeDB

This directory contains regression tests for ParadeDB’s pg_search extension.

Directory Structure

  • sql/: Contains SQL script files that are executed during testing
  • expected/: Contains expected output files for each test
  • results/ (ignored under git): Stores actual output files generated during test runs
  • common/: Contains common setup and cleanup scripts used by multiple tests

Test Organization

Tests are organized into logical groups with a common prefix:

  • mixedff_: Mixed Fast Fields tests
    • mixedff_basic_*: Basic mixed fast fields functionality tests
    • mixedff_edgecases_*: Edge cases and boundary condition tests
    • mixedff_queries_*: Tests for complex query features
    • mixedff_advanced_*: Tests for advanced features and optimizations

Each group uses its own setup and cleanup scripts from the common/ directory.

Adding New Tests

Step 1: Create SQL Test File

  1. Name your test file using the appropriate prefix and sequence number (e.g., PREFIX_your_test.sql)

  2. Start with the common setup script for your test group, e.g.,:

    \i common/PREFIX_setup.sql

  3. Add a descriptive header and echo statement:

    -- Tests my new feature

  4. Use deterministic data and ORDER BY clauses to ensure consistent results

  5. End with the appropriate cleanup script, e.g.,:

    \i common/PREFIX_cleanup.sql

Step 2: Generate Expected Output

Run your test to generate the expected output file:

cd pg_search
cargo pgrx regress --auto

Step 3: Verify the Expected Output

  1. Check the generated output file in expected/PREFIX_your_test.out
  2. Ensure all queries return at least one row of data
  3. Verify that any EXPLAIN plans look reasonable

Running Tests

Run All Tests

cd pg_search
cargo pgrx regress

Run Specific Tests

cd pg_search
cargo pgrx regress pg17 PREFIX_your_test

Common Pitfalls

  1. Non-deterministic Results: Use fixed dates and ORDER BY clauses to ensure consistent results
  2. Missing Data: Verify that all test queries return at least one row of data
  3. Timing Variations: Use COSTS OFF, TIMING OFF in EXPLAIN to avoid timing-dependent output

Contributing New Tests

When contributing new tests:

  1. Follow the naming convention of existing tests
  2. Use the appropriate common setup/cleanup scripts
  3. Check that your test produces deterministic results
  4. Include metadata comments at the top of the file explaining what’s being tested