ci.yml

name: CI
# Test strategy
#
# `test` -- fresh install (`pg-build-test`) across every PostgreSQL major
# extension_drop's own SQL claims to support (see META.json).
#
# `pg-upgrade-test` -- binary pg_upgrade legs: install the CURRENT
# extension_drop version on an OLD cluster, plant a dependency guard, binary
# pg_upgrade straight to a NEWER major, then run the suite in existing mode
# against the real migrated objects (see bin/test_existing). No update step
# and no bridge leg: extversion never changes across a leg, and there is no
# known pg_upgrade-unsafe old version to bridge from.
#
# Scope: push only runs on master (post-merge); PR commits are covered by
# pull_request -- avoids double-running CI for the same commit.
on:
  push:
    branches: [master]
  pull_request:
# CAT_TOOLS_GIT_REF, set here, builds cat_tools from that git ref instead of
# PGXN for every job that installs it (the Makefile's `cat_tools` target).
# Empty by default; set it only if PGXN's published cat_tools ever can't
# satisfy META.in.json's declared floor again. NEVER set it while cutting a
# release -- see RELEASE.md. release-safety below enforces that automatically.
env:
  CAT_TOOLS_GIT_REF: ""
concurrency:
  # A superseded push's pg_upgrade matrix (several binary pg_upgrades) is
  # pure waste once a newer push on the same ref supersedes it.
  group: ${{ github.workflow }}-${{ github.ref }}
  cancel-in-progress: true
jobs:
  # Style linter (https://github.com/Postgres-Extensions/linter, vendored at
  # .vendor/linter). Deliberately checked out WITHOUT submodules -- `make
  # lint` is the same command a developer runs locally, and lint.mk
  # self-initializes the submodule on first use (see its comment). Using the
  # exact same entry point here is what actually proves that self-init works,
  # rather than papering over it with a submodules: true checkout. The
  # linter's own test suite (fixtures + scanner edge cases) is that repo's
  # own CI's job, not this one's. No PostgreSQL needed -- sql-lint is a
  # standalone Perl script -- so this doesn't use the pgxn-tools container.
  lint:
    name: 🧹 SQL Lint
    runs-on: ubuntu-latest
    steps:
      - name: Check out the repo
        uses: actions/checkout@v5
      - name: Lint SQL
        run: make lint

  # A release built while a dependency is git-pinned instead of PGXN can't
  # actually be installed by a plain `pgxn install` -- see ../ai/RELEASE.md's
  # pre-release checklist. This is the automated version of that check.
  release-safety:
    name: 🔒 No git-pinned deps while releasing
    runs-on: ubuntu-latest
    steps:
      - name: Check out the repo
        uses: actions/checkout@v5
      - name: Fail if cutting a release with cat_tools git-pinned
        run: |
          if bin/in_release && [ -n "$CAT_TOOLS_GIT_REF$CAT_TOOLS_SKIP_INSTALL" ]; then
            echo "::error::default_version is a real version (releasing) while CAT_TOOLS_GIT_REF/CAT_TOOLS_SKIP_INSTALL is set at the workflow level -- revert the override before releasing."
            exit 1
          fi

  # Cheap gate that lets the heavy pg-upgrade-test job below skip itself on
  # commits that touch only docs. Must run on every push/pull_request (no
  # paths-ignore on the workflow itself) -- otherwise the required
  # all-checks-passed check would never report on a docs-only push and get
  # stuck Pending in branch protection (~/advanced-extension-testing.md §6f).
  # Deliberately NOT gating the pre-existing `test` job on this: `test` is
  # already cheap (a single `pg-build-test` per PG major), so there is
  # nothing costly to save by skipping it too -- only pg-upgrade-test (several
  # real binary pg_upgrades per push) is worth gating.
  changes:
    name: 🔍 Detect docs-only changes
    runs-on: ubuntu-latest
    outputs:
      docs_only: ${{ steps.diff.outputs.docs_only }}
    steps:
      - name: Check out the repo
        uses: actions/checkout@v5
        with:
          # Full history so BASE and HEAD are both reachable for `git diff`.
          fetch-depth: 0
      - name: Compute per-push changed files
        id: diff
        run: |
          if [ "${{ github.event_name }}" = "pull_request" ] && \
             [ "${{ github.event.action }}" = "synchronize" ] && \
             [ -n "${{ github.event.before }}" ]; then
            # A push to an already-open PR: before/after give the true
            # per-push diff, same as for a branch push.
            BASE="${{ github.event.before }}"
            HEAD="${{ github.event.after }}"
          elif [ "${{ github.event_name }}" = "pull_request" ]; then
            # First run for this PR (opened/reopened/etc, or synchronize
            # without a usable before): fall back to the whole base...head
            # diff.
            BASE="${{ github.event.pull_request.base.sha }}"
            HEAD="${{ github.event.pull_request.head.sha }}"
          else
            BASE="${{ github.event.before }}"
            HEAD="${{ github.event.after }}"
          fi

          echo "base=$BASE"
          echo "head=$HEAD"

          # Fail-safe is the literal FIRST thing written to GITHUB_OUTPUT, so
          # any early exit or error further down (a bad BASE/HEAD, a failed
          # git diff) leaves docs_only=false in place rather than silently
          # falling through to "skip the heavy job".
          echo "docs_only=false" >> "$GITHUB_OUTPUT"

          # A missing HEAD/BASE, or an all-zeros BASE (e.g. a new branch's
          # first push, where GitHub reports no prior commit), means we
          # can't compute a real diff -- run the full matrix rather than
          # risk skipping tests.
          if [ -z "$HEAD" ] || [ -z "$BASE" ] || [[ "$BASE" =~ ^0+$ ]]; then
            exit 0
          fi

          CHANGED=$(git diff --name-only "$BASE" "$HEAD" || echo __DIFF_FAILED__)

          if [ "$CHANGED" = "__DIFF_FAILED__" ] || [ -z "$CHANGED" ]; then
            exit 0
          fi

          DOCS_ONLY=true
          while IFS= read -r f; do
            if ! [[ "$f" =~ \.(md|asc)$ ]]; then
              DOCS_ONLY=false
              break
            fi
          done <<< "$CHANGED"

          echo "changed files:"
          echo "$CHANGED"
          echo "docs_only=$DOCS_ONLY" >> "$GITHUB_OUTPUT"

  test:
    # Gated behind lint: the 12-leg PG matrix below is comparatively
    # expensive, and every leg would fail anyway on a baseline that's already
    # broken by a lint violation. success() is required explicitly once a
    # job's `if:` references anything -- GitHub only assumes success() as a
    # default when no `if:` is written at all.
    needs: [lint]
    if: success()
    strategy:
      matrix:
        # Floor matches cat_tools's own declared PostgreSQL requirement
        # (12.0, see its META.in.json) -- extension_drop depends on
        # cat_tools, so it was never really going to work below that either.
        pg: [17, 16, 15, 14, 13, 12]
    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@v7
      - name: Test on PostgreSQL ${{ matrix.pg }}
        run: pg-build-test
      # Update-path leg (0.1.1 -> stable): runs here as extra steps in the
      # same already-running container/cluster rather than a separate job,
      # since a whole extra job means a whole extra container boot +
      # PostgreSQL startup for no isolation benefit -- nothing about this
      # leg needs its own environment, unlike e.g. pg_tle's job, which
      # genuinely does.
      #
      # PG11+ only: cat_tools.routine__parse_arg_types_text() (which
      # extension_drop's install script calls unconditionally, in both 0.1.1
      # and current) transitively hits cat_tools's _cat_tools.function__drop_temp,
      # which runs a hardcoded `DROP ROUTINE` -- syntax that doesn't exist
      # before PG11 -- see
      # https://github.com/Postgres-Extensions/extension_tools/issues/44.
      #
      # rsync: pgxntool's test-build feature needs it (see
      # pgxntool/run-test-build.sh), but pg-build-test above never actually
      # exercises test-build -- it goes through a lower-level PGXS path that
      # bypasses this repo's own `test:` Makefile target entirely. A plain
      # `make test`/`make install` (this step, and pg-upgrade-test below)
      # does, so it needs installing explicitly, same as pg-upgrade-test
      # already does.
      - name: Install rsync
        if: matrix.pg >= 11
        run: apt-get install -y rsync
      - name: "Prove the update path: install 0.1.1, plant guard, ALTER EXTENSION UPDATE, re-prove guard, assert version"
        if: matrix.pg >= 11
        run: bin/test_update_path.sh
      - name: Run the pgTAP suite in update mode (0.1.1 -> stable) against the result
        if: matrix.pg >= 11
        run: make test-update

  # Proves extension_drop survives a BINARY pg_upgrade (in-place catalog
  # migration to a newer PostgreSQL major), not just a fresh install. Each
  # leg: install the CURRENT extension_drop version on an OLD cluster, plant
  # + prove a dependency guard, binary pg_upgrade STRAIGHT to a NEWER major,
  # then run the suite against the REAL migrated objects in "existing" mode
  # (bin/test_existing). No update-to-current step: extension_drop has only
  # ever shipped one real version, so extversion is already current on both
  # sides of every leg (see bin/test_existing's own header comment for the
  # full reasoning, including why it has no bridge/update-scenario machinery
  # unlike its cat_tools model).
  #
  # Modest matrix: two adjacent-major legs (one near the floor, one near the
  # ceiling) plus one full-span leg (floor -> newest). The floor is 12, not
  # extension_drop's own claimed 9.3 -- see the top-of-file comment.
  pg-upgrade-test:
    # Gated behind the cheap jobs: this matrix is expensive (multiple binary
    # pg_upgrades), and every leg would fail anyway on a baseline already
    # broken by a failing fresh-install test. success() is required
    # explicitly once a job's `if:` references anything -- GitHub only
    # assumes success() as a default when no `if:` is written at all.
    needs: [changes, test]
    if: success() && needs.changes.outputs.docs_only != 'true'
    strategy:
      matrix:
        include:
          - old_pg: "12"
            new_pg: "13"
          - old_pg: "16"
            new_pg: "17"
          - old_pg: "12"
            new_pg: "17"
    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: Check out the repo
        uses: actions/checkout@v5
      - name: Install rsync
        run: apt-get install -y rsync
      - name: Recreate old cluster with data checksums enabled
        run: .github/scripts/pg_upgrade_cluster recreate-old ${{ matrix.old_pg }}
      - name: Install extension_drop (+ cat_tools) into old cluster
        run: make install
      - name: Prepare the old cluster (install + dependency guard)
        # prepare creates the database, installs extension_drop CASCADE
        # (auto-installing cat_tools) at the current version, then plants +
        # proves the dependency guard so the later existing-mode run cannot
        # silently drop+reinstall and test a fresh install instead.
        run: bin/test_existing prepare extension_drop_upgrade
      - name: Install PostgreSQL ${{ matrix.new_pg }}
        run: apt-get install -y postgresql-${{ matrix.new_pg }}
      - name: Install extension_drop (+ 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: .github/scripts/pg_upgrade_cluster upgrade ${{ matrix.old_pg }} ${{ matrix.new_pg }}
      - name: Run the suite against the pg_upgraded database (existing mode)
        # run-suite asserts the version, re-proves the dependency guard
        # still blocks a non-CASCADE drop (i.e. it survived pg_upgrade), then
        # runs the existing-mode-curated suite (see bin/test_existing) against
        # the REAL pg_upgraded database via --use-existing -- a plain fresh
        # `make test` would silently test a fresh install instead of the
        # migrated objects.
        run: bin/test_existing run-suite extension_drop_upgrade

  # Proves extension_drop can be deployed with NO filesystem footprint at
  # all, via pg_tle (AWS's Trusted Language Extensions -- a database-backed
  # catalog for installing an extension with no .control file on disk;
  # relevant for RDS/Aurora-style managed deployments). This is a distinct
  # dimension from the `test` job above: not fresh vs. updated vs. upgraded,
  # but filesystem-installed vs. registered purely through pg_tle's catalog.
  #
  # extension_drop REQUIRES cat_tools (see the Makefile's own `cat_tools`
  # target), so this job has a complication a leaf extension's pg_tle test
  # doesn't: cat_tools must ALSO be registered as a pg_tle extension, before
  # extension_drop, so `CREATE EXTENSION extension_drop CASCADE` resolves
  # its dependency through pg_tle too instead of failing (or, worse, silently
  # resolving from a stray filesystem install of cat_tools). Every step that
  # could plausibly write an extension file to disk is bracketed by
  # bin/assert_fs_clean checks -- see its header for why that has to be an
  # active, repeated assertion, not a one-time formality.
  #
  # A dedicated cluster, never shared with the `test` job above: pg_tle
  # requires shared_preload_libraries, and mixing pg_tle/non-pg_tle
  # extension installs on one cluster can misbehave. Also deliberately its
  # own job rather than folded into any existing matrix/loop -- the entire
  # point of this job is proving isolation from the filesystem, and any
  # sibling loop iteration doing a real `make install` would contaminate
  # that proof.
  #
  # Explicitly OUT OF SCOPE for this job (left as follow-up work):
  #   - The update path via pg_tle (ALTER EXTENSION UPDATE against a pg_tle-
  #     registered install). extension_drop has no prior released version to
  #     update FROM yet (see TEST_UPDATE_FROM's comment in the Makefile), so
  #     there's nothing real to exercise.
  #   - Binary pg_upgrade of a pg_tle-deployed extension. Heavier, separate
  #     concern from a fresh-install smoke test.
  pg-tle-test:
    # Gated behind the cheap `test` job: this job compiles pg_tle from
    # source and does a chained pg_tle registration, expensive enough not to
    # run against a baseline that's already broken by a failing fresh-install
    # test. success() is required explicitly once a job's `if:` references
    # anything -- GitHub only assumes success() as a default when no `if:` is
    # written at all.
    needs: [test]
    if: success()
    strategy:
      matrix:
        # Intersection of two independently-moving ranges, checked directly
        # rather than assumed: extension_drop's own tested range (9.3-17,
        # see the `test` job's matrix above) and pg_tle 1.5.2's supported
        # PostgreSQL range (12-18, dropped PG11 -- see
        # pgxntool/pgtle_versions.md). 18 isn't in the `test` job's own
        # matrix yet, so it's left out here too rather than testing a PG
        # major extension_drop's own baseline job doesn't cover.
        pg: [17, 16, 15, 14, 13, 12]
    name: 🧩 pg_tle ${{ matrix.pg }}
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    env:
      PG_TLE_RELEASE: "1.5.2"
      # This job always clones cat_tools from git (pg_tle registration needs
      # source, never PGXN), so it needs its own ref regardless of the
      # release-gated CAT_TOOLS_GIT_REF above. Empty by default (falls back
      # to the `stable` tag below); set job-scoped only as a temporary
      # override if PGXN's cat_tools goes stale again -- never at the
      # workflow level, which release-safety guards against.
      CAT_TOOLS_GIT_REF: ""
    steps:
      - name: Start PostgreSQL ${{ matrix.pg }}
        run: pg-start ${{ matrix.pg }}
      - name: Check out the repo
        uses: actions/checkout@v5
      - name: Install pgtap (test harness dependency)
        # pgTAP is a filesystem-installed dependency of the TEST HARNESS
        # itself, not part of what this job proves is pg_tle-only -- it's
        # never deployed via pg_tle. Installed explicitly here, before the
        # baseline snapshot below, so it's part of the accepted starting
        # state (like contrib) instead of tripping the contamination check
        # if something installed it lazily later.
        run: make pgtap
      - name: Snapshot filesystem extension control files (pre-pg_tle baseline)
        run: bin/assert_fs_clean snapshot ${{ matrix.pg }} /tmp/control_baseline.txt
      - name: Build and install pg_tle ${{ env.PG_TLE_RELEASE }}
        # flex/bison/libkrb5-dev aren't in the pgxn-tools image; pg_tle's
        # build needs them (guc-file.l, and clientauth.c includes gssapi.h).
        run: |
          apt-get install -y flex bison libkrb5-dev
          git clone --branch v${{ env.PG_TLE_RELEASE }} --depth 1 https://github.com/aws/pg_tle.git /tmp/pg_tle
          make -C /tmp/pg_tle install
      - name: Enable pg_tle and restart PostgreSQL ${{ matrix.pg }}
        run: |
          echo "shared_preload_libraries = 'pg_tle'" >> /etc/postgresql/${{ matrix.pg }}/test/postgresql.conf
          pg_ctlcluster ${{ matrix.pg }} test restart
          pg_isready -t 30
      - name: Register pg_tle, then cat_tools, then extension_drop -- all against template1
        # template1, not the ambient default db: pg_tle's registration
        # catalog is per-database, and createdb only inherits it because it
        # copies template1 by default. Every database used below is created
        # AFTER this step so it inherits all three registrations.
        #
        # cat_tools must be registered BEFORE extension_drop: extension_drop
        # requires cat_tools, so the CASCADE install below needs cat_tools
        # already resolvable through pg_tle's own catalog by the time it
        # runs. cat_tools vendors its own pgxntool copy directly (confirmed:
        # its only git submodule is an unrelated linter, so a plain `git
        # clone` alone gives a working `make run-pgtle`), so registering it
        # needs nothing beyond the exact same target this repo uses on
        # itself right after.
        run: |
          psql -d template1 -c "CREATE EXTENSION pg_tle"
          git clone https://github.com/Postgres-Extensions/cat_tools.git /tmp/cat_tools_tle
          # -C on both git and make, rather than `cd`: this is one continuous
          # shell script (a multi-line `run:` block), so a bare `cd` here
          # would still be in effect for the `make run-pgtle` below that's
          # meant to run against OUR OWN checkout, not the cat_tools clone.
          git -C /tmp/cat_tools_tle checkout "${CAT_TOOLS_GIT_REF:-stable}"
          PGDATABASE=template1 make -C /tmp/cat_tools_tle run-pgtle
          PGDATABASE=template1 make run-pgtle
      - name: Verify no stray extension control files landed on the filesystem
        # CRITICAL: a filesystem control file silently wins over a pg_tle-
        # registered extension of the same name, which would make this whole
        # job a false pass without ever raising an error. Run again after
        # every step below that could plausibly write extension files to
        # disk -- never trust a single check to catch everything.
        run: bin/assert_fs_clean verify ${{ matrix.pg }} /tmp/control_baseline.txt
      - name: Install extension_drop purely via pg_tle (fresh install, no filesystem trace)
        # Neither cat_tools nor extension_drop is ever `make install`ed in
        # this job, so a successful CASCADE install here can only be
        # resolving both through pg_tle's registration, not a control file
        # on disk. Checked explicitly here too (not just via the
        # comprehensive check above) as a guard specifically for the two
        # extensions under test, in case that check's exclude logic has a
        # bug.
        run: |
          test ! -e /usr/share/postgresql/${{ matrix.pg }}/extension/cat_tools.control
          test ! -e /usr/share/postgresql/${{ matrix.pg }}/extension/extension_drop.control
          createdb extension_drop_smoke
          psql -d extension_drop_smoke -c "CREATE EXTENSION extension_drop CASCADE"
      - name: Verify extension_drop works when deployed via pg_tle
        run: |
          INSTALLED=$(psql -d extension_drop_smoke -tAc "SELECT extversion FROM pg_extension WHERE extname = 'extension_drop'")
          EXPECTED=$(make -s print-PGXNVERSION 2>/dev/null | sed -n 's/.*set to "\(.*\)"$/\1/p')
          echo "installed=$INSTALLED expected=$EXPECTED"
          if [ -z "$INSTALLED" ] || [ -z "$EXPECTED" ] || [ "$INSTALLED" != "$EXPECTED" ]; then
            echo "FAIL: installed='$INSTALLED' expected='$EXPECTED'"; exit 1
          fi
          # A real function call, not just a successful install: exercises
          # add/get/remove against an extension (pg_tle itself) that's
          # genuinely present in this database, proving the pg_tle-deployed
          # functions actually execute correctly, not merely that CREATE
          # EXTENSION didn't error.
          psql -d extension_drop_smoke -v ON_ERROR_STOP=1 -c "SELECT extension_drop__add('pg_tle', 'SELECT 1')"
          psql -d extension_drop_smoke -v ON_ERROR_STOP=1 -c "SELECT * FROM extension_drop__get('pg_tle')" > /dev/null
          psql -d extension_drop_smoke -v ON_ERROR_STOP=1 -c "SELECT extension_drop__remove('pg_tle')"
      - name: Verify no stray extension control files after the fresh-install smoke test
        # Runs AFTER the complete flow, not before: the whole point of
        # pg_tle-mode testing is proving NOTHING touched the filesystem
        # THROUGHOUT the flow, not merely that the environment started
        # clean.
        run: bin/assert_fs_clean verify ${{ matrix.pg }} /tmp/control_baseline.txt

  # A single stable check name for use as a required status check in branch
  # protection. Matrix jobs produce names like "🐘 PostgreSQL 14" that change
  # with the matrix; this aggregates them into one. It passes if every needed
  # job succeeded or was skipped (e.g. a docs-only push skipping
  # pg-upgrade-test) and fails if any failed or were cancelled.
  all-checks-passed:
    needs: [lint, release-safety, changes, test, pg-upgrade-test, pg-tle-test]
    if: always()
    runs-on: ubuntu-latest
    steps:
      - 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