Re: [HACKERS] ERROR: MultiXactId 3268957 has not been created yet -- apparent wraparound after missused pg_resetxlogs

[Robert Haas]

On Mon, Oct 16, 2017 at 5:41 PM, alain radix  wrote:
> I’m facing a problem with a PostgreSQL 9.6.2 reporting this error when
> selecting a table
>
> ERROR:  MultiXactId 3268957 has not been created yet -- apparent wraparound
>
> The root cause is not a Postgres bug but a buggy person that used
> pg_resetxlogs obviously without reading or understanding the documentation.

Hmm, I hope you patched that person...

> I’m trying to extract data from the db to create a new one ;-)
>
> But pg_dump logically end with the same error.
>
> I’ve seen the row with t_max  = 3268957 page_inspect, I might use it to
> extract row from the page, but this will not be quite easy.

Not sure if this would work, but maybe try BEGIN ... UPDATE the row,
perhaps via TID qual ... ROLLBACK?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ERROR: MultiXactId 3268957 has not been created yet -- apparent wraparound after missused pg_resetxlogs

[Alvaro Herrera]

RADIX Alain - externe wrote:

> I'm facing a problem with a PostgreSQL 9.6.2 reporting this error when 
> selecting a table
> ERROR:  MultiXactId 3268957 has not been created yet -- apparent wraparound
> 
> The root cause is not a Postgres bug but a buggy person that used 
> pg_resetxlogs obviously without reading or understanding the documentation.
> 
> I'm trying to extract data from the db to create a new one ;-)
> But pg_dump logically end with the same error.
> 
> I've seen the row with t_max  = 3268957 page_inspect, I might use it to 
> extract row from the page, but this will not be quite easy.

I'm not quite sure what it is that you want, other than to chastise the
person who deleted pg_wal.  Maybe pageinspect's heap_page_item_attrs()
function, new in 9.6, can help you extract data from tuples with
"corrupt" xmax.  Or perhaps it is easier to return the pg_control
multixact counters to the original values?

> Is there a way to change the t_max to a value that won't fire the error, with 
> this row.

You could try setting it to 0, and/or set/reset the xmax flags in
infomask.

> Hopefully, this is not a production database :-D but the user want to
> start qualification of the new application version today .. no luck
> for him.

Hurray.  The other good news is that one of your engineers is now an
experienced person.

> At least, there is something to learn, renaming pg_xlog to pg_wal won't stop 
> everybody :-/

Yay?

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ERROR: MultiXactId 3268957 has not been created yet -- apparent wraparound after missused pg_resetxlogs

[RADIX Alain - externe]

Hi,

I'm facing a problem with a PostgreSQL 9.6.2 reporting this error when 
selecting a table
ERROR:  MultiXactId 3268957 has not been created yet -- apparent wraparound

The root cause is not a Postgres bug but a buggy person that used pg_resetxlogs 
obviously without reading or understanding the documentation.

I'm trying to extract data from the db to create a new one ;-)
But pg_dump logically end with the same error.

I've seen the row with t_max  = 3268957 page_inspect, I might use it to extract 
row from the page, but this will not be quite easy.

Is there a way to change the t_max to a value that won't fire the error, with 
this row.

Could pg_filedump be used in this problem configuration.

As usual, xlogs where  deleted even stored in a directory named pg_wal :( ( I 
made the change before 10 ) and xlogs reseted by the same person that commented 
the backup months ago.

Hopefully, this is not a production database :-D but the user want to start 
qualification of the new application version today .. no luck for him.

Any advice of how to extract data is welcome.

At least, there is something to learn, renaming pg_xlog to pg_wal won't stop 
everybody :-/

Regards.



Alain RADIX
Expert SGBD
EDF - DSP/CSP IT-DMA
Solutions Groupe EDF
Expertise Applicative
32 Avenue Pablo Picasso
92016 Nanterre
alain-externe.ra...@edf.fr
Tél. : 01.78.66.66.83

BAL Générique : 
support-oracle-nive...@edf.fr
BAL Générique : 
support-postgres-nive...@edf.fr
URL de notre communauté : https://sissi.edf.fr/web/expertise-sgbd/

[cid:image002.png@01D34687.91D9DDA0]

Un geste simple pour l'environnement, n'imprimez ce message que si vous en avez 
l'utilité.






Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.

[HACKERS] ERROR: MultiXactId 3268957 has not been created yet -- apparent wraparound after missused pg_resetxlogs

[alain radix]

Hi,



I’m facing a problem with a PostgreSQL 9.6.2 reporting this error when
selecting a table

ERROR:  MultiXactId 3268957 has not been created yet -- apparent wraparound



The root cause is not a Postgres bug but a buggy person that used
pg_resetxlogs obviously without reading or understanding the documentation.



I’m trying to extract data from the db to create a new one ;-)

But pg_dump logically end with the same error.



I’ve seen the row with t_max  = 3268957 page_inspect, I might use it to
extract row from the page, but this will not be quite easy.



Is there a way to change the t_max to a value that won’t fire the error,
with this row.



Could pg_filedump be used in this problem configuration.



As usual, xlogs where  deleted even stored in a directory named pg_wal L (
I made the change before 10 ) and xlogs reseted by the same person that
commented the backup months ago.



Hopefully, this is not a production database :-D but the user want to start
qualification of the new application version today .. no luck for him.



Any advice of how to extract data is welcome.



At least, there is something to learn, renaming pg_xlog to pg_wal won’t
stop everybody :-/



Regards.


Alain Radix