Prepared statements and postgreSQL schemas

Hi guys.

I’m using a postgreSQL with multiple schemas. To change between schemas I use the ‘SET schema TO …’ statement. That works ok in rails 3.0.

Rails 3.1 uses prepared statements and does not take into account the ‘schema_search_path’. This is a big problem because the statement is prepared once and then the same prepared statement is executed many times no matter if you changed the schema, i.e, the prepared statement is executed ALWAYS for the same schema, the one set when the statement was prepared.

I migrated yesterday from 3.0 to 3.1 and ended up with all my new data (INSERT operations) into the same schema.

‘exec_cache’ method in postgresql_adapter.rb uses the sql string as the cache key. Maybe we could include also the ‘schema_search_path’.

I didn’t want to go into details here, but I could create an issue or a pull request if this approach seems reasonably.

Juan

Perhaps if you do something like change schemas, you should clear the caches which deallocates the statements too. If I am reading the code right, ActiveRecord's #reset_column_information will go down to the connection and call #clear_cache! and hence that will deallocate the statements from there. Note, I do not use postgresql, but thought this may be helpful.

- Ken

Yes, that should do the trick, but that workaround could be avoided if we include the current schema as part of the statement’s key.

Something like:

postgresql_adapter.rb

def exec_cache(sql, binds)

sql_key = “#{schema_search_path}-#{sql}”

unless @statements.key? sql_key

nextkey = @statements.next_key

@connection.prepare nextkey, sql

@statements[sql_key] = nextkey

end

end

Single schema applications shouldn’t be affected. Multiple schema applications will still be able to use prepared statements without the need to clear the cache and loose this benefit.

I’m interested to see how this would affect my gem for multi-tenancy in Rails. I haven’t had a chance to try out Rails 3.1 yet unfortunately, but by the sounds of it, this would totally break it. Have you issued a pull request or talked to the Core team about this at all?

I created a pull request:

https://github.com/rails/rails/pull/3232

That patch works for me.