Why is my table continuousely written? -> XID issue? - Mailing list pgsql-general

From Peter
Subject Why is my table continuousely written? -> XID issue?
Date
Msg-id YuqRBap0ktay785n@gate.intra.daemon.contact
Whole thread Raw
In response to Why is my table continuousely written?  (Peter <pmc@citylink.dinoex.sub.org>)
List 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?




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: "Missing" column in Postgres logical replication update message
Next
From: Kevin Martin
Date:
Subject: Re: "Missing" column in Postgres logical replication update message