Re: Reconstructing transaction content after the fact

Lionel Bouton <lionel(dot)bouton(at)jtek(dot)fr> writes:
> Are txids in table file data indeed a means by which you can recover the
> data written by a single transaction (assuming the txids don't overflow
> the 32bit limit during the life of the cluster) ?

They are. You could reduce the connections between different rows by
writing them in different subtransactions of the parent transaction
(use SAVEPOINT/RELEASE SAVEPOINT, or exception blocks in plpgsql).
But unless there's many parallel transactions writing data, somebody
could probably still reconstruct things by assuming that nearby XIDs
represent subtransactions of a single transaction.

> 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.

> Is there a way to access these values by connecting to a PostgreSQL
> server instead of analyzing in-memory or on-disk data ?

SELECT xmin, ... FROM votes;

regards, tom lane