Unit / regression testing documentation

[ccleve]

I watched the excellent webinar on developing with pgx on Postgresconf. https://www.youtube.com/watch?v=RORkgaURcS0

Toward the end of the presentation Eric said that he was running out of time and he couldn't talk about unit testing. Can we get some documentation on that? Perhaps just a few paragraphs in the readme?

I've found some promising code in the Zombodb project for using Spi, but I'd really like to understand more.

Also, is it possible to do Postgres-style regression testing, of the type I'd get with "make check" when developing an extension in C?

https://www.postgresql.org/docs/14/regress-run.html

[eeeebbbbrrrr]

I have to admit, unit testing in general is not my strong point. I find it fairly difficult, even outside of pgx. I would like to do an hour or two on twitch someday about unit testing extensions with pgx. There's a handful of other videos I've done that you might find interesting. Probably the "SPI" video. https://www.twitch.tv/zombodb/videos

Regarding Postgres-style regression testing, sure, you can do that. ZomboDB has a little script called installcheck that makes this happen. I haven't put the brain power into how to tie it into cargo-pgx (tho that would be cool), but it was easy enough to bang out as a shell script. And have proven incredibly useful for ZDB as we ported all the regression tests from the old C version forward to the Rust version.

If you have some specific testing related topics you'd like for me to do in a video, please let me know. I'd need some time to prepare but could probably sneak something in before the end of the year.

[olirice]

Here's what I ended up with after adapting the script installcheck script

#! /bin/bash

########
# Vars #
########
TMPDIR="$(mktemp -d)"
export PGDATA="$TMPDIR"
export PGHOST="$TMPDIR"
export PGUSER=postgres
export PGDATABASE=postgres
export PGTZ=UTC
export PG_COLOR=auto

####################
# Ensure Clean Env #
####################
# Stop the server (if running)
trap 'pg_ctl stop -m i' sigint sigterm exit
# Remove temporary data dir
rm -rf "$tmpdir"

##############
# Initialize #
##############
# Initialize: setting PGUSER as the owner
initdb --no-locale --encoding=UTF8 --nosync -U "$PGUSER"
# Start the server
pg_ctl start -o "-F -c listen_addresses=\"\" -c log_min_messages=WARNING -k $PGDATA"
# Start the server
createdb contrib_regression

#########
# Tests #
#########
TESTDIR="test"
PGXS=$(dirname `pg_config --pgxs`)
REGRESS="${PGXS}/../test/regress/pg_regress"

# Collect Test List
TESTS=$(ls ${TESTDIR}/sql | sed -e 's/\..*$//' | sort )

# Execute the test fixtures
psql -v ON_ERROR_STOP=1 -f test/fixtures.sql -d contrib_regression

# Run tests
${REGRESS} --use-existing --dbname=contrib_regression --inputdir=${TESTDIR} ${TESTS}

It assumes your test directory structure looks like this

my-extension/
├─ ...
├─ test/
│  ├─ fixtures.sql
│  ├─ sql/
│  │  ├─ test_one.sql
│  │  ├─ test_two.sql
│  ├─ expected/
│  │  ├─ test_one.out
│  │  ├─ test_two.out

where fixtures.sql is a setup script that runs once before the test suite

[workingjubilee]

It would be a good idea for us to have an implementation of this so we can also adjust it to handle things like eliding line numbers and such reported in pgx's output versus the fixture we're comparing against, so that something like adding whitespace in the source code doesn't necessarily break the fixture and thus the diffs are less noisy. This is a strategy used by rustc, which also has these kinds of tests (called "UI tests", under /src/test/ui and documented in the rustc dev guide section on UI tests) for diagnostic information.

[philippemnoel]

Would love better documentation for pg_regress/integration testing in pgrx :)