Re: rfc - libpq extensions - Mailing list pgsql-general

AFAIK Using the same connection to do more queries at the same time is not
supported by the backend.
If you use transactions, it will only make your life harder to make sure the
transaction goes on on the same connection, and nobody else uses the same
connection meanwhile, messing with your ongoing transaction... unless the
postgres developers would be willing to implement connection independent
transactions (by the means of a transaction ID passed along with the query ?
this is DB sience fiction - might be good but it's not standard nor really
needed).

I assume your problems with connection pooling are the relatively big setup
time of a connection, and the possibility of running out of connections...
Now what you can do is using a smart connection pool which always keeps a
few spare connections, and if the nr. of spare connections gets below the
minimum allowed, opens new ones (before the application asks for it). This
way your application will always have a connection at hand immediately.
From time to time idle connections can be deleted to avoid keeping the nr.
of connections too high after peeks of load, while keeping the configured
minimum of idle connections.

AFAIK this is how apache HTTP connection pooling is working, and it works
well.

HTH,
Csaba.

-----Ursprungliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Iker Arizmendi
Gesendet: Freitag, 10. Januar 2003 18:10
An: pgsql-general@postgresql.org
Betreff: [GENERAL] rfc - libpq extensions


Currently libpq doesn't allow you to call PQsendQuery multiple
times in succession over an asynchronous connection. If libpq could
accept concurrent queries (with 1 or more SQL statements each) then
code functionally similiar to the following would come in handy (at
least to me :)

// hypothetical PGquery object maintains the state
// for a given query and allows for selective query
// cancellation
PGquery* pQuery1 = PQcreateQuery("SELECT...");
PGquery* pQuery2 = PQcreateQuery("UPDATE...");
PGquery* pQuery3 = PQcreateQuery("INSERT...");

PQconnExecute(pConn, pQuery1);
PQconnExecute(pConn, pQuery2);
PQconnExecute(pConn, pQuery3);

// event loop
while(1)
{
    poll(...)

    PQconsumeInput(pConn);

    // hypothetical PQqueryState
    if (PQqueryState(pQuery1) == PQ_QUERY_COMPLETE) {
        // process results
    }
    if (PQqueryState(pQuery2) == PQ_QUERY_COMPLETE) {
        // process results
    }
    if (PQqueryState(pQuery3) == PQ_QUERY_COMPLETE) {
        // process results
    }

}

As things stand now you have to wait for each query to finish before
issuing a new one. This poses some difficulties if you want to use
connection pooling in an event driven server (as I'm trying to do). In
particular, you have to perform your own queueing of queries while you
wait for a connection to become available. To deal with this issue I've
started work on some extensions to libpq to allow for both
multiple"in-flight" queries and support for connection pooling while
still providing support for the usual access techniques. I was hoping to
get some thoughts from folks on the list with regard to interest
in these features (and/or potential pitfalls).

Regards,
Iker



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)