Re: Reconstructing transaction content after the fact

On 2020-Sep-02, Tom Lane wrote:

> Lionel Bouton <lionel(dot)bouton(at)jtek(dot)fr> writes:

> > Are these t_xmin values ever cleaned up (by VACUUM or another mechanism)
> > ? If positive is there a way to configure the approximate time during
> > which these values can be recovered ?
>
> See VACUUM FREEZE. You couldn't hide connections immediately after
> insertion, but if the idea is to sanitize every so often, it'd help.

Starting with 9.4 which introduced the use of HEAP_XMIN_FROZEN
combination in infomask to replace rewriting the xmin value proper, the
Xids will be preserved by freezing, so that won't help.

One option to hide the xids might be to recreate the tables every once
in a while, with something like

BEGIN;
LOCK TABLE votes;
DROP TABLE IF EXISTS votes_copy;
CREATE TABLE votes_copy AS SELECT * FROM votes;
DROP TABLE votes;
ALTER TABLE votes_copy RENAME TO votes;
-- recreate indexes, if any?
COMMIT;

which will make all rows have the same Xmin. Since the voting process
involves a human act and the tables are not expected to be enormous, it
might not be totally out of the question to do this after every vote, or
in the worst case, once every minute or so.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services