ERROR: found xmin from before relfrozenxid

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Hey,
I'm trying to help a guy that is using pg9.6 but I'm not so familiar with the error message : 
ERROR:  found xmin 16804535 from before relfrozenxid 90126924
CONTEXT:  automatic vacuum of table db1.public.table_1"


It seems that the error has started appearing two weeks ago.  Data that I collected : 

-all the autovacuum params are set to default

-SELECT relname, age(relfrozenxid) as xid_age, 
    pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class 
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 4;
            relname            |  xid_age  | table_size 
-------------------------------+-----------+------------
  table_1            | 180850538 | 10 GB
table_2       | 163557812 | 10 GB
table_3 | 143732477 | 1270 MB
table_4       |  70464685 | 3376 MB

pg_controldata : 
Latest checkpoint's NextXID:          0:270977386
Latest checkpoint's NextOID:          25567991
Latest checkpoint's NextMultiXactId:  1079168
Latest checkpoint's NextMultiOffset:  68355
Latest checkpoint's oldestXID:        77980003
Latest checkpoint's oldestXID's DB:   16403
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1047846
Latest checkpoint's oldestMulti's DB: 16403
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0

It seems that the autovacuum cant vacuum table_1 and it has alot of dead_tuples. Moreover, it seems that the indexes are bloated.

schemaname relname n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum last_analyze
public table_10 5422370 0 382222 109582923 10760701

I tried to vacuum the table (full,freeze) but it didnt help.
I read about the wrap that can happen but to be honest I'm not sure that I understood id. 
What can I do to vacuum the table ? Can some one explain the logic behind the error message ?

Thanks.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Jerry Sievers-3
Mariel Cherkassky <[hidden email]> writes:

> Hey,
> I'm trying to help a guy that is using pg9.6 but I'm not so familiar
> with the error message : 
> ERROR:  found xmin 16804535 from before relfrozenxid 90126924
> CONTEXT:  automatic vacuum of table db1.public.table_1"

9.6.?...

That error or a very similar one was fixed in a recent point release.

HTH

--
Jerry Sievers
Postgres DBA/Development Consulting
e: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Yeah 9.6 !

On Wed, Jan 23, 2019, 9:51 PM Jerry Sievers <[hidden email] wrote:
Mariel Cherkassky <[hidden email]> writes:

> Hey,
> I'm trying to help a guy that is using pg9.6 but I'm not so familiar
> with the error message : 
> ERROR:  found xmin 16804535 from before relfrozenxid 90126924
> CONTEXT:  automatic vacuum of table db1.public.table_1"

9.6.?...

That error or a very similar one was fixed in a recent point release.

HTH

--
Jerry Sievers
Postgres DBA/Development Consulting
e: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
In reply to this post by Jerry Sievers-3
I'm checking the full version. 
As you said I saw that in 9.6.9 there was a fix for the next bug : 

Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, Álvaro Herrera)

This could happen if some tuples were locked (but not deleted). While queries would still function correctly, vacuum would normally ignore such pages, with the long-term effect that the tuples were never frozen. In recent releases this would eventually result in errors such as "found multixact nnnnn from before relminmxid nnnnn".

So basically, he just need to upgrade in order to fix it ? Or there is something else that need to be done?


‫בתאריך יום ד׳, 23 בינו׳ 2019 ב-21:51 מאת ‪Jerry Sievers‬‏ <‪[hidden email]‬‏>:‬
Mariel Cherkassky <[hidden email]> writes:

> Hey,
> I'm trying to help a guy that is using pg9.6 but I'm not so familiar
> with the error message : 
> ERROR:  found xmin 16804535 from before relfrozenxid 90126924
> CONTEXT:  automatic vacuum of table db1.public.table_1"

9.6.?...

That error or a very similar one was fixed in a recent point release.

HTH

--
Jerry Sievers
Postgres DBA/Development Consulting
e: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Adrien Nayrat-2
On 1/24/19 3:14 PM, Mariel Cherkassky wrote:

> I'm checking the full version.
> As you said I saw that in 9.6.9 there was a fix for the next bug :
>
> Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee,
> Álvaro Herrera)
>
> This could happen if some tuples were locked (but not deleted). While
> queries would still function correctly, vacuum would normally ignore
> such pages, with the long-term effect that the tuples were never frozen.
> In recent releases this would eventually result in errors such as "found
> multixact nnnnn from before relminmxid nnnnn".
>
> So basically, he just need to upgrade in order to fix it ? Or there is
> something else that need to be done?
>
>

Hello,

The fix prevent this error occur, but it doesn't fix tuples impacted by
this bug.

Did you try this : psql -o /dev/null -c "select * from table for update"
database


As suggested by Alexandre Arruda :
https://www.postgresql.org/message-id/CAGewt-ukbL6WL8cc-G%2BiN9AVvmMQkhA9i2TKP4-6wJr6YOQkzA%40mail.gmail.com



Regards,

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
I'm getting this issue when I try to connect to a specific db. Does it matters what table I specify ? Should I just choose a random table from the problematic db? If I'll dump the db and restore it it can help ?

On Fri, Jan 25, 2019, 10:19 AM Adrien NAYRAT <[hidden email] wrote:
On 1/24/19 3:14 PM, Mariel Cherkassky wrote:
> I'm checking the full version.
> As you said I saw that in 9.6.9 there was a fix for the next bug :
>
> Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee,
> Álvaro Herrera)
>
> This could happen if some tuples were locked (but not deleted). While
> queries would still function correctly, vacuum would normally ignore
> such pages, with the long-term effect that the tuples were never frozen.
> In recent releases this would eventually result in errors such as "found
> multixact nnnnn from before relminmxid nnnnn".
>
> So basically, he just need to upgrade in order to fix it ? Or there is
> something else that need to be done?
>
>

Hello,

The fix prevent this error occur, but it doesn't fix tuples impacted by
this bug.

Did you try this : psql -o /dev/null -c "select * from table for update"
database


As suggested by Alexandre Arruda :
https://www.postgresql.org/message-id/CAGewt-ukbL6WL8cc-G%2BiN9AVvmMQkhA9i2TKP4-6wJr6YOQkzA%40mail.gmail.com



Regards,

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Adrien Nayrat-2
On 1/25/19 6:20 PM, Mariel Cherkassky wrote:
> I'm getting this issue when I try to connect to a specific db. Does it
> matters what table I specify ? Should I just choose a random table from
> the problematic db? If I'll dump the db and restore it it can help ?

Error message is on "db1.public.table_1", but maybe other tables are
impacted.

If you can dump and restore your database it should fix your issue. Be
careful to apply minor update (9.6.11 f you can).


Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Update to the minor version should be an easy solution - yum update postgresql . What did you mean by carful

On Sat, Jan 26, 2019, 12:48 PM Adrien NAYRAT <[hidden email] wrote:
On 1/25/19 6:20 PM, Mariel Cherkassky wrote:
> I'm getting this issue when I try to connect to a specific db. Does it
> matters what table I specify ? Should I just choose a random table from
> the problematic db? If I'll dump the db and restore it it can help ?

Error message is on "db1.public.table_1", but maybe other tables are
impacted.

If you can dump and restore your database it should fix your issue. Be
careful to apply minor update (9.6.11 f you can).

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Adrien Nayrat-2
On 1/26/19 11:56 AM, Mariel Cherkassky wrote:
> Update to the minor version should be an easy solution - yum update
> postgresql . What did you mean by carful


Sorry, I meant, do not forget to apply update to be sure same bug do not
happen again.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
It seems that the version of the db is 9.6.10 : 
psql -U db -d db -c "select version()";
Password for user db: 
version 
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

and the error is still exist..

‫בתאריך שבת, 26 בינו׳ 2019 ב-12:59 מאת ‪Adrien NAYRAT‬‏ <‪[hidden email]‬‏>:‬
On 1/26/19 11:56 AM, Mariel Cherkassky wrote:
> Update to the minor version should be an easy solution - yum update
> postgresql . What did you mean by carful


Sorry, I meant, do not forget to apply update to be sure same bug do not
happen again.
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Alvaro Herrera-9
On 2019-Jan-30, Mariel Cherkassky wrote:

> It seems that the version of the db is 9.6.10 :
>
> psql -U db -d db -c "select version()";
> Password for user db:
> version
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
> (1 row)
>
>
> and the error is still exist..

Did you apply the suggested SELECT .. FOR UPDATE to the problem table?

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

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Hey,
As I said, I'm getting this error for all the objects in a specific db. I cant even connect to the database, I immediatly getting this error.
The bug was fixed in 9.6.10 but the db version is 9.6.10 so how can it happen ? The db was installed in that version from the first place and no upgrade was done

‫בתאריך יום ד׳, 30 בינו׳ 2019 ב-11:14 מאת ‪Alvaro Herrera‬‏ <‪[hidden email]‬‏>:‬
On 2019-Jan-30, Mariel Cherkassky wrote:

> It seems that the version of the db is 9.6.10 :
>
> psql -U db -d db -c "select version()";
> Password for user db:
> version
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
> (1 row)
>
>
> and the error is still exist..

Did you apply the suggested SELECT .. FOR UPDATE to the problem table?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
dumping the table and then restoring it solved the case for me. select for update didnt help..

thanks !

‫בתאריך יום ד׳, 30 בינו׳ 2019 ב-12:35 מאת ‪Mariel Cherkassky‬‏ <‪[hidden email]‬‏>:‬
Hey,
As I said, I'm getting this error for all the objects in a specific db. I cant even connect to the database, I immediatly getting this error.
The bug was fixed in 9.6.10 but the db version is 9.6.10 so how can it happen ? The db was installed in that version from the first place and no upgrade was done

‫בתאריך יום ד׳, 30 בינו׳ 2019 ב-11:14 מאת ‪Alvaro Herrera‬‏ <‪[hidden email]‬‏>:‬
On 2019-Jan-30, Mariel Cherkassky wrote:

> It seems that the version of the db is 9.6.10 :
>
> psql -U db -d db -c "select version()";
> Password for user db:
> version
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
> (1 row)
>
>
> and the error is still exist..

Did you apply the suggested SELECT .. FOR UPDATE to the problem table?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Hi All,
Apparently the issue appeared again in the same database but on different table . In the last time dumping and restoring the table helped. However, I dont understand why another table hit the bug if it was fixed in 9.6.9 while my db version is 9.6.10.

Any idea ?

‫בתאריך יום ב׳, 4 בפבר׳ 2019 ב-18:42 מאת ‪Mariel Cherkassky‬‏ <‪[hidden email]‬‏>:‬
dumping the table and then restoring it solved the case for me. select for update didnt help..

thanks !

‫בתאריך יום ד׳, 30 בינו׳ 2019 ב-12:35 מאת ‪Mariel Cherkassky‬‏ <‪[hidden email]‬‏>:‬
Hey,
As I said, I'm getting this error for all the objects in a specific db. I cant even connect to the database, I immediatly getting this error.
The bug was fixed in 9.6.10 but the db version is 9.6.10 so how can it happen ? The db was installed in that version from the first place and no upgrade was done

‫בתאריך יום ד׳, 30 בינו׳ 2019 ב-11:14 מאת ‪Alvaro Herrera‬‏ <‪[hidden email]‬‏>:‬
On 2019-Jan-30, Mariel Cherkassky wrote:

> It seems that the version of the db is 9.6.10 :
>
> psql -U db -d db -c "select version()";
> Password for user db:
> version
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
> (1 row)
>
>
> and the error is still exist..

Did you apply the suggested SELECT .. FOR UPDATE to the problem table?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Adrien Nayrat-2
On 3/12/19 8:58 AM, Mariel Cherkassky wrote:
> Apparently the issue appeared again in the same database but on
> different table . In the last time dumping and restoring the table
> helped. However, I dont understand why another table hit the bug if it
> was fixed in 9.6.9 while my db version is 9.6.10.

Hello,

Could you provide more details (logs...) and remind how you perform
database dump/restore?

This will help community to help you ;)

Regards,

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Hey,
The logs are full of info that I cant share. However, it full of the next messages : 
ERROR:  found xmin 16804535 from before relfrozenxid 90126924
CONTEXT:  automatic vacuum of table db1.public.table_1"
...

What I'm trying to understand here is if the bug was fixed or not. In the first time it appeared the dump and the restore solved the issue. However, is happened the second time on a different table. So basically I'm trying to understand how to solve it permanently. 

the dump command ; pg_dump -d db -U username -t table_name -f table.sql
I dropped the old table and restored it :
drop table table_name;
psql -d db -U username -f table.sql

‫בתאריך יום ד׳, 13 במרץ 2019 ב-14:24 מאת ‪Adrien NAYRAT‬‏ <‪[hidden email]‬‏>:‬
On 3/12/19 8:58 AM, Mariel Cherkassky wrote:
> Apparently the issue appeared again in the same database but on
> different table . In the last time dumping and restoring the table
> helped. However, I dont understand why another table hit the bug if it
> was fixed in 9.6.9 while my db version is 9.6.10.

Hello,

Could you provide more details (logs...) and remind how you perform
database dump/restore?

This will help community to help you ;)

Regards,
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Alexandre Arruda
To avoid a dump/restore, use this:

psql -o /dev/null -c "select * from table for update" database 

Using the last releases of the major versions solve the bug for me.

Best regards 

Em qua, 13 de mar de 2019 às 09:29, Mariel Cherkassky <[hidden email]> escreveu:
Hey,
The logs are full of info that I cant share. However, it full of the next messages : 
ERROR:  found xmin 16804535 from before relfrozenxid 90126924
CONTEXT:  automatic vacuum of table db1.public.table_1"
...

What I'm trying to understand here is if the bug was fixed or not. In the first time it appeared the dump and the restore solved the issue. However, is happened the second time on a different table. So basically I'm trying to understand how to solve it permanently. 

the dump command ; pg_dump -d db -U username -t table_name -f table.sql
I dropped the old table and restored it :
drop table table_name;
psql -d db -U username -f table.sql

‫בתאריך יום ד׳, 13 במרץ 2019 ב-14:24 מאת ‪Adrien NAYRAT‬‏ <‪[hidden email]‬‏>:‬
On 3/12/19 8:58 AM, Mariel Cherkassky wrote:
> Apparently the issue appeared again in the same database but on
> different table . In the last time dumping and restoring the table
> helped. However, I dont understand why another table hit the bug if it
> was fixed in 9.6.9 while my db version is 9.6.10.

Hello,

Could you provide more details (logs...) and remind how you perform
database dump/restore?

This will help community to help you ;)

Regards,
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Mariel Cherkassky
Hey,
The query was the first thing that I tried, it didnt solve the issue.
Guess I'll update to the latest version.

‫בתאריך יום ד׳, 13 במרץ 2019 ב-14:48 מאת ‪Alexandre Arruda‬‏ <‪[hidden email]‬‏>:‬
To avoid a dump/restore, use this:

psql -o /dev/null -c "select * from table for update" database 

Using the last releases of the major versions solve the bug for me.

Best regards 

Em qua, 13 de mar de 2019 às 09:29, Mariel Cherkassky <[hidden email]> escreveu:
Hey,
The logs are full of info that I cant share. However, it full of the next messages : 
ERROR:  found xmin 16804535 from before relfrozenxid 90126924
CONTEXT:  automatic vacuum of table db1.public.table_1"
...

What I'm trying to understand here is if the bug was fixed or not. In the first time it appeared the dump and the restore solved the issue. However, is happened the second time on a different table. So basically I'm trying to understand how to solve it permanently. 

the dump command ; pg_dump -d db -U username -t table_name -f table.sql
I dropped the old table and restored it :
drop table table_name;
psql -d db -U username -f table.sql

‫בתאריך יום ד׳, 13 במרץ 2019 ב-14:24 מאת ‪Adrien NAYRAT‬‏ <‪[hidden email]‬‏>:‬
On 3/12/19 8:58 AM, Mariel Cherkassky wrote:
> Apparently the issue appeared again in the same database but on
> different table . In the last time dumping and restoring the table
> helped. However, I dont understand why another table hit the bug if it
> was fixed in 9.6.9 while my db version is 9.6.10.

Hello,

Could you provide more details (logs...) and remind how you perform
database dump/restore?

This will help community to help you ;)

Regards,
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: found xmin from before relfrozenxid

Adrien Nayrat-2
On 3/13/19 1:59 PM, Mariel Cherkassky wrote:
> Hey,
> The query was the first thing that I tried, it didnt solve the issue.
> Guess I'll update to the latest version.

I read releases notes and I don't find any item that could be related to
the error you encounter. It could be either another bug in postgres or a
storage corruption.

Did you enable checksum when your have restored your database? (In order
to exclude possible storage corruption).

You don't have other error messages in logs?