Hi all
As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction.
Thanks and Regards







Hi all
As I understand every time there is a request to 
postgres a new backend is made, and when the request is finished, even if the 
connection is already active the backend dies. I wonder if is there any 
parameter that allow backends to remain beyond a transaction. Creating a new 
backend every time a transaction is made means forking the code and reallocating 
sort_memory. Although it is not a high resource usage, on short transactions as 
OLTPs it is a relevant work time, I think it would be interesting that a 
predefined number of backends were allowed to remain active beyond the 
transaction.
Thanks and Regards
Mon, 29 Jul 2002 11:32:46 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
question on backends
Just use persistent connections.

Chris
  ----- Original Message ----- 
  From: Luis Alberto Amigo Navarro 
  To: pgsql-hackers@postgresql.org 
  Sent: Monday, July 29, 2002 5:32 PM
  Subject: [HACKERS] question on backends


  Hi all
  As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction.
  Thanks and Regards







Just use persistent connections.
 
Chris

----- Original Message ----- 
From:
Luis Alberto 
  Amigo Navarro 
To: pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 5:32 PM
Subject: [HACKERS] question on 
  backends

Hi all
As I understand every time there is a request to 
  postgres a new backend is made, and when the request is finished, even if the 
  connection is already active the backend dies. I wonder if is there any 
  parameter that allow backends to remain beyond a transaction. Creating a new 
  backend every time a transaction is made means forking the code and 
  reallocating sort_memory. Although it is not a high resource usage, on short 
  transactions as OLTPs it is a relevant work time, I think it would be 
  interesting that a predefined number of backends were allowed to remain active 
  beyond the transaction.
Thanks and 
Regards
Mon, 29 Jul 2002 18:36:30 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: question on backends
How?
  ----- Original Message ----- 
  From: Christopher Kings-Lynne 
  To: Luis Alberto Amigo Navarro ; pgsql-hackers@postgresql.org 
  Sent: Monday, July 29, 2002 12:36 PM
  Subject: Re: [HACKERS] question on backends


  Just use persistent connections.

  Chris
    ----- Original Message ----- 
    From: Luis Alberto Amigo Navarro 
    To: pgsql-hackers@postgresql.org 
    Sent: Monday, July 29, 2002 5:32 PM
    Subject: [HACKERS] question on backends


    Hi all
    As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction.
    Thanks and Regards







How?

----- Original Message ----- 
From:
Christopher Kings-Lynne 
To: Luis Alberto Amigo Navarro ; pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 12:36 
PM
Subject: Re: [HACKERS] question on 
  backends

Just use persistent 
connections.
 
Chris

----- Original Message ----- 
From:
Luis 
    Alberto Amigo Navarro 
To: pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 5:32 
    PM
Subject: [HACKERS] question on 
    backends

Hi all
As I understand every time there is a request 
    to postgres a new backend is made, and when the request is finished, even if 
    the connection is already active the backend dies. I wonder if is there any 
    parameter that allow backends to remain beyond a transaction. Creating a new 
    backend every time a transaction is made means forking the code and 
    reallocating sort_memory. Although it is not a high resource usage, on short 
    transactions as OLTPs it is a relevant work time, I think it would be 
    interesting that a predefined number of backends were allowed to remain 
    active beyond the transaction.
Thanks and 
Regards
Mon, 29 Jul 2002 13:00:46 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: question on backends
libpq has a function pconnect as opposed to connect that will do it.  PHP and most other interfaces will let you use persistent connections.

Chris
  ----- Original Message ----- 
  From: Luis Alberto Amigo Navarro 
  To: Christopher Kings-Lynne ; pgsql-hackers@postgresql.org 
  Sent: Monday, July 29, 2002 7:00 PM
  Subject: Re: [HACKERS] question on backends


  How?
    ----- Original Message ----- 
    From: Christopher Kings-Lynne 
    To: Luis Alberto Amigo Navarro ; pgsql-hackers@postgresql.org 
    Sent: Monday, July 29, 2002 12:36 PM
    Subject: Re: [HACKERS] question on backends


    Just use persistent connections.

    Chris
      ----- Original Message ----- 
      From: Luis Alberto Amigo Navarro 
      To: pgsql-hackers@postgresql.org 
      Sent: Monday, July 29, 2002 5:32 PM
      Subject: [HACKERS] question on backends


      Hi all
      As I understand every time there is a request to postgres a new backend is made, and when the request is finished, even if the connection is already active the backend dies. I wonder if is there any parameter that allow backends to remain beyond a transaction. Creating a new backend every time a transaction is made means forking the code and reallocating sort_memory. Although it is not a high resource usage, on short transactions as OLTPs it is a relevant work time, I think it would be interesting that a predefined number of backends were allowed to remain active beyond the transaction.
      Thanks and Regards







libpq has a function pconnect as opposed to connect 
that will do it.  PHP and most other interfaces will let you use persistent 
connections.
 
Chris

----- Original Message ----- 
From:
Luis Alberto 
  Amigo Navarro 
To: Christopher Kings-Lynne ; pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 7:00 PM
Subject: Re: [HACKERS] question on 
  backends

How?

----- Original Message ----- 
From:
Christopher Kings-Lynne 
To: Luis Alberto Amigo Navarro ; pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 12:36 
    PM
Subject: Re: [HACKERS] question on 
    backends

Just use persistent 
    connections.
 
Chris

----- Original Message ----- 
From:
Luis 
      Alberto Amigo Navarro 
To: pgsql-hackers@postgresql.org

Sent: Monday, July 29, 2002 5:32 
      PM
Subject: [HACKERS] question on 
      backends

Hi all
As I understand every time there is a request 
      to postgres a new backend is made, and when the request is finished, even 
      if the connection is already active the backend dies. I wonder if is there 
      any parameter that allow backends to remain beyond a transaction. Creating 
      a new backend every time a transaction is made means forking the code and 
      reallocating sort_memory. Although it is not a high resource usage, on 
      short transactions as OLTPs it is a relevant work time, I think it would 
      be interesting that a predefined number of backends were allowed to remain 
      active beyond the transaction.
Thanks and 
  Regards
Mon, 29 Jul 2002 23:28:54 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: question on backends
On Mon, Jul 29, 2002 at 11:28:54PM +0800, Christopher Kings-Lynne wrote:
> libpq has a function pconnect as opposed to connect that will do it.

libpq has neither function, AFAIK.

As for persistent backends, it's on the TODO list, but I'm not aware
that anyone has put any work into implementing it. At the moment,
a backend connects to a single database for its entire lifecycle -- so
you'd either need one pool of persistent backends for each database
in use, or you'd need to allow a backend to change the database it is
connected to.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Mon, 29 Jul 2002 12:11:19 -0400
nconway@klamath.dyndns.org (Neil Conway)
Re: question on backends
if i put debug_level=1 i get for one connect and several inserts on backend
die after each insert
----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, July 29, 2002 8:42 PM
Subject: Re: [HACKERS] question on backends


> On Mon, 2002-07-29 at 11:32, Luis Alberto Amigo Navarro wrote:
> > Hi all
> > As I understand every time there is a request to postgres a new backend
> > is made, and when the request is finished, even if the connection is
> > already active the backend dies.
>
> I think you have misunderstood it. A new backend is forked only when a
> new connection is made, not for every transaction.
>
> There may be some frontends that do make a new connection for each http
> request or such, but most of them allow for persistent connections,
> either as an option or by default.
>
> > I wonder if is there any parameter
> > that allow backends to remain beyond a transaction. Creating a new
> > backend every time a transaction is made means forking the code and
> > reallocating sort_memory.
>
> ------------------
> Hannu
>
>



Mon, 29 Jul 2002 20:21:27 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: question on backends
On Mon, 2002-07-29 at 11:32, Luis Alberto Amigo Navarro wrote:
> Hi all
> As I understand every time there is a request to postgres a new backend
> is made, and when the request is finished, even if the connection is
> already active the backend dies.

I think you have misunderstood it. A new backend is forked only when a
new connection is made, not for every transaction.

There may be some frontends that do make a new connection for each http
request or such, but most of them allow for persistent connections,
either as an option or by default.

> I wonder if is there any parameter
> that allow backends to remain beyond a transaction. Creating a new
> backend every time a transaction is made means forking the code and
> reallocating sort_memory.

------------------
Hannu


29 Jul 2002 20:42:30 +0200
Hannu Krosing <hannu@tm.ee>
Re: question on backends
libpq
PQsetdb(........
----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, July 29, 2002 9:40 PM
Subject: Re: [HACKERS] question on backends


> On Mon, 2002-07-29 at 20:21, Luis Alberto Amigo Navarro wrote:
> > if i put debug_level=1 i get for one connect and several inserts on
backend
> > die after each insert
>
> What client do you use ?
>
> ----------
> Hannu
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>



Mon, 29 Jul 2002 20:50:00 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: question on backends
On Mon, 2002-07-29 at 20:21, Luis Alberto Amigo Navarro wrote:
> if i put debug_level=1 i get for one connect and several inserts on backend
> die after each insert

What client do you use ?

----------
Hannu


29 Jul 2002 21:40:35 +0200
Hannu Krosing <hannu@tm.ee>
Re: question on backends
On Mon, 2002-07-29 at 20:50, Luis Alberto Amigo Navarro wrote:
> libpq
> PQsetdb(........
> ----- Original Message -----
> From: "Hannu Krosing" <hannu@tm.ee>
> To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
> Cc: <pgsql-hackers@postgresql.org>
> Sent: Monday, July 29, 2002 9:40 PM
> Subject: Re: [HACKERS] question on backends
> 
> 
> > On Mon, 2002-07-29 at 20:21, Luis Alberto Amigo Navarro wrote:
> > > if i put debug_level=1 i get for one connect and several inserts on
> backend
> > > die after each insert
>

It should not happen.

I've run several websites using both php and python (which use libpq to
connect to backend), and they have been up for months connected to the
_same_ backend, doing inserts, updates, deletes and selects.

I guess there is some error in you code that manifests itself as dying
backends.

You can test this by trying to do several inserts in one transaction.
and see if the data is still there after the backend closes after the
first insert.

--------------
Hannu


29 Jul 2002 22:07:55 +0200
Hannu Krosing <hannu@tm.ee>
Re: question on backends
Ah yes - that was me making an unfortunate exptrapolation without thinking
it through.

Of course, PHP implements persistent connections for you, etc., etc., not
the postgres client library.

Chris

> -----Original Message-----
> From: Neil Conway [mailto:nconway@klamath.dyndns.org]
> Sent: Tuesday, 30 July 2002 12:11 AM
> To: Christopher Kings-Lynne
> Cc: Luis Alberto Amigo Navarro; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] question on backends
>
>
> On Mon, Jul 29, 2002 at 11:28:54PM +0800, Christopher Kings-Lynne wrote:
> > libpq has a function pconnect as opposed to connect that will do it.
>
> libpq has neither function, AFAIK.
>
> As for persistent backends, it's on the TODO list, but I'm not aware
> that anyone has put any work into implementing it. At the moment,
> a backend connects to a single database for its entire lifecycle -- so
> you'd either need one pool of persistent backends for each database
> in use, or you'd need to allow a backend to change the database it is
> connected to.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
>


Tue, 30 Jul 2002 09:28:31 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: question on backends