Thread: [RFC] libpq extensions - followup
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.
Iker Arizmendi <iker@research.att.com> writes:
> 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,
I must be missing something fundamental here. The backend doesn't
support multiple parallel queries, so how can you have "multiple queries
in flight" on the same connection?
regards, tom lane
Althought multiple queries can't be executed concurrently they can be
queued up by a connection object. For instance:
PGXquery* q1 = PQXcreateQuery(...);
PGXquery* q2 = PQXcreateQuery(...);
...
PGXquery* qN = PQXcreateQuery(...);
PQXexecute(conn1, q1);
PQXexecute(conn1, q2);
...
PQXexecute(conn1, qN);
while (1) { /* event loop code */ }
In this example, queries q1 through qN are "in flight" simultaneously as
far as the client is concerned even though the connection conn1 is
really queueing them up. During execution of the event loop, the
connection object manages the job of executing each query
sequentially (which, with respect to the client, is an implementation
detail).
Cheers,
Iker
On Sun, 26 Jan 2003 00:47:20 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Iker Arizmendi <iker@research.att.com> writes:
> > 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,
>
> I must be missing something fundamental here. The backend doesn't
> support multiple parallel queries, so how can you have "multiple
> queries in flight" on the same connection?
>
> regards, tom lane
BTW, the source tarball contains an example program that shows how multiple "in flight" queries are simulated. It also contains a multi-threaded example (where each thread waits on the connection pool). Cheers, Iker On Sun, 26 Jan 2003 00:47:20 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Iker Arizmendi <iker@research.att.com> writes: > > 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, > > I must be missing something fundamental here. The backend doesn't > support multiple parallel queries, so how can you have "multiple > queries in flight" on the same connection? > > regards, tom lane -- Iker Arizmendi AT&T Labs - Research Speech and Image Processing Lab e: iker@research.att.com w: http://research.att.com p: 973-360-8516
Iker Arizmendi <iker@research.att.com> writes:
> Althought multiple queries can't be executed concurrently they can be
> queued up by a connection object.
Hmm. This seems to presume that every query will be executed as a
separate transaction. That's pretty limiting (unless you suppose all
the queries are read-only).
regards, tom lane
> Hmm. This seems to presume that every query will be executed as a
> separate transaction. That's pretty limiting (unless you suppose all
> the queries are read-only).
True, but only at this early stage. There are (at least) two
ways which can be used to provide transaction support:
1) Explicit transaction start/end methods. Eg.
PQXbeginTx(pConn1);
PQXexecute(pConn1, q1);
PQXexecute(pConn1, q2);
PQXexecute(pConn1, q3);
PQXcommitTx(pConn1);
2) Or implicit transaction support. Eg.
PGXquery* q1 = PQXcreateQuery("BEGIN... UPDATE...");
PGXquery* q2 = PQXcreateQuery("UPDATE");
PGXquery* q3 = PQXcreateQuery("INSERT");
PGXquery* q4 = PQXcreateQuery("COMMIT");
In this latter case the PGXconn object would have to inspect the
SQL queries in search of transaction delimiters.
As far as uncommitted transactions go, the PGXconn object has a flag
that indicates whether its currently in a transaction - this flag can
be checked (it currently isn't) upon transitioning to the closed state.
At that point whatever policy governing uncommitted transactions can be
put in place before returning the connection to the pool.
Cheers,
Iker
On Sun, 26 Jan 2003 13:38:02 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Iker Arizmendi <iker@research.att.com> writes:
> > Althought multiple queries can't be executed concurrently they can
> > be queued up by a connection object.
>
> Hmm. This seems to presume that every query will be executed as a
> separate transaction. That's pretty limiting (unless you suppose all
> the queries are read-only).
>
> regards, tom lane
--
Iker Arizmendi
AT&T Labs - Research
Speech and Image Processing Lab
e: iker@research.att.com
w: http://research.att.com
p: 973-360-8516
BTW, the source tarball contains two example programs - an event-driven
version which demonstrates simulating multiple "in-flight" queries and a
multi-threaded version.
Cheers,
Iker
----- Original Message -----
From: "Iker Arizmendi" <iker@research.att.com>
Newsgroups:
comp.databases.postgresql.general,comp.databases.postgresql.questions
Sent: Sunday, January 26, 2003 2:02 AM
Subject: Re: [RFC] libpq extensions - followup
> Althought multiple queries can't be executed concurrently they can be
> queued up by a connection object. For instance:
>
> PGXquery* q1 = PQXcreateQuery(...);
> PGXquery* q2 = PQXcreateQuery(...);
> ...
> PGXquery* qN = PQXcreateQuery(...);
>
> PQXexecute(conn1, q1);
> PQXexecute(conn1, q2);
> ...
> PQXexecute(conn1, qN);
>
> while (1) { /* event loop code */ }
>
> In this example, queries q1 through qN are "in flight" simultaneously as
> far as the client is concerned even though the connection conn1 is
> really queueing them up. During execution of the event loop, the
> connection object manages the job of executing each query
> sequentially (which, with respect to the client, is an implementation
> detail).
>
> Cheers,
> Iker
>
> On Sun, 26 Jan 2003 00:47:20 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Iker Arizmendi <iker@research.att.com> writes:
> > > 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,
> >
> > I must be missing something fundamental here. The backend doesn't
> > support multiple parallel queries, so how can you have "multiple
> > queries in flight" on the same connection?
> >
> > regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)