autogenerate incorrectly identifies truncated index names as new (postgres)

[mcclurem]

I have a postgres database using naming conventions for automated naming of constraints following the naming scheme:

        meta = MetaData(naming_convention={
            "ix": "ix_%(column_0_N_label)s",
            "uq": "uq_%(table_name)s_%(column_0_N_name)s",
            "ck": "ck_%(table_name)s_%(constraint_name)s",
            "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
            "pk": "pk_%(table_name)s"
        })

class VRM(Base):
    __tablename__   = "vrm"
    __table_args__  = (UniqueConstraint('positive_refdes', 'positive_net', 'negative_refdes', 'negative_net', 'testcase_id'), UniqueConstraint("nickname", "testcase_id", name="_unique_vrm_name_constraint"))
    id              = Column(Integer, primary_key=True)
    nickname        = Column(String(256))
    testcase_id     = Column(ForeignKey("test_case.id"), nullable=True)
    positive_refdes = Column(String(64), nullable=False)
    negative_refdes = Column(String(64), nullable=False)
    positive_net    = Column(String(256), nullable=False)
    negative_net    = Column(String(256), nullable=False)

If I generate my database with a create_all I get my constraint named uq_vrm_positive_refdes_positive_net_negative_refdes_neg_fe9c as expected.
However when I subsequently run:
alembic revision --autogenerate -m "some message"
I get the following output:

INFO  [alembic.autogenerate.compare] Detected added unique constraint 'uq_vrm_positive_refdes_positive_net_negative_refdes_negative_net_testcase_id' on '['positive_refdes', 'positive_net', 'negative_refdes', 'negative_net', 'testcase_id']'
INFO  [alembic.autogenerate.compare] Detected removed unique constraint 'uq_vrm_positive_refdes_positive_net_negative_refdes_neg_fe9c' on 'vrm'

The generated upgrade contains the lines:

op.create_unique_constraint(op.f('uq_vrm_positive_refdes_positive_net_negative_refdes_negative_net_testcase_id'), 'vrm', ['positive_refdes', 'positive_net', 'negative_refdes', 'negative_net', 'testcase_id'])
op.drop_constraint('uq_vrm_positive_refdes_positive_net_negative_refdes_neg_fe9c', 'vrm', type_='unique')

the migration then fails because the op.f results in the correct naming scheme of uq_vrm_positive_refdes_positive_net_negative_refdes_neg_fe9c and postgres errors out because the constraint already exists

describing the table in postgres I see:

"_unique_vrm_name_constraint" UNIQUE CONSTRAINT, btree (nickname, testcase_id)
"uq_vrm_positive_refdes_positive_net_negative_refdes_neg_fe9c" UNIQUE CONSTRAINT, btree (positive_refdes, positive_net, negative_refdes, negative_net, testcase_id)

sqlalchemy version: 1.3.13
alembic version: 1.3.3
postgres server: 12.1
psycopg2 version: 2.8.4

[zzzeek]

hi there -

[EDIT: I am wrong here, please skip down to the later answers]

this is a known issue and is in #542. there's no good automated fix for it right now, so for these long names you'll have to adjust the op.f() names manually to not include the additional characters beyond the character limit.

[zzzeek]

let me just make sure this isn't slightly different from what's in #542

[zzzeek]

because in this case alembic is not comparing to sqlalchemy's own truncation which is strange

[zzzeek]

OK this is not failing for indexes because we fixed this in #421 so what is that about not including uniques

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Adjust unique constraint names based on dialect truncation rules https://gerrit.sqlalchemy.org/1696

[zzzeek]

if you want to try the patch out at https://gerrit.sqlalchemy.org/changes/1696/revisions/0086c17e540adecf4719cd18f55d805e9144a2cf/patch?zip that would help to confirm this is the complete approach.

[johnraz]

@zzzeek : I'm still having the very same issue on 1.4.0

Not using naming conventions at all but just passing a too long name to UniqueConstraint.

Not sure what other information can help, let me know.

Edit:

Yes my SQLAlchemy version would probably help:
SQLAlchemy==1.2.18

And I'm guessing the problem will originate from there looking at #542

[zzzeek]

@zzzeek : I'm still having the very same issue on 1.4.0

Not using naming conventions at all but just passing a too long name to UniqueConstraint.

this issue has to do with SQLAlchemy's own truncation rules. If the database is doing the truncation, that's #542 and you'll need to use a matching constraint name on your end.

[ilansh]

A bit late to the party, is there a non-hacky solution for this?

[zzzeek]

dont use too-long names would be your best bet, if the too-long names are in production DB already, hardcode them in your migraitons until you can change them