Re: No xmin in pg_database

On Thu, 2021-09-02 at 08:50 +0300, Alexander Kass wrote:
> Hi! I'm Alexander from DataGrip.
> We actively use xmin's from pg_catalog tables to incrementally
> synchronize our database model.
> We have a small number of users that do not have xmin in pg_database
> (we've asked them to try `select xmin from pg_database` and got
> `column xmin does not exist`).
> There is an old ticket https://youtrack.jetbrains.com/issue/DBE-7588
> (at that time there was no real need in that xmin so we just removed
> it)
> Now xmins are back and so are the problems.
> The most recent report is about version `PostgreSQL 12.4 (Ubuntu
> 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit`
>
> How can that happen that there is no xmin in pg_database?
> Is it a normal behaviour and that is configurable? Or is this a kind
> of data corruption?
> Can that happen to other tables?
>
> I haven't been able to find answers myself, so I'm asking for help :)
> Thanks in advance

All PostgreSQL tables have "xmin", and all catalog tables do as well.

If you use a non-standard table access method, a table might not
have "xmin". But that cannot apply to catalog tables.

Perhaps that was not really PostgreSQL, but some fork where the
persistence layer was modified?

I am not sure if it is a good idea to rely on "xmin" at all. These
numbers are recycled when transaction IDs wrap around, and you could
have two entries with the same "xmin" that have a totally different
meaning, because one of the rows is frozen and the other isn't.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com