MIME-Version: 1.0
References: <CAFCRh-8WDkn2N+s43fofR4eKHcJC8fiotqWqSGkDQ4Sh4ZYwDg@mail.gmail.com>
 <755975.1749565677@sss.pgh.pa.us>
In-Reply-To: <755975.1749565677@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Tue, 10 Jun 2025 17:46:50 +0200
Message-ID: <CAFCRh--LyhHAOYKWE+NDCN8cB4pGiO82p1Ye9pT9T7fv7i2BfA@mail.gmail.com>
Subject: Re: is pg_stat_activity "transactional"? How fast does it update?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--LyhHAOYKWE%2BNDCN8cB4pGiO82p1Ye9pT9T7fv7i2BfA%40mail.gmail.com>
Precedence: bulk

On Tue, Jun 10, 2025 at 4:27=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > So when and how fast does pg_stat_activity update, in the face of a
> > PQfinish?
>
> The shared state underlying pg_stat_activity is updated
> immediately when something changes.  However ...
>
> PQfinish is asynchronous: it sends a "goodbye" message and then
> closes the connection without waiting for a response.  So
> depending on system load, the associated backend could still be
> alive for some milliseconds.  There might be other client
> libraries that do that differently.
>
> Another thing that might be biting you is that a backend inspecting
> pg_stat_activity() takes a snapshot of the view's contents and then
> holds onto that snapshot until end of transaction.  You can get
> around that with pg_stat_clear_snapshot().

Many thanks Tom. Didn't know either of those facts. --DD