ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound
Hi, We have a postgres 10.7 database which reports a number of issues on user-created tables as well as system tables. Most errors are one of the following: -- ERROR: found xmin 1888159934 from before relfrozenxid 1998177448 -- ERROR: MultiXactId 613819197 does no longer exist -- apparent wraparound -- ERROR: could not access status of transaction 1927393975 DETAIL: Could not open file "pg_xact/072E": No such file or directory. I have tried several of the workarounds suggested online and in the web discussion groups: 1. vacuumdb of the entire database fails with the "found xmin from before relfrozenxid" error 2. pg_dump fails with the same error 3. SELECT sql on the affected tables fails with the error. So I cannot save the table, drop it and re-create it. 4. Removed the "global/pg_internal.init" file and re-started the cluster. Still the same errors. The database is up and running and most of the tables are accessible. But any kind of SQL on the 4 or 5 affected tables throws the error. Is there a way to repairing the corruption in this database? Postgres Version 10.7 on Linux(Ubuntu). Thanks. Alanoly Andrews (alano...@invera.com) This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'.
Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound
Thanks, Thom. I understand from your response that there is really no way to repair the current damage. Yes, we do take daily backups and we have, in fact, restored the database cluster to a point in time before the corruption, suffering some loss of data in the process. I'm now working with the snapshot of the corrupted database (on a different box) to see if there is something that can be done to repair the damage and avoid such a scenario in future. Yes, and I know that upgrading the Postgres version is the stock answer for situations like this. The upgrade is in the works. But I was still interested in what the postgres gurus/programmers/hackers had to say about this event. Regards. Alanoly. From: Thom Brown Sent: May 31, 2024 6:14 AM To: Laurenz Albe Cc: Alanoly Andrews ; pgsql-general@lists.postgresql.org Subject: Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound You don't often get email from t...@linux.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification> [Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes. On Fri, May 31, 2024, 09:29 Laurenz Albe mailto:laurenz.a...@cybertec.at>> wrote: On Thu, 2024-05-30 at 14:58 +, Alanoly Andrews wrote: > We have a postgres 10.7 database which reports a number of issues on > user-created > tables as well as system tables. Most errors are one of the following: > -- ERROR: found xmin 1888159934 from before relfrozenxid 1998177448 > -- ERROR: MultiXactId 613819197 does no longer exist -- apparent wraparound > -- ERROR: could not access status of transaction 1927393975 >DETAIL: Could not open file "pg_xact/072E": No such file or directory. > > Is there a way to repairing the corruption in this database? > Postgres Version 10.7 on Linux(Ubuntu). Perhaps, but you should hire an expert if the data are important for you. Also, while it's too late now, this could be the result of a bug in the version you are using that was subsequently repaired in 10.15: Prevent possible data loss from concurrent truncations of SLRU logs (Noah Misch) This rare problem would manifest in later “apparent wraparound” or “could not access status of transaction” errors. This is why it's important to keep up-to-date, but even the latest minor 10.x release is out of date as support was dropped back in 2022. If you manage to get this up and running again, I strongly recommend upgrading to the latest major and minor release (16.3 at the time of writing). Before you try doing anything though, create a physical backup of your database as situations like this tend to require invasive action that could potentially make the situation even worse. Also, did this problem only happen in the last day or two? How frequently do you take backups? If you have a backup from just before this issue starting showing itself, and you can afford losing data changes that have occured since the backup, you may find it far easier and quicker to resort to using that backup. Of course, you would need to prove to yourself that the backup was safe by running a VACUUM FREEZE on each database in that backup before starting to use it. If that runs without issue, you're probably in the clear. Best of luck. Thom This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'.
Streaming replication versus Logical replication
We are currently running some long-running SELECT queries on the replication database in a streaming replication pair. Some of these queries can run for 1 hour or more. To avoid errors related to "data no more being available" on the replication due to vacuuming of old data on the primary database, we have set the following parameters: max_standby_archive_delay = -1, max_standby_streaming_delay = -1, hot_standby_feedback = on. With these set, the long queries are able to run to completion, but there is table bloat on both the primary and the replicated databases, leading to throughput delay on the primary production database. Will this issue exist if we use "logical replication" instead? With the above three parameters set back to normal, will the replicated database get overwritten when vacuuming runs on the primary and removes old data? If it does not, will there be table bloat on the primary database? What is the mechanism by which data changes on the "publisher" are propagated to the "subscriber"? What happens when the subscriber database has an long-running query? Thanks. Alanoly Andrews. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
RE: Streaming replication versus Logical replication
Thanks, Ninad, for the response. So, am I to understand that when there is a long-running query on the subscriber, the vacuumed data updates from the publisher are held over on the subscriber until the query completes? If so, where and how are they held over, and what does it mean in terms of disk space (either on the publisher or on the subscriber)? Regards. Alanoly Andrews. From: Ninad Shah [mailto:nshah.postg...@gmail.com] Sent: Thursday, November 4, 2021 2:20 PM To: Alanoly Andrews Cc: pgsql-general@lists.postgresql.org Subject: Re: Streaming replication versus Logical replication [Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes. Yes, it is going to resolve the issue because streaming is completely a slave(with few exceptions). Even the VACUUM operation gets replicated through the master, which is not a case with logical replication. In logical replication, only data from a few tables gets replicated. In terms of database administration, they are different entities. In case the subscriber has long-running queries, unlike streaming replication, it does not affect synchronisation operations. Regards, Ninad Shah On Thu, 4 Nov 2021 at 21:16, Alanoly Andrews mailto:alano...@invera.com>> wrote: We are currently running some long-running SELECT queries on the replication database in a streaming replication pair. Some of these queries can run for 1 hour or more. To avoid errors related to "data no more being available" on the replication due to vacuuming of old data on the primary database, we have set the following parameters: max_standby_archive_delay = -1, max_standby_streaming_delay = -1, hot_standby_feedback = on. With these set, the long queries are able to run to completion, but there is table bloat on both the primary and the replicated databases, leading to throughput delay on the primary production database. Will this issue exist if we use "logical replication" instead? With the above three parameters set back to normal, will the replicated database get overwritten when vacuuming runs on the primary and removes old data? If it does not, will there be table bloat on the primary database? What is the mechanism by which data changes on the "publisher" are propagated to the "subscriber"? What happens when the subscriber database has an long-running query? Thanks. Alanoly Andrews. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
Using a different column name in a foreign table
Hello, I see that the syntax for the creation of a foreign table allows you to use a column name in the FT that is different from the one in the base table. Such a "create foreign table" statement executes successfully and creates the FT. But when I query the FT, I get an error wrt to the column that had been renamed. See example below: create foreign table tab1_ft ( id int, name char(10) options(column_name 'newname')) server xx options(schema_name 'public', table_name 'tab1'); select * from tab1_ft; ERROR: column "newname" does not exist HINT: Perhaps you meant to reference the column "tab1.name". CONTEXT: Remote SQL command: SELECT id, newname FROM public.tab1 So, it seems that the when the remote SQL command is composed, the mapping of 'newname' to the 'name' in the base table does not take effect. Is there a resolution to this issue? Awaiting some input. Alanoly Andrews. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
RE: Using a different column name in a foreign table
Thanks Tom, Guillaume and Adrian. My mistake in reversing the order of name and newname in the definition. After the switch, it works. ButI'm investigating another environment where the definition appears to have been correct and yet there is an error in select. Maybe I'll post again later, if needed. Thanks. A.A. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, January 21, 2022 11:52 AM To: Alanoly Andrews Cc: pgsql-general@lists.postgresql.org Subject: Re: Using a different column name in a foreign table [Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes. Alanoly Andrews writes: > I see that the syntax for the creation of a foreign table allows you to use a > column name in the FT that is different from the one in the base table. Such > a "create foreign table" statement executes successfully and creates the FT. > But when I query the FT, I get an error wrt to the column that had been > renamed. See example below: > create foreign table tab1_ft ( > id int, > name char(10) options(column_name 'newname')) server xx > options(schema_name 'public', table_name 'tab1'); > select * from tab1_ft; > ERROR: column "newname" does not exist > HINT: Perhaps you meant to reference the column "tab1.name". > CONTEXT: Remote SQL command: SELECT id, newname FROM public.tab1 > So, it seems that the when the remote SQL command is composed, the mapping of > 'newname' to the 'name' in the base table does not take effect. Huh? The CONTEXT line shows what was issued to the remote server, and it's very obvious that we *are* asking for "newname", as indeed is also implied by the error issued by the remote. I think you just didn't match the name correctly to the actual name on the remote. regards, tom lane This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
RE: Using a different column name in a foreign table
Following up on my previous message, there was a related problem with FT definitions for which I have a workaround. It seems that the new column_name after "options" in an FT definition cannot be in upper case. See below: When I define a foreign table like below, I get an error in the subsequent select statement: create foreign table scvmgl_rec ( MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM')) server pglpolaris12018 options (schema_name 'informix', table_name 'sctmsg_rec'); ai01pr00=# select * from scvmgl_rec; ERROR: column "MSG_CLNT_HOST_NM" does not exist CONTEXT: Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM informix.sctmsg_rec But when I define the FT as below, there is no error in the subsequent "select". create foreign table scvmgl_rec ( mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm')) server pglpolaris12018 options (schema_name 'informix', table_name 'sctmsg_rec'); So, apparently postgres transmits the remote query with double quotes around the upper-case column name; and such a query comes back with "column does not exist". But a double quote around a lower-case column name does not produce the error. Is this a bug that needs to be fixed? I always thought that table names and column names were case-insensitive with regard to SQL's. A.A. -Original Message- From: Alanoly Andrews Sent: Friday, January 21, 2022 12:03 PM To: Tom Lane ; Guillaume Lelarge ; Adrian Klaver Cc: pgsql-general@lists.postgresql.org Subject: RE: Using a different column name in a foreign table Thanks Tom, Guillaume and Adrian. My mistake in reversing the order of name and newname in the definition. After the switch, it works. ButI'm investigating another environment where the definition appears to have been correct and yet there is an error in select. Maybe I'll post again later, if needed. Thanks. A.A. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, January 21, 2022 11:52 AM To: Alanoly Andrews mailto:alano...@invera.com>> Cc: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org> Subject: Re: Using a different column name in a foreign table [Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes. Alanoly Andrews mailto:alano...@invera.com>> writes: > I see that the syntax for the creation of a foreign table allows you to use a > column name in the FT that is different from the one in the base table. Such > a "create foreign table" statement executes successfully and creates the FT. > But when I query the FT, I get an error wrt to the column that had been > renamed. See example below: > create foreign table tab1_ft ( > id int, > name char(10) options(column_name 'newname')) server xx > options(schema_name 'public', table_name 'tab1'); > select * from tab1_ft; > ERROR: column "newname" does not exist > HINT: Perhaps you meant to reference the column "tab1.name". > CONTEXT: Remote SQL command: SELECT id, newname FROM public.tab1 > So, it seems that the when the remote SQL command is composed, the mapping of > 'newname' to the 'name' in the base table does not take effect. Huh? The CONTEXT line shows what was issued to the remote server, and it's very obvious that we *are* asking for "newname", as indeed is also implied by the error issued by the remote. I think you just didn't match the name correctly to the actual name on the remote. regards, tom lane This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
RE: Using a different column name in a foreign table
Thanks Adrian. I was aware of that. But...in the case of FT definition, the quotes are mandatory after column_name in options. And it is a single quote. The problem here may be is that the "create foreign table" statement accepts only lower case after options. Remember that the double quotes for the column name in the "select" statement is generated by postgres, not by the end user. If the double quotes were not present, the query with the upper case would have worked. A.A. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Friday, January 21, 2022 1:14 PM To: Alanoly Andrews ; Tom Lane ; Guillaume Lelarge Cc: pgsql-general@lists.postgresql.org Subject: Re: Using a different column name in a foreign table [Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes. On 1/21/22 10:04 AM, Alanoly Andrews wrote: > Following up on my previous message, there was a related problem with > FT definitions for which I have a workaround. It seems that the new > column_name after "options" in an FT definition cannot be in upper case. > See below: > When I define a foreign table like below, I get an error in the > subsequent select statement: > create foreign table scvmgl_rec ( > MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM')) > server pglpolaris12018 options (schema_name 'informix', table_name > 'sctmsg_rec'); ai01pr00=# select * from scvmgl_rec; > ERROR: column "MSG_CLNT_HOST_NM" does not exist > CONTEXT: Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM > informix.sctmsg_rec But when I define the FT as below, there is no > error in the subsequent "select". > create foreign table scvmgl_rec ( > mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm')) > server pglpolaris12018 options (schema_name 'informix', table_name > 'sctmsg_rec'); So, apparently postgres transmits the remote query with > double quotes around the upper-case column name; and such a query > comes back with "column does not exist". But a double quote around a > lower-case column name does not produce the error. > Is this a bug that needs to be fixed? I always thought that table > names and column names were case-insensitive with regard to SQL's. You thought wrong: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS 'Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)' > A.A. > -- Adrian Klaver adrian.kla...@aklaver.com This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to a new disclaimer message.
