Re: Disabling an index temporarily

On Sun, Dec 13, 2015 at 1:33 AM, Oleg Bartunov <obartunov(at)gmail(dot)com> wrote:

>
> On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova <
> jaime(dot)casanova(at)2ndquadrant(dot)com> wrote:
>
>> indexrelid = 'indexname'::regclass;
>
>
> This works, but might bloat system catalog.
>
>
+1 for the functionality.
+1 for ALTER INDEX foo SET DISABLED

I mentioned the need for this functionality to PeterG as PgConfUS back in
March when he asked what I missed most about Oracle, where it came into
play when doing partitions swaps and similar bulk Data Warehouse
operations. He didn't seem to think it would be too hard to implement.

But the real win would be the ability to disable all indexes on a table
without specifying names. Even Oracle has to do this with an anonymous
pl/sql block querying dba_indexes or all_indexes, a pity for such a common
pattern.

So, I'd propose we following syntax:

ALTER INDEX foo SET DISABLED
-- does the SET indisvalid = false shown earlier.

ALTER TABLE foo DISABLE [NONUNIQUE] INDEXES
-- same, but joining to pg_class and possibly filtering on indisunique

REINDEX [DISABLED [INDEXES ON]] TABLE table_name [PARALLEL [degree]]
or
REINDEX [INVALID [INDEXES ON]] TABLE table_name [PARALLEL [degree]]

In this last case, REINDEX would walk the catalog as it does now, but
potentially filtering the table indexes on indisvalid = false. I'd ask that
we make a parallel spec part of the command even if it is not initially
honored.

This would be another feather in Postgres's cap of letting the user write
clear code and hiding implementation specific complexity.