[Patch] Raise specific exceptions for violated database constraints

Looks good.

I had been working on something like this a little while back in some spare cycles - the one thing I really found useful was to pull out the column name that had caused the violation. That lets you use the exceptions in your validations (and makes a non-race vuo really trivial) flagging the field to the end user that caused the problem.

I wonder if there's room in your patch to have a look at this? MySQL and SQLite both report the column(s) in the error message.

With Postgres I think you just get the index name. So you'd need to infer it from the name of the index (fragile - relies on the index having been created using a migration helper) or query the DB for that index to find the column(s) (or something else magical :)

I've added the original adapter exception to ActiveRecord::InvalidStatement (and descendents).

Using a separate class for the mapping seems a bit like overkill to me at this stage where the code involved comprises just a few lines. Extracting it would mean an additional class for each adapter. In case the amount of code grows, however, I'm all for separation.

Last thoughts on this are, do sqlite and postgresql not have error codes? I'm not sure the current approach you're taking there will work with LANG != C

I would have much preferred to dispatch on error codes for all databases, unfortunately this doesn't appear to be possible with the drivers as they are.

The pg driver doesn't propagate any error codes. Any improvements would have to rely on a patched driver.

The sqlite3 driver does support error codes, but all the interesting causes map to error code 1 and SQLite3::SQLException, as far as I can tell.

For both PostgreSQL and SQLite3, the error message doesn't seem to depend on the locale of the client app. It might be affected by the locale of the server.

From earlier - I think you're right and that it's cleaner to leave my suggestion out of your patch. Propagating the original exception is the best solution. Let a plugin wrap it up in a custom v_u_o or for some other purpose.

It seems, that Postgresql libpq doesn't return error codes at all

+1 for this please

This would be a HUGE help for us. We use unique contraints and foreign keys all over the place, and explicitly DON'T use validates_uniqueness_of due to performance concerns.

Thanks.

+1 for this Would love to use this to gracefully rescue unique and foreign keys. Would be cool to have the violated key name also like exception.key_name for tables with multiple fk indexes if supported by the DB but as is, is a huge help.

I'm currently trying to figure out a way to get at any information from PostgreSQL after an error occurred. For a uniqueness violation, I can get the columns involved, but I need a usable connection. Unfortunately, PostgreSQL rejects any attempt to use the connection until a ROLLBACK is executed.

Now, there are three cases that I don't know how to distinguish when I translate the exception:

• There is no transaction in progress and the connection is usable as is.
• I'm in a transaction and the connection is not usable until ROLLBACK.
• I'm in a nested transaction and the connection is not usable until ROLLBACK TO SAVEPOINT or ROLLBACK.

Fundamentally I'm uncomfortable doing anything that relies on issuing subsequent queries. If the API doesnt let you extract the relevant information, then just make sure that the error message and the like are available (as they are with a chained exception).

If it turns out people can figure out a reliable way to figure out which constraint / column caused the error, we can roll that in at a later date.

Good point, then I'll leave the patch as it is. Is there anything still missing?

if we can get some +1s from people using older postgresql releases, and/or the postgres gem instead of the pg gem. Then I'm happy to apply it.

I'm getting one sqlite test failure from this patch:

1) Failure: test_uniqueness_violations_are_translated_to_specific_exception(AdapterTest)

[./test/cases/adapter_test.rb:136:in `test_uniqueness_violations_are_translated_to_specific_exception'
 ./test/cases/../../../activesupport/lib/active_support/testing/setup_and_teardown.rb:62:in `__send__'
 ./test/cases/../../../activesupport/lib/active_support/testing/setup_and_teardown.rb:62:in `run']:

<ActiveRecord::RecordNotUnique> exception expected but was Class: <ActiveRecord::StatementInvalid> Message: <"SQLite3::SQLException: SQL logic error or missing database: INSERT INTO subscribers(nick) VALUES('me')"> ---Backtrace--- ./test/cases/../../lib/active_record/connection_adapters/abstract_adapter.rb:212:in log' ./test/cases/../../lib/active_record/connection_adapters/sqlite_adapter.rb:157:inexecute_without_query_record' ./test/cases/../../lib/active_record/connection_adapters/sqlite_adapter.rb:402:in catch_schema_changes' ./test/cases/../../lib/active_record/connection_adapters/sqlite_adapter.rb:157:inexecute_without_query_record' ./test/cases/helper.rb:37:in execute' ./test/cases/adapter_test.rb:137:intest_uniqueness_violations_are_translated_to_specific_exception' ./test/cases/adapter_test.rb:136:in test_uniqueness_violations_are_translated_to_specific_exception' ./test/cases/../../../activesupport/lib/active_support/testing/setup_and_teardown.rb:62:insend'

./test/cases/../../../activesupport/lib/active_support/testing/setup_and_teardown.rb:62:in run'

sqlite3-ruby 1.2.1, sqlite3 macport @3.6.11_0.

Will, I can't reproduce this problem. I've tried it on the 2-3-stable branch of a fresh git clone. Applied the patches and ran the tests for mysql, postgresql, sqlite3, and sqlite.

The drivers/gems I've been using are sqlite-ruby-2.2.3 and sqlite3-ruby-1.2.4.

1. Tested with postgres gem and PostgreSQL 8.3.

This and many other ActiveRecord tests don't pass with PostgreSQL 8.1 because of test/schema/postgresql_specific_schema.rb relying on DROP TABLE ... IF EXISTS which was introduced in 8.2.

Can anyone reproduce the sqlite errors? I'm happy to apply this but am
a little worried by them

As Sava points out, ActiveRecord tests are not compatible with PostgreSQL versions older than 8.2. Am I right in taking this as evidence that these older versions are de facto unsupported as they are not covered by any quality assurance?

The sqlite problems appear to be non-reproducible. So, what's holding back this patch? I'm happy to put in some more work if that's what is needed.

For anyone interested I've built on this to create a patch for Rails to handle db-level unique constraints gracefully, which has several advantages in many cases. Ticket is #3486

Also, is there a chance of applying the commits from ticket to to 2-3-stable? (talking about 4d614ec0429ce40c4784162c45ed06e125c0d7de, 605acee71391729ae82ba9012bc37f2f1716fb80, 9b39032925b68a724bd75174db642bc3d2f891fb) They seem to apply cleanly for me.

I am rails newbie, I tried installing your patch using
scripts/plugin install
and used your code to test, but throws error. Please help

arzvi - "rails newbie"s really shouldn't be trying to apply patches to the Rails core. Just wait for the 3.0 release.

@arzvi Here is a rollup of the patch based on Rails 2.3.5 release. Just drop this in your config/initializers. It omits the tests but "seems to work" for me with mysql.