Rails: constraint violation on create but not on update

Question

Note: This is a "railsier" (and more succinct) version of this question, which was getting a little long.

I'm getting Rails behavior on a production server that I can't replicate on the development server. The codebases are identical save for credentials and caching settings, and both are powered by Oracle 10g databases with identical schema (but different data).

My Rails application contains a user model, which has_one registration; registration in turn has_and_belongs_to_many company_ownerships through a registration_ownerships table. Upon registering, users fill out data pertinent to all three models, including a series of checkboxes indicating what registration_ownerships might apply to their account.

On the dev server, the registration process is seamless, no matter what data is entered. On production, however, if users check off any of the company ownership fields before submitting their registration, Oracle complains about a constraint violation on the primary key of the company_ownerships table (which is a two-field key based on company_ownership_id and registration_id) and users get the standard Rails 500 error screen. In every case, I've verified that no conflicting record on these two fields exists in the production database, so I don't know why the constraint is getting violated.

To further confuse things, if a user registers without listing any ownerships and later goes back and modifies their account to reflect ownership data (which is done through the same interface), the application happily complies with their request and Oracle is well-behaved (this is both on production and dev).

I've spent the past couple days trying to figure out what might be causing this problem and am reaching the end of my wits. Any advice would be greatly appreciated!

UPDATE 4/15/10: I've just noticed something that might be helpful. I tried registering identical accounts on dev and production and intentionally left the "phone" field blank, which is a required field. On dev, I got back the following message:

1 error prohibited this registration from being saved

There were problems with the following fields:

• Phone can't be blank

However, on production, I get this:

2 errors prohibited this registration from being saved

There were problems with the following fields:

• Phone can't be blank
• Phone can't be blank

My guess is that this is directly tied to the violated constraint on production--maybe as part of a commit, the field is inserted once, and then inserted again but violates the constraint the second time and then rolls back the entire transaction, removing all evidence of the initial insert. Any ideas about what might be causing this sort of behavior, either on the Rails side or on the Oracle side?

Answer 1

Firstly, check the application is connecting to the database you think it is connecting to.

Secondly, check datatypes, including numeric precision. For example, in the following example, the decimal places are silently truncated so the second insert fails with a duplicate key

create table test_pk (pk_1 number(3,0), pk_2 number(3,0), val varchar2(20));
alter table test_pk add constraint test_pk_pk primary key (pk_1, pk_2);
insert into test_pk values (10.1, 10.1, 'test 10.1');
insert into test_pk values (10.2, 10.2, 'test 10.2');

Worst case, disable the constraint, try the operation, and re-enable the constraint. That would tell you whether there really is some duplicate data going in. There might be some weird race condition happening with connection pooling and non-immediate operations (eg inserts in connection a, deletes in connection b, inserts in connection a again before connection b has committed.)

Answer 2

As it happens, there was a spare copy of the "registrations" model lying around the directory; even though it had a different name ("registrations_2349871.rb" or something like that) Rails was running all model functionality (saving, validating, etc) twice, hence the key constraint violation! I've never seen behavior like this before. Deleting the rogue file solved the problem.