Tom Lane wrote: > What we've done in the past for comparable situations is to make the > change in a new major version and teach pg_upgrade to detect and report > the need for changes --- in this case, it might do something like create > a script of REINDEX commands for the affected indexes. See e.g. > old_9_6_invalidate_hash_indexes().
I think the whole point is that any knn-gist indexes using this operator are completely broken (i.e. they return the wrong answer), so leaving them as-is in existing branches is not cool.
The idea of an extension update being the trigger for a fix sounds reasonable. Maybe we can have the current function under ~> that throws a WARNING each time it is invoked, inviting people to upgrade the extension; and the new extension would contain a new ~> with the right semantics. Then all the magic to rebuild objects has to occur in the upgrade .sql script.
Yes, the point is that ~> operator was especially designed to get knn-gist support.
However, its design was broken. And operator with that behavior can't be
accelerated using knn-gist. We could leave this operator "as is", but drop its
knn-gist support. But that would be discouraging since then ~> operator would
be left almost useless. Assuming that ~> operator seems to not being heavily
used (bug report was received after more than year after release), I proposed
to change operator behavior so that it can be accelerated by knn-gist.
I like Alvaro's proposal about extension upgrade. Assuming that there are not
many users of ~>, and even smaller amount of them has built depending objects
over ~> operator, I'd like to propose to not invent magic in upgrade .sql script.
In .sql script can just do DROP OPERATOR, and CREATE OPERATOR with
new function. If depending objects exist then this script will trigger an error.
Given this error, user can manually drop depending objects before entension
upgrade. Anyway, assuming that behavior of ~> operator was changed, depending
objects need to be adjusted not just rebuilt. So, magic would unlikely work in
