FAQ
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

Search Discussions

  • Christopher Kings-Lynne at Jul 29, 2002 at 10:36 am
    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
  • Luis Alberto Amigo Navarro at Jul 29, 2002 at 10:55 am
    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
  • Christopher Kings-Lynne at Jul 29, 2002 at 3:28 pm
    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
  • Neil Conway at Jul 29, 2002 at 4:11 pm

    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
  • Christopher Kings-Lynne at Jul 30, 2002 at 7:19 pm
    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
    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
  • Hannu Krosing at Jul 29, 2002 at 5:41 pm

    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
  • Luis Alberto Amigo Navarro at Jul 29, 2002 at 6:15 pm
    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
  • Hannu Krosing at Jul 29, 2002 at 6:38 pm

    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
  • Luis Alberto Amigo Navarro at Jul 29, 2002 at 6:44 pm
    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
  • Hannu Krosing at Jul 29, 2002 at 7:06 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 29, '02 at 9:27a
activeJul 30, '02 at 7:19p
posts11
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase