Subject: Odd off-by-one dirty buffers and checkpoint buffers written

From: "David G. Johnston" <[email-masked]>
Date: Tue, 19 Apr 2022 16:21:21 -0700

view bc is just a joining wrapper around pg_buffercache.

regression=# select datname, relname, count(*), sum(count(*)) over () AS
total from bc where isdirty group by datname, relname;
 datname | relname | count | total
---------+---------+-------+-------
(0 rows)

regression=# update tenk1 set stringu1 = stringu1 || '' where (unique1 %
384) = 3;
UPDATE 27
regression=# select datname, relname, count(*), sum(count(*)) over () AS
total from bc where isdirty group by datname, relname;
  datname   | relname | count | total
------------+---------+-------+-------
 regression | tenk1   |     3 |     3
(1 row)

regression=# checkpoint;
CHECKPOINT

2022-04-19 23:17:08.256 UTC [161084] LOG:  checkpoint starting: immediate
force wait
2022-04-19 23:17:08.264 UTC [161084] LOG:  checkpoint complete: wrote 4
buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s,
sync=0.002 s, total=0.009 s; sync files=2, longest=0.002 s, average=0.001
s; distance=12 kB, estimate=72358 kB

I've done this four times in a row and while the number of dirty buffers
shown each time vary (see below) I see that "wrote N buffers" is always
exactly one more than the total count of dirty buffers.  I'm just curious
if anyone has a quick answer for this unusual correspondence.

David J.

regression=# update tenk1 set stringu1 = stringu1 || '' where (unique1 %
384) = 3;
UPDATE 27
regression=# select datname, relname, count(*), sum(count(*)) over () AS
total from bc where isdirty group by datname, relname;
  datname   |       relname        | count | total
------------+----------------------+-------+-------
 regression | tenk1                |    33 |   102
 regression | tenk1_hundred        |     9 |   102
 regression | tenk1_thous_tenthous |    18 |   102
 regression | tenk1_unique1        |    27 |   102
 regression | tenk1_unique2        |    15 |   102
(5 rows)

2022-04-19 23:13:03.480 UTC [161084] LOG:  checkpoint starting: immediate
force wait
2022-04-19 23:13:03.523 UTC [161084] LOG:  checkpoint complete: wrote 103
buffers (0.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s,
sync=0.014 s, total=0.044 s; sync files=8, longest=0.008 s, average=0.002
s; distance=721 kB, estimate=110165 kB