Repository navigation
PG enum values ordering not stable #429
Description
Hello!
So one thing we noticed is that the generated code for enum does not seem to guarantee a stable ordering of values, at least for Postgres. The order is consistent most of the time, but not all the time.
It seems to be because the query done in models/enumvalue.dbtpl.go does not include ordering, thus may not necessarily return the rows in the same order each time, and client-side there is no sorting done either.
Fix should be easy (either add an ORDER BY clause, or sort client-side), but it's probably for you to decide where and how you would want to do that :)
Reacted by Adrian Smith
Activity
kenshaw commentedon Apr 22, 2026
@itizir Could you give me a specific example of a schema that does not have stable enums being emitted? Or is this in a custom template? I had tried to ensure the code generation from the templates was always consistent, but it's entirely plausible I missed something.
itizir commentedon Apr 23, 2026
So one case where we've observed the flakiness is an enum that gets initially created with two values, then in a subsequent migration one value is renamed while an new third one is added. I suspect that because of these edits, the database may then sometimes end up in a different state where the internal unsorted ordering is not the same.
And then we see that because we run a CI job that checks the generated code we commit does match the database schema by running all the migrations and regenerating the code...
Hope this clarifies things a little!
kenshaw commentedon Apr 23, 2026
@itizir I really need to see some kind of SQL schema that can reproduce this issue.
itizir commentedon Apr 27, 2026
I tried making a minimal test/example that would reproduce the issue somewhat consistently, but I'm having trouble: in full context there are many more DB operations happening, which may be necessary to increase the likelihood of it happening.
The specific schema that we saw the flake on goes something like
and then in a subsequent migration
And then because SQL does not guarantee a specific ordering of rows when no ordering is set, the query in
models/enumvalue.dbtpl.gomay sometimes return the values in a different order, depending what effect the alterations in the migration had on the enum table.kenshaw commentedon Apr 27, 2026
We can't really guarantee the schema that is returned. I know we have really tried and checked to make sure the code that Go emits is consistent. If your schema is changing, it's changing.
dbtpl(formerlyxo) is meant to be used only when your schema changes, to regenerate the code. It's not meant to be used in something like a CI/CD pipeline. The reason for this is, e.g., because we can't guarantee that the schema returned by the database is always consistent. As such, unless you're changing your schema daily, this really shouldn't be a problem, because any change to the*.dbtpl.*files should be committed when changes to the schema are being committed to source control.I'm not trying to suggest you're using the tool wrong, but I'm having a bit of trouble wondering why this would be a problem? If
dbtplis correctly picking up the generated enum values, does it matter much if there's chatter in your SCM for the generateddbtplmodels?itizir commentedon Apr 27, 2026
Well I'm only saying that we observed that Postgres does not guarantee that the rows for enum values in
pg_typeare always in the same order, even for the same schema. And that it is not much of a change to add something likeORDER BY e.enumsortorderto that statement.But then indeed this is only a problem if you need to always get the same generated output every time you run migrations and generate the code, which is not something typically needed outside a CI/CD context. Our workflow is to do that in every pipeline to make sure the committed go code is consistent with the committed SQL migrations. If you say this is not within the scope of how the generator should be used, that's fine too. And we are using a fork anyway: was just trying to share that observation in case it would help someone else.
kenshaw commentedon Apr 27, 2026
If you'd like to submit a PR to fix the ordering issue, by e.g. using
e.enumsortorder, more than willing to consider it!itizir commentedon Apr 27, 2026
Sounds good! Just wasn't sure if you had a preference in terms of whether to add the sort to the SQL, do it client-side in go, and what ordering to use (enumsortorder, name, etc)... Can make a PR with the option suggested above.
adrianmsmith commentedon Apr 30, 2026
I just want to chime in and say I agree with @itizir's proposal.
I previously had the experience that generated code was checked in to the repo, then the person who generated the code went on vacation, and I made a small change and re-generated the files and they were completely different. Evidently the person who had been generating the code used some options they'd forgotten to document. This generation was not using XO, but it still taught me an important lesson that
If you don't do either of these you run the risk of a developer checking in code which other team members can't reproduce.
So I think for teams using the second scenario (where CI verifies the generated code) it's good to have the generation process to generate consistent code given the same input parameters (the DB schema).
As the SQL standard says that a SELECT without any ORDER BY can return rows in any order, I think adding an ORDER BY to the SELECT to fetch enum values is a good solution. Although PostgreSQL returning the enum values in a weird order only seldom happens (I work with @itizir: it's only one enum and maybe happens less than 5% of the time and we don't really know why), it does seem to be happening sometimes, and it is certainly allowed to happen according to the SQL spec.