Re: BUG #17949: Adding an index introduces serialisation anomalies.

On Mon, Jun 26, 2023 at 2:53 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> FWIW, there is no fundamental reason why nbtree couldn't always
> preallocate a single empty leaf page during CREATE INDEX -- this leaf
> page is where values whose keyspace is between negative and positive
> infinity (i.e. all items) are located. That would fix this bug. Since,
> of course, it would make the theory of operation that you describe
> work reliably, even with an empty index. This isn't a serious
> proposal, of course -- lazily allocating the first real page has
> value, and we're hardly going to throw that away just to fix this bug.
> My point is that not allocating a leaf page in CREATE INDEX is the
> special case here, if anything.

I did briefly wonder about creating the root page on demand here
(probably with a bogus use of BT_WRITE or something like that),
which'd be pretty much equivalent to what you're suggesting there
except it'd work for existing empty indexes in the wild, but I wasn't
sure what complications that might have and didn't look further once I
thought of the 0001 patch's approach.