Re: More concurent transaction over single connection ?

On Wed, 9 Feb 2005, 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)

For the PHP case, it's not a limitation. pg_connect() and pg_pconnect() have the
same semantics, per specs. That is, there's no way to write a PHP program that
behaves differently depening on the usage of pg_connect or pg_pconnect.
You can always safely substitute a pg_pconnect with pg_connect, as far as PHP
is concerned (it may affect performance, but not semantics of the program).

Moreover, for a Web application, assuming you're using a multiprocess server
such as Apache, you can never tell which sub-process will handle your next
request. And database connections are per-process. So the problem you need to
solve is not multiple apps using the same connection, but also how to pass
connections along among sub-processes. Think bigger, and consider a
load-balancing solution, where requests are directed to different web frontents:
you'll need to pass database connections among different _hosts_.

It's the stateless nature for HTTP that makes web services really scalable.
Persistent connections destroy any possible state when the request is
done. You can't have a transaction span multiple requests, per design.

If you really need that, consider an application server. Anyway, beginning
a transaction in one page, and waiting for a second request from the client
in order to commit it is bad practice, since the wait can me potentially
very long (and you need a way to recover from that).

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it