[Twisted-web] my first post, writing a Web interface to a PostgreSQL db

[Valentino Volonghi aka Dialtone]

On Fri, Oct 14, 2005 at 10:34:36AM +0000, Michele Simionato wrote:

Hi Michele :),

> I need to write a Web interface to a PostgreSQL database and I am
> considering to use Twisted for that. So, I have a few questions.

Let's see if I can answer all of them.

> First of all, I would like to know if there already something available
> that I should look at. Second, I have a more specific question.

Depends on what 'Something' means in your view.
There are 2 projects that I know of that work with twisted and databases (or
postgresql) that are worth a try.

The first one is called Tada (Twisted Async Database API):
http://www.darkarts.co.za/projects/tada/

which is a new version of adbapi that instead of incredibly ignoring DB-API2
it makes it so that each DB-API2 method call returns a deferred. It also has
an SQLObject-like way of dealing with the database that may be useful.

Another effort is pgasync:
svn://svn.jamwt.com/pgasync/trunk

pgasync is a pure python database module that uses Twisted to implement the
PostgreSQL protocol. It tries to be DB-API2 compliant although failing in some
cases (not hard to fix but requires a bit of time that you may not have). Like
Tada it returns deferreds from DB-API2 method calls, but manages to do some
magic that avoids returning a deferred in some places.

The last one is the standard abapi module that while maybe not beautiful it is
indeed very simple and well tested in real life.

> Suppose I have a large table with 1,000,000 records and a SELECT query
> that returns 1000 results and takes 10 minutes.
> 
> I don't want my users to wait. I want to display the result of the
> query as an HTML table of 50 rows. The user should click, wait
> 30 seconds, see the result, then click a 'next' button to see
> the other 50 results, etc. In other words, I am looking for a lazy query
> functionality.

Lazy query functionality requires you to store the cursor that run the query
somewhere (togheter with the connection from which it was created) so that you
can use fetchmany() on it. There is no other way that comes to my mind.
I haven't experimented with this kind of jobs with any of the above, but I can
guess that adbapi is not the right tool for the object, whereas pgasync or
tada might be the right starting point (although they might reveal to be the
wrong approach too).

> Is that already available? If not, is it easy/difficult to implement?

It shouldn't be hard to implement as long as you don't need to migrate the
cursor and the connection to some other machine.

The idea that comes to mind is that you can store the connection with the
cursor in the session (or something like that, maybe the session is not the
right place but depends on the framework you are using) and reuse them for
each user. Of course if you are in a load balanced environment then you need
to be able to 'migrate' those connection from one computer to another one
(unless you are using stateful load balancers) which is extremely hard.

Another solution could be to take advantage of OFFSET and LIMIT keywords in
SQL to avoid calculating the whole 1000 results but only limiting the
resultset to the strictly necessary thus lowering response times.

Hope I've been helpful :)

-- 
Valentino Volonghi aka Dialtone
Now Running MacOSX 10.4
Blog: http://vvolonghi.blogspot.com
http://weever.berlios.de
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 186 bytes
Desc: not available
Url : http://twistedmatrix.com/pipermail/twisted-web/attachments/20051014/904fdb0c/attachment.bin