name: Upgrade Tests

on:
  workflow_dispatch:
    inputs:
      target_version:
        description: 'Target version to test upgrades to (leave empty for current branch)'
        required: false
        type: string
  pull_request:
    paths:
      - 'sql/**'
      - 'pg_tre.control'
      - 'include/pg_tre/page.h'
      - 'include/pg_tre/xlog.h'
      - '.github/workflows/upgrade-tests.yml'
  schedule:
    # Weekly on Sundays at 02:00 UTC.
    - cron: '0 2 * * 0'

jobs:
  test-upgrades:
    runs-on: ubuntu-24.04
    strategy:
      fail-fast: false
      matrix:
        pg_version: ['18']
        # Only versions whose on-disk format the current binary can still
        # read are upgrade-testable without a REINDEX.  The sparsemap
        # v5->v6 wire break (pg_tre 1.6.0) means 1.0.x-1.5.x indexes
        # require REINDEX after upgrade -- they are intentionally NOT in
        # this matrix (their .sql upgrade path still works; only on-disk
        # index read needs a rebuild).  1.12.0 is v6/v7 -> reads as v8
        # with no REINDEX, exercising the current additive-format path.
        # 2.0.0 -> 2.0.1 is a no-op-SQL bug-fix upgrade (same format v8).
        old_version: ['1.6.0', '1.12.0', '2.0.4', '2.1.0', '3.0.0', '3.0.1', '3.2.1', '3.2.2', '3.2.3', '3.2.4', '3.2.5', '3.2.6', '4.0.0', '4.0.1', '4.0.2', '4.1.0', '4.2.0', '4.2.1', '4.2.2', '4.2.3', '4.3.0', '4.3.1', '4.4.0', '4.4.1', '4.5.0']
        # When a release breaks on-disk format compatibility, list
        # the (old_version, pg_version) pairs to skip here.
        exclude: []

    steps:
      - name: Checkout current code
        uses: actions/checkout@v4
        with:
          submodules: recursive

      - name: Install build deps
        run: |
          sudo apt-get update
          sudo apt-get install -y \
            build-essential autoconf automake libtool gettext autopoint m4 \
            bison flex \
            wget ca-certificates gnupg

      - name: Install PostgreSQL ${{ matrix.pg_version }}
        run: |
          wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \
              | sudo gpg --dearmor -o /usr/share/keyrings/pgdg.gpg
          echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
              | sudo tee /etc/apt/sources.list.d/pgdg.list >/dev/null
          sudo apt-get update
          sudo apt-get install -y \
            postgresql-${{ matrix.pg_version }} \
            postgresql-server-dev-${{ matrix.pg_version }}
          sudo systemctl stop postgresql

      - name: Download old release v${{ matrix.old_version }}
        run: |
          # Clone the old tag WITH submodules at their pinned revs.  The
          # Codeberg source tarball does not carry submodule contents, so
          # a recursive clone is the only way to get the exact TRE/Lime
          # revs the old version built against.
          git clone --quiet --recurse-submodules --depth 1 \
              --branch "v${{ matrix.old_version }}" \
              https://codeberg.org/gregburd/pg_tre.git \
              "pg_tre-${{ matrix.old_version }}" || {
              echo "::error::Failed to clone v${{ matrix.old_version }}"
              exit 1
          }
          ls -la "pg_tre-${{ matrix.old_version }}"

      - name: Build and install old version v${{ matrix.old_version }}
        run: |
          cd pg_tre-${{ matrix.old_version }}
          export PG_CONFIG=/usr/lib/postgresql/${{ matrix.pg_version }}/bin/pg_config
          export PATH="/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH"
          if [ ! -f vendor/tre/configure.ac ]; then
              echo "::error::TRE submodule missing after recursive clone"
              exit 1
          fi
          make
          sudo env "PATH=$PATH" make install

      - name: Start PostgreSQL with old binary, create test data
        run: |
          export PATH="/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH"
          export PGDATA=/tmp/pgdata_upgrade
          initdb -D "$PGDATA" --auth-local=trust --auth-host=trust
          {
              echo "shared_buffers = 256MB"
              echo "port = 5432"
              echo "unix_socket_directories = '/tmp'"
          } >> "$PGDATA/postgresql.conf"
          pg_ctl -D "$PGDATA" start -w -l "$PGDATA/logfile"
          createdb -h /tmp upgrade_test
          # Install the old extension version and create test data.
          psql -h /tmp -d upgrade_test -X <<'SQL'
          CREATE EXTENSION pg_tre VERSION '${{ matrix.old_version }}';
          SELECT extversion FROM pg_extension WHERE extname = 'pg_tre';
          CREATE TABLE t (id serial PRIMARY KEY, body text);
          INSERT INTO t (body)
          SELECT 'The quick brown fox over the lazy dog row ' || i
          FROM generate_series(1, 1000) AS i;
          CREATE INDEX t_idx ON t USING tre (body);
          -- Capture the seq-scan baseline so we can verify the
          -- upgrade did not change result semantics.
          SET enable_seqscan = off;
          SELECT count(*) FROM t WHERE body %~~ tre_pattern('the', 0);
          SQL
          pg_ctl -D "$PGDATA" stop -w -m fast

      - name: Build and install current version
        run: |
          export PG_CONFIG=/usr/lib/postgresql/${{ matrix.pg_version }}/bin/pg_config
          export PATH="/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH"
          make clean
          make
          sudo env "PATH=$PATH" make install

      - name: Restart PG with new binary, run ALTER EXTENSION UPDATE
        run: |
          export PATH="/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH"
          export PGDATA=/tmp/pgdata_upgrade
          pg_ctl -D "$PGDATA" start -w -l "$PGDATA/logfile"
          # Find the target version from the control file.
          NEW_VERSION=$(awk -F"'" '/default_version/ {print $2}' \
              pg_tre.control)
          echo "Upgrading to: $NEW_VERSION"
          set -o pipefail
          psql -h /tmp -d upgrade_test -X -v ON_ERROR_STOP=1 <<SQL | tee /tmp/upgrade_check.out
          ALTER EXTENSION pg_tre UPDATE TO '$NEW_VERSION';
          SELECT extversion FROM pg_extension WHERE extname = 'pg_tre';
          SELECT tre_version();
          -- Verify the AM still works on the index built under the
          -- old version.
          SET enable_seqscan = off;
          SELECT count(*) FROM t WHERE body %~~ tre_pattern('the', 0);
          -- Differential: index scan must agree with seq-scan after
          -- the upgrade.
          CREATE TEMP TABLE idx_ids AS
              SELECT id FROM t WHERE body %~~ tre_pattern('the', 0);
          SET enable_seqscan = on;
          SET enable_indexscan = off;
          SET enable_bitmapscan = off;
          CREATE TEMP TABLE seq_ids AS
              SELECT id FROM t WHERE body %~~ tre_pattern('the', 0);
          SELECT
              (SELECT count(*) FROM idx_ids) =
                  (SELECT count(*) FROM seq_ids) AS counts_agree,
              NOT EXISTS (SELECT id FROM idx_ids
                          EXCEPT SELECT id FROM seq_ids)
              AND NOT EXISTS (SELECT id FROM seq_ids
                              EXCEPT SELECT id FROM idx_ids)
                  AS row_sets_agree;
          -- 4.4.0+: an index built before 4.4.0 keeps the legacy TID
          -- packing; REINDEX rebuilds it dense, with the same answers.
          SELECT pg_tre_index_tid_packing('t_idx') AS packing_before;
          SET client_min_messages = warning;
          REINDEX INDEX t_idx;
          RESET client_min_messages;
          SELECT pg_tre_index_tid_packing('t_idx') AS packing_after;
          SET enable_seqscan = off;
          SET enable_indexscan = on;
          SET enable_bitmapscan = on;
          CREATE TEMP TABLE idx2_ids AS
              SELECT id FROM t WHERE body %~~ tre_pattern('the', 0);
          SELECT NOT EXISTS (SELECT id FROM idx2_ids EXCEPT SELECT id FROM seq_ids)
             AND NOT EXISTS (SELECT id FROM seq_ids EXCEPT SELECT id FROM idx2_ids)
                 AS reindexed_agrees;
          SQL
          # Every boolean check above must print t.
          if grep -qE '(^|\| *| )f *(\||$)' /tmp/upgrade_check.out; then
              echo "::error::index and sequential scan disagree after the upgrade"
              exit 1
          fi
          pg_ctl -D "$PGDATA" stop -w -m fast

      - name: Diagnostics on failure
        if: failure()
        run: |
          if [ -f /tmp/pgdata_upgrade/logfile ]; then
              echo "::group::PostgreSQL log"
              tail -200 /tmp/pgdata_upgrade/logfile
              echo "::endgroup::"
          fi
          ls -la /usr/lib/postgresql/${{ matrix.pg_version }}/share/extension/pg_tre* || true