pg_validate_extupgrade compares an extension's two installation histories
A new beta tool compares a direct extension installation with an upgrade to the same version. Its detailed differences expose drift that a successful update command can leave behind.
An extension can install successfully at version Y and upgrade successfully to version Y while leaving different database objects behind. The install script describes the desired objects directly. The upgrade script has to transform an earlier collection into that same result. A missed constraint or changed function can make both commands succeed without making the resulting installations equivalent.
Julien Rouhaud's July 22 announcement introduces pg_validate_extupgrade 1.0.0 beta to compare those paths. It installs version X and upgrades it to Y, then compares the result with a direct installation of Y. The tool reports differences between the objects produced by the two histories.
I value PostgreSQL's extension interface because it can support substantial additions to the database. That capability also gives extension maintainers a substantial set of objects to keep consistent. Comparing the result of an upgrade with a fresh installation addresses a maintenance problem that a successful SQL command cannot settle.
The target version supplies the comparison
The tool requires an extension name, a starting version and a target version. Connection options cover host, port, database and user. The README available on the announcement date says connection defaults follow PostgreSQL's official clients.
Its supplied example uses an intentionally broken extension to show the report. The invocation below comes from that example. The version names belong to the test fixture, so they should not be substituted into an unrelated extension.
pg_validate_extupgrade -e pg_broken_extupgrade --from head-1.0 --to head-1.1
The comparison has a useful reference point. The target version's install script establishes one object definition, while the upgrade route establishes another. A difference identifies a disagreement between those routes. It does not require a maintainer to enumerate every possible mistake in advance.
That reference point also limits the conclusion. Agreement between both routes cannot establish that either implements the intended feature correctly. Both scripts can contain the same error. This tool checks convergence of their object definitions, which is a different question from checking the extension's functional behavior.
Equal counts can conceal different objects
The example report starts with relations that have equal counts in both installations but different membership. Some tables exist only on one side. Others appear on both sides with different attributes. Counting relations would therefore miss discrepancies that the detailed object comparison exposes.
A relation named public.logged differs in persistence, access privileges and storage options. Its comment differs too. The report distinguishes an absent value from a present value, so the reader can see whether one route omitted a property or assigned a different one.
Another relation has a different nullability setting and lacks a primary-key index and constraint on one side. That combination can materially change which data a database accepts. The important comparison is the resulting definition, even when the upgrade script completed without an error.
The fixture also shows different foreign-key actions. One definition uses cascading actions, while the other uses a restrictive deletion action. These are differences in future database behavior, not merely differences in the text of an installation script. A maintainer can use the reported constraint definition to identify the missing transformation.
Index and policy definitions need more than names
The README demonstrates an index present on both sides with different definitions. One has a predicate and the other does not. The shared name does not establish that both indexes cover the same rows.
A row-security policy likewise differs in its command, permissiveness, roles and qualification. Comparing the policy name alone would conceal those disagreements. The report gives the properties separately, allowing the maintainer to distinguish an incomplete upgrade from a deliberately changed definition that has not reached both scripts.
Function differences include argument lists, implementation language and result types. The sample also prints a source diff when function bodies differ. Even a source comment can appear in that comparison. The report therefore needs interpretation, since not every textual difference carries the same operational significance.
I find the property-level output useful because it explains what needs investigation. A missing foreign key and a changed comment deserve different responses. Presenting both as identifiable differences allows maintainers to decide whether to repair the scripts or explain an intentional distinction.
The check belongs beside behavioral tests
The announcement describes a Rust implementation under GPL version 3 and compatibility with PostgreSQL 9.2 through 14. That is a statement about the tool's supported server range. The actual extension still needs installation and upgrade scripts suitable for the server on which the comparison runs.
For an extension maintainer, this creates a distinct test case for each chosen starting and target version. A direct installation can exercise the latest script while overlooking an older upgrade route. Naming the starting version explicitly makes that route part of the test's input.
The sample output supplies concrete examples of the drift this approach detects. Existing behavioral tests can still establish whether a function returns the intended values or a table accepts the intended operations. The new comparison adds evidence that a user arriving through an upgrade receives the same object definitions as a user installing the target version directly.