PostgreSQL Regression Tests for ParadeDB

This directory contains the pg regress tests for ParadeDB’s pg_search extension. These run with cargo pgrx regress and do not need the extension to be manually installed: it is handled automatically.

For a complete overview of ParadeDB’s testing infrastructure (including unit tests, integration tests, and client property tests), please see the Testing section in CONTRIBUTING.md.

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:

  • columnar_: Columnar tests
    • columnar_basic_*: Basic columnar storage functionality tests
    • columnar_edgecases_*: Edge cases and boundary condition tests
    • columnar_queries_*: Tests for complex query features
    • columnar_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
  1. Add a descriptive header and echo statement:
   -- Tests my new feature
  1. Use deterministic data and ORDER BY clauses to ensure consistent results
  2. 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

cargo pgrx regress -p pg_search --auto -- pg18 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