Add sql-lint: style linter for PostgreSQL SQL files #282

Workflow file for this run

	name: CI
	on:
	push:
	branches:
	- master
	pull_request:
	env:
	PGUSER: postgres
	jobs:
	# Cheap gate that lets the heavy jobs below skip themselves on commits
	# that touch only docs. This 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 doc-only pushes and get
	# stuck Pending in branch protection.
	#
	# It also derives, from a SINGLE set of constants, the supported-PostgreSQL-
	# major lists that the test / extension-update / stepwise jobs consume (see
	# the "Derive ..." step). Because those lists live here and every heavy job
	# already needs this job, adding a PG major is a one-line edit with no new job.
	changes:
	name: 🔍 Detect changes & derive PG matrix
	runs-on: ubuntu-latest
	outputs:
	docs_only: ${{ steps.diff.outputs.docs_only }}
	# Derived PG-major lists (see the "Derive ..." step for their meaning).
	supported_pg: ${{ steps.pg.outputs.supported_pg }}
	update_pg: ${{ steps.pg.outputs.update_pg }}
	climb_pg: ${{ steps.pg.outputs.climb_pg }}
	legacy_pg: ${{ steps.pg.outputs.legacy_pg }}
	steps:
	- name: Check out the repo
	uses: actions/checkout@v6
	with:
	# Full history needed so BASE and HEAD below 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: a missing HEAD, 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
	echo "docs_only=false" >> "$GITHUB_OUTPUT"
	exit 0
	fi

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

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

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

	- name: Derive the supported-PostgreSQL-major lists
	id: pg
	run: \|
	# Spending 20+ lines to replace a handful of version references looks
	# silly on the surface, but the point is CONSISTENCY: every job -- the
	# fresh-install `test` matrix, the `extension-update-test` matrix and
	# the stepwise climb -- derives its PostgreSQL set from this ONE source,
	# so they cannot drift onto different version lists.
	#
	# SINGLE SOURCE OF TRUTH for the supported PostgreSQL majors. To add
	# or drop a PG major, edit ONLY the three constants below; the test,
	# extension-update-test and pg-upgrade-stepwise jobs all derive their
	# version lists from them (adding the newest major is a one-line NEWEST
	# bump). Do NOT hardcode a supported major in any job matrix or loop.
	#
	# NEWEST -- highest PostgreSQL major cat_tools is tested on.
	# CURRENT_FLOOR -- oldest major the CURRENT extension version supports:
	# the 0.2.3->0.3.0 update runs ALTER TYPE ... ADD
	# VALUE, which cannot run in a pre-PG12 transaction.
	# LEGACY_FLOOR -- oldest major the pre-0.2.2 install scripts still
	# load on (PG11 added pg_attribute.attmissingval and
	# PG12+ exposes the oid system column in SELECT *, both
	# of which those old scripts trip over). Only the
	# update and stepwise paths reach back this far.
	NEWEST=18
	CURRENT_FLOOR=12
	LEGACY_FLOOR=10

	# supported = CURRENT_FLOOR..NEWEST (newest-first). The FRESH-install
	# matrix (test job) runs exactly these.
	supported=$(seq "$NEWEST" -1 "$CURRENT_FLOOR")
	# update = supported plus the legacy floor: the extension-update job
	# additionally exercises the PG10-only pre-0.2.2 update scripts.
	update="$supported $LEGACY_FLOOR"
	# climb = LEGACY_FLOOR+1 .. NEWEST (ascending). The stepwise job starts
	# one cluster on the legacy floor and binary-pg_upgrades through every
	# later major in turn, so its targets are every major above the floor.
	climb=$(seq $((LEGACY_FLOOR + 1)) "$NEWEST")

	# Emit a JSON array from a list of ints, for the job matrices to
	# consume with fromJSON (GitHub evaluates a literal dollar-brace
	# expression even inside a run block, so none is written here).
	json() { printf '%s\n' "$@" \| paste -sd, - \| sed 's/^/[/; s/$/]/'; }

	echo "supported_pg=$(json $supported)" >> "$GITHUB_OUTPUT"
	echo "update_pg=$(json $update)" >> "$GITHUB_OUTPUT"
	echo "legacy_pg=$LEGACY_FLOOR" >> "$GITHUB_OUTPUT"
	# Space-separated for direct iteration in the stepwise bash loop.
	echo "climb_pg=$(echo $climb)" >> "$GITHUB_OUTPUT"

	# ===========================================================================
	# Test strategy
	#
	# A cat_tools install can be arrived at several ways, each of which can break
	# differently, so each is exercised by its own job below (the per-job comments
	# carry the details; this is the big picture):
	#
	# test -- FRESH install: CREATE EXTENSION at the current
	# version on every supported PostgreSQL. The baseline
	# a brand-new user gets.
	# extension-update-test -- IN-PLACE update: CREATE EXTENSION at an OLD version
	# then ALTER EXTENSION UPDATE (same PostgreSQL, no
	# pg_upgrade).
	# pg-upgrade-test -- BINARY pg_upgrade, SINGLE jump: install an OLD
	# version on an OLD major, binary-upgrade the cluster
	# straight to a NEWER major (skipping intermediate
	# majors), then update the extension. Proves objects
	# created on an old server work when read on a new one.
	# pg-upgrade-stepwise -- BINARY pg_upgrade, EVERY major in sequence: one
	# cluster climbing 10 -> 11 -> ... -> 18, exercising
	# each individual major-to-major transition in turn.
	#
	# Supported update origins are 0.2.0, 0.2.1 and 0.2.2 (0.1.x is unsupported).
	# 0.2.0 and 0.2.1 are BOTH tested as origins because ALTER EXTENSION UPDATE
	# takes the shortest path: a 0.2.0 origin updates straight through the
	# 0.2.0--0.2.2 script and never touches 0.2.1--0.2.2, so starting at 0.2.1 is
	# the only way to exercise the 0.2.1--0.2.2 update script (spelled out at the
	# pg-upgrade-test matrix).
	#
	# Two PostgreSQL-version floors shape the matrices:
	# - The pre-0.2.2 install scripts (0.2.0 / 0.2.1) load ONLY on PG10: PG11
	# added pg_attribute.attmissingval and PG12+ exposes the oid system column
	# in SELECT *, both of which those old scripts trip over. So a 0.2.0 / 0.2.1
	# origin can only start on PG10.
	# - The current version needs PG12+: the 0.2.3->0.3.0 update runs
	# ALTER TYPE ... ADD VALUE, which cannot run in a pre-PG12 transaction (and
	# an extension update script is one).
	#
	# KEY invariant: every pg_upgrade leg CLIMBS to a PostgreSQL that supports the
	# current version, then updates to the current version and runs the full suite
	# -- no leg stops short. A PG10/11 origin simply HOLDS the extension at 0.2.3
	# (the highest version reachable on those majors) until the cluster reaches
	# PG12+, where it is updated to the current version.
	# ===========================================================================
	test:
	needs: [changes]
	if: needs.changes.outputs.docs_only != 'true'
	strategy:
	matrix:
	# Current-supported majors, from the single source in the changes job.
	pg: ${{ fromJSON(needs.changes.outputs.supported_pg) }}
	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 and server headers
	# postgresql-server-dev-NN provides catalog/pg_class.h, which
	# test/gen-relkinds.sh reads for the relkind drift check in
	# test/sql/relation__.sql.
	run: apt-get install -y rsync postgresql-server-dev-${{ matrix.pg }}
	- name: Test on PostgreSQL ${{ matrix.pg }}
	run: \|
	# Fail if the relkind drift source is empty (headers missing): the
	# drift check must actually run on every version, not pass silently.
	make check-relkind-source
	# verify-results is the real gate: base.mk declares `verify-results:
	# test`, so this runs the suite via its `test` prerequisite and then
	# checks the pgtap/regression.diffs. A bare `make test` is redundant here
	# and would not gate anyway -- it never exits non-zero on regressions
	# (pgxntool marks installcheck `.IGNORE`), so failures would pass silently.
	make verify-results

	# 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:
	needs: [changes]
	if: needs.changes.outputs.docs_only != 'true'
	name: 🧹 SQL Lint
	runs-on: ubuntu-latest
	steps:
	- name: Check out the repo
	uses: actions/checkout@v6
	- name: Lint SQL
	run: make lint

	# Proves cat_tools survives a BINARY pg_upgrade (in-place catalog migration to a
	# newer PostgreSQL major), not just an in-place extension update. Each leg is a
	# SINGLE jump: install an old cat_tools on an old cluster, plant a dependency
	# guard, binary-pg_upgrade STRAIGHT to a newer major (skipping any intermediate
	# majors), update the extension to the current version, then run the suite
	# against the REAL migrated objects in "existing" mode. Catches view/catalog
	# breakage that only surfaces when objects created on an old server are read on a
	# new one. Every leg's new_pg supports the current version (PG12+); the
	# version-by-version climb through EVERY major is pg-upgrade-stepwise.
	pg-upgrade-test:
	needs: [changes]
	if: needs.changes.outputs.docs_only != 'true'
	strategy:
	matrix:
	include:
	# old_pg=10 origins: the FULL real-world journey for a user on old
	# PostgreSQL + old cat_tools. old_install is 0.2.0/0.2.1 -- the only
	# versions that install on PG10 (PG11 added pg_attribute.attmissingval,
	# PG12+ exposes oid in SELECT *, so 0.2.0/0.2.1 install ONLY on PG10).
	# The BRIDGE update to 0.2.3, run on PG10 BEFORE pg_upgrade, makes the
	# objects pg_upgrade-safe: the shipped 0.2.0->0.2.2 / 0.2.1->0.2.2
	# scripts leave fresh-vs-update divergences (relhasoids/relhaspkey never
	# stripped from _cat_tools.pg_class_v via the no-op `!= ANY` omit_column),
	# and the 0.2.2->0.2.3 update repairs them (its pg_class_v DROP+CREATE
	# rebuild strips the dropped columns). 0.2.3 is the highest version a
	# PG10 cluster can hold (0.2.3->0.3.0 needs PG12+); the single jump lands
	# on a PG12+ major, where the post-upgrade step updates 0.2.3 -> current.
	#
	# BOTH 0.2.0 and 0.2.1 appear as origins because ALTER EXTENSION UPDATE
	# takes the SHORTEST path: a 0.2.0 origin updates straight through the
	# 0.2.0--0.2.2 script and never exercises 0.2.1--0.2.2, so starting at
	# 0.2.1 is the ONLY way to exercise the 0.2.1--0.2.2 update script.
	- old_pg: "10"
	new_pg: "18"
	old_install: "0.2.0"
	bridge_to: "0.2.3"
	- old_pg: "10"
	new_pg: "18"
	old_install: "0.2.1"
	bridge_to: "0.2.3"
	# old_pg>=11: 0.2.0/0.2.1 do not install on PG11+, and such users are
	# already on 0.2.2+. A FRESH 0.2.2 install already strips relhasoids/
	# relhaspkey (fixed `!= ALL` omit_column), so it is pg_upgrade-safe with
	# no bridge. pg_upgrade to a PG12+ major, then update to the current
	# version.
	- old_pg: "11"
	new_pg: "12"
	old_install: "0.2.2"
	bridge_to: ""
	- old_pg: "11"
	new_pg: "18"
	old_install: "0.2.2"
	bridge_to: ""
	- old_pg: "12"
	new_pg: "13"
	old_install: "0.2.2"
	bridge_to: ""
	- old_pg: "12"
	new_pg: "18"
	old_install: "0.2.2"
	bridge_to: ""
	# Include old_install in the name so matrix legs that differ only by origin
	# version (e.g. the two 10 → 18 legs from 0.2.0 and 0.2.1) get distinct,
	# unambiguous check names.
	name: 🔄 Binary pg_upgrade ${{ matrix.old_pg }} → ${{ matrix.new_pg }} (from ${{ matrix.old_install }})
	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: Prepare the old cluster (install + bridge + dependency guard)
	# prepare-old installs cat_tools at old_install and, when bridge_to is set
	# (old_pg=10), ALTER EXTENSION UPDATEs to it BEFORE pg_upgrade -- the
	# bridge that makes the views pg_upgrade-safe (see prepare-old in the
	# script). It then plants + proves the dependency guard so the later
	# existing-mode run cannot silently drop+reinstall and test a fresh
	# install instead. The extension is always older than the current version,
	# so the post-upgrade ALTER EXTENSION UPDATE always has real work to do.
	run: >-
	bin/test_existing prepare-old cat_tools_upgrade
	"${{ matrix.old_install }}" "${{ matrix.bridge_to }}"
	- 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: Update the pg_upgraded extension to the current version
	# Exercises ALTER EXTENSION UPDATE on genuinely pg_upgraded objects (the
	# extension binary pg_upgrade just migrated), running the version-to-version
	# scripts up to the current version. The 0.2.3 -> 0.3.0 script uses
	# ALTER TYPE ... ADD VALUE, which is why every leg's new_pg is >= 12.
	run: bin/test_existing update cat_tools_upgrade
	- 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
	# suite against the REAL pg_upgraded + updated database via --use-existing
	# (so pg_regress does not drop/recreate it) -- a plain fresh `make test`
	# would silently test a fresh install instead of the migrated objects.
	run: bin/test_existing run-suite cat_tools_upgrade

	# Proves cat_tools survives EVERY individual major-to-major binary pg_upgrade
	# transition, not just a single big jump (that is pg-upgrade-test): ONE cluster
	# that starts on the oldest origin and climbs through every major in sequence.
	# Installs 0.2.0 on PG10, bridges to 0.2.3 (the highest version a PG10 cluster
	# can hold -- see the pg-upgrade-test matrix comment), plants the dependency
	# guard, then binary-pg_upgrades 10->11->12->...->18 ONE major at a time. The
	# extension is HELD at 0.2.3 for the 10->11 and 11->12 steps (PG11 cannot run
	# the 0.2.3->0.3.0 update, which needs PG12+); on reaching PG12 it is updated
	# 0.2.3 -> the current version, and from PG12 on the full suite runs in existing
	# mode at EVERY major (12..18) against the REAL pg_upgraded objects, re-proving
	# the guard survived each step. CI-heavy on purpose: it installs PostgreSQL 10
	# through 18, performs 8 sequential pg_upgrades, and runs the suite 7 times.
	pg-upgrade-stepwise:
	needs: [changes]
	if: needs.changes.outputs.docs_only != 'true'
	name: 🪜 Stepwise pg_upgrade 10 → 18 (from 0.2.0)
	runs-on: ubuntu-latest
	container: pgxn/pgxn-tools
	env:
	# Every pg_upgrade in the climb pairs two clusters that must share initdb
	# options (checksums, auth), exactly as in pg-upgrade-test.
	INITDB_OPTS: --data-checksums --auth trust
	DB: cat_tools_stepwise
	# Climb targets and starting (legacy) major, from the single source in the
	# changes job -- so a new PG major joins the climb with no edit here.
	CLIMB_PG: ${{ needs.changes.outputs.climb_pg }}
	LEGACY_PG: ${{ needs.changes.outputs.legacy_pg }}
	steps:
	- name: Start PostgreSQL 10
	run: pg-start 10
	- name: Recreate the PG10 cluster with data checksums enabled
	run: \|
	pg_ctlcluster 10 test stop
	pg_dropcluster 10 test
	# -p 5432: force the well-known port (see pg-upgrade-test for why).
	pg_createcluster -p 5432 10 test -- $INITDB_OPTS
	pg_ctlcluster 10 test start
	pg_isready -t 30
	- name: Check out the repo
	uses: actions/checkout@v6
	- name: Install rsync
	# Must NOT install a newer server-dev yet: `make install` just below runs
	# with the DEFAULT pg_config, which must still resolve to PG10. Each newer
	# major's server-dev is installed inside the climb loop, at which point the
	# default pg_config advances with it (the final-major suite reads that
	# version's catalog header for the relkind drift check; see the test job).
	run: apt-get install -y rsync
	- name: Install cat_tools into the PG10 cluster
	run: make install
	- name: Prepare PG10 (install 0.2.0, bridge to 0.2.3, plant guard)
	run: bin/test_existing prepare-old "$DB" 0.2.0 0.2.3
	- name: Climb every major via binary pg_upgrade (10 → 11 → ... → 18)
	# One sequential loop; each iteration binary-pg_upgrades the cluster from
	# $old to $new (a single major step). cat_tools is a SQL-only extension, so
	# pg_upgrade only needs its scripts present in the NEW cluster's sharedir:
	# `make install PG_CONFIG=<new>` before each step (the $old cluster already
	# has them, from the previous iteration or the PG10 install above). The
	# extension is held at 0.2.3 until PG12, where it is updated to the current
	# version; the suite then runs at every major from PG12 on (see the suite
	# policy inside the loop). The per-step pg_upgrade invocation and log
	# handling mirror pg-upgrade-test.
	run: \|
	old=$LEGACY_PG
	for new in $CLIMB_PG; do
	echo "=== binary pg_upgrade PostgreSQL $old -> $new ==="
	apt-get install -y postgresql-$new postgresql-server-dev-$new
	# PG_CONFIG explicit: several majors are installed, so the default
	# pg_config on PATH may not be $new's.
	make install PG_CONFIG=/usr/lib/postgresql/$new/bin/pg_config
	pg_ctlcluster $old test stop
	pg_createcluster -p 5432 $new test -- $INITDB_OPTS
	# PG17+ writes logs under the new datadir; older versions to CWD. Dump
	# both on failure (same handling as pg-upgrade-test).
	mkdir -p /tmp/pg_upgrade_logs
	chown postgres:postgres /tmp/pg_upgrade_logs
	su -c "cd /tmp/pg_upgrade_logs && /usr/lib/postgresql/$new/bin/pg_upgrade \
	-b /usr/lib/postgresql/$old/bin \
	-B /usr/lib/postgresql/$new/bin \
	-d /var/lib/postgresql/$old/test \
	-D /var/lib/postgresql/$new/test \
	-o '-c config_file=/etc/postgresql/$old/test/postgresql.conf' \
	-O '-c config_file=/etc/postgresql/$new/test/postgresql.conf'" postgres \
	\|\| { find /tmp/pg_upgrade_logs \
	/var/lib/postgresql/$new/test/pg_upgrade_output.d \
	-name '*.log' 2>/dev/null \| sort \| xargs -r tail -n +1; exit 1; }
	pg_ctlcluster $new test start
	pg_isready -t 30
	# Suite policy across the climb. We unfortunately CANNOT run the suite
	# against the OLD version (0.2.3) at the pre-PG12 steps (10->11, 11->12):
	# the suite matches the CURRENT version's objects and we do not maintain
	# an old-version expected-output set. So those steps only verify that
	# 0.2.3 SURVIVES each pg_upgrade -- i.e. the climb completing without
	# error. On reaching PG12 (the floor for 0.2.3->0.3.0, which needs
	# ALTER TYPE ... ADD VALUE) update once to the current version; from
	# PG12 on the extension is at current, so run the full suite in existing
	# mode at EVERY major (run-suite asserts the version and re-proves the
	# guard survived this step's pg_upgrade).
	if [ "$new" = 12 ]; then
	bin/test_existing update "$DB"
	fi
	if [ "$new" -ge 12 ]; then
	bin/test_existing run-suite "$DB"
	fi
	old=$new
	done

	# Proves the in-place extension update path: CREATE EXTENSION at an OLD cat_tools
	# version then ALTER EXTENSION UPDATE (no pg_upgrade, same PostgreSQL). On PG12+
	# it updates 0.2.2 -> current and runs the FULL suite against the updated
	# database (same expected output as a fresh install, so an updated DB must behave
	# identically). The PG10 leg only exercises the pre-0.2.2 update scripts, the
	# sole version where they still load. Complements pg-upgrade-test, which covers
	# the cross-major-version binary upgrade instead.
	extension-update-test:
	needs: [changes]
	if: needs.changes.outputs.docs_only != 'true'
	strategy:
	matrix:
	# PG12+: exercise the WIDEST update path we support — CREATE EXTENSION at
	# the 0.2.2 backward-compat floor, ALTER EXTENSION UPDATE to the CURRENT
	# version, and run the full suite against the updated database. 0.2.2 is
	# the floor because the 0.2.0/0.2.1 install scripts fail on PG11+/PG12+;
	# PG12 is the PostgreSQL floor because the update runs
	# `ALTER TYPE ... ADD VALUE`, which PG11 and below cannot run in an
	# extension update script (lifted in PG12).
	# PG10: the ONLY version where the pre-0.2.2 install scripts still load,
	# so the only place the 0.2.0->0.2.2 and 0.2.1->0.2.2 update scripts and
	# the 0.2.2->0.2.3 view rebuild on the broken path can be exercised. They
	# target 0.2.2/0.2.3 (not the current version) and use no
	# ALTER TYPE ... ADD VALUE, so they run on PG10. The PG10 leg runs only
	# those legacy checks — not the current-version suite (the current version
	# needs PG12+: the 0.2.3->0.3.0 update adds enum values via ALTER TYPE ...
	# ADD VALUE, unrunnable in a pre-PG12 transaction). See the per-step `if`
	# guards.
	#
	# Current-supported majors + the legacy PG10 floor, from the single
	# source in the changes job (update_pg = supported_pg plus legacy_pg).
	pg: ${{ fromJSON(needs.changes.outputs.update_pg) }}
	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 and server headers
	# server-dev provides catalog/pg_class.h for the relkind drift check
	# (see the "test" job); required so check-relkind-source below passes.
	# PG10 runs only the legacy-script checks (no suite), so it needs no headers.
	if: matrix.pg != '10'
	run: apt-get install -y rsync postgresql-server-dev-${{ matrix.pg }}
	- name: Install rsync
	if: matrix.pg == '10'
	run: apt-get install -y rsync
	- name: Install cat_tools (all versions)
	run: make install
	- name: Test pre-0.2.2 update scripts + 0.2.2→0.2.3 rebuild (PG10 only)
	# 0.2.0/0.2.1 install only on PG10; their update scripts target 0.2.2 (not
	# the current version) and are otherwise never exercised. Both origins are
	# checked because ALTER EXTENSION UPDATE takes the shortest path (see the
	# pg-upgrade-test matrix): 0.2.0 goes via the 0.2.0--0.2.2 script, 0.2.1 via
	# 0.2.1--0.2.2. update-check asserts each lands on 0.2.2. No suite runs (the
	# current version needs PG12+).
	#
	# The rebuild_020/rebuild_021 checks exercise the REAL broken path end to
	# end from BOTH pre-0.2.2 origins: a 0.2.0 (resp. 0.2.1) install on PG10
	# leaves relhasoids in _cat_tools.pg_class_v (the buggy 0.2.0--0.2.2 /
	# 0.2.1--0.2.2 omit_column no-op), and updating to 0.2.3 routes through
	# 0.2.2--0.2.3 (shortest path <origin>--0.2.2 then 0.2.2--0.2.3), firing
	# the conditional rebuild that strips relhasoids. 0.2.3 is the furthest
	# PG10 can reach (0.2.3--0.3.0 needs PG12+). On PG12+ relhasoids never
	# existed, so only PG10 exercises the rebuild. The psql assertion fails
	# loudly if the rebuild did not fire (relhasoids still present) --
	# complementing the stronger 10→18 pg_upgrade bridge legs. `$$` is escaped
	# as `\$\$` so the shell passes literal dollar quotes through to psql.
	if: matrix.pg == '10'
	run: \|
	bin/test_existing update-check cat_tools_from_020 0.2.0 0.2.2
	bin/test_existing update-check cat_tools_from_021 0.2.1 0.2.2
	for origin in 020:0.2.0 021:0.2.1; do
	db="cat_tools_rebuild_${origin%%:*}"
	from="${origin##*:}"
	bin/test_existing update-check "$db" "$from" 0.2.3
	psql -d "$db" -v ON_ERROR_STOP=1 -c "DO \$\$ BEGIN IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid='_cat_tools.pg_class_v'::regclass AND attname='relhasoids' AND NOT attisdropped AND attnum>0) THEN RAISE EXCEPTION 'pg_class_v still exposes relhasoids after update through 0.2.2->0.2.3 -- rebuild did not fire'; END IF; END \$\$"
	done
	- name: Update 0.2.2 → current and run the suite (existing mode, PG12+)
	# update-scenario creates a real database at 0.2.2, plants + proves the
	# dependency guard, ALTER EXTENSION UPDATEs to the current version, and
	# runs the suite against that updated database in existing mode (asserting
	# the version and that the guard still blocks a drop). Reusing the SAME
	# suite and expected output asserts the updated database behaves
	# identically to a fresh install.
	if: matrix.pg != '10'
	run: bin/test_existing update-scenario cat_tools_update 0.2.2

	# 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.
	# the heavy jobs gated off by the `changes` job on a docs-only push), and
	# fails if any failed or were cancelled.
	all-checks-passed:
	needs: [changes, test, lint, pg-upgrade-test, pg-upgrade-stepwise, 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
	# vi: expandtab ts=2 sw=2

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add sql-lint: style linter for PostgreSQL SQL files #282

Workflow file

Add sql-lint: style linter for PostgreSQL SQL files #282

Uh oh!

Workflow file for this run