Marking indexes out of date (WAS: loading data, creating indexes, clustering, vacuum) feature request?

Angva wrote:
> Looking for a small bit of advice...
>
> I have a script that updates several tables with large amounts of data.
> Before running the updates, it drops all indexes for optimal
> performance. When the updates have finished, I run the following
> procedure:
>
> recreate the indexes
> cluster the tables
> vacuum full analyze on the tables

Hi all,

I'd like to see a general way to take indexes off line without actually
losing their definitions.  For example, something like "ALTER TABLE [EN
| DIS] ABLE INDEXES", "ALTER INDEX [EN | DIS] ABLE", etc.  This could
also be used internally when a backend encounters an error
reading/writing an index.  Rather than refusing to execute queries, it
could just ignore indexes it knows are disabled or bad in some way and
re-plan as needed.

This would have two benefits.  First, the above scenerio would be much
simpler.  Rather than dropping and re-creating new indexes, you could
just disable and then re-enable them without having any knowledge of
their structure.  Secondly, it would allow us to put indexes in an
alternate table space on a non-redundant volume and, in the case of a
drive failure, be able to limp along, and get the system back to normal
simply by replacing the disk and issuing a REINDEX command.

I realize there are a couple gotchas with this.  For example, what to do
with unique indexes?  Perhaps a backend would still need to refuse to do
update/inserts on a table with degraded unique indexes, unless the index
was disabled explicitly?  And then, refuse to rebuild/re-enable the
index as normal if non-unique values found?


Thx for considering :-)

-Glen Parker