activerecord-pg_enum
activerecord-pg_enum copied to clipboard
Consider removing enum types as part of `db:drop` / `db:reset`
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:
- Create a migration where you use
create_enum
- Run the migration with
db:migrate
- 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?
What if I changed create_enum
to do IF NOT EXISTS
? Would that be sufficient?
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.
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.
@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.
@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.
**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