Prepared expression does not exist

Question

A simple Sinatra application is currently running, using a passenger, and using pgbouncer to pool the databases on the same server as the application. I currently receive a PG error periodically that the prepared statement "a \ d" does not exist.

  A PG :: Error occurred in #: 
 ERROR: prepared statement "a2" does not exist 

ruby code that runs before error

  def self.get_ownership_record (id, key)
   self.where ("user_id =? AND key =?", id, key) .first 
 end

pgbouncer config

 ;  ####################################################### #######;  ############## SECTION HEADER [DATABASES] #################;  ####################################################### ####### [databases] fakedatabase = fake [pgbouncer];  ----- Generic Settings --------------------------;  ------------------------------------------------- logfile = / opt / local / var / log / pgbouncer / pgbouncer.log pidfile = / opt / local / var / run / pgbouncer / pgbouncer.pid listen_addr = * listen_port = 5444;  unix_socket_dir = / tmp user = _webuser auth_file = / Users / Shared / data / global / pg_auth auth_type = trust pool_mode = transaction;  max_client_conn = 100;  default_pool_size = 20;  reserve_pool_size = 0;  reserve_pool_timeout = 5;  server_round_robin = 0;  ----- Log Settings ------------------------------;  -------------------------------------------------;  syslog = 0;  syslog_ident = pgbouncer;  syslog_facility = daemon;  log_connections = 1;  log_disconnections = 1;  log_pooler_errors = 1;  ----- Console Access Control --------------------;  ------------------------------------------------- admin_users = admin, nagios;  -------------------------------------------------;  server_reset_query = DISCARD ALL;  server_check_delay = 0 server_check_query = SELECT 1;  ;  server_lifetime = 3600;  server_idle_timeout = 600;  server_connect_timeout = 600;  server_login_retry = 15 

Is my only solution to disable prepared statements?

database.yml

  production:
   adapter: postgresql
   database: fakedatabase
   username: admin
   host: localhost
   port: 5444
   reconnect: true
   prepared_statements: false

EDIT

I updated pgbouncer.ini to use the session pool

pool_mode=session

and no comment

server_reset_query=DISCARD ALL;

and I still apparently accidentally get errors related to prepared statements, but this time

  An ActiveRecord :: StatementInvalid occurred in #: 

 PG :: Error: ERROR: bind message supplies 2 parameters, but prepared statement "a1" requires 0 

I have included instruction logging in my postgresql logs and, if possible, will report with more details.

Answer 1

Following the advice of Richard Hookston, after some trial and error.

my final setup looks like

database.yml

had to set prepared_statements to true

  production:
   adapter: postgresql
   database: fakedatabase
   username: admin
   host: localhost
   port: 5444
   reconnect: true
   prepared_statements: true

pgbouncer.ini

had to uncomment server_reset_query=DISCARD ALL;

and set pool_mode=session

  ;  ####################################################### #######
 ;  ############## SECTION HEADER [DATABASES] #################
 ;  ####################################################### #######

 [databases]

 fakedatabase = fake

 [pgbouncer]

 ;  ----- Generic Settings --------------------------
 ;  -------------------------------------------------
 logfile = / opt / local / var / log / pgbouncer / pgbouncer.log
 pidfile = / opt / local / var / run / pgbouncer / pgbouncer.pid
 listen_addr = *
 listen_port = 5444

 ;  unix_socket_dir = / tmp
 user = _webuser
 auth_file = / Users / Shared / data / global / pg_auth
 auth_type = trust
 pool_mode = session
 ;  max_client_conn = 100
 ;  default_pool_size = 20
 ;  reserve_pool_size = 0
 ;  reserve_pool_timeout = 5
 ;  server_round_robin = 0

 ;  ----- Log Settings ------------------------------
 ;  -------------------------------------------------
 ;  syslog = 0
 ;  syslog_ident = pgbouncer
 ;  syslog_facility = daemon
 ;  log_connections = 1
 ;  log_disconnections = 1
 ;  log_pooler_errors = 1

 ;  ----- Console Access Control --------------------
 ;  -------------------------------------------------
 admin_users = admin, nagios
 ;  -------------------------------------------------
 server_reset_query = DISCARD ALL;
 server_check_delay = 0
 server_check_query = SELECT 1;
 ;  server_lifetime = 3600
 ;  server_idle_timeout = 600
 ;  server_connect_timeout = 600
 ;  server_login_retry = 15

basically allow prepared statements in session pool mode with the default reset server request.

Answer 2

Perhaps reading frequently asked questions will help? Unless you have a good reason for this, session pooling should be reasonable.

Answer 3

You can use the transaction pool provided that you PREPARE and EXECUTE prepared the query within the same transaction (to avoid pgBouncer executing server_reset_query between them). A.

Answer 4

In my case, accessing the postgres directory and running "DEALLOCATE ALL" fixes the problem.

If you use a hero like this

 > heroku pg:psql -a app_name app_name::DATABASE=> DEALLOCATE ALL