Handle or track Postgres renaming of long index names when creating migrations

[sqlalchemy-bot]

Migrated issue, originally created by Kuan Butts (@kuanb)

Problem:
Alembic is generating long index names, Postgres is automatically shortening them when they are created, resulting in a situation where Alembic "loses track" of that index. Subsequent revisions will generate a new migration that involves dropping the index Postgres created and attempting to re-insert the original index name (which then gets shortened again, starting the loop over once again).

Here's an example name it's generating:
ix_analysis_module_public_results_new_user_summary_module_run_output

...and here is what it would be altered to by Postgres (due to max character length constraint):
ix_analysis_module_public_results_new_user_summary_mod_4597

It would be useful to either have Alembic check that such a change occurred and track it itself or to allow the user to provide a lookup table that equates a shortened name to a longer name, thus enabling it to connect the two when making new migrations.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

I need specifics on where this "generation" is occurring. is it plainly present in your migration file beforehand? or are you doing create_index() without using any name? is there a naming convention in use? also note https://bitbucket.org/zzzeek/sqlalchemy/issues/3537/apply-truncation-rules-to-all-naming though this issue notes that index names are truncated, so not clear where this is occurring.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: blocker

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

unless that truncated name you are seeing is sqlalchemy's truncation of the name. it looks like it.

[sqlalchemy-bot]

Kuan Butts (@kuanb) wrote:

Ah, it might be SQLAlchemy's naming convention, as outlined in Alembic's docs here: http://alembic.zzzcomputing.com/en/latest/naming.html#integration-of-naming-conventions-into-operations-autogenerate

Also, it looks like that issue you linked to is relevant / regarding the same issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

indexes have that truncation rule on them, other constraints do not. this is kind of a historical thing. it looks like good news, that those four digits are taken from the md5 of the full name, so they'd be deterministic. that is, it's safe to put this name in your model for now as the "name" of this index. the comparison rules would need to take this truncation into account.

[sqlalchemy-bot]

Kuan Butts (@kuanb) wrote:

Sounds good, thanks for the responses.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Apply DDLCompiler name rules to Index for autogenerate

The autogenerate compare scheme now takes into account the name truncation
rules applied by SQLAlchemy's DDL compiler to the names of the
:class:.Index object, when these names are dynamically truncated
due to a too-long identifier name. As the identifier truncation is
deterministic, applying the same rule to the metadata name allows
correct comparison to the database-derived name.

Change-Id: I270fbde4430a41f4bcc7857f1932347d86f07675
Fixes: #421

→ 4cdb25b

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Alex Rothberg wrote:

I think this is still an issue for very long unique constraints (e.g. if I have a column with unique=True, but not index=True).

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

@alex_rothberg at the moment there is no truncation for the naming conventions of other types of constraints, that is the https://bitbucket.org/zzzeek/sqlalchemy/issues/3989/warn-error-for-too-long-naming-convention issue. even though the original poster describes this problem in terms of "postgresql's truncation", that's not what we're dealing with here, we're dealing with SQLAlchemy's truncation of long names. Names that go straight to postgresql that the database itself truncates are unpredictable on the SQLAlchemy side.

[sqlalchemy-bot]

Alex Rothberg wrote:

I think I was a bit confused by your comment (https://bitbucket.org/zzzeek/sqlalchemy/issues/3989/warn-error-for-too-long-naming-convention#comment-46660590) as well as the corresponding code (https://bitbucket.org/zzzeek/alembic/src/3a4d4f4ab9af6e9726fe1b968b05a81351ba01e8/alembic/autogenerate/compare.py#lines-329). Perhaps this should be documented somewhere? I noticed the issue when alembic kept trying to drop / re-add a given unique constraint.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

if you're observing an issue then you need to post an issue here with specifics and we'll see what's going on. but if it's a unique constraint where the name is being truncated by PG, then you need a shorter naming convention for now until issue 3989 on SQLAlchemy is fixed. The subject is not simple to explain though there is lots of information about the subject of constraint names both at https://docs.sqlalchemy.org/en/latest/core/constraints.html#constraint-naming-conventions and https://alembic.zzzcomputing.com/en/latest/naming.html and the implementation as far as too-long names is not complete in any case.

[sqlalchemy-bot]

Alex Rothberg wrote:

The table name is title_department_year_asdf_xx_association, the column name is title_department_year_id, the format I am using is "uq": "uq_%(table_name)s_%(column_0_name)s", which led to a unique constrain that was too long. I solved this by setting index=True such that a unique index was created instead of a unique constraint. sqla / alembic then seemed to correctly handle the truncation.

[zzzeek]

hello me of the past -

Errrrr, this totally happens for uniques and #3989 has nothing to do with this, when you compare the name rendered in the DB to what's in the model, it has to be what SQLAlchemy actually rendered. Sorry @alex_rothberg

[zzzeek]

see #647