Skip to content

SCENARIOS-pg-diff.md

Latest commit

 

History

History
241 lines (197 loc) · 8.02 KB

SCENARIOS-pg-diff.md

File metadata and controls

241 lines (197 loc) · 8.02 KB

PG Catalog Diff Engine Scenarios

Goal: Implement func Diff(from, to *Catalog) *SchemaDiff that compares two catalog states and outputs structured add/drop/modify lists for all PG object types. Verification: LoadSQL(schemaA), LoadSQL(schemaB), call Diff, assert expected entries Reference sources: omni pg/catalog struct definitions, pgschema internal/diff design, PostgreSQL DDL semantics

Status: [ ] pending, [x] passing, [~] partial (needs upstream change)

Phase 1: Infrastructure + Core Object Types

1.1 Diff Types and Schema Diff

  • SchemaDiff struct and all diff entry types compile with no errors
  • Diff of two identical empty catalogs returns empty SchemaDiff
  • Diff of two identical non-empty catalogs returns empty SchemaDiff
  • Schema added — new schema in to not in from
  • Schema dropped — schema in from not in to
  • Schema modified — schema owner changed
  • Multiple schemas added/dropped/modified in one diff
  • pg_catalog and pg_toast schemas are excluded from diff

1.2 Table Diff (Basic)

  • Table added — CREATE TABLE in to only
  • Table dropped — table in from not in to
  • Table unchanged — identical table in both catalogs produces no entry
  • Multiple tables across different schemas
  • Table persistence changed (permanent ↔ unlogged)
  • Table in non-public schema added/dropped
  • Table with same name in different schemas treated as distinct objects
  • Table renamed detected as drop old + add new (identity is name-based)
  • Table ReplicaIdentity changed

1.3 Column Diff

  • Column added to existing table
  • Column dropped from existing table
  • Column type changed (e.g., int → bigint) detected via FormatType
  • Column nullability changed (NOT NULL added/removed)
  • Column default added
  • Column default removed
  • Column default value changed
  • Column identity added (GENERATED ALWAYS AS IDENTITY)
  • Column identity removed
  • Generated column added (GENERATED ALWAYS AS ... STORED)
  • Generated column expression changed
  • Generated column removed
  • Column collation changed
  • Multiple column changes on same table
  • Column unchanged — identical column produces no entry

1.4 Constraint Diff

  • PRIMARY KEY added
  • PRIMARY KEY dropped
  • UNIQUE constraint added
  • UNIQUE constraint dropped
  • CHECK constraint added
  • CHECK constraint dropped
  • CHECK constraint expression changed
  • Foreign key added
  • Foreign key dropped
  • Foreign key actions changed (ON DELETE CASCADE → SET NULL)
  • Foreign key match type changed
  • Constraint deferrable/deferred changed
  • EXCLUDE constraint added/dropped
  • Constraint identity by name — same name = same constraint

1.5 Index Diff

  • Standalone index added (CREATE INDEX)
  • Standalone index dropped
  • Index columns changed
  • Index access method changed (btree → hash)
  • Index uniqueness changed
  • Partial index WHERE clause changed
  • Index INCLUDE columns changed (NKeyColumns)
  • Index sort options changed (DESC, NULLS FIRST)
  • NULLS NOT DISTINCT changed
  • Expression index — expressions changed
  • PK/UNIQUE backing index skipped (ConstraintOID != 0)

Phase 2: Functions, Sequences, Types, and Relation Sub-Objects

2.1 Sequence Diff

  • Standalone sequence added
  • Standalone sequence dropped
  • Sequence increment changed
  • Sequence min/max values changed
  • Sequence cycle flag changed
  • Sequence start value changed
  • Sequence cache value changed
  • Sequence type changed (int4 → int8)
  • SERIAL/IDENTITY-owned sequence skipped (OwnerRelOID != 0)
  • Sequence unchanged produces no entry

2.2 Function and Procedure Diff

  • Function added
  • Function dropped
  • Function body changed
  • Function volatility changed (VOLATILE → IMMUTABLE)
  • Function strictness changed
  • Function security changed (DEFINER ↔ INVOKER)
  • Function language changed
  • Function return type changed
  • Function parallel safety changed
  • Function leak-proof changed
  • Function RETURNS SETOF changed
  • Procedure added/dropped (Kind='p')
  • Overloaded functions — same name different args are distinct
  • Function identity uses schema + name + input arg types (FormatType)
  • Function unchanged produces no entry

2.3 Enum Type Diff

  • Enum type added
  • Enum type dropped
  • Enum value added (new label appended)
  • Enum value added with BEFORE/AFTER positioning
  • Enum values reordered detected
  • Enum type identity by schema + name
  • Enum unchanged produces no entry

2.4 Domain Type Diff

  • Domain added
  • Domain dropped
  • Domain base type changed
  • Domain NOT NULL changed
  • Domain default changed
  • Domain constraint added
  • Domain constraint dropped
  • Domain constraint expression changed
  • Domain unchanged produces no entry

2.5 Trigger Diff

  • Trigger added on table
  • Trigger dropped from table
  • Trigger timing changed (BEFORE → AFTER)
  • Trigger events changed (INSERT → INSERT OR UPDATE)
  • Trigger level changed (ROW ↔ STATEMENT)
  • Trigger WHEN clause changed
  • Trigger function changed
  • Trigger enabled state changed
  • Trigger UPDATE OF columns changed
  • Trigger transition tables changed
  • Trigger arguments changed
  • Trigger unchanged produces no entry

2.6 View and Materialized View Diff

  • View added
  • View dropped
  • View definition changed (detected via GetViewDefinition deparse)
  • View CHECK OPTION changed (LOCAL ↔ CASCADED ↔ none)
  • Materialized view added
  • Materialized view dropped
  • Materialized view definition changed
  • View/matview treated as relation with appropriate RelKind

2.7 Range Type Diff

  • Range type added
  • Range type dropped
  • Range subtype changed
  • Range type identity by schema + name
  • Range unchanged produces no entry

Phase 3: Metadata Objects, Grants, and Edge Cases

3.1 Extension Diff

  • Extension added
  • Extension dropped
  • Extension schema changed
  • Extension relocatable flag changed
  • Extension unchanged produces no entry

3.2 Policy Diff (RLS)

  • Policy added on table
  • Policy dropped from table
  • Policy command type changed
  • Policy permissive/restrictive changed
  • Policy roles changed
  • Policy USING expression changed
  • Policy WITH CHECK expression changed
  • Table RLS enabled/disabled changed (RowSecurity flag)
  • Table FORCE RLS changed (ForceRowSecurity flag)

3.3 Comment Diff

  • Comment added on table
  • Comment dropped from table
  • Comment changed on table
  • Comment on column added/changed/dropped
  • Comment on index
  • Comment on function
  • Comment on schema
  • Comment on type (enum/domain)
  • Comment on sequence
  • Comment on constraint
  • Comment on trigger

3.4 Grant Diff

  • Grant on table added
  • Grant on table revoked
  • Grant privilege changed (SELECT → ALL)
  • Grant WITH GRANT OPTION changed
  • Column-level grant added/revoked
  • Grant on function added/revoked
  • Grant on schema added/revoked
  • Grant on sequence added/revoked
  • Grant identity: (objType, objName, grantee, privilege) tuple

3.5 Partition and Inheritance Diff

  • Partitioned table added (with PARTITION BY)
  • Partition child added (PARTITION OF)
  • Partition strategy detected in diff (list/range/hash)
  • Partition bound values captured in diff
  • Table inheritance changed (INHERITS list modified)

3.6 Determinism and Edge Cases

  • Diff output is deterministic — same input always produces same order
  • Diff with many objects of all types simultaneously
  • Empty schema (exists but has no objects) vs non-existent schema
  • Object with reserved word name (e.g., table named "order")
  • FK referencing table in different schema resolves correctly
  • Diff is symmetric in structure — Diff(A,B) add = Diff(B,A) drop