ci.yml

name: CI
on:
  push:
    branches:
      - master
    paths-ignore:
      - '**.md'
      - '**.asc'
  pull_request:
    paths-ignore:
      - '**.md'
      - '**.asc'
env:
  PGUSER: postgres
jobs:
  test:
    strategy:
      matrix:
        pg: [18, 17, 16, 15, 14, 13, 12, 11, 10]
    name: 🐘 PostgreSQL ${{ matrix.pg }}
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    steps:
      - name: Start PostgreSQL ${{ matrix.pg }}
        run: pg-start ${{ matrix.pg }}
      - name: Check out the repo
        uses: actions/checkout@v6
      - name: Install rsync
        run: apt-get install -y rsync
      - name: Test on PostgreSQL ${{ matrix.pg }}
        run: make test

  pg-upgrade-test:
    strategy:
      matrix:
        include:
          - old_pg: "10"
            new_pg: "11"
          - old_pg: "10"
            new_pg: "18"
          - old_pg: "11"
            new_pg: "12"
          - old_pg: "11"
            new_pg: "18"
          - old_pg: "12"
            new_pg: "13"
          - old_pg: "12"
            new_pg: "18"
    name: 🔄 Binary pg_upgrade ${{ matrix.old_pg }} → ${{ matrix.new_pg }}
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    env:
      # Both clusters must use the same initdb options so pg_upgrade sees
      # consistent settings (checksums, auth) on old and new clusters.
      INITDB_OPTS: --data-checksums --auth trust
    steps:
      - name: Start PostgreSQL ${{ matrix.old_pg }}
        run: pg-start ${{ matrix.old_pg }}
      - name: Recreate old cluster with data checksums enabled
        run: |
          pg_ctlcluster ${{ matrix.old_pg }} test stop
          pg_dropcluster ${{ matrix.old_pg }} test
          # -p 5432: pg_createcluster assigns the next available port, which
          # may not be 5432 after pg-start has claimed and released it. Force
          # 5432 so subsequent psql/createdb calls connect without -p.
          pg_createcluster -p 5432 ${{ matrix.old_pg }} test -- $INITDB_OPTS
          pg_ctlcluster ${{ matrix.old_pg }} test start
          pg_isready -t 30
      - name: Check out the repo
        uses: actions/checkout@v6
      - name: Install rsync
        run: apt-get install -y rsync
      - name: Install cat_tools into old cluster
        run: make install
      - name: Install cat_tools extension into upgrade test database
        run: psql -c "CREATE EXTENSION cat_tools"
      - name: Install PostgreSQL ${{ matrix.new_pg }}
        run: apt-get install -y postgresql-${{ matrix.new_pg }} postgresql-server-dev-${{ matrix.new_pg }}
      - name: Install cat_tools into new cluster
        # PG_CONFIG must be specified explicitly: at this point both old and new
        # PostgreSQL are installed, and the default pg_config on PATH may not be
        # the new version's.
        run: make install PG_CONFIG=/usr/lib/postgresql/${{ matrix.new_pg }}/bin/pg_config
      - name: Stop old cluster, binary pg_upgrade to PostgreSQL ${{ matrix.new_pg }}, start new cluster
        run: |
          pg_ctlcluster ${{ matrix.old_pg }} test stop
          pg_createcluster -p 5432 ${{ matrix.new_pg }} test -- $INITDB_OPTS
          # PG17+ writes logs to $new_datadir/pg_upgrade_output.d/; older
          # versions write to CWD. Search both on failure.
          mkdir -p /tmp/pg_upgrade_logs
          chown postgres:postgres /tmp/pg_upgrade_logs
          su -c "cd /tmp/pg_upgrade_logs && /usr/lib/postgresql/${{ matrix.new_pg }}/bin/pg_upgrade \
            -b /usr/lib/postgresql/${{ matrix.old_pg }}/bin \
            -B /usr/lib/postgresql/${{ matrix.new_pg }}/bin \
            -d /var/lib/postgresql/${{ matrix.old_pg }}/test \
            -D /var/lib/postgresql/${{ matrix.new_pg }}/test \
            -o '-c config_file=/etc/postgresql/${{ matrix.old_pg }}/test/postgresql.conf' \
            -O '-c config_file=/etc/postgresql/${{ matrix.new_pg }}/test/postgresql.conf'" postgres \
            || { find /tmp/pg_upgrade_logs \
                      /var/lib/postgresql/${{ matrix.new_pg }}/test/pg_upgrade_output.d \
                      -name '*.log' 2>/dev/null | sort | xargs -r tail -n +1; exit 1; }
          pg_ctlcluster ${{ matrix.new_pg }} test start
      - name: Verify extension version after upgrade
        run: |
          VERSION=$(psql -tAc "SELECT extversion FROM pg_extension WHERE extname = 'cat_tools'")
          echo "Extension version: ${VERSION:-<not found>}"
          echo "$VERSION" | grep -q "0.2.2"
      - name: Run test suite on upgraded cluster
        run: make test
      # TODO: also test ALTER EXTENSION cat_tools UPDATE here, once the
      # pg_upgrade source versions (e.g. 0.2.0) can install on the new_pg
      # version. Currently the pre-0.2.2 install scripts fail on PG11+ so
      # we cannot pg_upgrade from a cluster that has them installed.

  extension-update-test:
    strategy:
      matrix:
        # Restricted to PG10 only because the pre-0.2.2 install scripts use
        # unqualified SELECT * in views over pg_attribute/pg_constraint:
        #   - PG11 added attmissingval (pseudo-type anyarray) to pg_attribute,
        #     so cat_tools--0.2.0.sql and cat_tools--0.2.1.sql fail on PG11+
        #     with "column attmissingval has pseudo-type anyarray".
        #   - PG12 made the `oid` system column visible in SELECT *, causing
        #     "column oid specified more than once" on PG12+.
        # PG10 is the only version where both old scripts install cleanly.
        # TODO: when a future version's install script works on PG11+, expand
        # this matrix and add tests for the upgrade path from 0.2.2 onwards.
        pg: [10]
    name: ⬆️ Extension update test on PostgreSQL ${{ matrix.pg }}
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    steps:
      - name: Start PostgreSQL ${{ matrix.pg }}
        run: pg-start ${{ matrix.pg }}
      - name: Check out the repo
        uses: actions/checkout@v6
      - name: Install rsync
        run: apt-get install -y rsync
      - name: Install cat_tools (all versions)
        run: make install
      - name: Test upgrade from 0.2.0 (direct 0.2.0→0.2.2 path)
        run: |
          psql -c "CREATE EXTENSION cat_tools VERSION '0.2.0'"
          psql -c "ALTER EXTENSION cat_tools UPDATE"
          VERSION=$(psql -tAc "SELECT extversion FROM pg_extension WHERE extname = 'cat_tools'")
          echo "Version after 0.2.0 upgrade: ${VERSION:-<not found>}"
          echo "$VERSION" | grep -q "0.2.2"
      - name: Test upgrade from 0.2.1 (0.2.1→0.2.2 path)
        run: |
          createdb cat_tools_from_021
          psql -d cat_tools_from_021 -c "CREATE EXTENSION cat_tools VERSION '0.2.1'"
          psql -d cat_tools_from_021 -c "ALTER EXTENSION cat_tools UPDATE"
          VERSION=$(psql -d cat_tools_from_021 -tAc "SELECT extversion FROM pg_extension WHERE extname = 'cat_tools'")
          echo "Version after 0.2.1 upgrade: ${VERSION:-<not found>}"
          echo "$VERSION" | grep -q "0.2.2"
      - name: Run test suite on updated extension
        run: make test

  # A single stable check name for use as a required status check in branch
  # protection rules. Matrix jobs produce check names like "🐘 PostgreSQL 14"
  # which would all need to be listed individually and updated whenever the
  # matrix changes. This job passes if all others passed or were skipped (e.g.
  # on a docs-only push with paths-ignore), and fails if any failed or were
  # cancelled.
  all-checks-passed:
    needs: [test, pg-upgrade-test, extension-update-test]
    if: always()
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v6
      - name: Verify all jobs are listed in needs
        # Ensures this job won't silently ignore a newly-added job that was
        # omitted from the needs list above.
        run: |
          DEFINED=$(python3 -c "
          import yaml
          with open('.github/workflows/ci.yml') as f:
              w = yaml.safe_load(f)
          print('\n'.join(sorted(j for j in w['jobs'] if j != 'all-checks-passed')))
          ")
          NEEDED=$(echo '${{ toJson(needs) }}' | python3 -c "
          import json, sys
          print('\n'.join(sorted(json.load(sys.stdin))))
          ")
          if [ "$DEFINED" != "$NEEDED" ]; then
            echo "Some jobs are missing from all-checks-passed needs:"
            diff <(echo "$DEFINED") <(echo "$NEEDED")
            exit 1
          fi
      - name: Check all jobs passed or were skipped
        run: |
          if [[ "${{ contains(needs.*.result, 'failure') || contains(needs.*.result, 'cancelled') }}" == "true" ]]; then
            echo "One or more jobs failed or were cancelled"
            exit 1
          fi