Trouble processing long string and DB migration (1st argument: the table identifier does not refer to an existing ETS table)

Back Story: I’m trying to add all the timezones to my DB as an enum. I already do this with smaller values (not timezone-related) and it works fine.

execute "create type admin_role as enum #{convert_to_parens_string(get_admin_all_roles()}")
# executes as 
execute "create type employee_role_value as enum  ('owner', 'developer', 'admin', 'editor', 'contributor', 'viewer')"

Problem: In this non-working case I’m doing the exact same thing but the string output is huge. See it here. Function looks like this and is not working in execute.

# i have
 execute("create type time_zone as enum #{convert_to_parens_string(Tzdata.zone_list)}")
# should be, but fails with error 
execute("create type time_zone as enum ('Africa/Abidjan', 'Africa/Accra',...")

What I’ve Tried: Just trying to simply print is an issue. I cannot even get the values to print w.o truncation and I believe truncation might be happening during DB exec. The print statement ends at the <> symbol below but the data itself does not end there. It should go all the way to the Z’s.

IO.inspect(convert_to_parens_string(Tzdata.zone_list), limit: :infinity)
"('Africa/Abidjan', 'Africa/Accra', 'Africa/Addis_Ababa', 'Africa/Algiers', 'Africa/Asmara', 'Africa/Asmera', 'Africa/Bamako', 'Africa/Bangui', 'Africa/Banjul', 'Africa/Bissau', 'Africa/Blantyre', 'Africa/Brazzaville', 'Africa/Bujumbura', 'Africa/Cairo', 'Africa/Casablanca', 'Africa/Ceuta', 'Africa/Conakry', 'Africa/Dakar', 'Africa/Dar_es_Salaam'
'America/Regina', 'America/Resolute', 'America/Rio_Branco', 'Americ" <> ... # this is invalid and would cause an error

The function convert_to_parens_string works as expected since when I do File.write it writes the full string with no truncation to a file. If I paste this huge string into my DB enum thing it works (full string is shown at link above).

Question 1: How can I truly print w.o truncation? In iex the return is truncated and as shown before, using inspect w/ the limit: infinity is still truncated. Why is infinity not working?
Edit: I found this
Question 2: Can I print from inside a migration file? All my attempts have failed. I found writing to file works, but still not with my long string. I just get an error.

defmodule AddTimezoneType do
  use Ecto.Migration

  def change do
    File.write("output.txt", "Hello")  # this works
    File.write("output.txt", "#{convert_to_parens_string(Tzdata.zone_list)}") # this does not work

if it’s returning a truncated version this would cause the error but I don’t know why it would do this, or how even confirm it.

Lastly, this seems to have nothing to do with the issue but the error I get when trying to run migrations but keeps coming up:
* 1st argument: the table identifier does not refer to an existing ETS table

There are separate limit and printable_limit values. You also need to set printable_limit to have text shown without truncation.

Tangentially, I’m not sure that a database enum is the right tool to model timezones.

1 Like

Maybe I should have posted my function convert_to_parens_string before as the issue was in that.

It looks like within that, the issue is with Emun.join. I pass it a non-truncated list of all timezones and it returns a truncated string (?#@!).
It’s hard to show what I mean since the values are very long.

It might also just be truncated in the print statements. Either way the error is occurring here, and I don’t know why.

I’ll need to find some other way to handle this situation I guess. Thx for response

So the string isn’t the issue after all. Nor is Enum.join

It’s the migrations themselves. If I run the same process this way with sql it works.

iex> z = convert_to_parens_string( Tzdata.zone_list) # makes a postgres friendly list like  ('Africa/Abidjan', 'Africa/Accra', 'Africa/Addis_Ababa', 'Africa/Algiers', 'Africa/Asmara',....
iex> Ecto.Adapters.SQL.query(Repo, "CREATE TYPE timezone as ENUM #{z}", [])

I can’t figure out how to debug this since I tried everything. Such a nightmare bug and super unhelpful error message. No idea what that even means.

For now I’ve had to hardcode the output of Tzdata.zone_list to make it work.

I’ll also echo the sentiment that an enum is not a good way to store timezones. They change here and there. Not often but they do f.ex. in May 2018 North Korea changed its TZ offset value.

I am not a huge fan of the TZ libraries in Elixir that automatically and periodically update but at least one of them made it possible for you to manually update whenever you please and not have a background worker doing it (and I know in one place I worked we have programmed the library to pull TZ changes on each deploy, but not more often than once every 24h; worked pretty well for us).