The server you upgraded under the package

The upgrade went fine. The DBA moved the database to the new major version over the weekend, the app came up, the smoke tests passed. Monday’s release is the first deploy against it. Nobody changed the package. So what’s the deploy going to do?

Modules 1 through 5 were about a fleet on different versions at once. This one is about time: the same database, the same package, a newer engine than last week. That first deploy is where you find out whether your tooling knows the difference between “you changed this” and “the engine describes it differently now”. Get that wrong and an upgrade looks like drift on every expression you own.

The record that makes the difference

Every engine rewrites an expression when it stores it. You write Amount >= 0, SQL Server stores ([Amount]>=(0)). So SchemaSmith never compares your text with the catalog’s. It keeps a record, the ExpressionMap table, of every check constraint, computed column, default and filtered index it applied: the text you declared, the text the engine stored, and the version (on SQL Server, the compatibility level too) it was stored under.

On the next deploy, it asks what moved:

What moved	What the deploy does
Nothing	Leaves the object alone
Your declaration	Applies it
The live object	Re-applies your declaration: that’s drift
The engine version or compatibility level	Re-baselines: re-reads the stored text, updates the record, touches nothing

That last row is the upgrade.

Four engines, four first deploys

The lab deploys one Invoice table to the old tier, upgrades, and deploys again. SQL Server raises learn_upgrade from compatibility level 100 to 160 in place. PostgreSQL, MySQL and MariaDB dump the database from the old container and reload it into the new one, with the dump tools’ defaults.

SQL Server re-baselines and touches nothing. An expression’s stored text is frozen at the compatibility level it was created under, so all five read as recorded under 100:

Re-baselined 5 recorded expression(s): they were recorded under a different SQL Server version or compatibility level, and their stored canonical text is now refreshed for version 16.0… at compatibility level 160. No object was changed.

Without the record, that’s five objects re-applied on the first deploy after the bump, check constraints re-validated against every row. With it, it’s one log line.

PostgreSQL re-baselines four, and re-applies one. A dump writes every expression out as text, and the new server parses it back in. Most come back exactly as they were. One doesn’t: a status IN ('open','paid','void') check on a varchar column is stored as ANY ((ARRAY[…])::text[]) when it’s created, but a reload of that stored form produces ANY (ARRAY[(…)::text, …]). The live object really did move, so the deploy re-applies your declaration once, then re-baselines the rest:

Check Constraint public.invoice.ck_invoice_status modified or no longer in product
Add missing check constraint public.invoice.ck_invoice_status
Re-baselined 4 recorded expression(s): they were recorded under a different PostgreSQL version, …

Re-adding a check scans the table to validate it. On a big table, plan for that one deploy. pg_upgrade restores the schema the same way, so it applies there too.

MySQL builds what 5.7 was skipping. MySQL 5.7 can’t hold a check constraint, so Module 1’s policy skipped both checks on the old tier and exited 1. The first deploy that reaches 8.0 builds them, re-baselines the generated column, and exits 0:

Create check constraint: `Invoice`.`CK_Invoice_Status`
Create check constraint: `Invoice`.`CK_Invoice_Amount`
Re-baselined 1 recorded expression(s): …

Nothing in the package changed. The skipped checks were always in it, waiting for a server that could hold them. That’s the payoff of Module 1’s “author the modern thing”: the upgrade delivers it, with no release of its own.

MariaDB fails, exit 2.

Kindle stamp current (…) - skipping kindle
Error: PROCEDURE learn_upgrade.SchemaSmith_ParseTableJson does not exist

SchemaSmith installs helper procedures in each database it deploys to, and records a stamp so later deploys can skip reinstalling them. mysqldump copies tables, the stamp included, but not stored procedures unless you pass --routines. MariaDB 10.2 and 11.4 compute the same stamp, so the deploy believes helpers are there that aren’t. (MySQL’s 5.7 and 8.0 stamps differ, which is the only reason MySQL got away with it.) One deploy with --ForceReKindle reinstalls them, re-baselines three expressions, and exits 0. Better: dump with --routines and it never happens.

The deploy after that

Every engine’s next deploy is quiet: nothing re-baselined, nothing applied, exit 0. The record has caught up with the server, and as far as the package is concerned, the upgrade is over.

So read the first deploy after an upgrade as the upgrade’s receipt. Re-baselined lines are the engine describing your objects in its new dialect. A re-applied object is one the upgrade really changed. A created object is one the old server couldn’t hold. And a helper that “does not exist” right after a restore wants one --ForceReKindle.

Check yourself: After a PostgreSQL 12 to 16 upgrade by dump and reload, the first deploy re-baselines four expressions but drops and re-adds one check constraint. Nobody touched the package or the database. Is that drift?

No, but it isn’t a false alarm either. The reload parsed the check’s stored text back in and stored it in a different form (ANY (ARRAY[(…)::text, …]) instead of ANY ((ARRAY[…])::text[])), so the live object genuinely differs from what SchemaSmith recorded. Re-applying your declaration puts it back in the form a fresh create produces, and records it. It happens once, on the first deploy after the upgrade. The cost to plan for is the validation scan when the check is re-added on a large table.

A smith who moves to a new forge doesn’t re-hammer every piece he brought with him. He checks each one against his notes. Most read differently in the new light and are exactly what they were, so he updates the notes. One got bent in the move, so he straightens it. And the pieces the old forge couldn’t run hot enough for, he finally makes. Keep the notes, read the first firing, and the new forge is yours.

That’s the rolling upgrade, start to finish: a fleet on many versions, and each server moving to the next.

Upgrading a server under a package and not sure what the first deploy is telling you? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may every upgrade’s first deploy read like a receipt, not a surprise.

— Forge

Mark this lesson complete