We're using SQLAlchemy sharding to partition accounts across a couple
of databases. We want to add more partitions, but first we need to
eliminate some unnecessary cross-partition queries.
class FindShardableId(sqlalchemy.sql.ClauseVisitor):
def __init__(self, ids, key_fields, get_shard):
self.ids = ids
self.key_fields = key_fields
self.get_shard = get_shard
def _check_side(self, binary, side, other_side):
has_constant = (isinstance(side, sqlalchemy.Column)
and side.name in self.key_fields
and binary.operator ==
sqlalchemy.sql.operators.eq
and getattr(other_side, "value", None))
if has_constant:
self.ids.append(self.get_shard(other_side.value))
return has_constant
def visit_binary(self, binary):
if not self._check_side(binary, binary.left,
binary.right):
# Lazy load properties tend to be reversed, with the
constant on the left
self._check_side(binary, binary.right, binary.left)
def query_chooser(query):
ids = []
if query._criterion is not None:
FindShardableId(
ids,
set(["account_id", "account_guid"]),
lambda account_id:
shard_manager.shard_id_from_guid(account_id)
).traverse(query._criterion)
if len(ids) == 0:
logging.warn("\n\nExecuting query against all shards; "
"this may not be optimal:\n
\t{0}".format(str(query)))
return shards.keys()
else:
return ids
This works well most of the time, but we're finding that some queries
do not have a "value". These are all of the form
SELECT shopping_list_items.version AS shopping_list_items_version
FROM shopping_list_items
WHERE shopping_list_items.account_id = :param_1
AND shopping_list_items.shopping_list_item_id = :param_2
and :param1 is of the form _BindParamClause(u'%(63636624 param)s',
None, type_=UUID())
Typically, I'm seeing this come out of the innards of SQLAlchemy,
as one of several queries triggered by, say, a session.merge().
How do we work around this?
Thanks!
/George V. Reilly, Seattle
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
