准备的语句不存在 [英] Prepared Statement doesn't exist
问题描述
当前正在运行一个简单的sinatra应用程序，使用passenger，并使用pgbouncer将连接池连接到与该应用程序位于同一服务器上的数据库。当前，我间歇性地收到一个PG错误，表明准备好的语句 a\d不存在。
PG :: Error发生在＃：
错误中：预备语句 a2不存在
在错误之前执行的红宝石代码
def self.get_ownership_record（id，key）
self.where（ user_id =？AND key =？，id， key）.first
end
pgbouncer config
; ################################################ #######
; ############ SECTION HEADER [DATABASES] ###############
; ################################################ #######
[数据库]
fakedatabase =假
[pgbouncer]
; -----常规设置--------------------------
; -------------------------------------------------
日志文件= / 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
; -----日志设置------------------------------
; -------------------------------------------------
; syslog = 0
; syslog_ident = pgbouncer
; syslog_facility =守护程序
; log_connections = 1
; log_disconnections = 1
; log_pooler_errors = 1
; -----控制台访问控制--------------------
; -------------------------------------------------
admin_users = admin，nagios
; -------------------------------------------------
; server_reset_query =全部取消；
server_check_delay = 0
server_check_query = SELECT 1;
; server_lifetime = 3600
; server_idle_timeout = 600
; server_connect_timeout = 600
; server_login_retry = 15
我唯一的解决方案是关闭准备好的语句吗？
database.yml
生产：
适配器：postgresql
数据库：fakedatabase
用户名：admin
主机：localhost
端口：5444
重新连接：true
prepare_statements：false
编辑
我已经更新了pgbouncer.ini以使用会话池
pool_mode = session
和未注释的
server_reset_query = DISCARD ALL;
我似乎仍然在随机获取涉及准备好的语句的错误，但这一次
ActiveRecord :: StatementInvalid发生在＃：
PG :: Error：错误：绑定消息提供2个参数，但准备了语句 a1要求0
我已经在postgresq中打开了语句级别的日志记录l将记录日志并在可能的情况下提供更多详细信息。
遵循Richard Huxton的建议，并经过反复试验。 / p>
我的最终设置如下
database.yml
必须将 prepared_statements 设置为 true
生产：
适配器：postgresql
数据库：fakedatabase
用户名：admin
主机：localhost
端口：5444
重新连接：真
prepare_statements：真
pgbouncer。 ini
必须取消注释 server_reset_query = DISCARD ALL;
并设置 pool_mode = session
; ################################################ #######
; ############ SECTION HEADER [DATABASES] ###############
; ################################################ #######
[数据库]
fakedatabase =假
[pgbouncer]
; -----常规设置--------------------------
; -------------------------------------------------
日志文件= / 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
; -----日志设置------------------------------
; -------------------------------------------------
; syslog = 0
; syslog_ident = pgbouncer
; syslog_facility =守护程序
; log_connections = 1
; log_disconnections = 1
; log_pooler_errors = 1
; -----控制台访问控制--------------------
; -------------------------------------------------
admin_users = admin，nagios
; -------------------------------------------------
server_reset_query =全部取消；
server_check_delay = 0
server_check_query = SELECT 1;
; server_lifetime = 3600
; server_idle_timeout = 600
; server_connect_timeout = 600
; server_login_retry = 15
基本上允许使用默认服务器重置查询的会话池模式下的准备好的语句。
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.
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.
这篇关于准备的语句不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
