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

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