[sqlalchemy] something wrong with relationship caching at _trunk_
Hello!
I have a problem with my relationship caching with 0.7b1 (current
trunk)
When I perform query on `User` model with `username` param, than
access some lazy and cached separaterly from main query relationship -
`groups`.
After that I exec the same query on `User` model with another
`username` param and access `groups` relationship I got the same
groups as with first query and no sql being executed to get those
groups...
The same code works correctly with 0.6.5.
To reproduce:
% sudo invoke-rc.d memcached restart
[sudo] password for sector119:
Restarting memcached: memcached.
%
# sacache.py
from sqlalchemy import create_engine
from eps.model import init_model
from eps.model import meta
from eps.model import caching_query as cache
from eps.model import cache_user_relationships
from eps.model import User
from eps.model import SYSTEM_SCHEMA
def get_user(username):
user = meta.Session.query(User).\
options(cache_user_relationships).\
options(cache.FromCache('default',
'by_username')).\
filter_by(username=username,
disabled=False).first()
meta.Session.connection().execute('SET search_path TO {0},
{1}'.format(SYSTEM_SCHEMA,
user.locality.schema))
return user
def print_groups(user):
for g in u.groups:
print g.name
engine = create_engine('postgresql+psycopg2://
eps:mypassw...@127.0.0.1:5432/eps', echo=True)
init_model(engine)
u = get_user('sector119')
print '1. %s groups:' % u.username
print_groups(u)
print '2. %s groups:' % u.username
print_groups(u)
u = get_user('privat')
print '1. %s groups:' % u.username
print_groups(u)
print '2. %s groups:' % u.username
print_groups(u)
Output:
1. sector119 groups:
wheel
2. sector119 groups:
wheel
1. privat groups:
wheel
2. privat groups:
wheel
Echoing SQL:
2010-12-21 15:50:38,273 INFO sqlalchemy.engine.base.Engine select
version()
2010-12-21 15:50:38,274 INFO sqlalchemy.engine.base.Engine {}
2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine select
current_schema()
2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine {}
2010-12-21 15:50:38,276 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
# FIRST user
2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine SELECT
system.users.id AS system_users_id, system. users.username AS
system_users_username, system.users.password AS system_users_password,
system.users.first_name AS system_users_first_name,
system.users.last_name AS system_users_last_name, system.users.
middle_name AS system_users_middle_name, system.users.locality_id AS
system_users_locality_id, system.users. office_id AS
system_users_office_id, system.users.email AS system_users_email,
system.users.create_date ASsystem_users_create_date,
system.users.last_login AS system_users_last_login,
system.users.expire AS system_users_expire,
system.users.disabled AS system_users_disabled
FROM system.users
WHERE system.users.username = %(username_1)s AND system.users.disabled
= %(disabled_1)s
LIMIT %(param_1)s OFFSET %(param_2)s
2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine {'param_1':
1, 'disabled_1': False, 'username_1': 'sector119', 'param_2': 0}
2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine SELECT
system.localities.id AS system_localities_id,
system.localities.name AS system_localities_name,
system.localities.type AS system_localities_type,
system.localities.schema AS system_localities_schema
FROM system.localities
WHERE system.localities.id = %(param_1)s
2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine {'param_1':
1}
2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine SET
search_path TO system,ternopil
2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine {}
# FIRST user GROUPS
2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine SELECT
system.groups.id AS system_groups_id, system.groups.name AS
system_groups_name
FROM system.groups, system.users_groups
WHERE %(param_1)s = system.users_groups.user_id AND system.groups.id =
system.users_groups.group_id
2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine {'param_1':
2}
# NEXT user
2010-12-21 15:50:38,289 INFO sqlalchemy.engine.base.Engine SELECT
system.users.id AS system_users_id, system. users.username AS
system_users_username, system.users.password AS system_users_password,
system.users.first_name AS system_users_first_name,
system.users.last_name AS system_users_last_name, system.users.
middle_name AS system_users_middle_name, system.users.locality_id AS
system_users_locality_id, system.users. office_id AS
system_users_office_id, system.users.email AS system_users_email,
system.users.create_date ASsystem_users_create_date,
system.users.last_login AS system_users_last_login,
system.users.expire AS system_users_expire,
system.users.disabled AS system_users_disabled
FROM system.users
WHERE system.users.username = %(username_1)s
Re: [sqlalchemy] something wrong with relationship caching at _trunk_
I couldn't begin to know what the issue is with code fragments like this.
Though your print_groups() function here is wrong:
def print_groups(user):
for g in u.groups:
print g.name
On Dec 21, 2010, at 9:15 AM, sector119 wrote:
Hello!
I have a problem with my relationship caching with 0.7b1 (current
trunk)
When I perform query on `User` model with `username` param, than
access some lazy and cached separaterly from main query relationship -
`groups`.
After that I exec the same query on `User` model with another
`username` param and access `groups` relationship I got the same
groups as with first query and no sql being executed to get those
groups...
The same code works correctly with 0.6.5.
To reproduce:
% sudo invoke-rc.d memcached restart
[sudo] password for sector119:
Restarting memcached: memcached.
%
# sacache.py
from sqlalchemy import create_engine
from eps.model import init_model
from eps.model import meta
from eps.model import caching_query as cache
from eps.model import cache_user_relationships
from eps.model import User
from eps.model import SYSTEM_SCHEMA
def get_user(username):
user = meta.Session.query(User).\
options(cache_user_relationships).\
options(cache.FromCache('default',
'by_username')).\
filter_by(username=username,
disabled=False).first()
meta.Session.connection().execute('SET search_path TO {0},
{1}'.format(SYSTEM_SCHEMA,
user.locality.schema))
return user
def print_groups(user):
for g in u.groups:
print g.name
engine = create_engine('postgresql+psycopg2://
eps:mypassw...@127.0.0.1:5432/eps', echo=True)
init_model(engine)
u = get_user('sector119')
print '1. %s groups:' % u.username
print_groups(u)
print '2. %s groups:' % u.username
print_groups(u)
u = get_user('privat')
print '1. %s groups:' % u.username
print_groups(u)
print '2. %s groups:' % u.username
print_groups(u)
Output:
1. sector119 groups:
wheel
2. sector119 groups:
wheel
1. privat groups:
wheel
2. privat groups:
wheel
Echoing SQL:
2010-12-21 15:50:38,273 INFO sqlalchemy.engine.base.Engine select
version()
2010-12-21 15:50:38,274 INFO sqlalchemy.engine.base.Engine {}
2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine select
current_schema()
2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine {}
2010-12-21 15:50:38,276 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
# FIRST user
2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine SELECT
system.users.id AS system_users_id, system. users.username AS
system_users_username, system.users.password AS system_users_password,
system.users.first_name AS system_users_first_name,
system.users.last_name AS system_users_last_name, system.users.
middle_name AS system_users_middle_name, system.users.locality_id AS
system_users_locality_id, system.users. office_id AS
system_users_office_id, system.users.email AS system_users_email,
system.users.create_date ASsystem_users_create_date,
system.users.last_login AS system_users_last_login,
system.users.expire AS system_users_expire,
system.users.disabled AS system_users_disabled
FROM system.users
WHERE system.users.username = %(username_1)s AND system.users.disabled
= %(disabled_1)s
LIMIT %(param_1)s OFFSET %(param_2)s
2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine {'param_1':
1, 'disabled_1': False, 'username_1': 'sector119', 'param_2': 0}
2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine SELECT
system.localities.id AS system_localities_id,
system.localities.name AS system_localities_name,
system.localities.type AS system_localities_type,
system.localities.schema AS system_localities_schema
FROM system.localities
WHERE system.localities.id = %(param_1)s
2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine {'param_1':
1}
2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine SET
search_path TO system,ternopil
2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine {}
# FIRST user GROUPS
2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine SELECT
system.groups.id AS system_groups_id, system.groups.name AS
system_groups_name
FROM system.groups, system.users_groups
WHERE %(param_1)s = system.users_groups.user_id AND system.groups.id =
system.users_groups.group_id
2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine {'param_1':
2}
# NEXT user
2010-12-21 15:50:38,289 INFO sqlalchemy.engine.base.Engine SELECT
system.users.id AS system_users_id, system. users.username AS
system_users_username, system.users.password AS system_users_password,
system.users.first_name AS system_users_first_name,
system.users.last_name AS system_users_last_name, system.users.
middle_name AS system_users_middle_name, system.users.locality_id AS
