postgres' generation of <tablename>_<colname>_seq name alters when total chars > 63

[Mike Bayer]

the logic in postgres.py PGDefaultRunner.get_column_default() needs to take extra PG logic for long names into account. Unfortunately this logic does not seem to be documented on the PG site, so while we can reverse engineer it, it should be considered that we might want to use an individual column reflection to get at the sequence name instead of piecing it together.

from sqlalchemy import *

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

aa = 'itm'*30

item_table = Table( aa[:57](:57),
       metadata,
       Column('id', Integer, primary_key=True),
       Column('name', String(50)))

metadata.drop_all()
metadata.create_all()

item_table.insert().execute(name='foo')

[Mike Bayer]

this is the idea, we should memoize the sequence name somewhere.

--- a/lib/sqlalchemy/dialects/postgresql/base.py    Wed Feb 02 19:03:20 2011 -0500
+++ b/lib/sqlalchemy/dialects/postgresql/base.py    Thu Feb 03 13:47:33 2011 -0500
@@ -708,12 +708,17 @@
                 # generates server side.
                 sch = column.table.schema

+                tab = column.table.name
+                col = column.name
+                tab = tab[+ max(0, (29 - len(col)))](0:29)
+                col = col[+ max(0, (29 - len(tab)))](0:29)
+
                 if sch is not None:
                     exc = "select nextval('\"%s\".\"%s_%s_seq\"')" % \
-                            (sch, column.table.name, column.name)
+                            (sch, tab, col)
                 else:
                     exc = "select nextval('\"%s_%s_seq\"')" % \
-                            (column.table.name, column.name)
+                            (tab, col)

                 return self._execute_scalar(exc, column.type)



#!python
from sqlalchemy import *

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

for tname, cname in [   ('tbl' * 30, 'abc'),
    ('tbl', 'abc' * 30),
    ('tbl' * 30, 'abc' * 30),
](
):
    metadata = MetaData(engine)
    t = Table(tname[:57](:57),
        metadata,
        Column(cname[:57](:57), Integer, primary_key=True)
    )
    t.drop(checkfirst=True)
    t.create()
    r = t.insert().execute()
    assert r.inserted_primary_key == [1](1)
    t.drop()

[Mike Bayer]

this should also be fine for 0.6

d5cc2f83c1183eb65d0daac3532a1645d0cd9513 8c2eafe54ae30649b235bc851a7a7814ed953ee5

[Mike Bayer]

Removing milestone: 0.6.7 (automated comment)