apply truncation rules to all naming-convention names

Issue #3537 new
created an issue
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base

def _column_names(constraint, table):
    return '_'.join((c if isinstance(c, basestring) else for c in constraint.columns)

naming_convention = {
    'fk': 'fk_%(table_name)s_%(column_names)s_%(referred_table_name)s',
    'ix': 'ix_%(table_name)s_%(column_names)s',
    'column_names': _column_names,

Base = declarative_base()
Base.metadata.naming_convention = naming_convention

class A(Base):
    __tablename__ = 'test_very_long_name_abcdefghijklmnopqrstuvwxyz'
    __table_args__ = Index(None, 'abcdefghijklmnopqrstuvwxyz'),

    id = Column(Integer, primary_key=True)
    abcdefghijklmnopqrstuvwxyz = Column(Integer)

class B(Base):
    __tablename__ = 'test_very_long_name_abcdefghijklmnopqrstuvwxyz_b'

    id = Column(Integer, primary_key=True)
    abcdefghijklmnopqrstuvwxyz = Column(Integer, ForeignKey(''))

e = create_engine('postgresql:///test', echo=True)
raw_input('check the names...')
e.execute('DROP TABLE test_very_long_name_abcdefghijklmnopqrstuvwxyz, test_very_long_name_abcdefghijklmnopqrstuvwxyz_b')

This results in the following SQL (removed unrelated parts):

CREATE INDEX ix_test_very_long_name_abcdefghijklmnopqrstuvwxyz_abcde_5f65 ON test_very_long_name_abcdefghijklmnopqrstuvwxyz (abcdefghijklmnopqrstuvwxyz)

CREATE TABLE test_very_long_name_abcdefghijklmnopqrstuvwxyz_b (
        CONSTRAINT fk_test_very_long_name_abcdefghijklmnopqrstuvwxyz_b_abcdefghijklmnopqrstuvwxyz_test_very_long_name_abcdefghijklmnopqrstuvwxyz FOREIGN KEY(abcdefghijklmnopqrstuvwxyz) REFERENCES test_very_long_name_abcdefghijklmnopqrstuvwxyz (id)

SQLAlchemy properly truncates the index name and appends a hash for uniqueness, but the constraint name is left unmodified, resulting in Postgres truncating it to 63 characters (without adding a hash to the end to ensure uniqueness).

I think this behavior is quite inconsistent and may even result in problems depending on the structure since it may result in two truncated names being the same even though they were different before. It's also somewhat ugly in Alembic migration scripts as you'll have to use op.f('...') for the index to ensure it's truncated by SA while it's optional for FK names since they are not touched even if too long.

Comments (8)

  1. Michael Bayer repo owner

    I'm surprised PG truncates it instead of raising an error. that's pretty weird.

    the point of the truncation is when we're creating "throwaway" names that we aren't going to want to know later. This occurs for indexes because SQLA has for a long time had the "index=True" flag on Column which is the one (almost) single place where we break another one of SQLA's cardinal rules, that we never make up names :). to make an index we have to make up a name, so the truncation was added there so that the index can get created in all cases.

    naming convention's purpose is so that we can know exactly the name of any constraint or object we build. if those names are truncating, I guess that doesn't defeat its purpose fully because at least Alembic can re-locate the same name, though I'd not want to use naming conventions that are winding up in truncation. Guess it's unavoidable.

    the way the mechanics work here right now, to add the truncation in would mean that it only takes effect for naming-convention-led names. it still would not take effect for manually-specified names. the truncation here is generally not something that's at the "schema" level at all, it's used for on-the-fly column labels. its use in index names is idiosyncratic.

  2. Adrian reporter

    Guess it's unavoidable

    Yeah... for FKs or multi-column indexes it's sometimes easy to end up with very long index/constraint names...

  3. Michael Bayer repo owner

    the truncation logic in compiler -> _prepared_index_name would need to be pulled out and generalized to all name prep for constraint names.

    the 1.1 milestone is already overfull and needs to be shrinked a lot more. this needs tests in test_metadata->NamingConventionTest and an implementation in provided in order to be pushed up sooner.

  4. Michael Bayer repo owner

    the DDLCompiler should be given some public facing method that supplies the end-result name of all constraints which Alembic can call into; right now we have to code to _prepared_index_name().

  5. Aurélien Campéas

    What's the point of making up names for indexes ? Make it easier for migrations with alembic ? (otherwise, at least for Postgres the doc indicates that it will make up a name by itself).

  6. Michael Bayer repo owner

    @Aurélien Campéas a little perspective is warranted here. Apparently, recent Postgresql versions have decided to make the "name" field of CREATE INDEX optional. This is not at all how it works anywhere else:

    postgresql 8.4, name is required:

    mysql, all versions, name is required:


    SQL Server, SQLite (even SQLite, where everything is casual), etc., same thing. name is always required.

    it certainly is convenient that PG has figured out that doing auto-naming for indexes, the way databases do for everything else outside of tables and sequences, is handy. But that's not at all the norm. so SQLAlchemy has always had to try to deal with this.

  7. Log in to comment