3

I'm building an app on Pyramid framework and would like to use the web2py-DAL with it. Firebird-embedded is the database of choice.

That works great until I try to call multiple views from one web page asynchronously. Different errors like "Invalid cursor state", "Invalid cursor reference" or "Attempt to reclose a closed cursor" are coming up from the kinterbasdb driver where sqlite just breaks down without any error messages and takes python with it. These view callables are doing nothing but simple reads by SELECTing.

This happens in the case that pyramids root factory returns the same DAL object with each request. It seems like threads form different requests are working with the same cursor object, so the cursor gets closed while the other thread assumes the cursor to be here.

If I create a new DAL object on each request I get an other problem - every new connection on each request allocates memory and this memory doesn't get free. So after some docents requests there are some hundred MB wasted memory.

Unfortunately Sqlalchemy is not an option for this project.

Are there any ideas?

Gleb Sternharz
  • 116
  • 7
  • Why are you not using the full server version in this web environment? Embedded means this will run in-process, each one with exclusive access to the database. IMHO this explains why everything works until two different requests forces the server to try to open the database concurrently. The same will happen if a second request comes from different client while the server still is processing the first. – jachguate Apr 04 '11 at 17:43
  • You are better off asking this directly on the web2py mailing list. – Graham Dumpleton Apr 04 '11 at 23:11
  • @jachguate The problem is not the firebird - all the issues stay if i use the server version too. I have found that the DAL object cannot be shared around threads. I could create a new DAL object at the start of each thread and close it after. Now the question - where does a pyramid request thread end? Where is the right place to delete the DAL object? Another option would be to build a stack for db requests and then pass the db requests to the DAL object from a single thread... @Graham-Dumpleton Right. Or the Pyramid. I'll post my solution here if it is there. – Gleb Sternharz Apr 05 '11 at 10:51

2 Answers2

2

The point was that the DAL object should be created on every request. But after that it must be closed manually.

I did it in that way: There is a

request.add_finished_callback

property of the request object, so I extended the DAL object in that way:

class Root(DAL):
    def __init__(self, request, uri):
        DAL.__init__(self, uri, pool_size=0)
        request.add_finished_callback(self._close)

    def _close(self, request):
        self._adapter.close_all_instances('commit')

A new Root-object is then returned by the root_factory on each request.

Thanks to the web2py-users group!

Gleb Sternharz
  • 116
  • 7
1

Some solutions from the web2py mailing list.

Anthony
  • 25,201
  • 3
  • 23
  • 54
  • Yea! I'v posted the question there some hours ago, and now there are already very useful approaches. web2py-users group is great. – Gleb Sternharz Apr 06 '11 at 18:04