Re: pg_upgrade does not upgrade pg_stat_statements properly

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

> On Wed, Jul 14, 2021 at 11:59 AM Dave Cramer <davecramer(at)gmail(dot)com> wrote:
>
>>
>>
>> On Wed, 14 Jul 2021 at 14:47, David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Wednesday, July 14, 2021, Dave Cramer <davecramer(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> Notice the upgraded version is 1.5 and the new version is 1.8
>>>>
>>>> I would think somewhere in the upgrade of the schema there should have
>>>> been a create extension pg_stat_statements ?
>>>>
>>>
>>> That would be a faulty assumption. Modules do not get upgraded during a
>>> server version upgrade. This is a good thing, IMO.
>>>
>>
>> This is from the documentation of pg_upgrade
>>
>> Install any custom shared object files (or DLLs) used by the old cluster
>> into the new cluster, e.g., pgcrypto.so, whether they are from contrib or
>> some other source. Do not install the schema definitions, e.g., CREATE
>> EXTENSION pgcrypto, because these will be upgraded from the old cluster.
>> Also, any custom full text search files (dictionary, synonym, thesaurus,
>> stop words) must also be copied to the new cluster.
>>
>> If indeed modules do not get upgraded then the above is confusing at
>> best, and misleading at worst.
>>
>>
> "Install ... files used by the old cluster" (which must be binary
> compatible with the new cluster as noted elsewhere on that page) supports
> the claim that it is the old cluster's version that is going to result.
> But I agree that saying "because these will be upgraded from the old
> cluster" is poorly worded and should be fixed to be more precise here.
>
> Something like, "... because the installed extensions will be copied from
> the old cluster during the upgrade."
>

This is still rather opaque. Without intimate knowledge of what changes
have occurred in each extension I have installed; how would I know what I
have to fix after the upgrade.

Seems to me extensions should either store some information in pg_extension
to indicate compatibility, or they should have some sort of upgrade script
which pg_upgrade would call to fix any problems (yes, I realize this is
hand waving at the moment)

In this example the older version of pg_stat_statements works fine, it only
fails when I do a dump restore of the new database and then the error is
rather obtuse. IIRC pg_dump wanted to revoke all from public from the
function pg_stat_statements_reset() and that could not be found, yet the
function is there. I don't believe we should be surprising our users like
this.

Dave

>
> David J.
>
>