Re: More concurent transaction over single connection

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(at)archonet(dot)com>
To: "NTPT" <ntpt(at)seznam(dot)cz>
Cc: "Postgres General" <pgsql-general(at)postgresql(dot)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
>
>