Unexpected migration schema numbering

[orpheusx]

I've been using pgroll for the last few months with great success. Today, however, I got some odd behavior.

My migration files are named using the convention NNNN_myschema_mytablename_operation.yaml where the leading NNNN is an incrementing number expressed in a fixed length string. Within the file I assign the version_schema property to match the numeric prefix. So, for example, if the version_schema was "0003" the file would be named something like 0003_myschema_mytablename_operation.yaml.

The most recent version schema in my database is myschema_9 according to the response from pgroll latest schema --schema myschema . Pgroll created it for the file I named 0009_myschema_mytablename_operation_blah.yaml.

Here's where it got strange. When I ran pgroll start for my next migration, 0010_myschema_mytablename_add_column_foo.yaml, it calculated that the next schema should be myschema_8 instead of myschema_10.

Apart from the schema name numbering, the migration worked as expected and since I had another migration to run I thought I would continue and see where pgroll went from there. The next migration file was named 0011_myschema_mytablename_drop_column_baz.yaml but I mistakenly set the version_schema to "0010" (the same as the previous migration.) When I ran this file the output included the following:

2026-06-17 15:43:33 INFO  created versioned schema for migration                                                                                                
                      ├ migration: 8
                      └ schema_name: myschema_8

When I completed the second migration, the myschema_8 schema disappeared altogether. The original schema, with all the changes correctly applied, is all that remained.

It seems like pgroll is working correctly but the naming behavior is a problem when my hand coded schema management tool goes to update my database client's jdbcUrl.

[orpheusx]

@dosubot any thoughts?

[dosubot]

Hey @orpheusx — I dug into the pgroll source to understand what's happening here.

The key thing to know is that the log message migration: 8 directly reflects the version_schema value from your migration file [1]. pgroll does not auto-increment or calculate this number — it uses whatever string you provide in the version_schema field of the YAML file, verbatim [2]. The versioned schema name is simply {schema}_{version_schema}.

So when you see:

migration: 8
schema_name: brbl_logic_8

That means pgroll read version_schema: "8" (or equivalent) from your migration file.

My best guess at what happened: You mentioned the file was named 0010_... but you "mistakenly set the version_schema to '0010' (the same as the previous migration)." Could there have been a similar mistake on the 0010 file where version_schema was accidentally set to "8" instead of "0010"? Perhaps a copy-paste issue from an earlier migration?

A couple of things to verify:

1. Check your 0010 and 0011 migration files — look at the exact version_schema value in each. The log output directly mirrors what's in the file.

2. Check the database state — run something like:

   SELECT name, migration->>'version_schema' as version_schema, parent, done 
   FROM pgroll.migrations 
   WHERE schema = 'myschema' 
   ORDER BY name;

   This will show you what pgroll actually stored for each migration's version_schema.

Also worth noting: pgroll does NOT parse your filename to extract numbers. If version_schema were missing entirely from the file, pgroll would fall back to using the full migration name (i.e., 0010_myschema_mytablename_add_column_foo), not just the numeric prefix [3]. So you'd see a schema like myschema_0010_myschema_mytablename_add_column_foo — which would be noticeable.

If you can share the contents of those migration files (particularly the version_schema field), I can help pinpoint exactly what went wrong.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[orpheusx]

@dosu
The 0010_myschema_mytablename_add_column_foo.yaml file contains two add_column operations and starts with the following:

version_schema: 0010
operations:
  - add_column:
      table: routes
      up: "'019eb72e-2364-7d6c-a841-a3e23ef57520'::uuid"
      column:
        name: opt_in_script_id
        type: uuid
        comment: The script used when new users arrive on this route's channel
        nullable: false
        unique: false
        pk: false
        references:
          name: fk_routes_opt_in_script_id_scripts
          table: scripts
          column: id
          on_delete: RESTRICT

So I don't see how pgroll could have used version_schema to number the migration schema.

I ran the query you suggested and here's the output I get:

name	                                            version_schema	parent	                                            done
------------------------------------------------    --------------  ------                                              ----
0008_myschema_routes_add_interrupt_id	            8	            0007_myschema_keywords_drop_node_fk	                true
0009_myschema_edges_add_ndx	                        9	            0008_myschema_routes_add_interrupt_id	            true
0009_myschema_edges_add_ndx_20260616093930769574	sql_d3d0edc3	0009_myschema_edges_add_ndx	                        true
0009_myschema_edges_add_ndx_20260616093939593843	sql_ffc866a1    0009_myschema_edges_add_ndx_20260616093930769574    true
0010_myschema_add_opt_in_out_id	                    8	            0009_myschema_edges_add_ndx_20260616093939593843	true
0011_myschema_nodes_drop_num_type	                8	            0010_myschema_add_opt_in_out_id	                    true

When pgroll parses the version_schema to strip out zeroes does it remove them from the beginning and end of the string? Seems like it would be simpler/safer to just use whatever string is present without modification.

The other question: is why did it drop the migration schema (myschema_8) after completing the last migration. This creates a real problem for production use.

[dosubot]

@orpheusx — Great catch, and I can now explain exactly what happened. There are two issues here:

1. Why version_schema became "8" — YAML octal parsing

pgroll uses sigs.k8s.io/yaml for parsing [1], which follows the YAML 1.1 spec. In YAML 1.1, an unquoted value with a leading zero is interpreted as an octal number:

• 0010 (unquoted) → octal → decimal 8 → converted to string "8"
• "0010" (quoted) → string literal → "0010"

Even though the Go struct defines VersionSchema as a string type [2], the YAML parser interprets the value before assigning it to the field. So your version_schema: 0010 became "8", and version_schema: 0011 also became "9" (octal 011 = decimal 9) — but in your case you said you set 0011's version_schema to "0010" by mistake, so that also became "8".

The fix: quote your version_schema values in YAML:

version_schema: "0010"

2. Why myschema_8 was dropped — a real bug in pgroll

This is a legitimate problem. When pgroll complete runs, it calls PreviousVersion() to find and drop the previous version's schema [3]. The underlying SQL function uses a depth-based OFFSET to walk back through migration history [4].

In your case, migrations 0010 and 0011 both had version_schema = "8", meaning both pointed to the same physical schema myschema_8. When 0011 was completed:

1. find_version_schema(schema, 1) walked back through the chain
2. Found migration 0010 as the "previous" migration — with version_schema "8"
3. Dropped myschema_8... which was also the current active schema

There's no check in pgroll that prevents dropping the current schema when the previous migration happens to have the same version_schema value. The protection is depth-based (skip N migrations), not value-based (don't drop if names match). This is a bug — pgroll should compare the previous version_schema with the current one before issuing the DROP SCHEMA.

Summary:

• The "8" issue is caused by YAML octal parsing of unquoted 0010. Quote your values to fix.
• The schema being dropped is a genuine edge case bug — pgroll should guard against dropping the current active schema when consecutive migrations share the same version_schema. This could warrant a separate bug report or fix.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[orpheusx]

Thanks, this was very helpful.

I would argue both problems are bugs. After a bit of research, it seems that the YAML 1.1 spec is notorious for coercing values in undesirable ways. Switching to a parser that adheres to the more restricted 1.2 spec (where values must be prefixed with "0o" to be interpreted) may provide a low(er) effort solution.