test.yml

name: Test PostgreSQL Versions

on:
  push:
    branches: [ master, main ]
  pull_request:
    branches: [ master, main ]

jobs:
  test:
    runs-on: ubuntu-latest
    
    strategy:
      matrix:
        postgres-version: ['13', '14', '15', '16', '17', '18']
      fail-fast: false
    
    services:
      postgres:
        image: postgres:${{ matrix.postgres-version }}
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test
          POSTGRES_HOST_AUTH_METHOD: trust
          POSTGRES_INITDB_ARGS: --auth-host=trust --auth-local=trust
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    
    steps:
    - name: Checkout code
      uses: actions/checkout@v4
    
    - name: Install PostgreSQL client
      run: |
        sudo apt-get update
        sudo apt-get install -y postgresql-client
        psql --version
    
    - name: Prepare test database
      run: |
        until pg_isready -h localhost -p 5432 -U postgres; do
          echo "Waiting for postgres..."
          sleep 2
        done
        
        psql -h localhost -U postgres -d test -c 'SELECT version();'
        
        # Extensions
        psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;' || echo "Warning: pg_stat_statements not available"
        psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pgstattuple;'
        psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS intarray;'
        psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pg_buffercache;'
        psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS amcheck;'
        # amcheck needs execute privileges for non-superusers
        psql -h localhost -U postgres -d test -c 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO PUBLIC;'
        
        # Minimal privilege user
        psql -h localhost -U postgres -d test -c "CREATE USER dba_user;"
        psql -h localhost -U postgres -d test -c "GRANT pg_monitor TO dba_user;"
        psql -h localhost -U postgres -d test -c "GRANT CONNECT ON DATABASE test TO dba_user;"
        psql -h localhost -U postgres -d test -c "GRANT USAGE ON SCHEMA public TO dba_user;"
        
        psql -h localhost -U postgres -d test -c 'SELECT extname FROM pg_extension ORDER BY extname;'
        
        # Test tables for alignment (p1)
        psql -h localhost -U postgres -d test -c "CREATE TABLE align1 AS SELECT 1::int4, 2::int8, 3::int4 AS more FROM generate_series(1, 100000) _(i);"
        psql -h localhost -U postgres -d test -c "CREATE TABLE align2 AS SELECT 1::int4, 3::int4 AS more, 2::int8 FROM generate_series(1, 100000) _(i);"
        
        # Test tables for foreign key check (i3) — with intarray to catch operator ambiguity
        psql -h localhost -U postgres -d test -c "CREATE TABLE fk_parent (id int PRIMARY KEY, data text);"
        psql -h localhost -U postgres -d test -c "CREATE TABLE fk_child (id int PRIMARY KEY, parent_id int, data text, CONSTRAINT fk_test FOREIGN KEY (parent_id) REFERENCES fk_parent(id));"
        psql -h localhost -U postgres -d test -c "INSERT INTO fk_parent SELECT i, 'data_' || i FROM generate_series(1, 100000) i;"
        psql -h localhost -U postgres -d test -c "INSERT INTO fk_child SELECT i, (i % 100000) + 1, 'data_' || i FROM generate_series(1, 200000) i;"
        psql -h localhost -U postgres -d test -c "ANALYZE;"
        
        # Grant access
        psql -h localhost -U postgres -d test -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO dba_user;"
        psql -h localhost -U dba_user -d test -c 'SELECT current_user, session_user;'
    
    - name: Test wide mode
      run: |
        echo "\set postgres_dba_wide true" > ~/.psqlrc
        echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc
        echo "Testing all SQL files in wide mode with minimal privileges..."
        for f in sql/*; do
          echo "  Testing $f..."
          if ! PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1; then
            echo "❌ FAILED: $f in wide mode"
            echo "Error output:"
            PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f "$f"
            exit 1
          fi
        done
        echo "✅ All tests passed in wide mode"
    
    - name: Test normal mode
      run: |
        echo "\set postgres_dba_wide false" > ~/.psqlrc
        echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc
        echo "Testing all SQL files in normal mode with minimal privileges..."
        for f in sql/*; do
          echo "  Testing $f..."
          if ! PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1; then
            echo "❌ FAILED: $f in normal mode"
            echo "Error output:"
            PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f "$f"
            exit 1
          fi
        done
        echo "✅ All tests passed in normal mode"
    
    - name: Run regression tests
      run: |
        echo "\set postgres_dba_wide false" > ~/.psqlrc
        echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc
        
        echo "Running regression tests with minimal privileges..."
        
        echo "  Testing 0_node.sql..."
        OUTPUT=$(PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f sql/0_node.sql | grep Role)
        if [[ "$OUTPUT" == *"Primary"* ]]; then
          echo "    ✓ Role test passed"
        else
          echo "    ✗ Role test failed: $OUTPUT"
          exit 1
        fi
        
        echo "  Testing x1_alignment_padding.sql..."
        OUTPUT=$(PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f sql/x1_alignment_padding.sql | grep align)
        if [[ "$OUTPUT" == *"align1"* && "$OUTPUT" == *"align2"* && "$OUTPUT" == *"int4, more, int8"* ]]; then
          echo "    ✓ Alignment padding test passed"
        else
          echo "    ✗ Alignment padding test failed: $OUTPUT"
          exit 1
        fi
        
        echo "  Testing a1_activity.sql..."
        OUTPUT=$(PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f sql/a1_activity.sql | grep User)
        if [[ "$OUTPUT" == *"User"* ]]; then
          echo "    ✓ Activity test passed"
        else
          echo "    ✗ Activity test failed: $OUTPUT"
          exit 1
        fi
        
        echo "  Testing i3_non_indexed_fks.sql (with intarray extension)..."
        OUTPUT=$(PAGER=cat psql -h localhost -U dba_user -d test --no-psqlrc -f warmup.psql -f sql/i3_non_indexed_fks.sql 2>&1)
        if [[ "$OUTPUT" == *"ERROR"* ]]; then
          echo "    ✗ i3 test failed with error:"
          echo "$OUTPUT"
          exit 1
        elif [[ "$OUTPUT" == *"fk_child"* && "$OUTPUT" == *"fk_test"* ]]; then
          echo "    ✓ i3 foreign key test passed (found missing index on FK)"
        else
          echo "    ✗ i3 test failed: unexpected output"
          echo "$OUTPUT"
          exit 1
        fi
        
        echo "✅ All regression tests passed with minimal privileges"