search

ic-labs / django-icekit

GLAMkit is a next-generation Python CMS by the Interaction Consortium, designed especially for the cultural sector.
http://glamkit.com
MIT License
47 stars 11 forks source link

Misnamed DB relation names for long table names #306

Open jmurty opened 6 years ago

jmurty commented 6 years ago

I have just hit an issue that bites me occasionally where I need to explicitly set the db_table attribute of M2M fields in ICEkit to reasonable-length strings. Without this attribute, DB migrations seem to create relationship tables with invalid/missing FK constraints in PostgreSQL which will then fail in use.

This issue seems related to https://github.com/django-fluent/django-fluent-pages/issues/103 where the 63-character limit of PostgreSQL table/constraint/relation names was exceeded due to Fluent prepending text to db_table attributes.

In this case the missing relation name is limited to 63 characters, but is different from the name created in the DB.

Example error:

relation "advancedeventlisting_advancedeventlistingpage_limit_to_prim561a" does not exist
LINE 1: ...istingpage_limit_to_prim561a"."eventtype_id" FROM "advancede...

Example DB table that actually exists in DB (note the extra pagetype_ at the start):

pagetype_advancedeventlisting_advancedeventlistingpage_limib397

The problem is likely due to the db_table name being derived in two different ways, one without the pagetype_ prefix when generating DB creation queries, and another with that prefix when creating the actual DB tables.

mrmachine commented 6 years ago

@jmurty I think this is actually https://github.com/django-fluent/django-fluent-pages/issues/89

Since it has bitten us many times now, perhaps we should crack on and implement the fix upstream :)

jmurty commented 6 years ago

That Fluent ticket shows this is biting us regularly every several months. In fact, probably more often because we are likely just using the db_table hack as a workaround in a lot of cases without re-reporting the issue.

The key comment on that ticket is the workaround tip by @cogat:

To work around this, give your model a db_table that doesn't start with your app_label. This will prevent Fluent from prepending pagetypes_.

+1 for fixing this properly and finally in Fluent, though we need to be careful that the fix doesn't break more than it solves.