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.