Re: Disabling an index temporarily

On Mon, Dec 14, 2015 at 10:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>> So, I'd propose we following syntax:
>> ALTER INDEX foo SET DISABLED
>> -- does the SET indisvalid = false shown earlier.
>
> This is exactly *not* what Tatsuo-san was after, though; he was asking
> for a session-local disable, which I would think would be by far the more
> common use-case. It's hard for me to see much of a reason to disable an
> index globally while still paying all the cost to maintain it. Seems to
> me the typical work flow would be more like "disable index in a test
> session, try all your queries and see how well they work, if you conclude
> you don't need the index then drop it". Or perhaps you could imagine that
> you want the index selected for use only in certain specific sessions ...
> but the above doesn't cater for that use-case either.
>
> Certainly, there's opportunities to improve the flexibility of the
> index-disable specifications in the plug-in Oleg and Teodor did. But
> I think that that is the right basic approach: some sort of SET command,
> not anything that alters the catalogs. We already have lots of
> infrastructure that could handle desires like having specific values
> active in only some sessions.

I searched for "indisvalid" and this thread came up.

I need this exact same thing as Tatsuo-san; a way to session-local
disable index(es),
so that plpgsql functions can avoid certain indexes when they are
created/planned.

How would one go about to implement such a SET command, without
altering the catalog?

I noticed the RelationReloadIndexInfo() which appears to be doing a
light-weight update of index changes,
including "relation->rd_index->indisvalid = index->indisvalid".

Or maybe one could call index_set_state_flags(indexId,
INDEX_DROP_CLEAR_VALID) before the function is compiled/planned,
and then reset it using index_set_state_flags(indexId,
INDEX_CREATE_SET_VALID) after it has been compiled/planned?

If someone could give me guidance on where to start I would be grateful.
Even if I don't succeed implementing this, it's at least fun and
interesting to dig into the postgres source code to learn things.

Thanks

Joel