I have a table in a legacy database that has a column named field. It is impossible to link the table directly to an activeRecord model, since the latter will try to create the method field_changed? which is already defined.
So I created a view to rename the field and created the following rules to allow inserts, updates and deletes.
create or replace rule new_linked_fields_upd as on update to new_linked_fields
do instead
update linked_fields set race_id = new.race_id , master_id = new.master_id, field = new.field_name
where id = new.id;
create or replace rule new_linked_fields_ins as on insert to new_linked_fields
do instead
insert into linked_fields select nextval('linked_fields_id_seq'), new.race_id, new.field_name, new.master_id;
create or replace rule new_linked_fields_del as on delete to new_linked_fields
do instead
delete from linked_fields where id = old.id;
I linked the activeRecord model to the view.
class LinkedField < ActiveRecord::Base
self.table_name ="new_linked_fields"
self.primary_key = "id"
end
I had to add self.primary_key = "id", otherwise, methods such as LinkedField.last would fail (generating bad SQL).
In SQL everything works great. In ruby, I can update the attributes of a LinkedField, or destroy an instance, but I can't create one.
LinkedField.create(field_name:'toto',race_id:-3,master_id:4)
Returns the following error.
(0.3ms) BEGIN
SQL (1.0ms) INSERT INTO "new_linked_fields" ("field_name", "master_id", "race_id") VALUES ($1, $2, $3) RETURNING "id" [["field_name", "toto"], ["master_id", 4], ["race_id", -3]]
(0.3ms) ROLLBACK
ActiveRecord::StatementInvalid: PG::Error: ERROR: syntax error at end of input
LINE 1: DEALLOCATE
^
: INSERT INTO "new_linked_fields" ("field_name", "master_id", "race_id") VALUES ($1, $2, $3) RETURNING "id"
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:297:in `exec'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:297:in `dealloc'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:287:in `delete'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:1172:in `rescue in exec_cache'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:1155:in `exec_cache'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:665:in `block in exec_query'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activesupport-3.2.6/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:663:in `exec_query'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract/database_statements.rb:63:in `exec_insert'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract/database_statements.rb:90:in `insert'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/relation.rb:66:in `insert'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/persistence.rb:363:in `create'
from /Users/macbook/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.6/lib/active_record/timestamp.rb:57:in `create'
Any cue? I'm using postgres 9.0 and rails 3.2.6. Thanks
First, if you're able to update to PostgreSQL 9.1, view triggers are way easier to get right than rules.
Second, where is DEALLOCATE coming from? Is it trying to free a prepared statement?
Try enabling log_statement = 'all' in postgresql.conf, reloading PostgreSQL (pg_ctl reload, SIGHUP the postmaster, or restart it), re-run your test and examine Pg's logs. See what SQL your Rails program is actually running.