PGError: ERROR: permission denied: "RI_ConstraintTrigger_xxxxxxx" is a system trigger
Tuesday, December 8, 2009 at 11:25AM This was a fun one.
Today I set out to get one of my apps setup on the CI server we have use at work when I ran into this problem. It occurred only for a few of the remaining specs in the test suite that still use fixtures. The error looked something like this:
ActiveRecord::StatementInvalid in 'RegistryController should show the current registry for the current user - no params sent'
PGError: ERROR: permission denied: "RI_ConstraintTrigger_2681229" is a system trigger: ALTER TABLE "schema_migrations" ENABLE TRIGGER ALL;ALTER TABLE "users" ENABLE TRIGGER ALL;ALTER
......
ALL;ALTER TABLE "master_answers" ENABLE TRIGGER ALL;ALTER TABLE "questions" ENABLE TRIGGER ALL;ALTER TABLE "answers" ENABLE TRIGGER ALL;ALTER TABLE "people" ENABLE TRIGGER ALL
Turns out this is a permission issue with the how Postgres handles triggers. Triggers belong to the superuser and if you’ve set up your DB permissions properly your test user on your DB should NOT be a super user.
This author correctly identifies the problem but suggests the solution is to change the permissions on your test user:
Here is a better approach to addressing the actual problem (and thorough explanation):
I deviated from this approach slightly in that I only added the ‘require’ statement to pull in the hack in my CI environment (not for all the environments as the author suggests). I thoughts are that this change only needs to solve a problem in the CI env, so that is where it should live. This could bite me in the butt because it introduces an inconsistency in the adapter behavior across envs. To be honest, I’m not sure which is worse. We’ll see how it plays out.
Brian Chamberlain
So, some additional things to add to this:
For the monkey patch in the active_record_fk_hack.rb file you created. The author of that article forgot to mention that in order to make sure when Rails loads it doesn’t clobber your patch you need to require the file you’re patching. So your file should look like this
# Hack taken from http://kopongo.com/2008/7/25/postgres-ri_constrainttrigger-error
require 'active_record/connection_adapters/postgresql_adapter'
module ActiveRecord
module ConnectionAdapters
class PostgreSQLAdapter < AbstractAdapter
def disable_referential_integrity(&block)
transaction {
begin
execute "SET CONSTRAINTS ALL DEFERRED"
yield
ensure
execute "SET CONSTRAINTS ALL IMMEDIATE"
end
}
end
end
end
end
Two other things: If you’re not seeing this error it might be because you are creating your test env from schema.rb (which does NOT preserve foreign keys), using migrations or development_structure.sql to build your test db DOES preserve the foreign keys, or perhaps you’re not using foreign keys in your db (which for the record, I think is a bad idea… despite what DHH says (via secondary source)).
To check to see if you’ve got the triggers that will cause this you can use psql from the command line. Once you’ve connected to your db you can see all the triggers you have by doing select * from pg_trigger; To see if you’ll get the same permission based error on your specs you can run the command manually from psql ALTER TABLE people DISABLE TRIGGER ALL; (just make sure, if you don’t get a permission error, that you revert this with ALTER TABLE people ENABLE TRIGGER ALL;
Postgres, errors, programming, rails | in Programming, Rails 
