README.async

$Id$

Experimental Tcl interface to PostgreSQL asynchronous query processing

        by Karl Lehenbauer (karl-pg@sc.com) 10/2002 (revised 6/2004)

RATIONALE

From the C-interface docs:

The PQexec function is adequate for submitting commands in simple 
synchronous applications. It has a couple of major deficiencies however:

    * PQexec waits for the command to be completed. The application may 
      have other work to do (such as maintaining a user interface), in which 
      case it won't want to block waiting for the response.

    * Since control is buried inside PQexec, it is hard for the frontend 
      to decide it would like to try to cancel the ongoing command. (It 
      can be done from a signal handler, but not otherwise.)

    * PQexec can return only one PGresult structure. If the submitted 
      command string contains multiple SQL commands, all but the last 
      PGresult are discarded by PQexec.

WHAT THIS IS

A handful of new pg_* commands have been added to support asynchronous
operation, including cancelling requests that are currently being
processed and obtaining results from each SQL command when a
query contains multiple commands.

SOMEWHAT EXPERIMENTAL IN NATURE

This is a new Tcl interface to asynchronous query processing capabilities that
have been made available through the Postgres C interface.

We're calling it experimental because we think we'll want to evolve and change
the interface, perhaps simplifying it, as we gain experience with it.  So
if you use it, understand that we are not promising to provide the same
interface or backwards compatibility to this interface in future releases.

ASYNCHRONOUS QUERY PROCESSING COMMANDS

    pg_sendquery connection query

This works like pg_exec, except that the query is issued asynchronously
and pg_sendquery returns immediately without providing a result handle.

With Postgres 7.4 and above, you can also do variable substitution.

    pg_sendquery connection query var1 var2 ...

...for example...

    pg_sendquery $conn {insert into people values ($1, $2, $3, $4, $5);} \
        $name $address $city $state $zip

You can also execute prepared statements:

    pg_exec $conn {prepare insert_people (varchar, varchar, varchar, varchar, varchar) as insert into people values ($1, $2, $3, $4, $5);}

    pg_sendquery_prepared $conn insert_people $name $address $city $state $zip

To get result handles resulting from the execution of pg_sendqery (and there 
may be more than one if there are multiple SQL commands in the query), you 
need to repeatedly call

    pg_getresult connection

This will return the same sort of result handle that pg_exec returns.

If there is no query currently being processed or all of the results have
been obtained, pg_getresult returns nothing.


    pg_isbusy connection

pg_getresult can block if results aren't yet available.  To avoid this,
you can use pg_isbusy to check to see if the connection is busy processing
a query.

If this returns 1, pg_getresult will block if called.  If it's 0, you can
safely call pg_getresult and it won't block.

    pg_blocking

This sets whether a connection is set for blocking or nonblocking, and
allows that state to be changed.

 syntax:
 pg_blocking connection - returns the current state, 1 = blocking, 0 = non
 pg_blocking connection 1 - sets the connection to blocking
 pg_blocking connection 0 - sets the connection to nonblocking

Note - I'm not sure about all of the ramifications of setting a connection
nonblocking.  Even with a connection in the (default) blocking state, 
pg_isbusy seems to work OK and can be used in conjunction with pg_getresult
to keep from blocking while processing query results.


    pg_cancelrequest connection

This request that postgresql abandon processing of the current command
issued via pg_sendquery.

There is no guarantee that the request will be cancelled.  If it is and
you were in the middle of a transaction, the entire transaction  is cancelled.

You still need to call pg_getresult repeatedly until it doesn't return
anything, and handle (and discard) all of the returned result handles.


HOW TO USE IT

We really need some example code.  Probably we need some Tcl code that will
be part of the libary, pulled in with "package require Pgtcl", that will
issue a pg_sendrequest and iteratively call pg_isbusy on a timer, then
looping through passed-in Tcl code for each result until none are found.

You'll want to write something that issues the request via pg_sendquery.
Then you'll want a proc that does a pg_isbusy and if it is busy, calls
itself to run again after, oh, a tenth of a second or so, via "after".
If pg_isbusy returns 0, you can safely call pg_getresult to get a result.
(Use pg_result to examine the result, as in the past.)  If pg_getresult
returns an empty string, there is no more work to be done.

If you want to cancel a request that is currently in progress, use
pg_cancelrequest.  Note that you still need to do the pg_getresult
thing repeatedly until it returns nothing.  For more information, read
the C interface docs in the PostgreSQL documentation.

I know you'd like some example code.  We don't have any yet.  That's why we 
call it an alpha release.

LICENSE

Berkeley License.  Freely redistributable for any use including resale,
without royalty or other sucky GPL restrictions.  Don't sue us if it
kills your dog.