connection caching using where and postgres

[lcowell]

Hi I'm having an issue when using 'where' in a query. It appears to be returning the same results for each subsequent query on different database connections. For example:

%W{company_1 company_2}.map {|n| Apartment::Database.switch(n); User.where(:id => 100) }
# => #<User id: 100 name: "bob"> #<User id: 100 name: "bob"> 

However if I write the query using find_by_id, I get the results I expect having different names:

%W{company_1 company_2}.map {|n| Apartment::Database.switch(n); User.find_by_id(100) }
# => #<User id: 100 name: "bob"> #<User id: 100 name: "alan"> 

I have also tried calling Apartment.connection.clear_query_cache after each switch which doesn't affect the outcome. I've tried this with:

apartment 0.21.0
pg 0.14.0
rails 3.2.11

Is anyone able to reproduce this issue ? I'll try updating these gems and report back.

[lcowell]

I've updated to the following without any change in behaviour.

pg 0.15.1
rails 3.2.13

I also add that it doesn't seem to be a problem if you're calling 'where' through a scope.

[lcowell]

If I issue the same commands without the use of a block, I get the results I expect. eg.

Apartment::Database.switch("company_3")
User.where(:id => 100)
Apartment::Database.switch("company_4")
User.where(:id => 100)

I'm so confused it hurts. Why would running these command by iterating over a list have different behaviour than executing the commands sequentially ?

[bradrobertson]

weeeird... I've definitely never seen that. We have quite a bit of code like that to loop through all tenants and do stuff with them, never had a problem.

I'll see if I can replicate it sometime this week and get back to you.

[bradrobertson]

ah ok, I've been able to reproduce your problem, but it's just a problem with using the console and the way ActiveRecord::Relation's work.

When you say User.where(id: 1) you're not actually querying the database. You're just specifying criteria for a query. The actual query gets made at the moment that you want the data (so in your looping instance, it wants the return value)

The problem is, it loops once, builds a relation, loops again, builds a relation, then when it returns those two relations (and hence actually queries the db), they're within the scope of the 2nd Apartment Tenant

If you were do do something like:

%w{company_1 company_2}.map {|n| Apartment::Database.switch(n); User.where(id: 1).first }

Then it would work properly. That's why your find_by_id works.

Just remember that where is lazy, so you only actually make the query once the data is needed.

[lcowell]

Thanks for the explanation. That makes perfect sense.