Consider removing enum types as part of `db:drop` / `db:reset`

[michaelherold]

Since Postgres defines types in the postgres tables, running db:reset can get you into a situation where you cannot re-run your migrations that define your Postgres enums. As a simple reproduction:

1. Create a migration where you use create_enum
2. Run the migration with db:migrate
3. Reset your database with db:reset

The migration will fail because the type already exists.

This all makes sense, but I wonder if there's a way we could make it so db:drop also drops any enums defined by this gem?

[alassek]

What if I changed create_enum to do IF NOT EXISTS? Would that be sufficient?

[michaelherold]

Yeah, I think that would work. I doubt you'd ever have colliding names in a single app.

Once I finish up what I'm currently working on at work, I'll have an environment for you to play with.

[alassek]

It turns out that CREATE TYPE doesn't support IF NOT EXISTS so this is a bit more complicated. Probably going to have to use a DO block.

[stratigos]

@alassek thanks for this really handy gem! Im really enjoying using it in production.

I frequently reset my localdev databases, and run into this issue as well. In the meantime, I just wrote my own .sql file to destroy my custom enum types when I db:reset or db:setup.

As for IF NOT EXISTS :

It turns out that CREATE TYPE doesn't support IF NOT EXISTS so this is a bit more complicated. Probably going to have to use a DO block.

Im not sure if you meant a Ruby block or a SQL DO, but there are a few solutions for wrapping the CREATE TYPE in some other statement that first performs a check for the custom type. This answer from stackoverflow looks like a good candidate:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_type') THEN
        CREATE TYPE my_type AS
        (
            -- add fields...
        );
    END IF;
END$$;

If I had more time Id contribute towards a PR! Perhaps in a few weekends from now.

[alassek]

@stratigos yes, that is basically what I had in mind... but I think there's another wrinkle to this. I don't want to blithely wipe out preexisting enums in a production environment, so I think this also needs to tap into the functionality added by rails/rails#22967

So in development, create_enum will automatically drop an existing enum and redefine it. But in production, you will still get the error. I think that's how I want this to work.

[jasonfb]

**2021-10-13 - RESOLVED -JFB **

I seem to have this problem here with this on the Heroku Pipeline

the pipeline documentation is here https://devcenter.heroku.com/articles/release-phase (it's a bit tricky if you've never seen it before)

my release-tasks.sh file looks like:

## Step to execute
bundle exec rails db:migrate

# check for a good exit
if [ $? -ne 0 ]
then
  puts "*** RELEASE COMMAND FAILED"
  # something went wrong; convey that and exit
  exit 1
fi

(I think this is standard-- the docs say that the db:migrate should be here)

But for some reason this then causes PG::DuplicateObject: ERROR: type "bill_strategies" already exists

My app.json file -- were Heroku tells you to define certain formation settings -- looks like so:

{
  "scripts":  {
    "postdeploy": "bundle exec rails data:migrate"
  },
  "addons": [
    "heroku-postgresql:standard-0"
  ],
  "environments": {
    "test": {
      "formation": {
        "test": {
          "quantity": 1,
          "size": "standard-2x"
        }
      }
    },
    "review": {
      "addons": [
        "heroku-postgresql:hobby-dev"
      ]
    }
  }
}

Note that data_migrations is from the gem https://github.com/jasonfb/nonschema_migrations