Dealing with truncating identifiers on DB

Questions / Help
1

By default, PostgreSQL truncates identitfier (table names, fkey names etc) on 63 bytes, from their docs

“The system uses no more than NAMEDATALEN -1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes.”

Similar things happen on other DBs https://dev.mysql.com/doc/refman/8.3/en/identifier-length.html

And this can generate some undesired behaviour when using Ecto changesets default names. For example, here: ecto/lib/ecto/changeset.ex at master · elixir-ecto/ecto · GitHub

If you don’t manually choose a name for your FKEY, Ecto will keep thing it is called “my_very_very_very_very_long_and_very_explicit_and_even_longer_fkey” but postgres will silently save it as “my_very_very_very_very_long_and_very_explicit_and_even_longer_f” and thus ecto gets lost. Obviously this can be fixed by explicitely passing the fkey name from the start or even increasing NAMELEN on the DB, however I was wondering if anyone has any other idea here or if Ecto should somehow identity that the saved name is different than the one it provided and somehow result in an error?

Thanks!

1 Like
2

Hello and welcome!

Interesting! So I guess Rails checks this explicitly as it throws an error when the index name is too long. Due to my experience there, I’ve become accustom to manually naming indices in most cases, but I would say that this is a gotcha and I would open an issue in Ecto, unless there there is already an old closed one about it, of course.

So to answer your question, I would say: Yes :slight_smile:

2 Likes