Re: Suspending SELECTs

Alessandro Baretta <a(dot)baretta(at)barettadeit(dot)com> writes:
>I am aware that what I am dreaming of is already available through
>cursors, but in a web application, cursors are bad boys, and should be
>avoided. What I would like to be able to do is to plan a query and run
>the plan to retreive a limited number of rows as well as the
>executor's state. This way, the burden of maintaining the cursor "on
>hold", between activations of the web resource which uses it, is
>transferred from the DBMS to the web application server,

I think you're trying to do something at the wrong layer of your architecture. This task normally goes in your middleware layer, not your database layer.

There are several technologies that allow you to keep persistent database sessions open (for example, mod_perl, mod_cgi among others). If you combine these with what's called "session affinity" (the ability of a load-balancing server to route a particular user back to the same persistent session object every time), then you can create a middleware layer that does exactly what you need.

Basically, you create a session object that holds all of the state (such as your cursor, and anything else you need to maintain between requests), and send back a cookie to the client. Each time the client reconnects, your server finds the user's session object using the cookie, and you're ready to go.

The main trick is that you have to manage your session objects, primarily to flush the full state to the database, if too much time elapses between requests, and then be able to re-create them on demand. Enterprise Java Beans has a large fraction of its design devoted to this sort of object management.

There are solutions for this in just about every middleware technology, from Apache/perl to EJB to CORBA. Search for "session affinity" and you should find a lot of information.

Craig