pgrx icon indicating copy to clipboard operation
pgrx copied to clipboard

pgrx: add automatic upgrade script generation via schema snapshots

Open charmitro opened this issue 2 months ago • 0 comments

PostgreSQL extensions require upgrade scripts (extname--oldver--newver.sql) to migrate between versions. This patch implements automatic generation by comparing schema snapshots across versions.

Adds three components to pgrx-sql-entity-graph:

  1. Schema Snapshot Infrastructure (snapshot.rs, snapshot_types.rs):
    • Separate snapshot types omit TypeId fields (not stable across compilations)
    • JSON serialization with Box::leak for &'static str lifetime handling
    • Stores entity identifiers, signature hashes, dependency graphs
  2. Schema Diff Engine (diff.rs):
    • Detects additions, removals, modifications, renames
    • Signature hash-based fuzzy matching for rename detection
  3. Upgrade Script Generator (upgrade.rs):
    • Generates SQL DDL from detected changes
    • Topological sort for dependency-safe operation order

TypeId separation:

Runtime types (SqlGraphEntity, etc.) keep TypeId for type checking. Snapshot types (SqlGraphEntitySnapshot, etc.) omit TypeId completely. Conversion traits handle mapping, using __PgrxInternalTypeIdPlaceholder to avoid collision with legitimate types.

cargo-pgrx integration:

  • schema.rs saves snapshots alongside SQL files
  • package command auto-generates upgrade scripts from snapshot diffs

charmitro avatar Nov 03 '25 21:11 charmitro