Re: [HACKERS] FSM corruption leading to errors

Alvaro Herrera writes: > Tom Lane wrote: >> Also, we could at least discount the FSM root page and first intermediate >> page, no? That is, the upper limit could be >> >> pg_relation_size(oid::regclass, 'fsm') / 2 - >> 2*current_setting('block_size')::BIGINT >> >> I

Re: [HACKERS] FSM corruption leading to errors

Tom Lane wrote: > Ah, scratch that, after rereading the FSM README I see it's correct, > because there's a binary tree within each page; I'd only remembered > that there was a search tree of pages. > > Also, we could at least discount the FSM root page and first intermediate > page, no? That

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 24, 2016 at 9:47 PM, Tom Lane wrote: > > > Also, we could at least discount the FSM root page and first intermediate > page, no? That is, the upper limit could be > > pg_relation_size(oid::regclass, 'fsm') / 2 - > 2*current_setting('block_size')::BIGINT >

Re: [HACKERS] FSM corruption leading to errors

Michael Paquier writes: > Release notes refer to this page for methods to fix corrupted instances: > https://wiki.postgresql.org/wiki/Free_Space_Map_Problems > I just typed something based on Pavan's upper method, feel free to > jump in and improve it as necessary.

Re: [HACKERS] FSM corruption leading to errors

I wrote: > It looks to me like this is approximating the highest block number that > could possibly have an FSM entry as size of the FSM fork (in bytes) > divided by 2. But the FSM stores one byte per block. There is overhead > for the FSM search tree, but in a large relation it's not going to

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 24, 2016 at 9:34 PM, Tom Lane wrote: > > SELECT blkno, pg_freespace(oid::regclass, blkno) > FROM generate_series(pg_relation_size(oid::regclass) / > current_setting('block_size')::BIGINT, > pg_relation_size(oid::regclass, 'fsm') /

Re: [HACKERS] FSM corruption leading to errors

On Sat, Oct 22, 2016 at 7:31 AM, Michael Paquier wrote: > On Sat, Oct 22, 2016 at 5:17 AM, Jim Nasby wrote: >> On 10/20/16 10:15 PM, Michael Paquier wrote: >>> >>> 2) If anything is found, stop the server and delete the files manually. >>> 3)

Re: [HACKERS] FSM corruption leading to errors

On Sat, Oct 22, 2016 at 5:17 AM, Jim Nasby wrote: > On 10/20/16 10:15 PM, Michael Paquier wrote: >> >> 2) If anything is found, stop the server and delete the files manually. >> 3) Re-start the server. >> OK, that's troublesome and costly for large relations, but we know

Re: [HACKERS] FSM corruption leading to errors

On 10/20/16 10:15 PM, Michael Paquier wrote: 2) If anything is found, stop the server and delete the files manually. 3) Re-start the server. OK, that's troublesome and costly for large relations, but we know that's the safest way to go for any versions, and there is no need to complicate the

Re: [HACKERS] FSM corruption leading to errors

On Thu, Oct 20, 2016 at 3:37 PM, Pavan Deolasee wrote: > Just to clarify, I meant if we truncate the entire FSM then we'll need API > to truncate VM as well so that VACUUM rebuilds everything completely. OTOH > if we provide a function just to truncate FSM to match the

Re: [HACKERS] FSM corruption leading to errors

On Thu, Oct 20, 2016 at 11:34 AM, Michael Paquier wrote: > On Thu, Oct 20, 2016 at 2:50 PM, Pavan Deolasee > wrote: > > Actually, if we could add an API which can truncate FSM to the given heap > > block, then the user may not even need to

Re: [HACKERS] FSM corruption leading to errors

On Thu, Oct 20, 2016 at 2:50 PM, Pavan Deolasee wrote: > Actually, if we could add an API which can truncate FSM to the given heap > block, then the user may not even need to run VACUUM, which could be costly > for very large tables. FreeSpaceMapTruncateRel()? > Also,

Re: [HACKERS] FSM corruption leading to errors

On Thu, Oct 20, 2016 at 10:50 AM, Michael Paquier wrote: > > For VMs a good way would > be to use pg_visibility's pg_truncate_visibility_map(), but only for > 9.6~. Ah ok.. > For FSM there is no real solution, and actually a > pg_truncate_fsm would prove to be

Re: [HACKERS] FSM corruption leading to errors

On Thu, Oct 20, 2016 at 2:11 PM, Pavan Deolasee wrote: > 4. Remove FSM and VM files for the affected tables (I don't think if it's > safe to do this on a running server) Definitely not while the server is running... For VMs a good way would be to use pg_visibility's

Re: [HACKERS] FSM corruption leading to errors

On Wed, Oct 19, 2016 at 6:54 PM, Tom Lane wrote: > > > Can we document an existing procedure for repairing FSM corruption? > (VACUUM, maybe?) I'm afraid it may not be easy to repair the corruption with existing facilities. Most often the corruption will be on the standby

Re: [HACKERS] FSM corruption leading to errors

On Wed, Oct 19, 2016 at 6:44 PM, Heikki Linnakangas wrote: > On 10/19/2016 02:32 PM, Heikki Linnakangas wrote: > >> >>> >> Oh, forgot that this needs to be backported, of course. Will do that >> shortly... >> > > Done. > Thanks! > > This didn't include anything to cope with

Re: [HACKERS] FSM corruption leading to errors

Heikki Linnakangas writes: > This didn't include anything to cope with an already-corrupt FSM, BTW. > Do we still want to try something for that? I think it's good enough if > we prevent the FSM corruption from happening, but not sure what the > consensus on that might be..

Re: [HACKERS] FSM corruption leading to errors

On 10/19/2016 02:32 PM, Heikki Linnakangas wrote: On 10/19/2016 02:29 PM, Heikki Linnakangas wrote: On 10/19/2016 01:07 PM, Pavan Deolasee wrote: Anyways, we seem good to go with the patch. Ok, committed. Thanks for the analysis! Oh, forgot that this needs to be backported, of course. Will

Re: [HACKERS] FSM corruption leading to errors

On Wed, Oct 19, 2016 at 8:29 PM, Heikki Linnakangas wrote: > On 10/19/2016 01:07 PM, Pavan Deolasee wrote: >> >> Anyways, we seem good to go with the patch. > > Ok, committed. Thanks for the analysis! Thanks! I am surprised that you kept the TAP test at the end. -- Michael --

Re: [HACKERS] FSM corruption leading to errors

On 10/19/2016 02:29 PM, Heikki Linnakangas wrote: On 10/19/2016 01:07 PM, Pavan Deolasee wrote: Anyways, we seem good to go with the patch. Ok, committed. Thanks for the analysis! Oh, forgot that this needs to be backported, of course. Will do that shortly... - Heikki -- Sent via

Re: [HACKERS] FSM corruption leading to errors

On 10/19/2016 01:07 PM, Pavan Deolasee wrote: Anyways, we seem good to go with the patch. Ok, committed. Thanks for the analysis! - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] FSM corruption leading to errors

On Wed, Oct 19, 2016 at 2:37 PM, Heikki Linnakangas wrote: > >> > Actually, this is still not 100% safe. Flushing the WAL before modifying > the FSM page is not enough. We also need to WAL-log a full-page image of > the FSM page, otherwise we are still vulnerable to the torn

Re: [HACKERS] FSM corruption leading to errors

On 10/18/2016 07:01 AM, Pavan Deolasee wrote: On Mon, Oct 17, 2016 at 4:34 PM, Heikki Linnakangas wrote: visibilitymap_truncate is actually also wrong, in a different way. The truncation WAL record is written only after the VM (and FSM) are truncated. But

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 17, 2016 at 4:34 PM, Heikki Linnakangas wrote: > >> > visibilitymap_truncate is actually also wrong, in a different way. The > truncation WAL record is written only after the VM (and FSM) are truncated. > But visibilitymap_truncate() has already modified and dirtied

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 17, 2016 at 8:04 PM, Heikki Linnakangas wrote: > visibilitymap_truncate is actually also wrong, in a different way. The > truncation WAL record is written only after the VM (and FSM) are truncated. > But visibilitymap_truncate() has already modified and dirtied the

Re: [HACKERS] FSM corruption leading to errors

On 10/10/2016 05:25 PM, Michael Paquier wrote: On Fri, Oct 7, 2016 at 2:59 AM, Pavan Deolasee wrote: I believe the fix is very simple. The FSM change during truncation is critical and the buffer must be marked by MarkBufferDirty() i.e. those changes must make to the

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 17, 2016 at 4:14 PM, Pavan Deolasee wrote: > I think this is a major bug and I would appreciate any ideas to get the > patch in a committable shape before the next minor release goes out. We > probably need a committer to get interested in this to make

Re: [HACKERS] FSM corruption leading to errors

On Tue, Oct 11, 2016 at 5:20 AM, Michael Paquier wrote: > > > > > Once the underlying bug is fixed, I don't see why it should break again. > I > > added the above code to mostly deal with already corrupt FSMs. May be we > can > > just document and leave it to the user

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 10, 2016 at 11:41 PM, Pavan Deolasee wrote: > > > On Mon, Oct 10, 2016 at 7:55 PM, Michael Paquier > wrote: >> >> >> >> + /* >> +* See comments in GetPageWithFreeSpace about handling outside the >> valid >> +* range

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 10, 2016 at 7:55 PM, Michael Paquier wrote: > > > + /* > +* See comments in GetPageWithFreeSpace about handling outside the > valid > +* range blocks > +*/ > + nblocks = RelationGetNumberOfBlocks(rel); > + while (target_block >= nblocks &&

Re: [HACKERS] FSM corruption leading to errors

On Fri, Oct 7, 2016 at 11:50 PM, Anastasia Lubennikova wrote: > Could you please add the patches to commitfest? > I'm going to test them and write a review in a few days. Here you go: https://commitfest.postgresql.org/11/817/ -- Michael -- Sent via pgsql-hackers

Re: [HACKERS] FSM corruption leading to errors

On Mon, Oct 10, 2016 at 11:25 PM, Michael Paquier wrote: > At the same time, I have translated your script into a TAP test, I > found that more useful when testing.. Well... Here is the actual patch. -- Michael diff --git a/src/backend/storage/freespace/freespace.c

Re: [HACKERS] FSM corruption leading to errors

On Fri, Oct 7, 2016 at 2:59 AM, Pavan Deolasee wrote: > I investigated a bug report from one of our customers and it looked very > similar to previous bug reports here [1], [2], [3] (and probably more). In > these reports, the error looks something like this: > > ERROR:

Re: [HACKERS] FSM corruption leading to errors

06.10.2016 20:59, Pavan Deolasee: I investigated a bug report from one of our customers and it looked very similar to previous bug reports here [1], [2], [3] (and probably more). In these reports, the error looks something like this: ERROR: could not read block 28991 in file

[HACKERS] FSM corruption leading to errors

I investigated a bug report from one of our customers and it looked very similar to previous bug reports here [1], [2], [3] (and probably more). In these reports, the error looks something like this: ERROR: could not read block 28991 in file "base/16390/572026": read only 0 of 8192 bytes I