Обсуждение: No xmin in pg_database
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
From the version string I can suggest that it is vanilla postgres from The PostgreSQL Global Development Group (PGDG). But we will ask the user. I've checked PG source code, it checks system attrs (like xmin) existence in some cache. Maybe that is really corruption. Does postgres itself rely on xmins of system tables? //offtop As for xmin usage, we have a working scheme. We fetch objects based on dbage(xid), starting from the oldest uncommitted transaction of previous synchronization. Do you think it does not work? On Thu, Sep 2, 2021 at 11:53 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > 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 >
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Thu, 2021-09-02 at 08:50 +0300, Alexander Kass wrote:
>> 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`).
> All PostgreSQL tables have "xmin", and all catalog tables do as well.
Indeed. This seems to be evidence of corruption in the pg_attribute
catalog. If you're really lucky, reindexing pg_attribute might fix
it, though I wonder what other problems there are. (It's odd though
that identical corruption would happen to different installations.)
regards, tom lane
On Thu, 2021-09-02 at 12:10 +0300, Alexander Kass wrote: > As for xmin usage, we have a working scheme. We fetch objects based on > dbage(xid), starting from the oldest uncommitted transaction of > previous synchronization. > Do you think it does not work? I don't know what exactly you are doing, so I cannot be certain. age(xmin) will not necessarily tell you how many transactions ago the row was created... Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
I've asked the user to perform `SELECT xmin, * from pg_attribute WHERE attrelid = 'pg_catalog.pg_database'::regclass` to check the attributes. The user has sent me that a couple of times: both times xmin is different, attrelid is different, both have a big value. Does that mean that pg_database is recreated? Also system attributes are different. My result looks like: +----+--------+--------+------+ |xmin|attrelid|attname |attnum| +----+--------+--------+------+ |1 |1262 |tableoid|-6 | |1 |1262 |cmax |-5 | |1 |1262 |xmax |-4 | |1 |1262 |cmin |-3 | |1 |1262 |xmin |-2 | |1 |1262 |ctid |-1 | |1 |1262 |oid |1 | |1 |1262 |datname |2 | ... His result (apart from dat* attributes) contains only tableoid and oid. Also his attnum numeration starts from tableoid = 1, oid = 2, datname = 3 I'm puzzled PS: I'm not familiar with mailing lists. Is it ok to attach images here? On Thu, Sep 2, 2021 at 1:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Laurenz Albe <laurenz.albe@cybertec.at> writes: > > On Thu, 2021-09-02 at 08:50 +0300, Alexander Kass wrote: > >> 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`). > > > All PostgreSQL tables have "xmin", and all catalog tables do as well. > > Indeed. This seems to be evidence of corruption in the pg_attribute > catalog. If you're really lucky, reindexing pg_attribute might fix > it, though I wonder what other problems there are. (It's odd though > that identical corruption would happen to different installations.) > > regards, tom lane
Alexander Kass <alexander.kass@jetbrains.com> writes:
> I've asked the user to perform `SELECT xmin, * from pg_attribute WHERE
> attrelid = 'pg_catalog.pg_database'::regclass` to check the
> attributes.
> The user has sent me that a couple of times: both times xmin is
> different, attrelid is different, both have a big value.
> Does that mean that pg_database is recreated?
> Also system attributes are different. My result looks like:
> ...
> His result (apart from dat* attributes) contains only tableoid and
> oid. Also his attnum numeration starts from tableoid = 1, oid = 2,
> datname = 3
> I'm puzzled
It's pretty hard to read these details and not conclude that somebody
has been manually manipulating that catalog. Missing rows might possibly
be explained by index corruption ... but there is no scenario under which
pg_database could have an OID different from 1262, nor could this change
of attnums for the surviving rows ever have happened via Postgres-internal
processes.
I'm about ready to file this under "you broke it, you get to keep both
pieces".
regards, tom lane