Rails 3.2 frequent postgres prepared statement already exists errors

ruby-on-rails postgresql heroku ruby-on-rails-3.2 prepared-statement
Eugene Correia picture Eugene Correia · Jun 27, 2014 · Viewed 7.9k times · Source

I've been digging around stackoverflow trying to find others who get these prepared statements already exists errors.

In most cases configuring unicorn properly with the after/before fork resolves these issues.

However in my case we are still getting errors as such:

ActiveRecord::StatementInvalid: PG::Error: ERROR: prepared statement "a495" already exists: INSERT INTO "user_logins" ("account_id", "created_at", "ip_address", "user_agent", "user_id") VALUES ($1, $2, $3, $4, $5) RETURNING "id"

This error gets thrown in different areas in our app but always seems to have the same statement number 'a495'.

We are on rails 3.2.17, using postgres and we are on heroku.

I really have no idea why this is happening, but its starting to happen more frequently now.

Any help would be greatly appreciated.

In the rails stack trace this error is being thrown in the .prepare call. I'm confused because its checking for the sql key in the statements collection. If it doesn't exist it prepares the new one....however when trying to prepare it, its throwing the error. 

def prepare_statement(sql)
  sql_key = sql_key(sql)
  unless @statements.key? sql_key
    nextkey = @statements.next_key
    @connection.prepare nextkey, sql
    @statements[sql_key] = nextkey
  end
  @statements[sql_key]
end

Answer

quarterdome picture quarterdome · Jul 1, 2015

We had the same problem, and did very thorough investigation. We concluded that in our case, this error is caused by Rack::Timeout, that very occasionally interrupts code execution after the new statement has been already created, but before the counter is updated on Rails side. Next prepared statement then tries to use the same name (e.g. a494), and a collision occurred.

My belief is that Rails has not implemented prepared statements correctly. Instead of using the increasing counter (a001, a002, ...), they should have used GUIDs. This way, a race condition described above wouldn't be an issue.

We didn't find a workaround. Improving the performance of an app, and increasing the window for Rack::Timeout, made this problem nearly extinct, but it still happens from time to time.

Related questions

Change from SQLite to PostgreSQL in a fresh Rails project

I have a rails app that's databases are in SQLite (The dev and production). Since I am moving to heroku, I want to convert my database to PostgreSQL. Anyways, I heard that the local, development, database does not need to …

Read More
How do you write a case insensitive query for both MySQL and Postgres?

I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can't handle that. …

Read More
Installing PostgreSQL on Ubuntu for Ruby on Rails

I currently have Ruby on Rails installed via RVM in Ubuntu 12.04. The default database is set up in SQLite3, but I'd like to switch to PostgreSQL for the purposes of pushing to Heroku. How can I accomplish this?

Read More