ruby - Rails, PostgreSQL, and History Triggers

Question

SO...

I am adding history tables populated by triggers for auditing in my project via something like...

execute <<-SQL
  CREATE OR REPLACE FUNCTION process_history_table() RETURNS TRIGGER AS $history_table$
    BEGIN
      IF (TG_OP = 'DELETE') THEN
          INSERT INTO history_table VALUES (DEFAULT, 'D', now(), OLD.*);
          RETURN OLD;
      ELSIF (TG_OP = 'UPDATE') THEN
          INSERT INTO history_table VALUES (DEFAULT, 'U', now(), NEW.*);
          RETURN NEW;
      ELSIF (TG_OP = 'INSERT') THEN
          INSERT INTO history_table VALUES (DEFAULT, 'I', now(), NEW.*);
          RETURN NEW;
      END IF;
      RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
  $history_table$ LANGUAGE plpgsql;

  CREATE TRIGGER history_table
  AFTER INSERT OR UPDATE OR DELETE ON table
    FOR EACH ROW EXECUTE PROCEDURE process_history_table();
SQL

...and this will work for production and other environments. The problem is when someone runs bundle exec rake db:drop db:create db:schema:load db:migrate RAILS_ENV=test or something similar (most important is the db:schema:load portion), this will bypass trigger creation as triggers are not saved in the db/schema.rb file.

Perhaps the correct solution is to say that when using rails, developers should never run db:schema:load and always run db:migrate instead to ensure all migrations can continuously be re-run. However, we have not been operating that way for a long time and I believe it would be quite painful to do so as we may need to update several dozen or more migrations. Any thoughts on how I could incorporate triggers into my application incrementally and have the developer / test environments continue to be built / re-created the same way as today would be very helpful.

Thanks!

Answer 1

If you need or want database-specific features that ActiveRecord doesn't understand then you should switch to db/structure.sql for keeping track of your schema. db/structure.sql is pretty much a raw dump of your schema made using the database's native tools so it will contain triggers, CHECK constraints, indexes on function results, and everything else.

Switching is easy:

1. Update your config/application.rb to contain config.active_record.schema_format = :sql.
2. Do a rake db:structure:dump to get an initial db/structure.sql.
3. Delete db/schema.rb from your directory tree and revision control.
4. Add db/structure.sql to revision control.
5. Adjust your rake habits:
   • Use db:structure:dump instead of db:schema:dump
   • Use db:structure:load instead of db:schema:load

Everything else should work as usual (assuming, of course, that you're sane and using PostgreSQL for development, testing, and production).

With this change made, your triggers will be tracked in db/structure.sql and recreating the database won't lose them.