Rails 3.2 frequent postgres prepared statement already exists errors

Question

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

On non-Heroku PostgreSQL I'd be setting a `log_line_prefix` that identified the pid and session, enabling `log_statement = all`, and looking at the logs. In Heroku I don't know if that's possible. — Craig Ringer, Jun 28 '14 at 04:05

score 39 · Answer 1 · answered Jul 01 '15 at 16:41

39

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.

answered Jul 01 '15 at 16:41

quarterdome

1,559
2
12
15

1

This makes sense -- we get the same problem (Rails 4/Heroku/Puma), and I'll look for correlation with timeouts. Thanks. – David Aldridge Aug 30 '15 at 19:31
Did you open up a ticket with Rails? Has it been fixed? – Schneems Sep 22 '16 at 01:15
5

Tickets: https://github.com/rails/rails/pull/17607 and https://github.com/rails/rails/pull/25827 – Schneems Sep 22 '16 at 16:42

score 4 · Answer 2 · answered Jun 30 '14 at 17:29

4

This is typically not a Postgres issue, but an issue with sharing database connections in something like Unicorn:

Understanding Heroku Postgres Log Statements and Common Errors

answered Jun 30 '14 at 17:29

catsby

11,276
3
37
37

1

We have unicorn configured as heroku recommends so I'm not sure what else we can do from that stand point. – Eugene Correia Jul 03 '14 at 16:09
1

you can turn off prepared statements; here's an initializer that will do that: https://gist.github.com/catsby/0b676949b6cd414357df – catsby Jul 03 '14 at 20:12
3

Also note that Rails 4.1 has this as a setting on database.ml: `prepared_statements: false` as per [Rails Guide](http://edgeguides.rubyonrails.org/configuring.html#connection-preference) – Charles Forcey Oct 24 '14 at 16:18
@CharlesForcey Does turning off prepared statements actually preventy this error, though? – Dogweather Nov 30 '18 at 01:59

score 1 · Answer 3 · answered Oct 11 '17 at 20:44

Here's my solution for Heroku, which unfortunately is a little involved. On the plus side, though, you don't need to suffer from 100's of error notifications when this error starts happening. All that's needed is that the app/dyno is restarted.

The basic outline of the procedure is that when we detect a ActiveRecord::StatementInvalid exception, with an error message description that contains the words 'prepared statement', we run the heroku restart command using Heroku's platform-api gem.

Put the platform-api gem in your Gemfile, and run bundle install
Set the HEROKU_API_KEY to the correct value. (You can generate a key from your Heroku dashboard). Use heroku config:set HEROKU_API_KEY=whatever-the-value-is.
Set the HEROKU_APP_NAME to the correct value. You can get this information from the heroku CLI, but it's just whatever you called your app.
Add the following to your ApplicationController (/app/controllers/application_controller.rb):

...

class ApplicationController < ActionController::Base

rescue_from ActiveRecord::StatementInvalid do |exception|
  # notify your error handler, or send an email, or whatever
  # ...
  if exception.message =~ /prepared statement/
    restart_dyno
  end
end

def restart_dyno
 heroku = PlatformAPI.connect_oauth(ENV["HEROKU_API_KEY"])
 heroku.dyno.restart(ENV["HEROKU_APP_NAME"], "web")
end

end

That's it. Hope this helps.

this has stopped working for me after a couple years trusty service — Will, Mar 15 '18 at 21:29

Rails 3.2 frequent postgres prepared statement already exists errors

3 Answers3