How to disable prepared statement in heroku with postgres database

Question

I fixed an issue on my rails project locally (with postgres config) while adding in database.yml this statement:

test:
  prepared_statements: false

The bug that I fixed was related to this issue:

 PG::ProtocolViolation: ERROR: bind message supplies 2 parameters, but prepared            statement "a24" requires 1 

Now, I want to fix it on my production app hosted on Heroku with a postgres database. I don't know how to disable prepared statement as the database.yml is automatically generated. I tried to append:

/database?prepared_statements=false

to my database's URI but it ends up with a DATABSE_URL that is wrong and so I can't connect to my database.

What is the process and the right syntax to disable prepared_statement?

Answer 1

We were worried about brittleness and maintaining consistency between staging/production (using DATABASE_URL on Heroku) and development/test (using database.yml/database.example.yml).

Inspired by Rails' tests, we put this in config/initializers/disable_prepared_statements.rb:

ActiveRecord::Base.establish_connection(
  ActiveRecord::Base.remove_connection.merge(
    :prepared_statements => false
  )
)

remove_connection returns a hash of the connection parameters of the connection being removed. This should let any database.yml or DATABASE_URL continue working.

Answer 2

As of Feb 19th 2014, heroku no longer overrides database.yml so you can turn off prepared statements in your production and staging (or default) block of the database.yml file as recommended by the latest docs:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  prepared_statements: false

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  url:  <%= ENV['DATABASE_URL'] %>
  pool: <%= ENV['DB_POOL'] || ENV['MAX_THREADS'] || 5 %>

staging:
  <<: *default
  url:  <%= ENV['DATABASE_URL'] %>
  pool: <%= ENV['DB_POOL'] || ENV['MAX_THREADS'] || 5 %>

Answer 3

You should just be able to add ?prepared_statements=false to the existing database url and restart your dynos. This worked for us.

heroku config:add DATABASE_URL=[old database url here]?prepared_statements=false

To check that it's set after restarting your server you can open a console and query ActiveRecord::Base.connection_config.