{"id":43,"date":"2007-09-22T17:18:19","date_gmt":"2007-09-22T23:18:19","guid":{"rendered":"http:\/\/www.pervasivecode.com\/blog\/2007\/09\/22\/making-rails-raketest-not-drop-your-pgsql-database\/"},"modified":"2007-09-22T17:25:52","modified_gmt":"2007-09-22T23:25:52","slug":"making-rails-raketest-not-drop-your-pgsql-database","status":"publish","type":"post","link":"http:\/\/www.pervasivecode.com\/blog\/2007\/09\/22\/making-rails-raketest-not-drop-your-pgsql-database\/","title":{"rendered":"Making Rails’ rake:test not drop your PGSQL database"},"content":{"rendered":"

Let’s say you’re using Rails with PostgreSQL<\/a> and the TSearch2<\/a> built-in full text search engine.<\/p>\n

Did you notice that every time you run rake test<\/code>, that depends on db:test:prepare<\/code>, which depends on db:test:clone<\/code>, which depends on db:test:purge<\/code>, which drops the database and creates it again?<\/p>\n

Along with your dropped database goes the TSearch2 functions that wrap the C libraries that do the actual work. So, in effect, you no longer have TSearch2 installed. (“Uh… I kinda needed those…”) Presumably if you have tests that exercise search functionality, they will always fail because the TSearch2 functions are gone by the time the tests run.
\n
\nSince these functions are just wrappers for C libraries, which are not subject to the PostgreSQL plugin security model, PostgreSQL wisely prevents any old user from getting at them. Only a superuser can create them, which means you can’t just add the tsearch2.sql script to a Rails DB migration and get them back each time that way.<\/p>\n

Options include:<\/p>\n

    \n
  1. Making a setuid script (or a script with the postgres user’s password embedded) that the migration can run, which will log in as the postgres user, run the tsearch2.sql script, and grant permissions to your Rails DB user to use them<\/li>\n
  2. Changing the rules of the PostgreSQL instance you’re using to allow any old user to mess with C libraries (a pretty big security hole, but maybe you don’t care about that on your development DB on your laptop), and putting tsearch2.sql in a migration. (I dunno if this is even technically possible, but it seems like such a bad idea that I’m not even bothering to look.)<\/li>\n
  3. Using Rake to tell Rails not to drop and re-create your database for each test run, but instead to migrate back to 0 and then re-migrate to the latest version.<\/li>\n<\/ul>\n

    I chose #3. Here’s the code, which is in my Rakefile:<\/p>\n

    \r\n# don't drop the test database; migrate it back to 0\r\nRake::TaskManager.class_eval do\r\n  def delete_task(task_name)\r\n    @tasks.delete(task_name.to_s)\r\n  end\r\n  Rake.application.delete_task(\"db:test:purge\")\r\nend\r\nnamespace :db do\r\n    namespace :test do\r\n        task :purge do\r\n            ActiveRecord::Migrator.migrate(\"db\/migrate\/\", 0)\r\n        end\r\n    end\r\nend\r\n<\/pre>\n
    In the rare case where you really wanted to drop and re-create your test database, just use the command line PostgreSQL commands dropdb and createdb, and then (still as the postgres user) run the tsearch2.sql script.<\/p>\n
    Then resume normal Rails rake:test use, until such time as you irrevocably hose your database (really?) whereupon you’ll need to use the dropdb\/createdb method again.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Let’s say you’re using Rails with PostgreSQL and the TSearch2 built-in full text search engine. Did you notice that every time you run rake test, that depends on db:test:prepare, which depends on db:test:clone, which depends on db:test:purge, which drops the database and creates it again? Along with your dropped database goes the TSearch2 functions that … Continue reading “Making Rails’ rake:test not drop your PGSQL database”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,19,20],"tags":[],"class_list":["post-43","post","type-post","status-publish","format-standard","hentry","category-databases","category-postgresql","category-ruby-on-rails"],"_links":{"self":[{"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/posts\/43"}],"collection":[{"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/comments?post=43"}],"version-history":[{"count":0,"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/posts\/43\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/media?parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/categories?post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.pervasivecode.com\/blog\/wp-json\/wp\/v2\/tags?post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}