Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1qpsLm-007OFa-50
	for pgsql-admin@arkaria.postgresql.org; Mon, 09 Oct 2023 15:39:18 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1qpsLj-00FfMG-Uo
	for pgsql-admin@arkaria.postgresql.org; Mon, 09 Oct 2023 15:39:16 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <ronljohnsonjr@gmail.com>)
	id 1qpsLj-00FfM8-9V
	for pgsql-admin@lists.postgresql.org; Mon, 09 Oct 2023 15:39:16 +0000
Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
	(Exim 4.94.2)
	(envelope-from <ronljohnsonjr@gmail.com>)
	id 1qpsLh-000oAH-HR
	for pgsql-admin@lists.postgresql.org; Mon, 09 Oct 2023 15:39:15 +0000
Received: by mail-yb1-xb2a.google.com with SMTP id
 3f1490d57ef6-d9a3c94c134so375272276.2
        for <pgsql-admin@lists.postgresql.org>;
 Mon, 09 Oct 2023 08:39:13 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20230601; t=1696865952; x=1697470752;
 darn=lists.postgresql.org;
        h=in-reply-to:from:references:to:content-language:subject:user-agent
         :mime-version:date:message-id:from:to:cc:subject:date:message-id
         :reply-to;
        bh=TjBUe4d+XBnqZ4vjoHfQn8QYf7WI7L9uNYkgt4Yy7LM=;
        b=fCi9xPE3/Tz5KTPgrXRcrJBGO5DWYT9lgdUPHjyrSNUwzBQcqekgTKXQyqtMLm/VlZ
         d4tIJOi9zJbNZ270MXFaSSvqayU+y1Id4micCq4dGwDqt4ufvzaW5DYiL9Rn4JjDhP2x
         Hmfk6WLlo0l35HB02u8y9u/Gtd5M8JfGNKd+cEx4yX3iPF7Y2/trjhBaIMAr/JYRk0qu
         8cRBYE171aWVZx8CCmdq75AR1VBBrqyXxPPXUT4tKkQaaBECuH5FTaaj0+X4GYa9vGCS
         hZes2Bwa4BOoN/kTkybYuDBSKY0lI4W7pn7FV2Z524uvLQxAgNS5+PZus10B7VOzOl7/
         vbRw==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20230601; t=1696865952; x=1697470752;
        h=in-reply-to:from:references:to:content-language:subject:user-agent
         :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject
         :date:message-id:reply-to;
        bh=TjBUe4d+XBnqZ4vjoHfQn8QYf7WI7L9uNYkgt4Yy7LM=;
        b=HAv9INncalGeB0hIci6X+p9PnEcGOri0GrIXQp3NVhDpad5MMdIrqY0yvRmBxjYbtd
         uAQLcOoPY+37vPV9xK2bcEA0XiS5A1Cjie7svsl2NKSdLLruVQNNM0lefHw6NchkFsfa
         C3tm7L4Xnr9ola9LFT0YXz44FwQaU4cdDY8aDzJk7iSR+8hv0XD2KtevbfFROoeCFTee
         AqQUTAVaUmWcNSPPKZbQxKaAn17dvQ6oBIlsAYHRnMstvd71QSJWha/ZM2fIffmWZfau
         ogauV8gd3lgdLOnkQPdwwMuBLCw0IXwqCraVqHp0GoQnalRxRXbwY7DnF/H3XmVeXMTz
         rBiA==
X-Gm-Message-State: AOJu0YyRhhqJN0tGqjTy0cLg2QBeWo80M8KSIFNpGA8M8FiIjr9shAUs
	vEcisBk3KYREJvI5pyyGFadEklw/EFw=
X-Google-Smtp-Source: 
 AGHT+IGvg+mXiTRb2yKpqsQW4wRsBKbG48i4UZqSM77KtUUH0K/7zClDKVHJhaFe+crctOf9xxOpUA==
X-Received: by 2002:a25:8e07:0:b0:d86:56ac:feff with SMTP id
 p7-20020a258e07000000b00d8656acfeffmr15008864ybl.6.1696865952353;
        Mon, 09 Oct 2023 08:39:12 -0700 (PDT)
Received: from ?IPV6:2600:1700:bd2c:b810:c116:92a9:442a:ffc3?
 ([2600:1700:bd2c:b810:c116:92a9:442a:ffc3])
        by smtp.googlemail.com with ESMTPSA id
 t40-20020a25aaab000000b00d853795d3e6sm3161316ybi.9.2023.10.09.08.39.12
        for <pgsql-admin@lists.postgresql.org>
        (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128);
        Mon, 09 Oct 2023 08:39:12 -0700 (PDT)
Content-Type: multipart/alternative;
 boundary="------------Eo108d0F6MCPNjCFgXmxgIA0"
Message-ID: <897f06dd-b6a4-eeef-1b88-e643a4bb5c6b@gmail.com>
Date: Mon, 9 Oct 2023 10:39:11 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Discard All in pgbouncer
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAJk5AtachHvTqeMT=Y+3wKaRgQnO8ypBUPvGQK4JFH5thkxcew@mail.gmail.com>
 <CAAo1mbkLHPZYr7AGthoSg3pFf14LLNbEbZdCJUUoVxm6Bd1jjg@mail.gmail.com>
 <CAJk5AtbOeXVLDj5frFBy3Bw9xxja-bY1hcObMWonQ-xW-Oq=HQ@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAJk5AtbOeXVLDj5frFBy3Bw9xxja-bY1hcObMWonQ-xW-Oq=HQ@mail.gmail.com>
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/897f06dd-b6a4-eeef-1b88-e643a4bb5c6b%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Eo108d0F6MCPNjCFgXmxgIA0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

You're */still/* missing the point.  If pgbouncer doesn't need to use a 
connection, then... it's going to sit there "idle". That's what "idle" means!

What we haven't explicitly stated, but implied, is that pg_stat_activity 
*does not* clear the query field when the status = 'idle'.  Thus, as we've 
said before, DISCARD ALL /*was*//**/ the last statement to be executed.

On 10/9/23 09:18, Rajesh Kumar wrote:
> Purpose of bouncer is handle idle connections right? Is there a time 
> period for the DISCARD ALL to go away? If so, how to configure?
>
> The part I don't understand is if bunch of connections in the name of 
> Discard all is idle ,  what difference it make?
>
> On Mon, 9 Oct 2023, 19:42 Samed YILDIRIM, <samed@reddoc.net> wrote:
>
>     Hi Rajesh,
>
>     DISCARD ALL is the query that is executed by PgBouncer in order to
>     sanitise a database session before assigning it to another client. If
>     you see this query in pg_stat_activity and status of the session is
>     idle, most probably it is ok. Probably what you see is the last
>     executed query for the session. And the session is idle and waiting to
>     be assigned to a client. :)
>
>     Best regards.
>     Samed YILDIRIM
>
>     On Mon, 9 Oct 2023, 17:07 Rajesh Kumar, <rajeshkumar.dba09@gmail.com>
>     wrote:
>
>         Hi,
>
>         I could see queries like DISCARD ALL is idle for more than
>         5minutes (we are using pgbouncer).
>
>         Why is it idle for more than 5mns? I read about pgbouncer and
>         could not understand this thing. If resetting a connection using
>         query becomes one idle connection, I don't understand the pooling
>         system here
>
>         Plz help
>

-- 
Born in Arizona, moved to Babylonia.
--------------Eo108d0F6MCPNjCFgXmxgIA0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    You're <b><i>still</i></b> missing the point.  If pgbouncer doesn't
    need to use a connection, then... it's going to sit there "idle". 
    That's what "idle" means!<br>
    <br>
    What we haven't explicitly stated, but implied, is that
    pg_stat_activity <b>does not</b> clear the query field when the
    status = 'idle'.  Thus, as we've said before, DISCARD ALL <i><b>was</b></i><i><b></b></i>
    the last statement to be executed.<br>
    <br>
    <div class="moz-cite-prefix">On 10/9/23 09:18, Rajesh Kumar wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAJk5AtbOeXVLDj5frFBy3Bw9xxja-bY1hcObMWonQ-xW-Oq=HQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">Purpose of bouncer is handle idle connections
        right? Is there a time period for the DISCARD ALL to go away? If
        so, how to configure?
        <div dir="auto"><br>
        </div>
        <div dir="auto">The part I don't understand is if bunch of
          connections in the name of Discard all is idle ,  what
          difference it make?</div>
      </div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Mon, 9 Oct 2023, 19:42
          Samed YILDIRIM, &lt;<a href="mailto:samed@reddoc.net"
            moz-do-not-send="true" class="moz-txt-link-freetext">samed@reddoc.net</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0 0 0
          .8ex;border-left:1px #ccc solid;padding-left:1ex">
          <div dir="auto">
            <div>Hi Rajesh,
              <div dir="auto"><br>
              </div>
              <div dir="auto">DISCARD ALL is the query that is executed
                by PgBouncer in order to sanitise a database session
                before assigning it to another client. If you see this
                query in pg_stat_activity and status of the session is
                idle, most probably it is ok. Probably what you see is
                the last executed query for the session. And the session
                is idle and waiting to be assigned to a client. :)<br>
                <br>
                <div data-smartmail="gmail_signature" dir="auto">Best
                  regards.<br>
                  Samed YILDIRIM</div>
              </div>
              <br>
              <div class="gmail_quote">
                <div dir="ltr" class="gmail_attr">On Mon, 9 Oct 2023,
                  17:07 Rajesh Kumar, &lt;<a
                    href="mailto:rajeshkumar.dba09@gmail.com"
                    target="_blank" rel="noreferrer"
                    moz-do-not-send="true" class="moz-txt-link-freetext">rajeshkumar.dba09@gmail.com</a>&gt;
                  wrote:<br>
                </div>
                <blockquote class="gmail_quote" style="margin:0 0 0
                  .8ex;border-left:1px #ccc solid;padding-left:1ex">
                  <div dir="auto">Hi,
                    <div dir="auto"><br>
                    </div>
                    <div dir="auto">I could see queries like DISCARD ALL
                      is idle for more than 5minutes (we are using
                      pgbouncer).</div>
                    <div dir="auto"><br>
                    </div>
                    <div dir="auto">Why is it idle for more than 5mns? I
                      read about pgbouncer and could not understand this
                      thing. If resetting a connection using query
                      becomes one idle connection, I don't understand
                      the pooling system here </div>
                    <div dir="auto"><br>
                    </div>
                    <div dir="auto">Plz help</div>
                  </div>
                </blockquote>
              </div>
            </div>
          </div>
        </blockquote>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Born in Arizona, moved to Babylonia.</div>
  </body>
</html>

--------------Eo108d0F6MCPNjCFgXmxgIA0--