Re: pg_upgrade does not upgrade pg_stat_statements properly

On 7/29/21 12:00 PM, David G. Johnston wrote:
> Ok, looking at the flow again, where exactly would the user even be able
> to execute "CREATE EXTENSION" meaningfully?  The relevant databases do
> not exist (not totally sure what happens to the postgres database
> created during the initdb step...) so at the point where the user is
> "installing the extension" all they can reasonably do is a server-level
> install (they could maybe create extension in the postgres database, but
> does that even matter?).
>
> So, I'd propose simplifying this all to something like:
>
> Install extensions on the new server

Extensions are not installed on the server level. Their binary
components (shared objects) are, but the actual catalog modifications
that make them accessible are performed per database by CREATE
EXTENSION, which executes the SQL files associated with the extension.
And they can be performed differently per database, like for example
placing one and the same extension into different schemas in different
databases.

pg_upgrade is not (and should not be) concerned with placing the
extension's installation components into the new version's lib and share
directories. But it is pg_upgrade's job to perform the correct catalog
modification per database during the upgrade.

> Any extensions that are used by the old cluster need to be installed
> into the new cluster.  Each database in the old cluster will have its
> current version of all extensions migrated to the new cluster as-is. 
> You can use the ALTER EXTENSION command, on a per-database basis, to
> update its extensions post-upgrade.

That assumes that the extension SQL files are capable of detecting a
server version change and perform the necessary (if any) steps to alter
the extension's objects accordingly.

Off the top of my head I don't remember what happens when one executes
ALTER EXTENSION ... UPGRADE ... when it is already on the latest version
*of the extension*. Might be an error or a no-op.

And to make matters worse, it is not possible to work around this with a
DROP EXTENSION ... CREATE EXTENSION. There are extensions that create
objects, like user defined data types and functions, that will be
referenced by end user objects like tables and views.