pg_upgrade does not upgrade pg_stat_statements properly
Upgrading from
10.5 to 13.3 using pg_upgrade -k
The following is the result of an upgrade
select * from pg_extension ;
oid | extname | extowner | extnamespace | extrelocatable |
extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
12910 | plpgsql | 10 | 11 | f |
1.0 | |
16403 | pg_stat_statements | 10 | 2200 | t |
1.5 | |
(2 rows)
test=# \df+ pg_stat_statements_reset
List of functions
Schema | Name | Result data type | Argument data types
| Type | Volatility | Parallel | Owner | Security | Access privileges
| Language | Source code | Description
--------+--------------------------+------------------+---------------------+------+------------+----------+-------+----------+---------------------------+----------+--------------------------+-------------
public | pg_stat_statements_reset | void |
| func | volatile | safe | davec | invoker | davec=X/davec
+| c | pg_stat_statements_reset |
| | |
| | | | | |
pg_read_all_stats=X/davec | | |
(1 row)And this is from creating the extension in a new db on the same instance
foo=# select * from pg_extension ;
oid | extname | extowner | extnamespace | extrelocatable |
extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
12910 | plpgsql | 10 | 11 | f |
1.0 | |
16393 | pg_stat_statements | 10 | 2200 | t |
1.8 | |
(2 rows)
foo=# \df+ pg_stat_statements_reset
List of functions
Schema | Name | Result data type |
Argument data types | Type | Volatility |
Parallel | Owner | Security | Access privileges | Language | Source
code | Description
--------+--------------------------+------------------+--------------------------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------------+-------------
public | pg_stat_statements_reset | void | userid oid DEFAULT
0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0 | func | volatile | safe
| davec | invoker | davec=X/davec | c |
pg_stat_statements_reset_1_7 |
(1 row)
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 ?
Dave
Dave Cramer
On Wednesday, July 14, 2021, Dave Cramer <davecramer@gmail.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.
David J.
On Wed, 14 Jul 2021 at 14:47, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Wednesday, July 14, 2021, Dave Cramer <davecramer@gmail.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.
Dave
Show quoted text
David J.
On Wed, Jul 14, 2021 at 11:59 AM Dave Cramer <davecramer@gmail.com> wrote:
On Wed, 14 Jul 2021 at 14:47, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Wednesday, July 14, 2021, Dave Cramer <davecramer@gmail.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."
David J.
On Wed, 14 Jul 2021 at 15:09, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Wed, Jul 14, 2021 at 11:59 AM Dave Cramer <davecramer@gmail.com> wrote:
On Wed, 14 Jul 2021 at 14:47, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Wednesday, July 14, 2021, Dave Cramer <davecramer@gmail.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
Show quoted text
David J.
On Wed, Jul 14, 2021 at 12:21 PM Dave Cramer <davecramer@gmail.com> wrote:
On Wed, 14 Jul 2021 at 15:09, David G. Johnston <
david.g.johnston@gmail.com> wrote: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.
The point of this behavior is that you don't have to fix anything after an
upgrade - so long as your current extension version works on the new
cluster. If you are upgrading in such a way that the current extension and
new cluster are not compatible you need to not do that. Upgrade instead to
a lesser version where they are compatible. Then upgrade your extension to
its newer version, changing any required user code that such an upgrade
requires, then upgrade the server again.
David J.
Dave Cramer <davecramer@gmail.com> writes:
On Wed, 14 Jul 2021 at 15:09, David G. Johnston <david.g.johnston@gmail.com>
wrote:"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.
That's exactly why we don't force upgrades of extensions. It is on the
user's head to upgrade their extensions from time to time, but we don't
make them do it as part of pg_upgrade. (There are also some
implementation-level reasons to avoid this, IIRC, but the overall
choice is intentional.)
I agree this documentation could be worded better. Another idea
is that possibly pg_upgrade could produce a list of extensions
that are not the latest version, so people know what's left to
be addressed.
regards, tom lane
Dave Cramer
On Wed, 14 Jul 2021 at 15:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Cramer <davecramer@gmail.com> writes:
On Wed, 14 Jul 2021 at 15:09, David G. Johnston <
david.g.johnston@gmail.com>
wrote:
"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.That's exactly why we don't force upgrades of extensions. It is on the
user's head to upgrade their extensions from time to time, but we don't
make them do it as part of pg_upgrade. (There are also some
implementation-level reasons to avoid this, IIRC, but the overall
choice is intentional.)I agree this documentation could be worded better.
As a first step I propose the following
diff --git a/doc/src/sgml/ref/pgupgrade.sgml
b/doc/src/sgml/ref/pgupgrade.sgml
index a83c63cd98..f747a4473a 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -305,9 +305,10 @@ make prefix=/usr/local/pgsql.new install
Install any custom shared object files (or DLLs) used by the old
cluster
into the new cluster, e.g., <filename>pgcrypto.so</filename>,
whether they are from <filename>contrib</filename>
- or some other source. Do not install the schema definitions, e.g.,
- <command>CREATE EXTENSION pgcrypto</command>, because these will be
upgraded
- from the old cluster.
+ or some other source. Do not execute CREATE EXTENSION on the new
cluster.
+ The extensions will be upgraded from the old cluster. However it may
be
+ necessary to recreate the extension on the new server after the
upgrade
+ to ensure compatibility with the new library.
Also, any custom full text search files (dictionary, synonym,
thesaurus, stop words) must also be copied to the new cluster.
</para>Another idea
is that possibly pg_upgrade could produce a list of extensions
that are not the latest version, so people know what's left to
be addressed.
It would be possible to look at the control files in the new cluster to see
the default version and simply output a file with the differences.
We can query pg_extension for the currently installed versions.
Dave
Show quoted text
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
As a first step I propose the following
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade. sgml index a83c63cd98..f747a4473a 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -305,9 +305,10 @@ make prefix=/usr/local/pgsql.new install Install any custom shared object files (or DLLs) used by the old cluster into the new cluster, e.g., <filename>pgcrypto.so</filename>, whether they are from <filename>contrib</filename> - or some other source. Do not install the schema definitions, e.g., - <command>CREATE EXTENSION pgcrypto</command>, because these will be upgraded - from the old cluster. + or some other source. Do not execute CREATE EXTENSION on the new cluster. + The extensions will be upgraded from the old cluster. However it may be + necessary to recreate the extension on the new server after the upgrade + to ensure compatibility with the new library. Also, any custom full text search files (dictionary, synonym, thesaurus, stop words) must also be copied to the new cluster. </para>
I think this needs some work to distinguish between core extensions where
we know the new server already has a library installed and external
extensions where it’s expected that the library that is added to the new
cluster is compatible with the version being migrated (not upgraded). In
short, it should never be necessary to recreate the extension. My
uncertainty revolves around core extensions since it seems odd to tell the
user to overwrite them with versions from an older version of PostgreSQL.
David J.
On Thu, 15 Jul 2021 at 11:01, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
As a first step I propose the following
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index a83c63cd98..f747a4473a 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -305,9 +305,10 @@ make prefix=/usr/local/pgsql.new install Install any custom shared object files (or DLLs) used by the old cluster into the new cluster, e.g., <filename>pgcrypto.so</filename>, whether they are from <filename>contrib</filename> - or some other source. Do not install the schema definitions, e.g., - <command>CREATE EXTENSION pgcrypto</command>, because these will be upgraded - from the old cluster. + or some other source. Do not execute CREATE EXTENSION on the new cluster. + The extensions will be upgraded from the old cluster. However it may be + necessary to recreate the extension on the new server after the upgrade + to ensure compatibility with the new library. Also, any custom full text search files (dictionary, synonym, thesaurus, stop words) must also be copied to the new cluster. </para>I think this needs some work to distinguish between core extensions where
we know the new server already has a library installed and external
extensions where it’s expected that the library that is added to the new
cluster is compatible with the version being migrated (not upgraded). In
short, it should never be necessary to recreate the extension. My
uncertainty revolves around core extensions since it seems odd to tell the
user to overwrite them with versions from an older version of PostgreSQL.
Well clearly my suggestion was not clear if you interpreted that as over
writing them with versions from an older version of PostgreSQL.
Dave Cramer
Show quoted text
On Thursday, July 15, 2021, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
Install any custom shared object files (or DLLs) used by the old cluster into the new cluster, e.g., <filename>pgcrypto.so</filename>, whether they are from <filename>contrib</filename> - or some other source. However it may be + necessary to recreate the extension on the new server after the upgrade + to ensure compatibility with the new library.My uncertainty revolves around core extensions since it seems odd to tell
the user to overwrite them with versions from an older version of
PostgreSQL.
Ok. Just re-read the docs a third time…no uncertainty regarding contrib
now…following the first part of the instructions means that before one
could re-run create extension they would need to restore the original
contrib library files to avoid the new extension code using the old
library. So that whole part about recreation is inconsistent with the
existing unchanged text.
David J.
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
Well clearly my suggestion was not clear if you interpreted that as over
writing them with versions from an older version of PostgreSQL.
Ignoring my original interpretation as being moot; the section immediately
preceding your edit says to do exactly that.
David J.
On Thu, 15 Jul 2021 at 11:15, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Thursday, July 15, 2021, David G. Johnston <david.g.johnston@gmail.com>
wrote:On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
Install any custom shared object files (or DLLs) used by the old cluster into the new cluster, e.g., <filename>pgcrypto.so</filename>, whether they are from <filename>contrib</filename> - or some other source. However it may be + necessary to recreate the extension on the new server after the upgrade + to ensure compatibility with the new library.My uncertainty revolves around core extensions since it seems odd to
tell the user to overwrite them with versions from an older version of
PostgreSQL.Ok. Just re-read the docs a third time…no uncertainty regarding contrib
now…following the first part of the instructions means that before one
could re-run create extension they would need to restore the original
contrib library files to avoid the new extension code using the old
library. So that whole part about recreation is inconsistent with the
existing unchanged text.
The way I solved the original problem of having old function definitions
for pg_stat_statement functions in the *new* library was by recreating the
extension which presumably redefines the functions correctly.
I'm thinking at this point we need something a bit more sophisticated like
ALTER EXTENSION ... UPGRADE. And the extension knows how to upgrade itself.
Dave
Show quoted text
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
I'm thinking at this point we need something a bit more sophisticated like
ALTER EXTENSION ... UPGRADE. And the extension knows how to upgrade itself.
I’m not familiar with what hoops extensions jump through to facilitate
upgrades but even if it was as simple as “create extension upgrade” I
wouldn’t have pg_upgrade execute that command (or at least not by
default). I would maybe have pg_upgrade help move the libraries over from
the old server (and we must be dealing with different databases having
different extension versions in some manner…).
David J.
On Thu, 15 Jul 2021 at 11:29, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Thursday, July 15, 2021, Dave Cramer <davecramer@gmail.com> wrote:
I'm thinking at this point we need something a bit more sophisticated like
ALTER EXTENSION ... UPGRADE. And the extension knows how to upgrade
itself.I’m not familiar with what hoops extensions jump through to facilitate
upgrades but even if it was as simple as “create extension upgrade” I
wouldn’t have pg_upgrade execute that command (or at least not by
default). I would maybe have pg_upgrade help move the libraries over from
the old server (and we must be dealing with different databases having
different extension versions in some manner…).
Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
to make it better ?
Dave
Show quoted text
David J.
On 2021-Jul-15, Dave Cramer wrote:
Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
to make it better ?
I thought the suggestion of having pg_upgrade emit a file with a list of
all extensions needing upgrade in each database was a fairly decent one.
--
Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/
On 2021-Jul-15, Alvaro Herrera wrote:
On 2021-Jul-15, Dave Cramer wrote:
Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
to make it better ?I thought the suggestion of having pg_upgrade emit a file with a list of
all extensions needing upgrade in each database was a fairly decent one.
Eh, and
pg_upgrade [other switches] --upgrade-extensions
sounds good too ...
--
Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/
On Thu, Jul 15, 2021 at 8:43 AM Dave Cramer <davecramer@gmail.com> wrote:
On Thu, 15 Jul 2021 at 11:29, David G. Johnston <
david.g.johnston@gmail.com> wrote:I’m not familiar with what hoops extensions jump through to facilitate
upgrades but even if it was as simple as “create extension upgrade” I
wouldn’t have pg_upgrade execute that command (or at least not by
default). I would maybe have pg_upgrade help move the libraries over from
the old server (and we must be dealing with different databases having
different extension versions in some manner…).Well IMHO the status quo is terrible. Perhaps you have a suggestion on how
to make it better ?
To a certain extent it is beyond pg_upgrade's purview to care about
extension explicitly - it considers them "data" on the database side and
copies over the schema and, within reason, punts on the filesystem by
saying "ensure that the existing versions of your extensions in the old
cluster can correctly run in the new cluster" (which basically just takes a
simple file copy/install and the assumption you are upgrading to a server
version that is supported by the extension in question - also a reasonable
requirement). In short, I don't have a suggestion on how to improve that
and don't really consider it a terrible flaw in pg_upgrade.
I'll readily admit that I lack sufficient knowledge here to make such
suggestions as I don't hold any optionions that things are "quite terrible"
and haven't been presented with concrete problems to consider alternatives
for.
David J.
On Thu, 15 Jul 2021 at 12:13, Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
On 2021-Jul-15, Alvaro Herrera wrote:
On 2021-Jul-15, Dave Cramer wrote:
Well IMHO the status quo is terrible. Perhaps you have a suggestion on
how
to make it better ?
I thought the suggestion of having pg_upgrade emit a file with a list of
all extensions needing upgrade in each database was a fairly decent one.I think this is the minimum we should be doing.
Eh, and
pg_upgrade [other switches] --upgrade-extensions
sounds good too ...
Ultimately I believe this is the solution, however we still need to teach
extensions how to upgrade themselves or emit a message saying they can't,
or even ignore if it truly is a NOP.
Dave
On Thu, Jul 15, 2021 at 9:16 AM Dave Cramer <davecramer@gmail.com> wrote:
Eh, and
pg_upgrade [other switches] --upgrade-extensions
sounds good too ...Ultimately I believe this is the solution, however we still need to teach
extensions how to upgrade themselves or emit a message saying they can't,
or even ignore if it truly is a NOP.
If it's opt-in and simple I don't really care but I doubt I would use it as
personally I'd rather the upgrade not touch my application at all (to the
extent possible) and just basically promise that I'll get a reliable
upgrade. Then I'll go ahead and ensure I have the backups of the new
version and that my application works correctly, then just run the "ALTER
EXTENSION" myself. But anything that will solve pain points for
same-PostgreSQL-version extension upgrading is great.
I would say that it probably should be "--upgrade-extension=aaa
--upgrade_extension=bbb" though if we are going to the effort to offer
something.
David J.
