MIME-Version: 1.0
References: <aWjbBd5z+LJNWX1l@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0t6j0VYuUpxZ8JLq-ERoUriZ0rK=+8PCUtjRirmSmCx7A@mail.gmail.com>
 <aWojE18aF9qj0EPI@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0s9j3x5UPpgaQdDyGA=MNjVEkT73SL7LMoVEKUwiZrVqA@mail.gmail.com>
 <aW4gwqVybB4INCvu@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0s6FkEHFdgKf8J6vueZGwsH+08LvV0YPBXa4Dw_8QgtTw@mail.gmail.com>
 <aXCroXBLiUpxk0Ap@ip-10-97-1-34.eu-west-3.compute.internal>
 <aXFpAisDKy6g_cKx@paquier.xyz> <CAA5RZ0sQxAu6bU8wTgvs+aTSvhBcziH9jCJ27aS1hzKsm2kmTQ@mail.gmail.com>
 <CAHGQGwGBkPEK=NpLuk1HSmccu6OA2FYqkkGcA-Hb3WLs6dX=cg@mail.gmail.com> <aXHVC6NxERhcThZV@ip-10-97-1-34.eu-west-3.compute.internal>
In-Reply-To: <aXHVC6NxERhcThZV@ip-10-97-1-34.eu-west-3.compute.internal>
From: Fujii Masao <masao.fujii@gmail.com>
Date: Thu, 22 Jan 2026 21:12:18 +0900
Message-ID: <CAHGQGwHttst8tv_WWYNoGGfL1UAq4kiy6dpFXoxEkJwHMS9FtQ@mail.gmail.com>
Subject: Re: Flush some statistics within running transactions
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, Michael Paquier <michael@paquier.xyz>, 
	pgsql-hackers@lists.postgresql.org, Zsolt Parragi <zsolt.parragi@percona.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHGQGwHttst8tv_WWYNoGGfL1UAq4kiy6dpFXoxEkJwHMS9FtQ%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 22, 2026 at 4:43=E2=80=AFPM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi,
>
> On Thu, Jan 22, 2026 at 10:56:48AM +0900, Fujii Masao wrote:
> > On Thu, Jan 22, 2026 at 10:41=E2=80=AFAM Sami Imseih <samimseih@gmail.c=
om> wrote:
> > >
> > > Sure, Bertrand mentioned early in the thread that the anytime flushes
> > > could be made configurable. Perhaps that is a good idea where we can
> > > default with something large like 10s intervals for anytime flushes, =
but allow
> > > the user to configure a more frequent flushes ( although I would thin=
k
> > > that 1 sec is the minimum we should allow ).
> >
> > +1 on adding an option to control the interval. With a fixed interval
> > (for example, 1s), log_lock_waits messages could be emitted that freque=
ntly,
> > which may be annoying for some users.
> >
> > Of course, it would be even better if these periodic wakeups did not tr=
igger
> > log_lock_waits messages at all, though.
>
> pgstat_report_anytime_stat() is called with the force parameter set to fa=
lse,
> means that the flushes are done with nowait =3D true means that LWLockCon=
ditionalAcquire()
> is used. In that case, do you still see cases where log_lock_waits messag=
es could
> be triggered due to the new flush?

I haven't read the patch in detail yet, but after applying patch 0001 and
causing a lock wait (for example, using the steps below), I observed that
log_lock_waits messages are emitted every second.

    [session 1]
    create table tbl as select id from generate_series(1, 10) id;
    begin;
    select * from tbl where id =3D 1 for update;

    [session 2]
    begin;
    select * from tbl where id =3D 1 for update;

With this setup, the following messages were logged once per second:

    LOG:  process 72199 still waiting for ShareLock on transaction 771
after 63034.119 ms
    DETAIL:  Process holding the lock: 72190. Wait queue: 72199.

Regards,

--=20
Fujii Masao