rupg-compat

The PostgreSQL compatibility harness for rupg.

This harness turns "compatible with PostgreSQL" from a claim into a number that a machine computed. It builds real PostgreSQL servers from pinned commits, sends the same input to an oracle and to rupg, and compares every byte that a client can see. It runs the PostgreSQL regression and isolation suites, the test suites of 48 clients, sqllogictest and generated queries, and it publishes one number for each level.

It is a separate repository for three reasons. It must build and run PostgreSQL, which no crate that ships with rupg should do. It does not link a rupg crate, so a bug in the engine's codec cannot be on both sides of the comparison. And a suite that lives inside the code it tests is a suite whose failures are easy to explain away.

The design is spec/21-testing.md section 21.3, and the levels it reports are spec/05-compatibility.md section 5.1.

Early. The crate builds and CI is green, but no suite runs yet. The pins are in pins.toml. rupg-compat pins prints them and rupg-compat levels prints the five levels. The first milestone, M0, builds the oracles and the runner, and it closes when the harness scores 100 percent against the oracle of 19. Every command below is the planned interface from the spec.

The oracle is a real PostgreSQL server built from the pin. It is correct by definition, also where it disagrees with the documentation. When the oracle gives an answer that depends on the run, for example the order of rows without ORDER BY, the harness runs the oracle twice and removes the unstable case. A test may not add an exclusion. The closed list of 15 excluded items is in spec/05 section 5.3, and the report prints each exclusion that a run used, with its count.

A level is at 100 percent when every item of its denominator gives the same result on rupg as on the oracle, after the exclusions.

Every oracle runs with the same fixed configuration: initdb --no-locale with UTF8, TimeZone UTC, DateStyle ISO, MDY, lc_messages C, SCRAM passwords, and TLS with a fixed test certificate. A second nightly set runs with an ICU locale and a time zone that is not UTC. The 19 pin moves on a branch and lands on one day, with every count taken again.

The server under test runs with the same configuration files as the oracle. rupg-compat oracle conf DIR --port N copies postgresql.conf, the host rules, the certificate and its key from the data directory of the oracle to DIR, and changes only the port. Then rupg serve -D DIR reads them. Without these files, pg_settings, pg_file_settings, pg_hba_file_rules, ssl and max_prepared_transactions give other values than the oracle, and the cause is the setup and not the server.

The generator checks each query with one of five oracles: differential, TLP, NoREC, PQS and layout equivalence over five layouts of the same data. Each shim version from 14 to 18 runs its own suites against its own oracle, at the cadence of spec/05 section 5.6.5.

The commands below are the planned interface. The harness is one Rust crate with a binary named rupg-compat, built with Rust 1.98.0.

cargo run --release -- oracle build --version 19
cargo run --release -- record psql
cargo run --release -- replay corpus/traces/psql.trace
cargo run --release -- diff "SELECT 1"
cargo run --release -- regress
cargo run --release -- isolation
cargo run --release -- dump --server 127.0.0.1:5432
cargo run --release -- gate --server 127.0.0.1:5432
cargo run --release -- connect --server 127.0.0.1:5432
cargo run --release -- version --server 127.0.0.1:5432
cargo run --release -- client pgx
cargo run --release -- gen --oracle tlp --seed 42
cargo run --release -- layout --seed 42 --server 127.0.0.1:5432
cargo run --release -- strip --rupg-bin ../rupg/target/release/rupg --seed 42
cargo run --release -- crash --rupg-bin ../rupg/target/release/rupg --seed 42
cargo run --release -- reduce corpus/reduced/case.sql
cargo run --release -- bisect corpus/reduced/case.sql --rupg ../rupg --good v0.4.0 --bad main
cargo run --release -- tap
cargo run --release -- jepsen
cargo run --release -- report
cargo run --release -- reference --check

Every command takes --compat-version V, which sets rupg.compat_version on rupg and picks the oracle of version V. up starts the servers, and client, regress, isolation, tap and jepsen each run one suite. Replay maps OIDs, runs a new SCRAM exchange for each server, and rewrites the key of each CancelRequest.

Every command also takes --cgroup DIR, the cgroup v2 directory of the server that the run measures. Each result file then has the peak memory (memory.peak) and the CPU time (usage_usec of cpu.stat) of that server, as spec/02 section 2.6.1 says, and the report shows the ratio of the oracle to rupg for each suite. Before Linux 6.12 the kernel cannot reset memory.peak, so give each suite a new cgroup to get the peak of that suite. The report also lists the failing tests of each run of the server under test. report --rupg-commit SHA writes the commit of rupg under test in the pins table, because the pins of the binary can be older.

Six of the 48 clients are closed source or run only as a hosted service: DataGrip, TablePlus, Postico 2, Tableau, Power BI and Looker. CI cannot run them, so report shows each of them in L1 with the numbers of the driver that it uses: pgjdbc for DataGrip and Looker, Npgsql for Power BI, psqlODBC for Tableau, and libpq for TablePlus and Postico 2. These rows have no count of their own, and the ratchet checks only the row of the driver. A trace that a person records of one of these tools replaces the driver row. DBeaver and Beekeeper Studio have no headless mode, so clients/dbeaver and clients/beekeeper send the queries of their PostgreSQL code at the pinned release through pgjdbc and node-postgres. Replay also maps an OID in a text column with the name oid, because Beekeeper Studio casts the OID of a function to text and sends it back to pg_get_functiondef.

catalog runs SELECT * on each catalog and each view of L2 in a new database, two times on the oracle and one time on the server under test, and compares the rows. With --schema gate, it first runs clients/gate.sql in the new database of each server, so the rows of the gate schema are compared too. That run is the suite catalog-gate. The report shows the rows of L2 tier 1 and tier 2 apart (spec/05 section 5.1). Tier 1 is pg_type, pg_namespace, pg_class and pg_attribute. Tier 2 is pg_index, pg_constraint, pg_attrdef, pg_description, pg_enum, pg_range, pg_proc and each relation of information_schema. Each tier row is the sum of the equal rows of its relations, of the sum of their rows.

catalog-corpus is the catalog corpus that the recording proxy captured from the clients (spec/05 section 5.6.5). It reads the traces in corpus/traces and keeps each query that starts with SELECT or WITH, reads the catalog, changes nothing and has no parameters. Each query is in the suite one time. The suite runs clients/gate.sql in a new database, runs the queries two times on the oracle and one time on the server under test, and compares the answers with the rules of the catalog suite. The name of a case is the name of the first trace that sends the query and the number of the query in that trace.

types checks the four functions of each type (spec/07 section 7.9 of rupg). For each value of suites/types/values.txt, it runs three cases: SELECT 'v'::T as a simple query for the text output, the same query with a binary result for the send function, and SELECT $1 with the binary value of the oracle as a parameter of type T for the receive function. A value that the oracle rejects has only the first two cases, so the error is compared too. The report shows the suite in L3.

The oracles are built with lz4, because PostgreSQL 19 then makes lz4 the default of default_toast_compression, as the spec of rupg gives. The compression changes which rules of the system views take a TOAST value, so it changes the OIDs of the objects that initdb makes.

The oracles are also built with zstd, because rupg has zstd. With zstd, wal_compression accepts zstd, and pg_settings shows it in enumvals.

dump runs clients/pg_dump/roundtrip.sql in a new database of the server under test and dumps it with pg_dump of the oracle build. Then it restores the dump into the oracle with psql and with pg_restore, dumps the oracle again, and compares the texts. It also compares the dump of the server under test with the dump of the oracle after the same script, and restores the dump of the oracle into the server under test. The comparison ignores the line -- Dumped from database version and the random key of \restrict.

The workflow nightly builds rupg main each night and runs .github/nightly.sh, which runs each suite on the oracle and on rupg and then report (spec/05 section 5.8). Each server runs in a new cgroup v2 for each suite, so the report has the peak memory and the CPU time of each suite also on a kernel before Linux 6.12. A second job pushes the report to commits/<commit>.md on the branch pages, with a line in its README.md. The job with the suites has a read-only token, and only the job that pushes can write. The result files and the logs stay as an artifact for 14 days. workflow_dispatch takes rupg-ref to test another commit.

sqllogictest fetches the sqllogictest corpus of SQLite at the commit of [sqllogictest] in pins.toml into the work directory. It runs each record of the test files under the given paths on the oracle and on the server under test, and compares the answers of the two servers. It does not use the expected answers of the corpus, because they are the answers of SQLite. A record that skipif postgresql or onlyif with another engine names does not run. A statement gives its command tag or its error. A query gives its rows in the order of its sort mode, or its error. A file passes when each record gives the answer of the oracle, and the report has the rows "sqllogictest files" and "sqllogictest records" at L3. Each worker uses the database slt_<n> on each server. Without --server, the oracle runs against itself in a second database. The nightly runs select1.test to select5.test, evidence and index/between, which are 135,648 records.

generate reads gram.y and kwlist.h of the oracle source at the pin, which have 743 rules and 3,476 productions, and writes statements that start from SelectStmt, InsertStmt, UpdateStmt or DeleteStmt. The seed (--seed N, else the clock) makes a schema of three tables with data, and then each statement, so the same seed gives the same run. The seed is on the first line and on the last line of the output. Each statement runs in a transaction that rolls back, on two databases of the oracle and on the server under test. A statement that does not give the same answer on the two oracle databases is unstable and does not count. A choice of a production uses its weight, and after each 200 statements the weight of a production that no statement used goes up to two times its value, to at most 64. Past a depth of 9 or 80 tokens, each choice takes a production with the smallest expansion. The output gives the productions that the run used. The statements that are different go to generate/<seed>.sql in the work directory, with the schema and the two answers, and the report has the row "generated statements" at L3. Most of the statements give an error on the oracle, so this is the differential oracle for the parser and for the order of the checks. The layout oracle follows.

reduce FILE makes a SQL file that fails on the server under test smaller, for example a file of generate/ or logic/ in the work directory. It runs the statements of the file in order in a new database on the oracle and on the server under test. The way the file fails is the first statement with a different answer, and for that statement the first different message type and the SQLSTATE on each side. The first pass removes statements with delta debugging. The second pass removes clauses, groups in parentheses, items of lists and single tokens, and puts the content of a group or NULL in place of the group. The third pass puts 0, 1, '' or 'a' in place of a literal. A step stays only if the file still fails in the same way, so that the reduction does not move to a different bug. The result goes to FILE.reduced.sql (or --out FILE), with the way it fails on the first line. --max-runs N (default 3,000) stops the reduction after N runs of the file. The comparison does not use the table OIDs of a RowDescription or the OIDs of user types, because they differ between two clusters.

bisect FILE --rupg DIR --good SHA --bad SHA finds the rupg commit that causes a failure. First it runs the file at the bad commit, and the way the file fails there is the signature, as in reduce. Then it makes a binary search over the commits from the good commit to the bad commit. For each commit it builds rupg in work/bisect/ (a git worktree and a target directory), starts the build on --port N (default 5599) with the configuration of the oracle, and runs the file on the build and on the oracle. A commit is bad if the file fails with the same signature. A search over 1,000 commits builds about 10 commits. If a commit does not build or the server does not start, the search skips that commit. If the bad commit is older than the good commit, the search finds the first commit where the failure stops, which is the commit of the fix.

copy runs 67 cases of COPY in a new database on the oracle and on the server under test, and compares the messages of each case. The table src has 15 columns of common types and rows with special values: NULL, NaN, infinity, a tab, a new line, a backslash, \N, the text NULL, an empty text and text that is not ASCII. There are 30 COPY TO cases for each format and its options, for a query, for a column list, for ENCODING and for the errors of the options. There are 7 round trip cases: the oracle writes src in one format, and each side reads that output into dst. There are 30 COPY FROM cases with fixed data, for the escapes, the end marker, WHERE, DEFAULT, FORCE_NOT_NULL, FORCE_NULL, HEADER MATCH, ON_ERROR, LOG_VERBOSITY, REJECT_LIMIT, ENCODING and the errors. A COPY FROM case compares the messages of the copy and then the rows of the table. The CopyData messages of a copy compare as one string, because a server can cut the data in other places. The report has the row "COPY cases" at L3.

logic uses the schema and the data of the seed, as generate does, and makes a predicate over the columns of one table or of two joined tables for each check. The type of each expression is known, so that most predicates are correct. TLP checks that the rows of a query are the rows where the predicate is true, false or NULL, for SELECT *, SELECT DISTINCT * and count(*). NoREC checks that count(*) with the predicate in WHERE is the sum of (p) IS TRUE over all the rows. PQS asks the oracle for the value of a predicate on a row, changes the predicate so that it is true on that row, and checks that the row is in the answer of the server. A check is first done on the oracle, and it is skipped when a query gives an error there. The differential check compares the answer of each query on the server with the answer of the oracle. The failed checks go to logic/<seed>.sql in the work directory, and the report has the row "logic checks" at L3. The nightly runs 5,000 predicates with the date as the seed.

layout is the layout equivalence oracle. A rupg table can have rows in the hot store, in the cold store, or in both. The seed makes three tables of --rows N rows (default 200) with an id column, and the data goes into a new database for each of six layouts: hot only (INSERT), cold only (COPY into a table USING columnar), mixed (half the rows by COPY, the other half by INSERT), mixed after change (mixed, then updates, deletes and inserts), no summaries (mixed with rupg.use_summaries = off) and many workers (hot only with the parallel settings at zero cost). Then --count N queries (default 300) run on each layout: rows, aggregates, groups, ORDER BY with LIMIT, and joins, each with a predicate of logic. The answer in each layout must be the answer of the oracle on the same data, and the run also counts the answers that differ from the hot layout of the same server. If the server does not accept a step of the setup of a layout, that layout is not available and the run gives the error. The failed queries go to layout/<seed>.sql in the work directory, and the report has the row "layout checks" at L3. The nightly runs 1,000 queries with the date as the seed.

strip is the summary deletion run. It starts --rupg-bin FILE on a new data directory in work/strip/ on --port N (default 5598), and loads the data of the seed in the hot only, cold only, mixed and mixed after change layouts, with a primary key on id and an index on each other column. It runs the queries of layout and keeps the answers. Then it stops rupg, copies the data directory, runs rupg strip --summaries on the copy and starts rupg on the copy. Each answer must be the same, byte for byte. Then it drops the indexes that are not constraints and compares again. If rupg strip fails, or the copy has no databases because the server keeps them in memory, the run says that the comparison is not available. The report has the row "answers without summaries" at L3. The nightly runs it after the other suites, with 1,000 queries and the date as the seed.

crash is the crash run. It starts --rupg-bin FILE on a new data directory in work/crash/ on --port N (default 5597), and runs --rounds N (default 20) rounds of transactions from the seed: inserts of up to --rows N rows (default 1000), updates, deletes, TRUNCATE, CREATE TABLE, CREATE INDEX and ALTER TABLE ADD COLUMN. Each transaction runs on the oracle first, and then on rupg when the oracle accepts it. One in five rolls back. At the end of a round, rupg sometimes also gets a CHECKPOINT, or a transaction that stays open. Then the run stops rupg with SIGKILL, or with SIGTERM in each fourth round, and starts it again on the same data directory. Each table, and the list of the tables, indexes and columns, must give the same answer as on the oracle. A statement that fails on rupg only is also a failure. The report has the row "tables after a crash" at L4. A failed run writes the committed statements and the failed checks to work/crash/<seed>.sql.

gate runs clients/gate.sql in a new database of the oracle and of the server under test. Then it runs clients/<name>/run.sh gate for psql, pgAdmin 4, pgjdbc, psycopg, node-postgres, pgx and asyncpg. Each gate program lists the tables, the columns, the types, the indexes and the constraints of the schema gate. psql uses its describe commands, pgAdmin 4 walks its browser tree and writes the SQL tab of each object, and pgjdbc writes the results of DatabaseMetaData. The other drivers have no describe command, so they run the catalog queries of clients/gate-queries.sql and write the columns that they describe for a query of each table. A client passes when its list on the server under test is equal to its list on the oracle. The test of each client is gate in its oracle-fail.txt and expected-fail.txt. The gate does not run a client whose test is in oracle-fail.txt. The command exits with 1 when a client fails that is not in expected-fail.txt, or when a client in that list passes. pipeline --server uses the lists in suites/pipeline in the same way, with the name of each test and each trace as the test.

connect runs clients/<name>/run.sh connect for psql, libpq and the 15 drivers: pgjdbc, psycopg, psycopg2, asyncpg, node-postgres, postgres.js, pgx, lib/pq, Npgsql, tokio-postgres, sqlx, the Ruby pg gem, Postgrex, the R2DBC driver and psqlODBC. The Rust programs are crates of their own in connect/, so the trace scenarios keep their lock files and build without TLS. The proxy has no TLS, so the traces cannot show the TLS handshake. This suite does that check. Each program connects to the database postgres with TLS, checks the certificate of the oracle and requires channel binding where the client can do it. Then it runs one query that gives system_user, the TLS flag and the TLS version of its own connection, writes one line and disconnects. The runner writes the certificate of the oracle to root.crt in the run directory and sets PGSSLMODE=verify-full, PGSSLROOTCERT and PGCHANNELBINDING=require. A client passes when its line on the server under test is equal to its line on the oracle. The test of each client is connect in its lists.

version connects to the server under test and checks the four places where rupg gives its version, as spec/05 section 5.7 says. server_version at startup and SHOW server_version must be N.M (rupg X.Y.Z), where N is the compat version. server_version_num must be N0M in the PostgreSQL form, and version() must be PostgreSQL N.M (rupg X.Y.Z) on <target>, compiled by rustc <version>, 64-bit. rupg.version at startup and SHOW rupg.version must be X.Y.Z, and SET rupg.version must fail with 55P02, as for a parameter with the context internal. The server must send rupg.version after the standard parameters of version N, and it must not send crdb_version, mz_version or questdb_version. The oracle does not have these forms, so the command needs --server.

1. The oracle decides. When the documentation and the oracle disagree, the oracle is correct.
2. The regression number is the strict count: a test passes only when pg_regress says so. The statement count is published for progress and never used for a gate.
3. A test leaves the denominator only when it needs a regress.c function or a C extension that rupg does not have, or when its purpose is an excluded item such as the exact EXPLAIN output. The list is published with the reason for each test in suites/regress/excluded.txt, and the report shows it.
4. expected-fail.txt for each client may only get shorter, and each line names its cause and the milestone that fixes it.
5. ratchet.toml holds the best number of each row, and rupg-compat report fails when a number goes below it. A number may go down only with an entry in spec/24 that says why.
6. The number can go down when the pin moves. The fall is published with its cause, and the pin is never held back to keep a number.
7. A difference that a later PostgreSQL release fixed is recorded as "fixed upstream" and is not counted against a shim.
8. One page is published for each commit on main, with the L1 to L5 numbers for 19, the shim numbers, the failing items, and the peak memory and CPU time of rupg and of the oracle for each suite.

pins.toml          PostgreSQL commits, client versions, the rupg commit
oracle/            build scripts for the six oracles
src/               the runner, the proxy, the comparison, the generator
clients/<name>/    run.sh, the gate and connect programs, oracle-fail.txt, expected-fail.txt
suites/pipeline/   oracle-fail.txt, expected-fail.txt for libpq_pipeline
suites/regress/    excluded.txt, the tests that leave the counts of the regression suite, milestones.txt, the milestone of each other test, and explain.txt, the tests whose EXPLAIN lines are filtered
corpus/            traces, the differential corpus, reduced cases
shim/<N>/          divergences.toml, and the reference output of the oracle of N in reference/
jepsen/            the Jepsen and Elle workloads
ratchet.toml       the best number of each row
reports/<date>/    one table for each level, and each shim version

A suite can show that a bug exists, not that no bug exists. The numbers say that the suites found no difference after a stated effort, and the release report states the effort in hours and cases. The harness does not test the speed of rupg. That is rupg-bench.

Read CONTRIBUTING.md. The milestones are issues with the label kind/milestone.

Apache-2.0. See LICENSE-APACHE. This project is not affiliated with the PostgreSQL Global Development Group.