[Pkg-postgresql-public] GiST rebuild

[Markus Wanner]

Hi,

Stephen Frost wrote:
> Pointing to a script would be nice, or even just providing an SQL
> command which pulls out all of the GIST indexes.

Uhm..  "..and includes the recreation commands", I'd add.

> I think they will want them recreated, but at the same time, I think
> very few users will actually have GIST indexes in the grand scheme of
> things.

For users who do *not* have any GiST indices, this should certainly be a
no-op. So I'm only talking about users actually *having* GiST indices.
Out of those, most will want to convert their indices.

The only exception I can think of is, that the user doesn't use the
standard database locations so the pg_*cluster toolchain doesn't know
about the installed database(s).


What I have in mind is something like this:

 * Check if this is an upgrade from pre 8.3.5 to 8.3.5 or newer. Do only
   perform the following steps if that's the case.

 * notify/warn user about GiST changes and necessity to recreate GiST
   indices.

 * Check for GiST indices in known databases.

   If there are GiST indices:

     * Offer to automatically recreate GiST indices after the upgrade,
       maybe warn that this can take some time.

     * Mention an alternative script which automatically does this.

   If there are none:

     * Mention the same script, just in case there are non-standard
       location databases.



The process should IMO ensure the following things:

 * The vast majority of users don't have GiST indices. Those are
   notified, but don't bothered anymore.

 * For the GiST users, automatic GiST recreation can be performed,
   if wanted.

 * In all cases, the user may choose to do it manually, maybe with the
   help of our script.


Does anybody see any problem with that approach?

Regards

Markus Wanner