Thread: ERROR: found xmin from before relfrozenxid
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_1 | 0 | 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.
Mariel Cherkassky <mariel.cherkassky@gmail.com> 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: postgres.consulting@comcast.net
Yeah 9.6 !
On Wed, Jan 23, 2019, 9:51 PM Jerry Sievers <gsievers19@comcast.net wrote:
Mariel Cherkassky <mariel.cherkassky@gmail.com> 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: postgres.consulting@comcast.net
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 <gsievers19@comcast.net>:
Mariel Cherkassky <mariel.cherkassky@gmail.com> 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: postgres.consulting@comcast.net
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,
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 <adrien.nayrat@anayrat.info 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,
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).
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 <adrien.nayrat@anayrat.info 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).
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.
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 <adrien.nayrat@anayrat.info>:
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.
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
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 <alvherre@2ndquadrant.com>:
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
dumping the table and then restoring it solved the case for me. select for update didnt help..
thanks !
בתאריך יום ד׳, 30 בינו׳ 2019 ב-12:35 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:
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 <alvherre@2ndquadrant.com>: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
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 <mariel.cherkassky@gmail.com>:
dumping the table and then restoring it solved the case for me. select for update didnt help..thanks !בתאריך יום ד׳, 30 בינו׳ 2019 ב-12:35 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>: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 <alvherre@2ndquadrant.com>: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
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,
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 <adrien.nayrat@anayrat.info>:
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,
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 <mariel.cherkassky@gmail.com> 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 90126924CONTEXT: 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.sqlI 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 <adrien.nayrat@anayrat.info>: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,
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 <adaldeia@gmail.com>:
To avoid a dump/restore, use this:psql -o /dev/null -c "select * from table for update" databaseUsing the last releases of the major versions solve the bug for me.Best regardsEm qua, 13 de mar de 2019 às 09:29, Mariel Cherkassky <mariel.cherkassky@gmail.com> 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 90126924CONTEXT: 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.sqlI 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 <adrien.nayrat@anayrat.info>: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,
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?