[Pkg-postgresql-public] GiST rebuild

[Stephen Frost]

Markus,

* Markus Wanner (markus at bluegap.ch) wrote:
>  * 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.

This is fine.

>  * 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.

I dislike offering to recreate the indexes during the upgrade.  I really
don't think that's an appropriate time to try and do this.  GiST index
rebuilding will almost certainly take quite a while.  I'd mention the
script and encourage the user to run it against all databases they have
with GiST indexes.  This would avoid having to contact the database at
all during the upgrade process.  I would probably also include a
'README.GiST' or something like that, and point the user to it, which
would include the relevant queries to pull out what GiST indexes exist,
and how to turn that list of tables into a list of commands.

> 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?

I'm not a big fan of trying to automatically do it during the upgrade.
It could miss databases which aren't known to the toolchain (leading to
a false sense of security for users...) and there could be errors and/or
other problems during the index recreation (DB runs out of disk space,
index recreation fails because it's a unique index and the data isn't
actually unique, other things...).  It's not a trivial thing to do when
you consider the possible failure cases and that makes it not
appropriate to do during an upgrade.

	Thanks,

		Stephen
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
Url : http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20081110/c8933c2c/attachment.pgp