Skip to content

Fix PostgreSQL version inconsistency causing structure.sql CI failures

Problem

The structure-sql CI job is failing due to PostgreSQL version inconsistencies between CI environments and the stored structure.sql file in the repository.

Root Cause

As identified in MR !2450, this appears to be caused by:

  1. PostgreSQL 16.10 introduced changes that add RESTRICT and unrestrict lines to pg_dump output (upstream commit)
  2. Our CI doesn't enforce a specific minor PostgreSQL version, so it uses the latest v16 (currently 16.10)
  3. The stored structure.sql in our repository was generated with PostgreSQL 16.9, which doesn't include these lines
  4. This creates a diff that causes the CI job to fail

Example Failure

diff --git a/registry/datastore/migrations/structure.sql b/registry/datastore/migrations/structure.sql
index c6c81ce..038f8f0 100644
--- a/registry/datastore/migrations/structure.sql
+++ b/registry/datastore/migrations/structure.sql
@@ -1,3 +1,4 @@
+RESTRICT TEE3acXKaNihr9sc7Aq4cJDzGOmx7Kilmd0yXArTavciHdXMUQP2G4DacidLi04
 SELECT
     pg_catalog.set_config('search_path', '', FALSE);
 
@@ -16937,3 +16938,4 @@ ALTER TABLE public.repository_blobs
 ALTER TABLE public.tags
     ADD CONSTRAINT fk_tags_repository_id_and_manifest_id_manifests FOREIGN KEY (top_level_namespace_id, repository_id, manifest_id) REFERENCES public.manifests (top_level_namespace_id, repository_id, id) ON DELETE CASCADE;
 
+unrestrict TEE3acXKaNihr9sc7Aq4cJDzGOmx7Kilmd0yXArTavciHdXMUQP2G4DacidLi04

Solution Options

  1. Pin PostgreSQL minor version in CI - Enforce a specific PostgreSQL version (e.g., 16.9) in CI configuration
  2. Update structure.sql - Regenerate structure.sql with PostgreSQL 16.10 to include the new format
  3. Filter pg_dump output - Modify the structure generation process to filter out version-specific lines

Impact

This affects all new branches and MRs, causing CI failures in the structure-sql job and blocking development workflow.

References