Re: Preventing index scans for non-recoverable index AMs

On Wed, 2008-12-17 at 21:23 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> >
> > On Wed, 2008-12-17 at 18:01 -0500, Jaime Casanova wrote:
> > > On Wed, Dec 17, 2008 at 5:54 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > >
> > > > We don't yet have a mechanism for an
> > > > index AM to say "damn, this index is screwed up, don't use it".
> > >
> > > mark pg_index.indisvalid and/or pg_index.indisready to false in the
> > > hot standby node?
> >
> > We can't edit the database until recovery is over, so that doesn't help
> > us while in recovery mode. So not an option.
>
> Maybe we should add a WAL record that's the physical representation for
> "mark this index invalid", and have any transaction that modifies a hash
> index write that to WAL. It should be simple code to write, because
> the underlying replay is based on a regular heap update.

Doesn't sound like it would work. It doesn't really matter how you
*decide* to do this, it's when you do this that counts.

What we need is a way for recovery to remember a list of pending actions
that can then be issued in a transaction at the end of recovery. As you
pointed out, the Startup process cannot issue transactions, so that
means this is harder than it should be.

Short route is to:
* allow Startup process to run transactions (when recovery finished)
* introduce another rmgr API call that gets called in its own
transaction at the end of recovery

Longer route is to
* allow startup process to queue up work following recovery
* have another process (autovac-ish) get spawned immediately after
recovery to read the list and execute - this would allow us to startup
quickly even if the rmgr decided to mark index invalid and then
completely rebuild the index.

Which sounds like a major project in itself.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support