On Thu, Jul 29, 2021 at 06:19:56PM -0400, Tom Lane wrote:
> Bruce Momjian <br...@momjian.us> writes:
> > I think we need to first give clear instructions on how to find out if
> > an extension update is available, and then how to update it. I am
> > thinking we should supply a query which reports all extensions that can
> > be upgraded, at least for contrib.
>
> I suggested awhile ago that pg_upgrade should look into
> pg_available_extensions in the new cluster, and prepare
> a script with ALTER EXTENSION UPDATE commands for
> anything that's installed but is not the (new cluster's)
> default version.
OK, done in this patch. I am assuming that everything that shows an
update in pg_available_extensions can use ALTER EXTENSION UPDATE. I
assume this would be backpatched to 9.6.
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 0c47a6b8cc..ad5f391995 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -241,6 +241,8 @@ issue_warnings_and_set_wal_level(void)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906)
old_9_6_invalidate_hash_indexes(&new_cluster, false);
+ report_extension_updates(&new_cluster);
+
stop_postmaster(false);
}
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 7038ac12bf..ca0795f68f 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -455,6 +455,7 @@ void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster,
bool check_mode);
void old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster);
+void report_extension_updates(ClusterInfo *cluster);
/* parallel.c */
void parallel_exec_prog(const char *log_file, const char *opt_log_file,
diff --git a/src/bin/pg_upgrade/version.c b/src/bin/pg_upgrade/version.c
index a3c193316d..b82afafd22 100644
--- a/src/bin/pg_upgrade/version.c
+++ b/src/bin/pg_upgrade/version.c
@@ -468,3 +468,81 @@ old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
else
check_ok();
}
+
+
+/*
+ * report_extension_updates()
+ * Report extensions that should be updated.
+ */
+void
+report_extension_updates(ClusterInfo *cluster)
+{
+ int dbnum;
+ FILE *script = NULL;
+ bool found = false;
+ char *output_path = "update_extensions.sql";
+
+ prep_status("Checking for extension updates");
+
+ for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
+ {
+ PGresult *res;
+ bool db_used = false;
+ int ntups;
+ int rowno;
+ int i_name;
+ DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(cluster, active_db->db_name);
+
+ /* find hash indexes */
+ res = executeQueryOrDie(conn,
+ "SELECT name "
+ "FROM pg_available_extensions "
+ "WHERE installed_version != default_version"
+ );
+
+ ntups = PQntuples(res);
+ i_name = PQfnumber(res, "name");
+ for (rowno = 0; rowno < ntups; rowno++)
+ {
+ found = true;
+
+ if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+ pg_fatal("could not open file \"%s\": %s\n", output_path,
+ strerror(errno));
+ if (!db_used)
+ {
+ PQExpBufferData connectbuf;
+
+ initPQExpBuffer(&connectbuf);
+ appendPsqlMetaConnect(&connectbuf, active_db->db_name);
+ fputs(connectbuf.data, script);
+ termPQExpBuffer(&connectbuf);
+ db_used = true;
+ }
+ fprintf(script, "ALTER EXTENSION %s UPDATE;\n",
+ quote_identifier(PQgetvalue(res, rowno, i_name)));
+ }
+
+ PQclear(res);
+
+ PQfinish(conn);
+ }
+
+ if (script)
+ fclose(script);
+
+ if (found)
+ {
+ report_status(PG_REPORT, "notice");
+ pg_log(PG_REPORT, "\n"
+ "Your installation contains extensions that should be updated\n"
+ "with the ALTER EXTENSION command. The file\n"
+ " %s\n"
+ "when executed by psql by the database superuser will update\n"
+ "these extensions.\n\n",
+ output_path);
+ }
+ else
+ check_ok();
+}
