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 -- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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 On Sep 13, 2011, at 11:10 AM, Juan Manuel Cuello wrote:> 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-- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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.
On Tue, Sep 13, 2011 at 12:17 PM, Ken Collins <ken@metaskills.net> wrote:
>
> 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
>
> On Sep 13, 2011, at 11:10 AM, Juan Manuel Cuello wrote:
>
> > 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
>
> --
> 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 rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-core?hl=en.
>
>
--
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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
I''m interested to see how this would affect my gem<https://github.com/bradrobertson/apartment> 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. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
I created a pull request: https://github.com/rails/rails/pull/3232 That patch works for me. On Wed, Oct 5, 2011 at 10:32 AM, Bradley <bradleyrobertson@gmail.com> wrote:> I''m interested to see how this would affect my gem<https://github.com/bradrobertson/apartment> 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. > > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. >-- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.