SQLAlchemy is confused by long table names.

[Former user]

(original reporter: ged) For example the attached example fails on postgres with the following traceback:

Traceback (most recent call last):
  File "test_long_tablenames.py", line 20, in <module>
    metadata.drop_all()
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/schema.py", line 1175, in drop_all
    connectable.drop(self, checkfirst=checkfirst, tables=tables)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 683, in drop
    self._run_visitor(self.dialect.schemadropper, entity, connection=connection, **kwargs)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 708, in _run_visitor
    element.accept_visitor(visitorcallable(conn, **kwargs))
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/schema.py", line 1178, in accept_visitor
    visitor.visit_metadata(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/ansisql.py", line 944, in visit_metadata
    table.accept_visitor(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/sql.py", line 2558, in accept_visitor
    visitor.visit_table(self)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/ansisql.py", line 960, in visit_table
    self.execute()
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1190, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 496, in execute
    return Connection.executors[c](c)(self, object, *multiparams, **params)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 511, in execute_text
    self._execute_raw(context)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 560, in _execute_raw
    self._execute(context)
  File "/home/ged/devel/sqlalchemy/lib/sqlalchemy/engine/base.py", line 578, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) cannot drop table users because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 '\nDROP TABLE users' {}

This is because upon creation, the table name is silently truncated to 63 characters by Postgres... Then when dropping, ANSISchemaDropper::visit_metadata compute wrongly the collection of tables to drop: the table with the long name is not included (because of the dialect.has_table using the full name and not the truncated one, and hence thinking the table is not present in the database).

Btw: the max_identifier length in databases/postgres.py is currently 68. Shouldn't it be 63? (63 is the default identifier length in recent Postgres versions)

[Mike Bayer]

just change the length to 63 then, it is probably a mistake on my part that I thought it was 68.

but also, why would we care about tables with names that are longer than that allowed by the database ? (hence low priority, but please commit this fix and verify that it works)

[Former user]

(original author: ged) Fix commited even though it doesn't fix anything in practice. This is part of a larger problem where the max identifier length is totally ignored (in all dialects) for table names. Should I open another ticket for this?

Why should we care? Because most people don't know this limit by heart and even if they do they might accidentally be bitten by it (especially with generated table names). And since it fails silently it can take a while to figure what's happening. Now, IMHO acceptable behaviors would be to either fail noticeably or fall back to a truncated name as Postgres itself does. We just shouldn't fail silently.

For what it's worth, when you do it on the command line, Postgres issues a Notice. Is that notice easily available programmatically too?

NOTICE: identifier "this_is_a_very_very_very_very_very_very_very_very_long_table_name" will be truncated to "this_is_a_very_very_very_very_very_very_very_very_long_table_na"

For the record: http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS and http://www.postgresql.org/docs/7.2/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS (the limit we 31 in PostgreSQL 7.2 and earlier)

[Mike Bayer]

really, it fails silently? ive observed if you use a table or column name longer than the limit, things fail instantly all over the place. keeping in mind that a Table is dialect agnostic, at what point would you like an explicit fail condition ?

[Mike Bayer]

change from 68->63 in changeset:2944 changeset:2959

[Former user]

(original author: ged) depends how you define "fail". What it does now is: it actually creates the table in the database but the name in the database is truncated, while all reference in SQLAlchemy are not. Hence failing all over the place indeed. Just that this failure can be anywhere in your code. A saner approach would be to either:

• just after issuing a "create table" statement to the db, check that it has the name we gave it, and if it's different, either raise an exception or update the table name in SA so that everything works as if nothing happened.

OR

• truncate the name before sending it to the database (as I believe happens for other identifiers).

[Mike Bayer]

For this ticket I need a reproducing test case to reopen. I just tried the test below without issue; apparently when PG receives a too-long tablename, it truncates it in all cases. SQLA (0.4) doesn't care and can even reflect the table.

from sqlalchemy import *

engine = create_engine("postgres://scott:tiger@127.0.0.1/test", echo=True)

engine.execute("""
CREATE TABLE this_is_a_table_name_that_is_longer_than_this_test_allows_because_this_is_more_than_sixty_three_characters (
    foo INTEGER
)
""")

m = MetaData(engine)
t1 = Table("this_is_a_table_name_that_is_longer_than_this_test_allows_because_this_is_more_than_sixty_three_characters", m, autoload=True)

t1.insert().execute(foo=1)
assert t1.select().execute().fetchall() == [(1,)]((1,))

assert t1.select().apply_labels().alias().execute().fetchall() == [(1,)]((1,))

[Mike Bayer]

so SQLA can read/write from/to tables with too long names, but create/drop fails because has_table() uses a different query.

[Mike Bayer]

45defff23dd7a51cbdb95e4a5b724620c29fa72e b2b754c2ce3a9672a135f01246dcb9521a88e2c4 disallows create(), drop() and similar from being used with a table/schema name that is too long for what is configured in the dialect. I'm having a hard time seeing the use case for a too-long name except that someone is trying to run a PG app with a long name on Oracle, which has shorter names - in which case they'd want to see the create() fail immediately. The compiler won't stop you from using a table with a too-long name though; PG allows it, other DB's like Oracle would just raise an error.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)