[RFC] libpq extensions - followup - Mailing list pgsql-general

Hello everyone,

A while back I suggested that it would be useful if asynchronous
connections could support multiple queries "in flight", especially if
you're working on event driven applications (see topic "rfc - libpq
extensions"). I've since started work on a simple library, which I
call libpqx, that I think could potentially provide a nice alternative
to libpq. I was hoping the folks on the list could discuss whether such
a library would be useful and if so, provide some feedback regarding its
design. The library code is very much in its infancy but I believe it
provides a reasonable foundation that can be easily extended. The
library source tarball can be found on my home page:

http://www.arizmendi.org

Please keep in mind that the current code is only meant to demonstrate
the feasibility of its design - it's still full of "TODO"s, "assert"s
and "abort"s.

Cheers,
Iker

DESIGN CRITERIA:
===========

Here are some of the things I thought the library should do, and that
I tried to address:

1) Support for event driven and threaded programs.

2) Multiple "in flight" queries on asynchronous connections must be
permitted. For event driven applications this is key.

3) Support connection pooling for event-driven and threaded programs.
After getting started on (2), I realized this was also necessary. One of
the reasons to write event driven servers is to support a larger number
of clients than you otherwise would with a process-per-client or
thread-per-client architecture. Without connection pooling an event
driven architecture doesn't help - it only moves the problem to the DB
server (which will spawn a process for each connection).

4) Easy to use interface. I used libpq as a starting model but made
several changes and added two new abstractions - connection pool
(PGXpool) and query (PGXquery). The latter is necessary to support (2)
and it also provides a foundation for parameterized queries (eg, "DELETE
FROM TableA WHERE x= ?"). It might also serve to hide the details of
bytea encoding.

5) Thread safe. For event driven applications this isn't an issue
(except on SMP machines - this needs to be addressed). In threaded
programs, multiple threads can share a common connection pool (although
they must each have their own PGXconn object).

IMPLEMENTATION:
===========

Most of the coding is pretty straight-forward (at least I tried to make
it so) but there are two implementation details that should be noted:

1) Connection pooling and event driven servers.
When implementing an event driven server on *NIX you typically have a
poll/select event loop that listens for activity on a range of file
descriptors. Without connection pooling one establishes a connection to
the backend and uses the socket descriptor to listen on. However, in a
pooled implementation a connection may not actually be backed by a
socket until the pool connection count goes positive. In this case the
client will have nothing to register in its event loop. To deal with
this issue, the PGXconn object uses a pipe to the connection pool to
receive availability notifications - an "eventable" descriptor is thus
always available to give to clients.

2) Explicit state machine implementation.
The PGXconn object relies on the use of explicit machine states to
get all its work done (synchronous operations get there own special
state). Each machine states implements a common set of methods
(defined in a struct of function pointers) in a separate file which
keeps the code layout clean. At the moment I implemented states that
mimic the way libpq does its thing but I think there's potential to
cleanly provide additional functionality (non-blocking query writes, for
instance) by simply adding new states. P.S. My implementation of a state
machine is probably pretty naive since I only recently started using
them aggressively - any suggestions here would be much appreciated.

3) Wrapping of libpq.
During this first cut, I leveraged libpq as much as possible but
in the future it may be worth while for libpqx to wean itself off
the libpq API and instead use more of its underlying code.