B-tree page deletion boundary cases - Mailing list pgsql-hackers

For the sake of concurrency, our B-tree implementation has a phased process
for reusing empty pages.  Excerpting from nbtree/README:

    A deleted page cannot be reclaimed immediately, since there may be other
    processes waiting to reference it (ie, search processes that just left the
    parent, or scans moving right or left from one of the siblings).  These
    processes must observe that the page is marked dead and recover
    accordingly.  Searches and forward scans simply follow the right-link
    until they find a non-dead page --- this will be where the deleted page's
    key-space moved to.

    ...

    A deleted page can only be reclaimed once there is no scan or search that
    has a reference to it; until then, it must stay in place with its
    right-link undisturbed.  We implement this by waiting until all
    transactions that were running at the time of deletion are dead; which is
    overly strong, but is simple to implement within Postgres.  When marked
    dead, a deleted page is labeled with the next-transaction counter value.
    VACUUM can reclaim the page for re-use when this transaction number is
    older than the oldest open transaction.

The VACUUM that deletes a page's last tuple calls _bt_pagedel(), which flags
the page BTP_DELETED and stores therein the result of ReadNewTransactionId().
When a later VACUUM visits such a page and observes that the stored XID is now
less than or equal to RecentXmin, it adds the page to the FSM.  An INSERT or
UPDATE will pull the page from the FSM and repurpose it.

As I mentioned[1] peripherally back in November, that algorithm has been
insufficient since the introduction of non-XID-bearing transactions in
PostgreSQL 8.3.  Such transactions do not restrain RecentXmin.  If no running
transaction has an XID, RecentXmin == ReadNewTransactionId() and the page
incorrectly becomes available for immediate reuse.  This is difficult to
encounter in practice.  VACUUM acquires a cleanup lock on every leaf page in
each index.  Consequently, a problem can only arise around a leaf page
deletion when two VACUUMs visit the page during the narrow window when
_bt_steppage() has released the pin on its left sibling and not yet acquired a
read lock on the target.  Non-leaf deletions might not require such narrow
conditions, but they are also exponentially less frequent.  Here is a test
procedure illustrating the bug:

1. In session S1, run these commands:
BEGIN;
CREATE TABLE t (x int, filler character(400));
INSERT INTO t SELECT *, '' FROM generate_series(1, 10000);
CREATE INDEX ON t(x);
DELETE FROM t WHERE x >= 2000 AND x < 4000;
COMMIT;

BEGIN;
SET LOCAL enable_seqscan = off;
SET LOCAL enable_bitmapscan = off;
DECLARE c CURSOR FOR SELECT x FROM t WHERE x >= 1990 AND x < 4510;
FETCH 5 c;

2. Attach gdb to S1 and set a breakpoint on _bt_getbuf.
3. In S1, run "FETCH 10 c".  This will hit the breakpoint.
4. In another session S2, run these commands:
VACUUM VERBOSE t; -- mark some pages BTP_DELETED
VACUUM VERBOSE t; -- update FSM to know about the pages
-- reuse the pages
INSERT INTO t SELECT * FROM generate_series(10001, 12000);

5. Exit gdb to free up S1.  The FETCH only returns five rows.

(The "filler" column makes each index page correspond to more heap pages.
Without it, heap page pins prevent removing some of the tuples on the index
page under test.)


The fix is to compare the stored XID to RecentGlobalXmin, not RecentXmin.  We
already use RecentGlobalXmin when wal_level = hot_standby.  If no running
transaction has an XID and all running transactions began since the last
transaction that did bear an XID, RecentGlobalXmin == ReadNewTransactionId().
Therefore, the correct test is btpo.xact < RecentGlobalXmin, not btpo.xact <=
RecentGlobalXmin as we have today.  This also cleanly removes the need for the
bit of code in _bt_getbuf() that decrements btpo.xact before sending it down
for ResolveRecoveryConflictWithSnapshot().  I suggested[2] that decrement on
an unprincipled basis; it was just masking the off-by-one of using "<=
RecentGlobalXmin" instead of "< RecentGlobalXmin" in _bt_page_recyclable().

This change makes empty B-tree pages wait through two generations of running
transactions before reuse, so some additional bloat will arise.  Furthermore,
the set of transactions having snapshots precluding reuse of the page will
continue to grow until the next transaction to allocate an XID commits.  The
alternative of occasionally returning wrong query results won't do, though.
While we could explore fundamentally-different page deletion algorithms for
PostgreSQL 9.3, this is the only fix coming to mind that's suitable for today.

For purposes of log message writing, this patch effectively reverts commits
758bd2a433d64bed00ca084203b3e5ccfdea4499 and
e1cd66f74862936d84acf3008118d6094c56ad58.  I've attempted to document in
comments all the questions raised over on the SP-GiST/hot standby thread[3].

Thanks,
nm

[1] http://archives.postgresql.org/message-id/20111122031745.GA10556@tornado.leadboat.com
[2] http://archives.postgresql.org/message-id/20110616144746.GA13694@tornado.leadboat.com
[3] http://archives.postgresql.org/message-id/20120419065516.GC12337@tornado.leadboat.com