Prepared statements and postgreSQL schemas

595 views
Skip to first unread message

Juan Manuel Cuello

unread,
Sep 13, 2011, 11:10:28 AM9/13/11
to rubyonra...@googlegroups.com
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

Ken Collins

unread,
Sep 13, 2011, 11:17:49 AM9/13/11
to rubyonra...@googlegroups.com

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

Juan Manuel Cuello

unread,
Sep 13, 2011, 4:55:42 PM9/13/11
to rubyonra...@googlegroups.com
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.


--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-co...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.


Bradley

unread,
Oct 5, 2011, 9:32:54 AM10/5/11
to rubyonra...@googlegroups.com
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?

Juan Manuel Cuello

unread,
Oct 5, 2011, 11:45:29 AM10/5/11
to rubyonra...@googlegroups.com
I created a pull request:
On Wed, Oct 5, 2011 at 10:32 AM, Bradley <bradleyr...@gmail.com> wrote:
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?

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/JQj4MB_lhRMJ.
Reply all
Reply to author
Forward
0 new messages