You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by yj...@apache.org on 2025/01/20 04:03:21 UTC
(cloudberry) 24/25: Analyzedb: Add materialized views to list of tables to be analyzed (#16410)
This is an automated email from the ASF dual-hosted git repository.
yjhjstz pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 566a3d8a1def49dbebb6f59d7c4cec990008149a
Author: Marcus Robb <ma...@initworx.com>
AuthorDate: Tue Sep 12 10:37:28 2023 -0700
Analyzedb: Add materialized views to list of tables to be analyzed (#16410)
Previously, analyzedb did not include materialized views when preparing the list of tables to be analyzed. This caused refreshed materialized views to have poor performance until analyzed.
Solution:
Updated the SQL statements in analyzedb that collect the list of tables to also include relkind='m' from pg_class.
This will add materialized views to the list of hash tables to be analyzed for each run.
---
gpMgmt/bin/analyzedb | 10 +++++-----
gpMgmt/test/behave/mgmt_utils/analyzedb.feature | 15 +++++++++++++++
.../test/behave/mgmt_utils/steps/analyzedb_mgmt_utils.py | 16 +++++++++++++++-
3 files changed, 35 insertions(+), 6 deletions(-)
diff --git a/gpMgmt/bin/analyzedb b/gpMgmt/bin/analyzedb
index 010db0c762..cc51e26592 100755
--- a/gpMgmt/bin/analyzedb
+++ b/gpMgmt/bin/analyzedb
@@ -52,14 +52,14 @@ NUM_REPORTS_TO_SAVE = 3
GET_ALL_DATA_TABLES_SQL = """
select n.nspname as schemaname, c.relname as tablename
from pg_class c, pg_namespace n
-where c.relnamespace = n.oid and c.relkind='r'::char
+where c.relnamespace = n.oid and (c.relkind='r'::char or c.relkind = 'm'::char)
and (c.relnamespace >= 16384 or n.nspname = 'public' or n.nspname = 'pg_catalog')
and c.oid not in (select ftrelid from pg_foreign_table)
"""
GET_VALID_DATA_TABLES_SQL = """
select n.nspname as schemaname, c.relname as tablename from pg_class c, pg_namespace n where
-c.relnamespace = n.oid and c.oid in (%s) and c.relkind='r'::char and (c.relnamespace >= 16384 or n.nspname = 'public' or n.nspname = 'pg_catalog') and c.oid not in (select ftrelid from pg_foreign_table)
+c.relnamespace = n.oid and c.oid in (%s) and (c.relkind='r'::char or c.relkind = 'm'::char) and (c.relnamespace >= 16384 or n.nspname = 'public' or n.nspname = 'pg_catalog') and c.oid not in (select ftrelid from pg_foreign_table)
"""
GET_REQUESTED_AO_DATA_TABLE_INFO_SQL = """
@@ -86,7 +86,7 @@ GET_REQUESTED_LAST_OP_INFO_SQL = """
GET_ALL_DATA_TABLES_IN_SCHEMA_SQL = """
select n.nspname as schemaname, c.relname as tablename
from pg_class c, pg_namespace n
-where c.relnamespace = n.oid and c.relkind='r'::char
+where c.relnamespace = n.oid and (c.relkind='r'::char or c.relkind = 'm'::char)
and (c.relnamespace >= 16384 or n.nspname = 'public' or n.nspname = 'pg_catalog')
and c.oid not in (select ftrelid from pg_foreign_table)
and n.nspname = '%s'
@@ -112,7 +112,7 @@ and c.relkind = 'p' and c.relispartition
GET_REQUESTED_NON_AO_TABLES_SQL = """
select n.nspname as schemaname, c.relname as tablename
from pg_class c, pg_namespace n
-where c.relnamespace = n.oid and c.relkind='r'::char
+where c.relnamespace = n.oid and (c.relkind='r'::char or c.relkind = 'm'::char)
and (c.relnamespace >= 16384 or n.nspname = 'public' or n.nspname = 'pg_catalog')
and c.oid not in (select ftrelid from pg_foreign_table)
and c.oid not in (select relid from pg_appendonly)
@@ -554,7 +554,7 @@ class AnalyzeDb(Operation):
At the same time, parse the requested columns and populate the col_dict.
If a requested table is partitioned, expand all the leaf partitions.
"""
- logger.info("Getting and verifying input tables...")
+ logger.info("Getting and verifying input tables and materialized views...")
if self.single_table:
# Check that the table name given on the command line is schema-qualified.
diff --git a/gpMgmt/test/behave/mgmt_utils/analyzedb.feature b/gpMgmt/test/behave/mgmt_utils/analyzedb.feature
index 93d90fd332..5809c7745a 100644
--- a/gpMgmt/test/behave/mgmt_utils/analyzedb.feature
+++ b/gpMgmt/test/behave/mgmt_utils/analyzedb.feature
@@ -1775,3 +1775,18 @@ Feature: Incrementally analyze the database
And the user runs "dropdb schema_with_temp_table"
And the user drops the named connection "default"
+ Scenario: analyzedb can handle the table name with special utf-8 characters.
+ Given database "special_encoding_db" is dropped and recreated
+ And the user connects to "special_encoding_db" with named connection "default"
+ And the user executes "CREATE TEMP TABLE spiegelungssätze (c1 int) DISTRIBUTED BY (c1)" with named connection "default"
+ When the user runs "analyzedb -a -d special_encoding_db"
+ Then analyzedb should return a return code of 0
+
+ Scenario: analyzedb finds materialized views
+ Given a materialized view "public.mv_test_view" exists on table "pg_class"
+ And the user runs "analyzedb -a -d incr_analyze"
+ Then analyzedb should print "-public.mv_test_view" to stdout
+ And the user runs "analyzedb -a -s public -d incr_analyze"
+ Then analyzedb should print "-public.mv_test_view" to stdout
+ And the user runs "analyzedb -a -t public.mv_test_view -d incr_analyze"
+ Then analyzedb should print "-public.mv_test_view" to stdout
diff --git a/gpMgmt/test/behave/mgmt_utils/steps/analyzedb_mgmt_utils.py b/gpMgmt/test/behave/mgmt_utils/steps/analyzedb_mgmt_utils.py
index 3bbdca1ea7..f7bf6f1bdf 100644
--- a/gpMgmt/test/behave/mgmt_utils/steps/analyzedb_mgmt_utils.py
+++ b/gpMgmt/test/behave/mgmt_utils/steps/analyzedb_mgmt_utils.py
@@ -37,7 +37,6 @@ DEFAULT PARTITION default_dates);
"""
-
@given('there is a regular "{storage_type}" table "{tablename}" with column name list "{col_name_list}" and column type list "{col_type_list}" in schema "{schemaname}"')
def impl(context, storage_type, tablename, col_name_list, col_type_list, schemaname):
schemaname_no_quote = schemaname
@@ -98,6 +97,13 @@ def impl(context, view_name, table_name):
create_view_on_table(context.conn, view_name, table_name)
+@given('a materialized view "{view_name}" exists on table "{table_name}"')
+def impl(context, view_name, table_name):
+ with closing(dbconn.connect(dbconn.DbURL(dbname=context.dbname))) as conn:
+ create_materialized_view_on_table_in_schema(context.conn, viewname=view_name,
+ tablename=table_name)
+
+
@given('"{qualified_table}" appears in the latest state files')
@then('"{qualified_table}" should appear in the latest state files')
def impl(context, qualified_table):
@@ -462,3 +468,11 @@ def create_view_on_table(conn, viewname, tablename):
" AS SELECT * FROM " + tablename
dbconn.execSQL(conn, query)
conn.commit()
+
+
+def create_materialized_view_on_table_in_schema(conn, tablename, viewname):
+ query = "DROP MATERIALIZED VIEW IF EXISTS " + viewname + ";" \
+ "CREATE MATERIALIZED VIEW " + viewname + \
+ " AS SELECT * FROM " + tablename
+ dbconn.execSQL(conn, query)
+ conn.commit()
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org