1: Multiprocess server (Like Apache 1.x ) : Each process use one persistent connection. Right ? One proces can serve only one request in present time. Right ? When request is finished, process hold your connection open and awaiting a new request. From the point of view of the transactions it is OK, because transactions over one persistant connection are "serialized" by nature.
2: One process, but multiple threads . If each thread have your separate db connections, it is ok, it is like previous example, just substitute word "process" by word "thread"
3: One process, multiple threads, all threads share the same one persitant connection. Because one thread serve one request in present time, but threads can run "concurently" (AFIAK ), I am affraid, that multiple transactions over the single connection in this scenario will result a complette mess. I am right ?
Please execuse my wrong english.
----- Original Message ----- From: "Richard Huxton" <dev@xxxxxxxxxxxx>
To: "NTPT" <ntpt@xxxxxxxxx>
Cc: "Postgres General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Wednesday, February 09, 2005 11:45 AM
Subject: Re: More concurent transaction over single connection
NTPT wrote:AFAIK (7.4.x) there is one limitation in persistant connections to postgresql from various frontends ( http://cz.php.net/manual/en/features.persistent-connections.php ), because it can not use transactions in situation where more concurent tasks use a single connection (execuse my wrong english)
I suggest to add some sort of "context" identificator to frontend/backend protocol to overcome this limit. Ie frontend - ( like PHP for example ) make ONE persistant connection and different scripts are served over this connection. But frontend add for each instance of script a unique "context" identificator and postgresql server will treat different "contexts" as they was send by different connections. The results wil be sorted by "context" by frontend and feeded to apprpriate instance of the php script
You've just reinvented connections. The problem is at the application end really, since PHP doesn't provide a middle-ware layer to manage this sort of stuff. Typically, java-based application servers manage this sort of thing for you.
I think it may add some benefit to avoiding connection starting costs, especially in case where database and client are in greater network distance and/or need to use some expensive procedure to start connection and allow a relay simple and transparent connection pooling, may be a some type od "spare servers" like in Apache (MinSpareServers and Max SpareServers configuration directive )
Perhaps take a look at pgpool connection pooling.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
