Why is my table continuousely written? -> XID issue?

From: Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Why is my table continuousely written? -> XID issue?
Date: 2022-08-03 15:15:17
Message-ID: YuqRBap0ktay785n@gate.intra.daemon.contact
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I had a look into what actually changed in the table. At the first
write that I grabbed, four rows in that segment had such a change:

117ee000 77 00 00 00 00 df b8 82 8e a4 00 00 64 00 a0 00 |w...........d...|
117ee000 77 00 00 00 f0 22 b4 f3 68 d3 00 00 64 00 a0 00 |w...."..h...d...|

117ee0a0 f8 5c 00 00 f8 5c 00 00 09 00 00 00 06 00 f7 8b |.\...\..........|
117ee0b0 13 00 04 00 93 00 18 0d d8 27 fe 01 00 00 00 00 |.........'......|
117ee0b0 13 00 04 00 93 01 18 0d d8 27 fe 01 00 00 00 00 |.........'......|

1aad8000 77 00 00 00 30 fc 8c a5 80 8a 00 00 58 00 a8 00 |w...0.......X...|
1aad8000 77 00 00 00 e8 12 8a f3 6e 5b 00 00 58 00 a8 00 |w.......n[..X...|

1aad80a0 f6 5d 00 00 00 00 00 00 |.........]......|
1aad80b0 09 00 00 00 06 00 6c d5 10 00 04 00 03 08 18 0d |......l.........|
1aad80b0 09 00 00 00 06 00 6c d5 10 00 04 00 03 09 18 0d |......l.........|

If I get that right, then it is the HEAP_XMIN_COMMITTED from
t_infomask that got set for the rows.

The next write is a bit different:

0000a000 76 00 00 00 60 9d 84 d0 23 4c 04 00 70 00 50 01 |v...`...#L..p.P.|
0000a000 78 00 00 00 80 81 e9 05 78 0d 04 00 70 00 50 01 |x.......x...p.P.|

0000a690 cf 51 00 00 00 00 00 00 |504c54...Q......|
0000a690 cf 51 00 00 13 5e 00 00 |504c54...Q...^..|
0000a6a0 00 00 00 00 06 00 05 00 12 00 04 00 03 2b 18 0d |.............+..|
0000a6a0 00 00 00 00 06 00 05 00 12 00 04 00 93 23 18 0d |.............#..|

003ec000 76 00 00 00 30 98 85 d0 59 e9 04 00 60 00 48 01 |v...0...Y...`.H.|
003ec000 78 00 00 00 08 33 1b 03 bd 97 04 00 60 00 48 01 |x....3......`.H.|

003eddf0 cf 51 00 00 00 00 00 00 00 00 00 00 06 00 f6 01 |.Q..............|
003eddf0 cf 51 00 00 13 5e 00 00 00 00 00 00 06 00 f6 01 |.Q...^..........|
003ede00 02 00 04 00 03 2b 18 0d f3 56 2a 00 00 00 00 00 |.....+...V*.....|
003ede00 02 00 04 00 93 23 18 0d f3 56 2a 00 00 00 00 00 |.....#...V*.....|

It seems here is the t_xmax for the row updated, and also flags in
t_infomask changed - and there are about 1000 rows (of some 3 mio.
that are contained in this segment) where this is done.

So if I get this right, it looks like some out-of-band housekeeping is
done here.
Now, while I would understand that this is done as occasion offers
when a record is read anyway, and I do not think that I grok the
XID housekeeping in fullness, I also do not think that I have any open
transactions that would persist a server restart. So I was under the
impression that a server restart plus a vacuum FULL, without any
further update/delete operations, should resolve all the needs for
such housekeeping for the time being, and quiesce the situation.

Apparently it does not. So, then, what does?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Martin 2022-08-03 15:19:03 Re: "Missing" column in Postgres logical replication update message
Previous Message Adrian Klaver 2022-08-03 15:12:45 Re: "Missing" column in Postgres logical replication update message