[Pkg-postgresql-public] GiST rebuild

[Markus Wanner]

Hi,

Stephen Frost wrote:
> I dislike offering to recreate the indexes during the upgrade.

Your point is well taken.

I generally dislike leaving lots of trivial administrative tasks to the
admin, which is why I'd like to *optionally* automate it. One can always
choose to do it manually.

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

That's why I mentioned a warning about that as well. If you think
recreating your GiST indices takes very long and you want to postpone it
you can do so.

Not offering the automatism because it could take long for some users
seems overly cautious to me.

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

During such an upgrade, the database is stopped and restarted anyway, so
"not having to contact" seems a fishy argument to me.

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

Sure, that can be done.

> It could miss databases which aren't known to the toolchain (leading to
> a false sense of security for users...)

I've mentioned those and that must certainly be part of the warning. But
again, that's certainly a minority of users, who better known what they
are doing. I think all others are thankful for simplicity.

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

These issues need to be considered, yes. But it's certainly possible for
a script to check for such errors and warn the user if GiST reindexing
failed. That's not much of an argument for not trying, IMO.


Anyway, so far that's my opinion, standing against Stephen's. And I
certainly haven't implemented it, so I might still step back and revoke
this proposal :-)

Other opinions? Vote now!

Regards

Markus Wanner