RoR Migrations incorrectly consuming system tables in PostgreSQL

rubyonrails-talk
1

Hi,

I have been building an RoR app. I've been making heavy use of Migrations for database work and like the system. I am currently using PostgreSQL for the backend.

One annoying aspect of migrations is that it doesn't (yet) support decimal fields - when migration moves data from dev to test it converts them to float. I've learned to work around this with some manual "alter table" statements in my migration. (It also looks like this will be fixed in a future release of Rails: http://dev.rubyonrails.org/ticket/5454).

However, I have similar problem which I can't figure out how to fix easily. I played around with activating a GIS feature in Postgres which permits easy distance / radius searching. In order to enable this, Postgres builds 6-7 system tables in my database.

Now when I run migrations, RoR is dropping these system tables and attempting to re-create them using it's broken understanding of their structure (converting field types willy-nilly, including changing some custom datatypes into floats). This behavior is breaking the GIS support (of course).

Does anyone have any thoughts or experience on how to have RoR suppress migrations on specific tables in the database? I'm hoping I'm missing some easy configuration that will tell Rails to ignore certain tables..

Thanks for any advice or assistance!

Steve

2

Try using the :sql schema_format instead of :ruby in config/environment.rb

jeremy

3

One more option is to add some direct SQL statements to whatever you need.

I am also using Postgres as the backend for Famundo, and I use some advanced Postgres features not supported by migrations (schems, tablespaces, views, triggers, etc...). TO use those we just use direct SQL in the migration files. SO something like this:

ActiveRecord::Base.connection.execute "CREATE VIEW ....."

And on the self.down, we drop it with direct commands. To make it a bit easier, we added our own helpers. So that we don't have to write ActiveRecord::Base.connection.execute. We just write exec_sql 'whatever'.

With this we get the good of both worlds. We have migrations that are one of the biggest blessings in RoR, and we still get to use all the advanced Postgres features.

One last remark, if you are using Postgres: Postgres transacts all the DDL change commands, which means you can have better migration protection, in case a migration fails in the middle. The regular migrations can leave your DB in an intermediate state. Using the Postgres transactions correctly this becomes a non-issue.

Guy.