CODEWITHSUNDEEP
postgresqlsinatrapgbouncer
danmanstx
danmanstx

Reputation: 1712

Prepared Statement doesn't exist

Currently running a simple sinatra app, using passenger, and using pgbouncer for connection pooling to a database on the same server as the app. Currently I am intermittently getting a PG error that the prepared statement "a\d" doesn't exist.

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

the ruby code that is executed before the 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 turn off prepared statements?

database.yml

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

EDIT

I have updated the pgbouncer.ini to use session pooling

pool_mode=session

and uncommented

server_reset_query=DISCARD ALL;

and I am still seemingly, randomly getting errors involving 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 turned on statement level logging in my postgresql logs and will report back with more details if possible.

Upvotes: 12

Views: 21086

Answers (4)

kazuwombat
kazuwombat

Reputation: 1653

In my case access to postgres directory and run "DEALLOCATE ALL" resolve the problem.

If you use heroku, like this

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

Upvotes: 1

Richard Huxton
Richard Huxton

Reputation: 22893

Perhaps reading the FAQ would help? Unless you have a good reason not to, session-pooling should be sensible.

Upvotes: 0

qris
qris

Reputation: 8162

You can use transaction pooling, provided that you PREPARE and EXECUTE the prepared query inside the same transaction (to avoid pgBouncer running the server_reset_query in between).

Upvotes: -1

danmanstx
danmanstx

Reputation: 1712

follwing Richard Huxton advice, and 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 a session pool mode with the default server reset query.

Upvotes: 5

Related Questions