Misnamed DB relation names for long table names

[jmurty]

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 django-fluent/django-fluent-pages#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]

@jmurty I think this is actually django-fluent/django-fluent-pages#89

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

[jmurty]

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.