Caching DAL Selects - only cache.ram works?

[Brian M]

I've been playing with caching and looking through the mailing list and have a bit of confusion about whether or not you can actually cache DAL Selects with anything other than cache.ram due to issues with what can/cannot be pickled. When I use

db(...).select(...,cache=(cache.disk, 300), cacheable=True)

Caching works just fine. But if I try it with cache.disk or cache.redis instead then I get errors which seem to be related to not being able to unpickle the cached select results.

type 'exceptions.TypeError'> ("cannot create 'pyodbc.Row' instances", <type 'pyodbc.Row'>, ....)

This, admittedly old, post seems to suggest that cache.redis should be pretty much drop-in for cache.ram but that's not my experience. https://groups.google.com/forum/?fromgroups#!searchin/web2py/caching$20model$20data/web2py/Tn4TiLPScII/jtMR1KtT4V8J

So does the ability to cache DAL Selects really only exist for cache.ram or am I missing something?

Brian

[Anthony]

It should work. Could be a bug specific to using pyodbc. Have you tried it with other databases/drivers? Feel free to file a PyDAL issue.

Anthony

[Anthony]

You can also see what happens if you do not set cacheable=True.

[Brian M]

Further investigation shows that cache.redis plays nicely with pyodbc as long as you have all uniquely named fields in your select. The selects that I was working with involved multiple tables that each had a "Name" field that was being selected. If I add .with_alias() to provide unique names then caching worked but that's also not really convenient. Is this the way it is supposed to work?

[Brian M]

So it caching selects to disk or redis supposed to only work if you have uniquely named fields? It doesn't seem to make a difference if I set cachable=True or not.

[Massimo Di Pierro]

please post a minimal model to reproduce the problem.

[Brian M]

Massimo,

Sure, just downloaded the nightly source zip and managed to reproduce with a simple app. Note it does seem to have to be using pyodbc & MS SQL Server, it doesn't happen if you're using SQLite.

Model

db.define_table('person',
                Field('name', 'string', notnull=True)
                )

db.define_table('pet',
                Field('name', 'string', notnull=True),
                Field('person_id', 'reference person', notnull=True)
                )

Controllers

def setup():
    # initial population of database
    db(db.pet.id>0).delete()
    db(db.person.id>0).delete()
    person_id = db.person.insert(name='Charlie Brown')
    db.pet.insert(name='Snoopy', person_id=person_id)
   
    person_id = db.person.insert(name='John Arbuckle')
    db.pet.insert(name='Garfield', person_id=person_id)
    db.pet.insert(name='Odie', person_id=person_id)
   
    pet_owners = db(db.pet.person_id==db.person.id).select()
    return dict(pet_owners=pet_owners)

def RAMcache():
    pet_owners = db(db.person.id > 0).select(
        db.person.id,
        db.person.name,
        db.pet.id,
        db.pet.name,
        join=[
            db.pet.on(db.person.id==db.pet.person_id)
            ],
        cache=(cache.ram, 3600), cacheable=True
    )
   
    return dict(pet_owners=pet_owners, last_sql=last_sql)

def DISKcache_Failing():
    #Fails because multiple fields with same name (id & name)
    pet_owners = db(db.person.id > 0).select(
        db.person.id,
        db.person.name,
        db.pet.id,
        db.pet.name,
        join=[
            db.pet.on(db.person.id==db.pet.person_id)
            ],
        cache=(cache.disk, 3600), cacheable=True
    )

    return dict(pet_owners=pet_owners)

def DISKcache_Working():
    # Works find since all unique field names
    pet_owners = db(db.person.id > 0).select(
        db.person.id,
        db.person.name,
        db.pet.id.with_alias('pet_id'),
        db.pet.name.with_alias('pet_name'),
        join=[
            db.pet.on(db.person.id==db.pet.person_id)
            ],
        cache=(cache.disk, 3600), cacheable=True
    )
   
    return dict(pet_owners=pet_owners)

Attempting to load the DISKCache_Failing controller a second time results in an error:

<type 'exceptions.TypeError'> ("cannot create 'pyodbc.Row' instances", <type 'pyodbc.Row'>, (((u'id', <type 'int'>, None, 10, 10, 0, False), (u'name', <type 'str'>, None, 512, 512, 0, False), (u'id', <type 'int'>, None, 10, 10, 0, False), (u'name', <type 'str'>, None, 512, 512, 0, False)), {u'id': 2, u'name': 3}, 1, u'Charlie Brown', 1, u'Snoopy'))

Version

web2py™	Version 2.16.1-stable+timestamp.2017.11.14.05.54.25

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22.

Traceback (most recent call last): File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\restricted.py", line 219, in restricted exec(ccode, environment) File "C:/Users/Brian/Documents/source_code/web2py_src/web2py/applications/caching_demo/controllers/cache_demo.py", line 48, in <module> File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\globals.py", line 419, in <lambda> self._caller = lambda f: f() File "C:/Users/Brian/Documents/source_code/web2py_src/web2py/applications/caching_demo/controllers/cache_demo.py", line 43, in DISKcache cache=(cache.disk, 3600), cacheable=True File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\packages\dal\pydal\objects.py", line 2250, in select return adapter.select(self.query, fields, attributes) File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\packages\dal\pydal\adapters\base.py", line 761, in select cache, sql, fields, attributes, colnames) File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\packages\dal\pydal\adapters\base.py", line 752, in _cached_select time_expire) File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\cache.py", line 444, in __call__ item = self.storage.get(key) File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\cache.py", line 401, in get return self[key] File "C:\Users\Brian\Documents\source_code\web2py_src\web2py\gluon\cache.py", line 350, in __getitem__ value = pickle.load(val_file) TypeError: ("cannot create 'pyodbc.Row' instances", <type 'pyodbc.Row'>, (((u'id', <type 'int'>, None, 10, 10, 0, False), (u'name', <type 'str'>, None, 512, 512, 0, False), (u'id', <type 'int'>, None, 10, 10, 0, False), (u'name', <type 'str'>, None, 512, 512, 0, False)), {u'id': 2, u'name': 3}, 1, u'Charlie Brown', 1, u'Snoopy'))

While running DISKCache_Working multiple times works just fine. The difference seems to be that the version that fails ends up with two "id" and two "name" fields while the version that works has all unique field names.

pet_owners

:

person.id person.name pet_id pet_name 1 Charlie Brown 1 Snoopy 2 John Arbuckle 2 Garfield 2 John Arbuckle 3 Odie

I've created an issue on GitHub too https://github.com/web2py/web2py/issues/1915