Liking cljdoc? Tell your friends :D

PostgreSQL regression baseline

The default regression profile is PostgreSQL 17.7 (REL_17_7). The PostgreSQL 18.6 (REL_18_6) profile has its own inventory and strict gate. The schedule inventory records which files have been classified. The output comparison helps locate differences in SQL behavior. Neither is a percentage of PostgreSQL features implemented.

What the comparison measures

Agreement is the share of PostgreSQL's expected output lines that appear, in order, in pg-datahike's output. Only source excerpts (LINE n: and carets) are removed before comparison. Commands, rows, errors and notices remain.

A score of 100% means every expected line occurs in order. It does not mean the outputs match exactly: extra output does not lower the score. Echoed SQL, formatting and cascading errors also contribute to the metric. Use it to find a file and its first meaningful difference; verify behavior with statement-level differential tests.

The authoritative per-file scores are in agreement.edn. The strict admissions in campaign.edn use a stronger comparison of complete normalized output.

Recorded baseline: 2026-10-24

The baseline recorded in PR #350 was measured against PostgreSQL 17.7 before the additional fixture prerequisites in PR #354.

MeasureRecorded value
Files in the pinned parallel schedule222
Scheduled files classified as out of scope69
Application-facing scheduled files153
Scheduled files measured152; test_setup runs as the bootstrap
Additional files measurednumeric_big and reindex_catalog
Total files measured154
Files with 100% expected-line coverage16
Mean per-file agreement64.4%
Approximate line-weighted agreement55.5%

The agreement runner also discovers SQL files outside the parallel schedule. Its denominator therefore differs from the schedule inventory. The pinned SQL directory contains 224 files; after excluding the 69 files in scope.edn, 155 are candidates, of which 154 have recorded scores; test_setup has no independent score because it runs as the bootstrap. A missing result must remain unmeasured rather than reuse an older artifact.

The line-weighted estimate represents about 81,700 unmatched expected lines out of 183,800. It gives larger files more weight than the per-file mean; both measures include presentation and fixture effects as well as semantic differences.

Expected-line coverageFiles
100%16
90–99%11
70–89%37
50–69%51
Below 50%39

Separate probes with corrected prerequisites raised geometry from 7.2% to 14.9% and horology from 37.8% to 49.0%. These are fixture corrections, not changes in server behavior. They are not incorporated into the recorded aggregate baseline above.

Remaining compatibility areas

The estimates below come from classifying errors in the recorded outputs. They overlap: a rejected CREATE can cause many missing-relation and transaction-aborted errors. PostgreSQL deliberately raises errors in its own tests too. These counts guide investigation; they do not count independent defects or predict how much a feature will improve agreement.

Approximate unmatched linesAreaNext investigation
19,200Cascading failuresFind the first failed statement and distinguish missing fixtures from unsupported behavior
12,800Parser gapsExpression indexes, window GROUPS/EXCLUDE, SEARCH/CYCLE, array assignment, DDL and geometric operators
7,400Missing functionsJSON path functions, record population, range constructors and tsquery functions
4,900SQL/JSON and JSON pathQuery/value/table expressions and path evaluation
4,700Ranges and multirangesTypes, constructors and operators
3,100DDL and routinesClassify unsupported forms of functions, operators, aggregates, rules and ALTER statements individually
3,100Name and join resolutionParenthesized joins, qualified names and missing FROM references
3,000EXPLAIN outputSeparate SQL API requirements from PostgreSQL planner presentation
2,600Text searchQuery construction and remaining operators
1,600PartitioningEstablish the intended routing and catalog contract
1,500XMLDecide the supported surface
1,400Remaining typesregproc family, inet/cidr and transaction identifiers
700MERGEAction selection and effects
15,700Other errorsCompare individual statements, including expected errors

Support decisions belong to individual capabilities. A file's scope classification does not establish whether every statement in it should be supported.

Scope

scope.edn excludes 69 scheduled files covering PostgreSQL physical access methods, planner and storage internals, physical partitioning tests, server extension hooks, administration and security, replication, internal sanity checks and platform collation matrices.

Some excluded files also contain useful application-facing behavior. For example, PL/pgSQL execution is implemented, but the entire upstream plpgsql file is not a compatibility gate. Focused differential tests must establish the supported subset.

Row order without ORDER BY can also differ. Investigate ordering required by a SQL construct independently from incidental order in expected output; an aggregate's ORDER BY is part of its contract.

PostgreSQL 18.6

The live whole-file gate includes advisory_lock, boolean, comments, delete, lseg, macaddr, md5, select_having and select_implicit. Each matched a fresh native 18.6 oracle and pg-datahike v0.1.390 after removing source excerpts and their caret markers. The gate uses the pinned upstream output and gives each file a separate database. numeric_big and portals_p2 also run in separate databases with the minimal setup declared in fixtures.edn. Focused tests also cover upstream CREATE TABLE and CREATE TABLE LIKE slices, including catalog state after rejected statements.

Discovery caseObserved gaps
aggregatesCustom aggregate fixtures cause cascades. Built-in aggregate coverage and resolution remain open.
arraysArray input, type resolution, collated and other array_sort orderings, array_sample and array_reverse. Numeric array_sort has strict slices for ordering, dimensions and bounds.
copyselectCOPY diagnostics. Strict wire slices check legacy FORCE QUOTE CSV output and successive COPY FROM STDIN commands.
create_table and create_table_likePartitioning, some persistence metadata and optional LIKE properties such as INCLUDING GENERATED. A subquery in a default is rejected with a different SQLSTATE.
groupingsetsGrouping sets, rollup and cube are unsupported. The v0.1.390 replay completed with early rejections; the initial October run exhausted the heap.
prepareArity, declared type coercion and unknown-type errors now agree in focused probes. A strict slice covers CREATE TABLE AS EXECUTE; path inference and catalog output still differ.

The October intake attempted the 164 selected files. The native run completed 164 and the then-current pg-datahike build completed 162. The two incomplete target cases were groupingsets and database, where unsupported database rename left a run-created database behind. Global database DDL discovery requires an exclusive disposable endpoint. The full agreement manifest remains unqualified. Use the campaign manifest for admissions and per-file blockers, and the fixture descriptor for prerequisite and native-helper boundaries.

The residual map records the later paired discovery across all 164 application-facing files. It tracks output outcomes and fixture readiness independently. Findings link to upstream SQL ranges and distinguish wrong results, internal errors, unsupported SQL and fixture boundaries. A covered finding must name a live strict slice. Files without a recorded cause remain visible in the census.

PG_REGRESS_PROFILE=18 bb pg-regress-wave residuals

Run the current gate with PostgreSQL 18 clients and a pg-datahike server with database provisioning enabled:

PG_REGRESS_PROFILE=18 bb pg-regress-setup
PG_REGRESS_PROFILE=18 bb pg-regress-wave strict

PostgreSQL 18 client cutover

The candidate reports 18.6 and uses the catalog generated from REL_18_6. The stock client checks use that catalog and the published Datahike 0.8.1937 artifact. The pgdump-roundtrip CI job uses native PostgreSQL as the dump source and pg-datahike as the restore target.

Load the following into disposable native and pg-datahike databases to reproduce the client probes.

CREATE TABLE client_parent (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE client_child (
  id int PRIMARY KEY,
  parent_id int REFERENCES client_parent,
  amount int CHECK (amount > 0)
);
CREATE INDEX client_child_amount_idx ON client_child (amount);
CREATE VIEW client_view AS SELECT id FROM client_child;

Point PGHOST, PGPORT, PGUSER, and PGDATABASE at each endpoint. Run both commands with PostgreSQL 18 client binaries.

psql -X -E -v ON_ERROR_STOP=1 -c '\d client_child' -c '\d+ client_child'
pg_dump --schema-only --no-owner --no-privileges > fixture-schema.sql

The first changes cover pg_constraint.conperiod, conenforced, conparentid, the typed extension configuration columns, and the typed namespace ACL column. Relation discovery now has the PostgreSQL field types for the relation estimates and freeze horizons. Datahike reports zero heap pages and invalid freeze IDs because its storage has no PostgreSQL heap. A tuple estimate of -1 denotes an unknown estimate. Basic \d on the 18.6 candidate shows both tables, the view, the primary and secondary indexes, the check, and both directions of the foreign-key relationship. That path exercises a set-returning function and UNION ALL VALUES for foreign-key discovery. Detailed \d+ also succeeds on the ordinary-table fixture after correlated unnest and array-element joins were fixed. The 18.6 candidate reports NOT NULL constraints in pg_constraint (contype = 'n') and links them to the correct physical column positions. The current metadata derives the PostgreSQL names for unnamed constraints. ALTER COLUMN SET NOT NULL validates existing rows before updating enforcement and metadata on ordinary tables. DROP NOT NULL removes that state. The regression checks rollback and atomicity when an ALTER contains several actions. Explicit constraint names and changing nullability on inherited columns remain open.

Adding a column with a literal default fills existing rows. Changing or dropping the default affects later writes, including COPY with an omitted column. The fast_default.sql slice also checks that short rows in a multi-row VALUES insert retain their original boundaries. Backfilling expression defaults on populated tables remains open. Combining ADD COLUMN and ADD CHECK on that column in one ALTER currently returns 42703. Native 18.6 validates the check against the backfilled rows.

The first stock dump query over relations also exposed a Datahike planner problem. It exhausted a 1.2 GiB heap on this fixture. The planner projected the outer relation's join keys, kept repeated bindings, and joined those copies back to the same outer rows. The local probe completed in under a second with the change now released in Datahike 0.8.1937.

Routine discovery now has typed routine ACLs and language OIDs. The type scan can read the first byte of a PostgreSQL name. The operator scan can cast an implementation routine's regproc to its numeric OID. The built-in operator access methods, conversion and text search catalogs provide the identities used by the remaining discovery scans.

The native SQL was captured with PGOPTIONS='-c log_statement=all' pg_dump --schema-only. The first replay produced errors in 32 of 48 read queries. Successive batches reduced the count to 23, 17, 14, six, and finally zero. The later replays used the candidate's fixture OIDs in place of the native OIDs. The exact column scan now returns all six fixture columns, including the three derived NOT NULL constraint names.

Stock pg_dump completes against a fresh 18.6 candidate with Datahike 0.8.1937. The generated view and foreign key definitions qualify their references when the search path is empty, and the dump declares vector as an extension. Restoring into an empty native 18.6 database with pgvector 0.8.1 available matched the fixture's columns, constraints, indexes, and view definition. The restored view and primary-key, NOT NULL, foreign-key, and check-constraint behavior passed. The fixture contains no vector columns.

Use test/integration/pgdump/schema-export.sh for the same check. All eleven strict files also match on the candidate after source-position normalization.

The expanded fixture compares ALTER-generated defaults as part of the schema restore. It exports table data through COPY and restores it into native 18.6, then compares the actual values. Data-bearing pg_dump still needs pg_get_sequence_data and correct sequence state.

Running the comparison

bb pg-regress-wave inventory       # schedule classification and admissions
bb pg-regress-gate                 # compare all results with the baseline
bb pg-regress-measure              # run all files and rewrite agreement.edn
AGREEMENT_FILES="geometry horology" bb pg-regress-probe

Full runs take roughly an hour on the development machine and run outside per-PR CI. The gate flags a file that loses more than its recorded tolerance, loses full expected-line coverage, or stops being measurable. A flagged drop needs investigation; a higher score alone does not establish correctness.

For a direct upstream run with prerequisites in one disposable database:

bb pg-regress-with-fixtures date time timetz timestamp timestamptz interval horology

Keep the per-file manifest as the source of scores instead of maintaining a second table that can drift. Regression artifacts under .internal/pg-regress/ contain the actual output and diffs needed to investigate a discrepancy.

Can you improve this documentation?Edit on GitHub

cljdoc builds & hosts documentation for Clojure/Script libraries

Keyboard shortcuts
Ctrl+kJump to recent docs
←Move to previous article
→Move to next article
Ctrl+/Jump to the search field
× close