Failed transaction error in rails 4.1.8 and postgres

[tomcartwrightuk]

We are using rails 4.1.8 and Postgres and are receiving the following error when the unique index on the unique_key field is violated:

Shortener::ShortenedUrl Load (0.3ms)  SELECT  "shortened_urls".* FROM "shortened_urls"  WHERE "shortened_urls"."url" = 'http:/hello/boomsdf'  ORDER BY "shortened_urls"."id" ASC LIMIT 1
   (0.1ms)  BEGIN
  SQL (1.0ms)  INSERT INTO "shortened_urls" ("created_at", "unique_key", "updated_at", "url") VALUES ($1, $2, $3, $4) RETURNING "id"  [["created_at", "2015-02-09 16:18:19.247016"], ["unique_key", "g83vq"], ["updated_at", "2015-02-09 16:18:19.247016"], ["url", "http:/hello/boomsdf"]]

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_shortened_urls_on_unique_key"
DETAIL:  Key (unique_key)=(g83vq) already exists.

: INSERT INTO "shortened_urls" ("created_at", "unique_key", "updated_at", "url") VALUES ($1, $2, $3, $4) RETURNING "id"
retrying with different unique key

  SQL (0.6ms)  INSERT INTO "shortened_urls" ("created_at", "unique_key", "updated_at", "url") VALUES ($1, $2, $3, $4) RETURNING "id"  [["created_at", "2015-02-09 16:18:19.247016"], ["unique_key", "gret4"], ["updated_at", "2015-02-09 16:18:19.247016"], ["url", "http:/hello/boomsdf"]]
PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
(three more times of this section)
.
.
.
too many retries, giving up

Is this a PG specific error? Has anyone seen it before? As a short term fix we are wrapping our call to ShortenedUrl.generate in a rescue block - it's not pretty.

[mreinsch]

It's PostgreSQL related, it rejects any SQL statements after an error within a transaction. The workaround is to start a new nested transaction when creating the entry. You can do this by adding

Shortener::ShortenedUrl.transaction(:requires_new => true) do
  ...
end

If you wrap the block which retries the generation in shortened_url.rb (https://github.com/jpmcgrath/shortener/blob/master/app/models/shortener/shortened_url.rb#L64), then the retry should work.

Could you create a pull request once you resolved this?

[mreinsch]

try the psql branch: https://github.com/jpmcgrath/shortener/tree/psql

[jpmcgrath]

@tomcartwrightuk did @mreinsch's suggestion fix your issue? If so, I will write a spec to exercise the transaction code and merge this is to develop for inclusion in an upcoming release.

[mreinsch]

I actually tried that on a psql DB a while ago and it fixed it. The main challenge for a spec is that this issue will only occur with psql, not sqlite.

[jpmcgrath]

According to this http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html, SQLite does support nested transactions:

This works on MySQL and PostgreSQL. SQLite3 version >= '3.6.8' also supports it.

[mreinsch]

They are supported, but only PostgreSQL needs it, as it aborts any transaction in which a failure occurred. So if you'd want to write a test for that, you'd need psql to really reproduce the issue.

[jpmcgrath]

Gotcha.

[ManuelFrigerio]

Guys has anyone fixed this issue?
It's quite annoying when it happens, especially when you need to create short links as soon as possible (like in my case).

If @mreinsch's solution works, how do I implement it in my app?

Thanks!

[craigmcnamara]

This is solved in #95