I was just about to commit a patch that revises the btree index build
procedure as discussed here:
http://archives.postgresql.org/pgsql-general/2004-05/msg00480.php
specifically, not using shared buffers during index build and bypassing
WAL-logging in favor of just fsyncing the index file before commit.
I was actually writing the commit message when it occurred to me that
this would seriously break PITR. If the WAL datastream doesn't contain
enough info to rebuild the index then rolling forward from a past backup
isn't gonna work.
I thought for a little bit about a magic "reconstruct the index" WAL
entry that would invoke the index build procedure in toto, but that
doesn't look like it will fly either. (Two problems: during crash
recovery, you couldn't be sure that what's on disk for the underlying
table exactly matches the index you need to build --- it could be a
later state of the table; and besides, the environment of the WAL replay
process isn't capable of running user-defined functions, so it couldn't
work for functional indexes.)
So AFAICS, we've got to dump the index contents into WAL to support
PITR. This is a tad annoying.
What I'm thinking about right now is tweaking the index-build code to
write to WAL only if it sees that PITR is actually in use. It would
have to look at the GUC variables to determine whether WAL archiving
is enabled. If archiving isn't turned on, then we could assume that
rollforward from a past backup isn't needed in this installation, and
use the WAL-less index build method.
Comments?
regards, tom lane