Re: 'Invalid lp' during heap_xlog_delete - Mailing list pgsql-hackers

create table t (c char(1111));
alter table t alter column c set storage plain;

-- Make sure there actually is an allocated page 0 and it is empty.
-- REDO Delete would ignore a non-existant page: XLogReadBufferForRedoExtended: return BLK_NOTFOUND;
-- Hopefully two row deletes don't trigger autovacuum and truncate the empty page.
insert into t values ('1'), ('2');
delete from t;

checkpoint; -- Checkpoint the empty page to disk.

-- This insert should be before the next checkpoint 'start'. I don't want to replay it.
-- And, yes, there needs to be another checkpoint completed to skip its replay and start
-- with the replay of the delete below.
insert into t values ('1'), ('2');

-- Checkpoint needs to start in another session. However, I need to stall the checkpoint
-- to prevent it from writing the dirty page to disk until I get to the vacuum below.
select 'Please start checkpoint in another session';
select pg_sleep(4);

-- Below is the problematic delete.
-- It succeeds now(online) because the dirty page has two rows on it.
-- However, with respect to crash recovery there are 3 possible scenarios depending on timing.
-- 1) The ongoing checkpoint might write the page with the two rows on it before
-- the deletes. This leads to BLK_NEEDS_REDO for the deletes. It works
-- because the page read from disk has the rows on it.
-- 2) The ongoing checkpoint might write the page just after the deletes.
-- In that case BLK_DONE will happen and there'll be no problem. LSN check.
-- 3) The checkpoint can fail to write the dirty page because a vacuum can call
-- smgrtruncate->DropRelFileNodeBuffers() which invalidates the dirty page.
-- If smgrtruncate safely completes the physical truncation then BLK_NOTFOUND
-- happens and we skip the redo of the delete. So the skipped dirty write is OK.
-- The problme happens if we crash after the 2nd checkpoint completes
-- but before the physical truncate 'mdtruncate()'.
delete from t;

-- The vacuum must complete DropRelFileNodeBuffers.
-- The vacuum must sleep for a few seconds to allow the checkpoint to complete
-- such that recovery starts with the Delete REDO.
-- We must crash before mdtruncate() does the physical truncate. If the physical
-- truncate happens the BLK_NOTFOUND will be returned and the Delete REDO skipped.

vacuum t;

--------------------------------------------------------