I included a uniqueness constraint in my app to prevent what appears to be the same entry from appearing in a list multiple times and confusing people.
I later realised that I hadn’t taken PostgreSQL’s case sensitivity into account so multiple otherwise identical names were allowed, potentially confusing people.
-- SELECT * FROM tags ----------- name ----------- john JOHN jOhN ----------- (3 rows)
Rather than slinging data around in the model validations AND again in the database constraints I decided the best way to deal with it would be to convert the column from
citext (case insensitive text) and let Postgres deal with it.
The first thing I tried was to migrate the column to citext:
def change change_column :tags, :name, :citext end
The result was an error stating that Postgres doesn’t know about citext;
PG::UndefinedObject: ERROR: type "citext" does not exist. That was easily fixed, I added
enable_extension 'citext' to the migration and ran
rails db:migrate again.
The next problem I encountered was when I tried to rollback. Whenever I write a migration I try to remember to test that it works backwards as well as forwards. In this case I received an
ActiveRecord::IrreversibleMigration error due to the fact that
change_column is not reversible (the current way it’s defined doesn’t let you specify what column type it’s being changed from so the rollback has no idea what to change it back to).
I could have used a
reversible block for each direction but I find separate
down methods to be clearer. In this case I renamed
up and duplicated it (with two minor changes) for
def up enable_extension 'citext' change_column :tags, :name, :citext end def down disable_extension 'citext' change_column :tags, :name, :string end
The rollback still failed though, this time with a mysterious error stating the column doesn’t exist:
PG::UndefinedColumn: ERROR: column "name" of relation "tags" does not exist
After double checking for typos, scratching my head a bit and then triple checking for spelling mistakes it finally dawned on me what I’d done. By disabling the citext extension too early I’d effectively dropped a cloak of invisibility over the citext column. Postgres could no longer see it.
Once I swapped the order of the method calls in the
down method the migration worked in both directions and everything was hunky dory. Everything, that is, except for one little niggle…
I thought about future me and realised what could happen in six months time (or six days for that matter) once I’d forgotten all about this little disappearing column problem. Some day in the future I’ll probably copy this migration into another file, or maybe even another app.
The up migration will work, the down migration will work and I’ll be happy as larry… right up until I try to rollback even further. After this roll back the citext extension will be gone and rolling back the next migration that refers to a citext column will raise an undefined column error… in a completely different file!
The chances of all those things happening are vanishingly small but if it were to happen I can see myself spending hours pulling my hair out trying to figure it out! If fixing it were difficult I might decide to accept the (tiny) risk of it happening but in this case defending against it is as easy as splitting it out into two migrations and providing the reason why in my commit message:
# migration 1.rb class EnableCitext < ActiveRecord::Migration[6.0] def change enable_extension 'citext' end end # migration 2.rb class ConvertTagNameToCaseInsensitive < ActiveRecord::Migration[6.0] def up change_column :tags, :name, :citext end def down change_column :tags, :name, :string end end
Now, if I ever copy and paste the contents of
ConvertTagNameToCaseInsensitive into another migration it will either work perfectly (in both directions) or I’ll get a sensible error reminding me to enable the citext extension first.