rails: PostgreSQL prepared statement failure inside transaction results in a permanently cached prepared statement that's never cleaned up.

ActiveRecord 4.0. Verified with PostgreSQL 9.2 and 9.3 and pg gem 0.16 and 0.17

Assume you have some basic table, User for this example’s purposes.

In a Rails console:

User.find(1)
=> .....

At this point, the PostgreSQL adapter has created a prepared statement for selecting a User by ID and cached it in the connection. To verify:

ActiveRecord::Base.connection.instance_variable_get(:@statements).to_a
=> [
  [0] [
    [0] "\"$user\",public-SELECT  \"users\".* FROM \"users\"  WHERE \"users\".\"id\" = $1 LIMIT 1",
    [1] "a1"
  ]
]

Now from a separate session, let’s modify the schema for the User table. The use case here is adding a column to a live system without causing downtime to users:

ALTER TABLE users ADD new_metric_column integer;

Back in the previous Rails console, we then run this:

User.transaction { User.find(1) }
=> [2013-09-23 17:53:03] DEBUG ActiveRecord::Base        :    (0.2ms)  BEGIN
[2013-09-23 17:53:03] DEBUG ActiveRecord::Base        :   User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]
[2013-09-23 17:53:03] ERROR ActiveRecord::Base        : PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
: SELECT  "users".* FROM "users"  WHERE "users"."id" = $1 LIMIT 1
[2013-09-23 17:53:03] DEBUG ActiveRecord::Base        :    (0.5ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
: SELECT  "users".* FROM "users"  WHERE "users"."id" = $1 LIMIT 1
from /Users/nixme/Projects/ML/flabongo/vendor/bundle/ruby/2.0.0/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:512:in `exec'

What’s happening is that since the shape of users has changed, the a1 prepared statement is no longer valid. The AR PostgreSQL adapter correctly sees this and tries to run DEALLOCATE a1. But the transaction’s already invalid at that point, and so even the DEALLOCATE fails, leaving that connection’s @statements pool with a permanently bad prepared statement.

For reference, on the PostgreSQL side, the logs show:

ERROR:  cached plan must not change result type
STATEMENT:  SELECT  "users".* FROM "users"  WHERE "users"."id" = $1 LIMIT 1
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  DEALLOCATE a1

Now that might seem like an arbitrary transaction, but all AR saves wrap in a transactions, so a validation or callback might be selecting a User. Imagine in a production system, all old app servers are now permanently broken without a restart. This makes live migrations without downtime basically impossible, unless I’m missing something.

I think ideally, the transaction is aborted, then DEALLOCATE is run outside a transaction and thus successfully removes that prepared statement for the connection. Then the original transaction is retried automatically and the necessary prepared statements are re-created automatically.

Another option is just to blow up this one transaction, but at the very least get that DEALLOCATE to succeed. So that any follow-up transactions will work and fail the least number of requests.

For now, we’ve disabled prepared_statements after monkey-patching some fixes from 4-0-STABLE (see #12023)

/cc @tenderlove since I think you’ve done some of the PG prepared statement support?