(cloudberry) 14/23: pg_upgrade: check for views using removed operators

[re...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

reshke pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git

commit f8e419e9573177c30eacdcc1f86b96381e917955
Author: Kevin Yeap <ky...@vmware.com>
AuthorDate: Fri Feb 9 16:56:43 2024 -0800

    pg_upgrade: check for views using removed operators
    
    Views that use removed operators will cause upgrade to fail. This
    happens during metadata restore on the target cluster because pg_restore
    will error try to create a view using operators that do not exist
    anymore. This is not ideal as we could be many hours into upgrade before
    a view using a removed operator causes pg_upgrade to fail. This check
    calls a support function on the source cluster to check if views using
    removed operators exist.
---
 src/bin/pg_upgrade/greenplum/check_gp.c | 85 +++++++++++++++++++++++++++++++++
 1 file changed, 85 insertions(+)

diff --git a/src/bin/pg_upgrade/greenplum/check_gp.c b/src/bin/pg_upgrade/greenplum/check_gp.c
index 6e243acec6..f7c1eb538e 100644
--- a/src/bin/pg_upgrade/greenplum/check_gp.c
+++ b/src/bin/pg_upgrade/greenplum/check_gp.c
@@ -27,6 +27,7 @@ static void check_online_expansion(void);
 static void check_for_array_of_partition_table_types(ClusterInfo *cluster);
 static void check_multi_column_list_partition_keys(ClusterInfo *cluster);
 static void check_for_plpython2_dependent_functions(ClusterInfo *cluster);
+static void check_views_with_removed_operators(void);
 
 /*
  *	check_greenplum
@@ -47,6 +48,7 @@ check_greenplum(void)
 	check_for_array_of_partition_table_types(&old_cluster);
 	check_multi_column_list_partition_keys(&old_cluster);
 	check_for_plpython2_dependent_functions(&old_cluster);
+	check_views_with_removed_operators();
 }
 
 /*
@@ -822,3 +824,86 @@ teardown_GPDB6_data_type_checks(ClusterInfo *cluster)
 	}
 
 }
+
+static void
+check_views_with_removed_operators()
+{
+	char  output_path[MAXPGPATH];
+	FILE *script = NULL;
+	bool  found = false;
+	int   dbnum;
+	int   i_viewname;
+
+	prep_status("Checking for views with removed operators");
+
+	snprintf(output_path, sizeof(output_path), "views_with_removed_operators.txt");
+
+	for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
+	{
+		PGresult   *res;
+		int			ntups;
+		int			rowno;
+		DbInfo	   *active_db = &old_cluster.dbarr.dbs[dbnum];
+		PGconn	   *conn;
+		bool		db_used = false;
+
+		conn = connectToServer(&old_cluster, active_db->db_name);
+		PQclear(executeQueryOrDie(conn, "SET search_path TO 'public';"));
+
+		/*
+		 * Disabling track_counts results in a large performance improvement,
+		 * several orders of magnitude, when walking the views in
+		 * view_has_removed_operators.
+		 */
+		PQclear(executeQueryOrDie(conn, "SET track_counts TO off;"));
+
+		/* Install check support function */
+		PQclear(executeQueryOrDie(conn,
+								  "CREATE OR REPLACE FUNCTION "
+								  "view_has_removed_operators(OID) "
+								  "RETURNS BOOL "
+								  "AS '$libdir/pg_upgrade_support' "
+								  "LANGUAGE C STRICT;"));
+		res = executeQueryOrDie(conn,
+								"SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname) AS badviewname "
+								"FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid "
+								"WHERE c.relkind = 'v' AND "
+								"view_has_removed_operators(c.oid) = TRUE;");
+
+		PQclear(executeQueryOrDie(conn, "DROP FUNCTION view_has_removed_operators(OID);"));
+		PQclear(executeQueryOrDie(conn, "SET search_path to 'pg_catalog';"));
+		PQclear(executeQueryOrDie(conn, "RESET track_counts;"));
+
+		ntups = PQntuples(res);
+		i_viewname = PQfnumber(res, "badviewname");
+		for (rowno = 0; rowno < ntups; rowno++)
+		{
+			found = true;
+			if (script == NULL && (script = fopen(output_path, "w")) == NULL)
+				pg_fatal("Could not create necessary file:  %s\n", output_path);
+			if (!db_used)
+			{
+				fprintf(script, "Database: %s\n", active_db->db_name);
+				db_used = true;
+			}
+			fprintf(script, "  %s\n", PQgetvalue(res, rowno, i_viewname));
+		}
+
+		PQclear(res);
+		PQfinish(conn);
+	}
+
+	if (found)
+	{
+		pg_log(PG_REPORT, "fatal\n");
+		gp_fatal_log(
+			   "| Your installation contains views using removed operators.\n"
+			   "| These operators are no longer present on the target version.\n"
+			   "| These views must be updated to use operators supported in the\n"
+			   "| target version or removed before upgrade can continue. A list\n"
+			   "| of the problem views is in the file:\n\t%s\n\n", output_path);
+	}
+	else
+		check_ok();
+}
+


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org