SQLAlchemy 1.3 long name truncation

[RazerM]

I have an existing database A. A has had an alembic migration applied in the past with op.f('some_long_constraint_name'), which PostgreSQL truncated to some_long_constraint_.

I have a new database B. Applying the schema using alembic means it has a constraint named some_long_constr_f5b8.

I can't write a future migration that drops this constraint that will work in both cases.

I also can't write an alembic migration to fix the old truncated name because that migration would not work on B.

Is the intended solution that I manually rename all my previously-truncated names to use the new 4-character hash? It might be worth a mention in the SQLAlchemy changelog, but maybe it's just me who didn't immediately see the connotations of the change.

[zzzeek]

are you using f() to avoid a naming convention ?

[RazerM]

I'm not sure what you mean. I'm using a naming convention, and op.f() is present in my migrations around names that have already been converted (usually from --autogenerate).

[zzzeek]

anyway it seems likely something needs to be added on the SQLAlchemy side to handle this case. currently the options are:

1. use a plain string or sqlalchemy.sql.elements.quoted_name(). naming convention will grab it and produce a conv(), where then rule #2 applies

2. use a conv() (e.g. op.f()). compiler picks it up and applies dialect-specific truncation rules.

3. use a plain string and there's no naming convention in place. in 1.2 this goes right through for all constraints except indexes, where it would raise IdentifierError for the too many characters. in 1.3, we apply the same logic to all constraints, not just indexes.

So basically the behavior you are seeing is how it has always been for indexes, just not other constraints. So what are you doing with your indexes ?

If you change your constraint name to op.f("some_long_constraint_"), everything works and no migration is needed? or are you targeting different backends with different identifier lengths ?

[RazerM]

So what are you doing with your indexes ?

I didn't have any that were truncated.

If you change your constraint name to op.f("some_long_constraint_"), everything works and no migration is needed?

That'll work for me. Thanks for your help.

[zzzeek]

I've added some extra info on the new behavior but it's true, there's not really a way to get a long name straight through to the database anymore: https://docs.sqlalchemy.org/en/latest/changelog/migration_13.html#new-multi-column-naming-convention-tokens-long-name-truncation

SQLAlchemy's style is to always make something be possible so this is kind of a SQLAlchemy problem. we should open over there.

[taion]

FWIW, I don't think the issue here necessarily is that one can't force a long name through to the database for truncation.

The issue is more that, for previously-generated migrations, the behavior now is different. The old migrations have the long constraint names, which previous would have gotten truncated, but now when running the same migrations, the result on the database side is different.

This means that re-running a set of migrations from scratch may not work, if those migrations were generated against SQLAlchemy 1.2. What can happen is that migration A can create a FK and just use the implicit truncation; migration B might try to drop that FK, and it will use the truncated name. When re-running those migrations in order against 1.3, the FK will get created with a different name, and migration B will fail.

We ended up grepping through our codebase and replacing all long constraint names. Fairly easy for FKs; less so for CKs.

In retrospect, we probably should have just truncated migration history.

[zzzeek]

The issue is more that, for previously-generated migrations, the behavior now is different. The old migrations have the long constraint names, which previous would have gotten truncated, but now when running the same migrations, the result on the database side is different.

choices are:

• SQLAlchemy should have a create_engine flag that disables all long name truncation.

• op.f() is deprecated in Alembic and is changed to emit a string directive that will exclude itself from long name truncation in SQLAlchemy so that old migrations work without change. this might need a new feature in SQLAlchemy not sure.

• a new directive with some new name, op.legacy_f(), or op.notrunc(), or some better name than those (though what is "f()" anyway), that does the same as above, and people who are having this issue and want their old migrations to work identically can replace the use of op.f() with this new directive. Because we would have to assume that other users want the newer behavior for their old migrations

I have a feeling the third way might be most appropriate, since I think this is not a widespread issue and in your case you went through and changed your old migrations in any case.

This means that re-running a set of migrations from scratch may not work, if those migrations were generated against SQLAlchemy 1.2. What can happen is that migration A can create a FK and just use the implicit truncation; migration B might try to drop that FK, and it will use the truncated name. When re-running those migrations in order against 1.3, the FK will get created with a different name, and migration B will fail.

when you say "re-running", you're referring to running all the downgrade migrations, right? In my experience, downgrade migrations are useful in a production environment in a very limited scope, that is, you wouldn't go on production and run downgrades through all your older migrations with a different version of the software. There are many reasons that won't work in general. The usefulness of downgrade in production is, to reverse a localized set of upgrades that were just run and had some kind of issue. You would have ensured these upgrades were downgradable in the immediate case on a staging environment. The other case where you might use downgrades is in development, but development envs more likely re-run from scratch most times.

I'm not trying to argue alembic shouldn't support downgrade running all the way to the base, but that with this behavioral change, we can provide a helper to mitigate it but I don't know that we can reverse that a backwards incompatible change happened.

We ended up grepping through our codebase and replacing all long constraint names. Fairly easy for FKs; less so for CKs.

In retrospect, we probably should have just truncated migration history.

[taion]

Sorry, I didn't mean to suggest that either SQLAlchemy or Alembic needed to fix anything. I'd advocate for "do nothing" here, because it's been long enough since 1.3 has landed that this is likely not a live issue for many people. I came across the issue by coincidence and thought I'd leave a note with what I did, in case it proves useful to anyone else.

The way this actually came up for us is that, in CI, we run the stack of upgrade migrations before running our test suite, to ensure that the migrations do what we expect them to do, and that tests pass against a database constructed with the migrations (rather than just one created with create_all()). So this was the "re-run from scratch" case.

[zzzeek]

we have our first dupe request for this in #647.

[zzzeek]

might not be exactly a dupe.