Re: Suspending SELECTs - Mailing list pgsql-performance

On Mon, 2006-01-16 at 11:13 +0100, Alessandro Baretta wrote:
> 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, and, most
> importantly, the responsibility for garbage-collecting stale cursors is
> implicitely delegated to the garbage-collector of active user sessions. Without
> this mechanism, we are left with two equally unpleasant solutions: first, any
> time a user instantiates a new session, a new cursor would have to be declared
> for all relevant queries, and an ad-hoc garbage collection daemon would have to
> be written to periodically scan the database for stale cursors to be closed;
> otherwise, instead of using cursors, the web application could resort to
> OFFSET-LIMIT queries--no garbage collection issues but pathetic performance and
> server-load.
>
> Do we have any way out?
>
> Alex

I know that Tom has pretty much ruled out any persistent cursor
implementation in the database, but here's an idea for a workaround in
the app:

Have a pool of connections used for these queries.  When a user runs a
query the first time, create a cursor and remember that this user
session is associated with that particular connection.  When the user
tries to view the next page of results, request that particular
connection from the pool and continue to use the cursor.  Between
requests, this connection could of course be used to service other
users.

This avoids the awfulness of tying up a connection for the entire course
of a user session, but still allows you to use cursors for
performance.

When a user session is invalidated or times out, you remove the mapping
for this connection and close the cursor.  Whenever there are no more
mappings for a particular connection, you can use the opportunity to
close the current transaction (to prevent eternal transactions).

If the site is at all busy, you will need to implement a pooling policy
such as 'do not open new cursors on the connection with the oldest
transaction', which will ensure that all transactions can be closed in a
finite amount of time, the upper bound on the duration of a transaction
is (longest_session_duration * connections in pool).

Limitations:

1. You shouldn't do anything that acquires write locks on the database
using these connections, because the transactions will be long-running.
To mitigate this, use a separate connection pool.

2. Doesn't work well if some queries take a long time to run, because
other users may need to wait for the connection, and another connection
won't do.

3. If this is a busy web site, you might end up with potentially many
thousands of open cursors.  I don't know if this introduces an
unacceptable performance penalty or other bottleneck in the server?

-- Mark Lewis