Re: More concurent transaction over single connection - Mailing list pgsql-general

Ok. Let,s have a some model scenarios . Let it be a web server with some
embedded language like PHP.


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@archonet.com>
To: "NTPT" <ntpt@seznam.cz>
Cc: "Postgres General" <pgsql-general@postgresql.org>
Sent: Wednesday, February 09, 2005 11:45 AM
Subject: Re: [GENERAL] 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
>
>