Rails 2 vs Rails 3 db:test:prepare oddness

Reference: http://gist.github.com/636670

I use PostgreSQL in my project and I have triggers and functions defined
as Active Record migrations. In Rails 2.3.10 this works exactly as
expected. However, in Rails 3 it does not. The DBMS service is the same
in both cases so this variation is cause by some implementation change
in Rails-3.

The difference is that in Rails 2.3 the triggers and functions are
created by db:test:prepare and in Rails 3.0 they are not. In both
Rails-2 and Rails-3 the development and production databases that are
created by db:migrate are identical, complete with the functions and
triggers. However, Rails 3 db:test:prepare does not create the
functions or triggers in the test database at all.

This is a fairly significant failing and if it is a bug then I am very
surprised to discover it. If I am doing something wrong then I would
like to know what it is.

James Byrne wrote in post #955823:

Reference: http://gist.github.com/636670

I use PostgreSQL in my project and I have triggers and functions defined
as Active Record migrations.

As a former stored-procedure fan myself, I would strongly encourage you
to move those into the application layer. Maintenance of SPs is a
nightmare, version control and testing are generally difficult to
impossible, and they are generally just not worth the trouble.

That goes double for triggers.

Best,

Marnen Laibow-Koser wrote in post #955846:

As a former stored-procedure fan myself, I would strongly encourage you
to move those into the application layer. Maintenance of SPs is a
nightmare, version control and testing are generally difficult to
impossible, and they are generally just not worth the trouble.

This is not possible in a audit compliant environment. And the
generation of both functions and triggers in the production and
development database is properly handled by the Rails-3 Active Record
migration task. The question at hand is: Is there a bug in the
db:test:prepare task in Rails-3 or not? If not then what need I do
differently?

James Byrne wrote in post #955871:

Marnen Laibow-Koser wrote in post #955846:

As a former stored-procedure fan myself, I would strongly encourage you
to move those into the application layer. Maintenance of SPs is a
nightmare, version control and testing are generally difficult to
impossible, and they are generally just not worth the trouble.

This is not possible in a audit compliant environment.

Perhaps. I'm not at all convinced of this, and there seem to be some
circumstances in which logic in the DB actually makes things harder to
audit, not easier.

If you can explain what audit functionality you think you need from SPs
and triggers, I'll try to provide more specific advice.

And the
generation of both functions and triggers in the production and
development database is properly handled by the Rails-3 Active Record
migration task. The question at hand is: Is there a bug in the
db:test:prepare task in Rails-3 or not?

I am aware that this is the question at hand, but I do not think it is
the only question to be answered. Your life really will be easier if
you get rid of your stored procedures and triggers.

If not then what need I do
differently?

I suspect db:test:prepare is loading the DB from the schema file, not
rerunning all the migrations -- at least, that's what it *should* be
doing.

If I am right about this, then you need to get the SPs and triggers into
the schema file. There may be a plugin to do that (as Foreigner does
for foreign keys); if not, then you'll have to set the schema dumper to
use SQL instead of Ruby for the schema file.

Best,

This was caused by forgetfulness. When we switched to Rails3 we did so
by generating a new Rails3 project and copying the .git directory from
the Rails2 version and resolving the diffs. But we missed setting
config.active_record.schema_format = :sql.