{"action":"created","issue":{"url":"https://api.github.com/repos/rails/rails/issues/1627","labels_url":"https://api.github.com/repos/rails/rails/issues/1627/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/1627/comments","events_url":"https://api.github.com/repos/rails/rails/issues/1627/events","html_url":"https://github.com/rails/rails/issues/1627","id":1034963,"number":1627,"title":"AR 3.1 / pgbouncer / PostgreSQL issue with prepared statements","user":{"login":"underley","id":58145,"avatar_url":"https://avatars.githubusercontent.com/u/58145?v=3","gravatar_id":"","url":"https://api.github.com/users/underley","html_url":"https://github.com/underley","followers_url":"https://api.github.com/users/underley/followers","following_url":"https://api.github.com/users/underley/following{/other_user}","gists_url":"https://api.github.com/users/underley/gists{/gist_id}","starred_url":"https://api.github.com/users/underley/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/underley/subscriptions","organizations_url":"https://api.github.com/users/underley/orgs","repos_url":"https://api.github.com/users/underley/repos","events_url":"https://api.github.com/users/underley/events{/privacy}","received_events_url":"https://api.github.com/users/underley/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/activerecord","name":"activerecord","color":"0b02e1"},{"url":"https://api.github.com/repos/rails/rails/labels/PostgreSQL","name":"PostgreSQL","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":66,"created_at":"2011-06-10T08:23:41Z","updated_at":"2015-01-29T03:51:21Z","closed_at":null,"body":"Using exec_cache with pgbouncer in transaction mode, as the connections pool can cause random problems.\r\n\r\nScenario:\r\n- the application performs a query through ActiveRecord:\r\n\r\nSomething.find 2\r\nSomething Load (0.9ms) SELECT \"somethings\" .* FROM \"somethings\" WHERE \"somethings.\" Id \"= $ 1 LIMIT 1 [[\" id \", 2]]\r\n\r\n- pgbouncer connects to the backend (lets call it PG-1)\r\n- ActiveRecord creates a prepared statement\r\n- pgbouncer prepares for the backend PG-1 prepared statement called a3:\r\n\r\n2011-06-10 9:34:04 EDT LOG: duration: 0.505 ms parse a3: SELECT \"somethings\" .* FROM \"somethings\" WHERE \"somethings.\" Id \"= $ 1 LIMIT 1\r\n\r\n- ActiveRecord execute prepared statement\r\n- pgbouncer performs pg backend EXECUTE a3 (...) on PG-1\r\n\r\n> 2011-06-10 9:34:04 EDT LOG: duration: 0.048 ms bind a3: SELECT \"somethings\" .* FROM \"somethings\" WHERE \"somethings.\" Id \"= $ 1 LIMIT 1\r\n> 2011-06-10 9:34:04 GMT DETAIL: parameters: $ 1 = '2 '\r\n> 2011-06-10 9:34:04 EDT LOG: duration: 0.045 ms execute a3: SELECT \"somethings\" .* FROM \"somethings\" WHERE \"somethings.\" Id \"= $ 1 LIMIT 1\r\n> 2011-06-10 9:34:04 GMT DETAIL: parameters: $ 1 = '2 '\r\n\r\n- the application receives the result\r\n\r\n> Something Id: 2, symbol: \"sss2\", description: nil, created_at: \"2011-06-10 07:19:34\", updated_at: \"2011-06-10 07:19:34\"\r\n\r\n- at this time a different application process connects to pgbouncer, goes to the backend PG-1 and takes it (for example - with begin transaction)\r\n- the first application process once again executes the same query, with other parameters\r\n\r\nSomething.find 3\r\n\r\n- pgbouncer connects to the backend (but this time it goes to the PG-2, because the PG-1 is busy)\r\n- ActiveRecord has prepared statement in exec_cache (called a3)\r\n- ActiveRecord execute prepared statemnet a3\r\n\r\n> Something Load (1.2ms) SELECT \"somethings\" .* FROM \"somethings\" WHERE \"somethings.\" Id \"= $ 1 LIMIT 1 [[\" id \", 3]]\r\n\r\n- pgbouncer performs  EXECUTE a3 (...) on backend PG-2\r\n- Fail - the PG-2 has no such prepared statement\r\n\r\n> PGError: ERROR: Prepared statement \"a3\" does not exist\r\n\r\nSolutions:\r\n- switching pgbouncer mode to session - bad, because it increases the consumption of resources\r\n- wrapping request in transaction - wrong - also increases consumption of resources, with additional side effec - there may be long-lasting transactions that will cause deadlocks\r\n- add option to not use ActiveRecord exec_cache - fix all issues with pgbouncer and additionaly another described [here](http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/)\r\n"},"comment":{"url":"https://api.github.com/repos/rails/rails/issues/comments/71964859","html_url":"https://github.com/rails/rails/issues/1627#issuecomment-71964859","issue_url":"https://api.github.com/repos/rails/rails/issues/1627","id":71964859,"user":{"login":"kapso","id":344198,"avatar_url":"https://avatars.githubusercontent.com/u/344198?v=3","gravatar_id":"","url":"https://api.github.com/users/kapso","html_url":"https://github.com/kapso","followers_url":"https://api.github.com/users/kapso/followers","following_url":"https://api.github.com/users/kapso/following{/other_user}","gists_url":"https://api.github.com/users/kapso/gists{/gist_id}","starred_url":"https://api.github.com/users/kapso/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kapso/subscriptions","organizations_url":"https://api.github.com/users/kapso/orgs","repos_url":"https://api.github.com/users/kapso/repos","events_url":"https://api.github.com/users/kapso/events{/privacy}","received_events_url":"https://api.github.com/users/kapso/received_events","type":"User","site_admin":false},"created_at":"2015-01-29T03:51:21Z","updated_at":"2015-01-29T03:51:21Z","body":"+1 just experienced it in production\r\n\r\n```ruby\r\ngem 'rails', '4.2.0'\r\ngem 'puma', '2.11.0'\r\ngem 'pg', '0.18.1'\r\ngem 'postgres_ext', '2.4.0'\r\n```\r\n\r\n\r\n```\r\nvendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:637:in `prepare': PG::DuplicatePstatement: ERROR:  prepared statement \"a3\" already exists\r\n: SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = $1 LIMIT 1 (ActiveRecord::StatementInvalid)\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:637:in `prepare_statement'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:596:in `exec_cache'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:585:in `execute_and_clear'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/database_statements.rb:336:in `select'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `block in select_all'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:83:in `cache_sql'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `select_all'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/querying.rb:39:in `find_by_sql'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/statement_cache.rb:107:in `execute'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/core.rb:186:in `find_by'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.0/lib/active_record/dynamic_matchers.rb:70:in `find_by_id'\r\n\tfrom vendor/bundle/ruby/2.2.0/gems/sorcery-0.9.0/lib/sorcery/adapters/active_record_adapter.rb:85:in `find_by_id'\r\n```\r\n\r\n"}}