Release v1.0.0 #249

Workflow file for this run

.github/workflows/upgrade-tests.yml at 4be5224

	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_textsearch.control'
	- '.github/workflows/upgrade-tests.yml'
	schedule:
	# Run weekly on Sundays at 2 AM UTC
	- cron: '0 2 * * 0'

	jobs:
	test-upgrades:
	runs-on: ubuntu-latest
	strategy:
	fail-fast: false
	matrix:
	pg_version: [17, 18]
	old_version: ['0.2.0', '0.3.0', '0.4.0', '0.4.1', '0.4.2', '0.5.0', '0.5.1', '0.6.0', '0.6.1']

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

	- name: Install PostgreSQL ${{ matrix.pg_version }}
	run: \|
	sudo apt-get update
	sudo apt-get install -y wget ca-certificates gnupg
	sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
	wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \| sudo apt-key add -
	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: \|
	# Download the source tarball from GitHub releases
	DOWNLOAD_URL="https://github.com/timescale/pg_textsearch/releases/download/v${{ matrix.old_version }}/pg_textsearch-${{ matrix.old_version }}.tar.gz"
	echo "Downloading from: $DOWNLOAD_URL"
	if curl -fsSL -o pg_textsearch-old.tar.gz "$DOWNLOAD_URL"; then
	tar -xzf pg_textsearch-old.tar.gz
	ls -la
	else
	echo "::error::Failed to download v${{ matrix.old_version }}"
	exit 1
	fi

	- name: Build and install old version v${{ matrix.old_version }}
	run: \|
	cd pg_textsearch-${{ matrix.old_version }}
	export PG_CONFIG=/usr/lib/postgresql/${{ matrix.pg_version }}/bin/pg_config
	make clean \|\| true
	make
	sudo make install

	- name: Start PostgreSQL with old binary and create test data
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH
	export PGDATA=/tmp/upgrade_test_data
	LIB_NAME="pg_textsearch"

	initdb -D $PGDATA
	echo "shared_buffers = 256MB" >> $PGDATA/postgresql.conf
	echo "port = 5432" >> $PGDATA/postgresql.conf
	echo "unix_socket_directories = '/tmp'" >> $PGDATA/postgresql.conf
	echo "shared_preload_libraries = '${LIB_NAME}'" >> $PGDATA/postgresql.conf

	pg_ctl -D $PGDATA start -w -l $PGDATA/logfile
	createdb -h /tmp upgrade_test

	- name: Install old extension and create test data
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH

	psql -h /tmp -d upgrade_test << 'SQL'
	-- Install old version
	CREATE EXTENSION pg_textsearch VERSION '${{ matrix.old_version }}';

	-- Check installed version
	SELECT extversion AS old_version FROM pg_extension WHERE extname = 'pg_textsearch';

	-- Create test table with documents
	CREATE TABLE upgrade_docs (
	id SERIAL PRIMARY KEY,
	content TEXT NOT NULL
	);

	INSERT INTO upgrade_docs (content) VALUES
	('PostgreSQL is a powerful open source database system'),
	('Full text search with BM25 ranking provides relevance scoring'),
	('The quick brown fox jumps over the lazy dog'),
	('Database indexing improves query performance significantly'),
	('Search engines use inverted indexes for fast lookups');

	-- Create BM25 index
	CREATE INDEX idx_upgrade_bm25 ON upgrade_docs
	USING bm25 (content) WITH (text_config = 'english');

	-- Run a query and save results for comparison
	CREATE TABLE pre_upgrade_results AS
	SELECT id, content <@> 'database search'::text AS score
	FROM upgrade_docs
	ORDER BY score
	LIMIT 5;

	SELECT * FROM pre_upgrade_results;
	SQL

	- name: Stop PostgreSQL, install current version, restart
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH
	export PG_CONFIG=/usr/lib/postgresql/${{ matrix.pg_version }}/bin/pg_config
	export PGDATA=/tmp/upgrade_test_data

	# Stop Postgres (still running old binary)
	pg_ctl -D $PGDATA stop -w

	# Build and install current version (overwrites old binary)
	cd $GITHUB_WORKSPACE
	make clean
	make
	sudo make install

	# Restart with new binary
	pg_ctl -D $PGDATA start -w -l $PGDATA/logfile

	- name: Upgrade extension to current version
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH

	# Perform the upgrade
	psql -h /tmp -d upgrade_test << 'SQL'
	-- Upgrade extension
	ALTER EXTENSION pg_textsearch UPDATE;

	-- Verify new version
	SELECT extversion AS new_version FROM pg_extension WHERE extname = 'pg_textsearch';

	-- Rebuild indexes (required when upgrading across format changes)
	REINDEX INDEX idx_upgrade_bm25;
	SQL

	- name: Verify upgrade succeeded
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH

	# Extract expected version prefix from control file (e.g., "0.5" from "0.5.0-dev")
	EXPECTED_VERSION=$(awk -F"'" '/^[\t ]*default_version/ {print $2}' pg_textsearch.control \| cut -d. -f1,2)

	psql -h /tmp -d upgrade_test -v expected_version="$EXPECTED_VERSION" << 'SQL'
	\set ON_ERROR_STOP on

	-- Store expected version in temp table for use in DO blocks
	CREATE TEMP TABLE _expected(version_prefix text);
	INSERT INTO _expected VALUES (:'expected_version');

	-- Test 1: Verify extension upgraded to expected version
	DO $$
	DECLARE
	ver text;
	expected text;
	BEGIN
	SELECT version_prefix INTO expected FROM _expected;
	SELECT extversion INTO ver FROM pg_extension WHERE extname = 'pg_textsearch';
	IF ver NOT LIKE expected \|\| '%' THEN
	RAISE EXCEPTION 'FAIL: Extension did not upgrade properly. Version: %, expected: %*', ver, expected;
	END IF;
	RAISE NOTICE 'PASS: Extension upgraded to version: %', ver;
	END $$;

	-- Test 2: Verify BM25 index scan works with ORDER BY ... LIMIT
	-- Note: Must use explicit to_bm25query() in PL/pgSQL - implicit resolution doesn't work
	DO $$
	DECLARE
	rec record;
	BEGIN
	SELECT * INTO rec FROM upgrade_docs
	ORDER BY content <@> to_bm25query('database', 'idx_upgrade_bm25') LIMIT 1;
	IF rec.id IS NULL THEN
	RAISE EXCEPTION 'FAIL: Query returned no results';
	END IF;
	RAISE NOTICE 'PASS: BM25 query works, got id=%', rec.id;
	END $$;

	-- Test 3: Verify scores are negative (BM25 returns negative for ASC)
	DO $$
	DECLARE
	score_val float8;
	BEGIN
	SELECT content <@> to_bm25query('database', 'idx_upgrade_bm25') INTO score_val
	FROM upgrade_docs
	ORDER BY 1 LIMIT 1;
	IF score_val IS NULL OR score_val >= 0 THEN
	RAISE EXCEPTION 'FAIL: BM25 score should be negative, got: %', score_val;
	END IF;
	RAISE NOTICE 'PASS: BM25 scoring correct, score=%', score_val;
	END $$;

	-- Test 4: Verify index scan is used (run EXPLAIN outside DO block)
	EXPLAIN (COSTS OFF)
	SELECT * FROM upgrade_docs
	ORDER BY content <@> to_bm25query('database', 'idx_upgrade_bm25')
	LIMIT 5;

	-- Test 5: Insert new data and verify it's searchable
	INSERT INTO upgrade_docs (content) VALUES
	('New document added after upgrade to test index updates');

	DO $$
	DECLARE
	rec record;
	BEGIN
	SELECT * INTO rec FROM upgrade_docs
	ORDER BY content <@> to_bm25query('upgrade', 'idx_upgrade_bm25') LIMIT 1;
	IF rec.id IS NULL THEN
	RAISE EXCEPTION 'FAIL: Could not find newly inserted document';
	END IF;
	RAISE NOTICE 'PASS: New document searchable, id=%', rec.id;
	END $$;

	-- Test 6: Create new index after upgrade
	CREATE TABLE post_upgrade_docs (content TEXT);
	INSERT INTO post_upgrade_docs VALUES ('Testing post-upgrade index creation');
	CREATE INDEX idx_post_upgrade ON post_upgrade_docs
	USING bm25 (content) WITH (text_config = 'english');

	DO $$
	DECLARE
	score_val float8;
	BEGIN
	SELECT content <@> to_bm25query('testing', 'idx_post_upgrade') INTO score_val
	FROM post_upgrade_docs
	ORDER BY 1 LIMIT 1;
	IF score_val IS NULL OR score_val >= 0 THEN
	RAISE EXCEPTION 'FAIL: New index query failed, score=%', score_val;
	END IF;
	RAISE NOTICE 'PASS: New index works, score=%', score_val;
	END $$;

	SELECT 'All compatible upgrade tests passed!' as final_status;
	SQL

	- name: Cleanup
	if: always()
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH
	pg_ctl -D /tmp/upgrade_test_data stop -m immediate \|\| true
	rm -rf /tmp/upgrade_test_data

	# Note: v0.1.0 incompatible upgrade test was removed because
	# shared_preload_libraries is now required. The v0.1.0 extension SQL
	# references functions (e.g., bm25_get_current_score) that no longer exist
	# in the current binary. Upgrades from v0.2.0+ are tested in test-upgrades.

	# Test clean install (no upgrade) on both PG versions
	test-clean-install:
	runs-on: ubuntu-latest
	strategy:
	matrix:
	pg_version: [17, 18]

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

	- name: Install PostgreSQL ${{ matrix.pg_version }}
	run: \|
	sudo apt-get update
	sudo apt-get install -y wget ca-certificates gnupg
	sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
	wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \| sudo apt-key add -
	sudo apt-get update
	sudo apt-get install -y postgresql-${{ matrix.pg_version }} postgresql-server-dev-${{ matrix.pg_version }}
	sudo systemctl stop postgresql

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

	- name: Test clean installation
	run: \|
	export PATH=/usr/lib/postgresql/${{ matrix.pg_version }}/bin:$PATH
	export PGDATA=/tmp/clean_test_data
	LIB_NAME="pg_textsearch"

	initdb -D $PGDATA
	echo "port = 5432" >> $PGDATA/postgresql.conf
	echo "unix_socket_directories = '/tmp'" >> $PGDATA/postgresql.conf
	echo "shared_preload_libraries = '${LIB_NAME}'" >> $PGDATA/postgresql.conf

	pg_ctl -D $PGDATA start -w -l $PGDATA/logfile
	createdb -h /tmp clean_test

	psql -h /tmp -d clean_test << 'SQL'
	CREATE EXTENSION pg_textsearch;

	-- Verify version
	SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch';

	-- Basic functionality test
	CREATE TABLE docs (id SERIAL PRIMARY KEY, content TEXT);
	INSERT INTO docs (content) VALUES
	('PostgreSQL full text search'),
	('BM25 ranking algorithm'),
	('Document retrieval system');

	CREATE INDEX idx_docs ON docs USING bm25 (content) WITH (text_config = 'english');

	-- Test query
	SELECT id, content <@> 'search ranking'::text AS score
	FROM docs
	ORDER BY score
	LIMIT 3;

	RAISE NOTICE 'Clean installation test passed!';
	SQL

	pg_ctl -D $PGDATA stop -m immediate
	rm -rf $PGDATA

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Release v1.0.0 #249

Workflow file

Release v1.0.0 #249

Uh oh!

Workflow file for this run