Re: Any better plan for this query?..

Hi,

Le 12 mai 09 à 18:32, Robert Haas a écrit :
> implement this same logic internally? IOW, when a client disconnects,
> instead of having the backend exit immediately, have it perform the
> equivalent of DISCARD ALL and then stick around for a minute or two
> and, if a new connection request arrives within that time, have the
> old backend handle the new connection...

A much better idea to solve this, in my opinion, would be to have
pgbouncer as a postmaster child, integrated into PostgreSQL. It allows
for choosing whether you want session pooling, transaction pooling or
statement pooling, which is a more deterministic way to choose when
your client connection will benefit from a fresh backend or an
existing one. And it's respecting some backend timeouts etc.
It's Open-Source proven technology, and I think I've heard about some
PostgreSQL distribution where it's already a postmaster's child.

<handwaving>
And when associated with Hot Standby (and Sync Wal Shipping), having a
connection pooler in -core could allow for transparent Read-Write
access to the slave: at the moment you need an XID (and when connected
on the slave), the backend could tell the pgbouncer process to
redirect the connection to the master. With such a feature, you don't
have to build client side high availability, just connect to either
the master or the slave and be done with it, whatever the SQL you're
gonna run.
</>

>

Regards,
--
dim