Re: pg_upgrade does not upgrade pg_stat_statements properly

On Thu, 15 Jul 2021 at 14:31, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thu, Jul 15, 2021 at 11:14 AM Jan Wieck <jan(at)wi3ck(dot)info> wrote:
>
>> On 7/15/21 1:10 PM, David G. Johnston wrote:
>> > ... But while
>> > PostgreSQL doesn't really have a choice here - it cannot be expected to
>> > subdivide itself - extensions (at least external ones - PostGIS is one
>> I
>> > have in mind presently) - can and often do attempt to support multiple
>> > versions of PostgreSQL for whatever major versions of their product
>> they
>> > are offering. For these it is possible to adhere to the "change one
>> > thing at a time principle" and to treat the extensions as not being
>> part
>> > of "ALL the changes from one major version to the target version..."
>>
>> You may make that exception for an external extension like PostGIS. But
>> I don't think it is valid for one distributed in sync with the core
>> system in the contrib package, like pg_stat_statements. Which happens to
>> be the one named in the subject line of this entire discussion.
>>
>>
> Yep, and IIUC running "CREATE EXTENSION pg_stat_statements VERSION '1.5';"
> works correctly in v13 as does executing
>
While it does work there are issues with dumping and restoring a database
with the old version of pg_stat_statements in it that would only be found
by dumping and restoring.

> "ALTER EXTENSION pg_stat_statements UPDATE;" while version 1.5 is
> installed.
>

> So even without doing the copying of the old contrib libraries to the new
> server such a "one at a time" procedure would work just fine for this
> particular contrib extension.
>

You cannot copy the old contrib libraries into the new server. This will
fail due to changes in the API and various exported variables either not
being there or being renamed.

>
> And since the OP was unaware of the presence of the existing ALTER
> EXTENSION UPDATE command I'm not sure at what point a "lack of features"
> complaint here is due to lack of knowledge or actual problems (yes, I did
> forget too but at least this strengthens my position that one-at-a-time
> methods are workable, even today).
>

You are correct I was not aware of the ALTER EXTENSION UPDATE command, but
that doesn't change the issue.
It's not so much the lack of features that I am complaining about; it is
the incompleteness of the upgrade. pg_upgrade does a wonderful job telling
me what extensions are incompatible with the upgrade before it does the
upgrade, but it fails to say that the versions that are installed may need
to be updated.

Dave