Re: CUBE seems a bit confused about ORDER BY

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

> > This was discussed upthread and the solution found was "objects need to
> > be rebuilt, indexes need to be reindexed". The point of Alexander's
> > query was to find affected objects that need such treatment. Teodor
> > explicitly disregarded any change in pg_upgrade because the database
> > you're upgrading *from* is supposed to have gotten indexes reindexed,
> > etc.
>
> I don't think that is really going to be acceptable. People do not like
> minor version updates that break their databases. If we start doing
> that we're going to find people refusing to apply minor updates, which
> is not a place we want to be.

I was uncomfortable with the idea of not doing anything to fix old
databases.

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

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services