This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 9bd7847235 Add pg_dynamic_tables system view. (#771)
9bd7847235 is described below
commit 9bd784723501b74202ed5baff4ee2287a38e723f
Author: Zhang Mingli <[email protected]>
AuthorDate: Fri Dec 13 23:04:41 2024 +0800
Add pg_dynamic_tables system view. (#771)
This commit introduces a new system view, pg_dynamic_tables, which
provides information about dynamic tables in PostgreSQL.
Similar to pg_matviews, this view displays relations that are
dynamic tables. Since dynamic tables are implemented as materialized
views internally, the definition of pg_matviews remains unchanged to
maintain consistency across the system.
Authored-by: Zhang Mingli [email protected]
---
src/backend/catalog/system_views.sql | 13 +++++++++++++
src/test/regress/expected/dynamic_table.out | 25 +++++++++++++++++++++++++
src/test/regress/sql/dynamic_table.sql | 2 ++
3 files changed, 40 insertions(+)
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index d9ed9ce963..312ba17ddc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -166,6 +166,19 @@ CREATE VIEW pg_matviews AS
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE C.relkind = 'm';
+CREATE VIEW pg_dynamic_tables AS
+ SELECT
+ N.nspname AS schemaname,
+ C.relname AS dynamictablename,
+ pg_get_userbyid(C.relowner) AS dynamictableowner,
+ T.spcname AS tablespace,
+ C.relhasindex AS hasindexes,
+ C.relispopulated AS ispopulated,
+ pg_get_viewdef(C.oid) AS definition
+ FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+ LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
+ WHERE C.relkind = 'm' and C.relisdynamic = true;
+
CREATE VIEW pg_indexes AS
SELECT
N.nspname AS schemaname,
diff --git a/src/test/regress/expected/dynamic_table.out
b/src/test/regress/expected/dynamic_table.out
index d95af994ff..4877841483 100644
--- a/src/test/regress/expected/dynamic_table.out
+++ b/src/test/regress/expected/dynamic_table.out
@@ -313,6 +313,31 @@ WARNING: relation of oid "XXX" is not dynamic table
(1 row)
+SELECT * FROM pg_dynamic_tables;
+ schemaname | dynamictablename | dynamictableowner | tablespace |
hasindexes | ispopulated | definition
+----------------------+------------------+-------------------+------------+------------+-------------+-----------------------------------
+ dynamic_table_schema | dt3 | gpadmin | | f
| t | SELECT mv_t2.a, +
+ | | | |
| | mv_t2.b, +
+ | | | |
| | mv_t2.c +
+ | | | |
| | FROM mv_t2 +
+ | | | |
| | WHERE (mv_t2.a = 2);
+ dynamic_table_schema | dt4 | gpadmin | | f
| t | SELECT dt3.a, +
+ | | | |
| | dt3.b, +
+ | | | |
| | dt3.c +
+ | | | |
| | FROM dt3 +
+ | | | |
| | WHERE (dt3.b = 3);
+ dynamic_table_schema | dt5 | gpadmin | | f
| t | SELECT dt3.a, +
+ | | | |
| | dt3.b, +
+ | | | |
| | dt3.c +
+ | | | |
| | FROM ((dt3 +
+ | | | |
| | JOIN t2 USING (a, b, c)) +
+ | | | |
| | JOIN mv_t2 USING (a, b, c));
+ dynamic_table_schema | dt_schedule | gpadmin | | f
| t | SELECT t2.a, +
+ | | | |
| | t2.b, +
+ | | | |
| | t2.c +
+ | | | |
| | FROM t2;
+(4 rows)
+
RESET enable_answer_query_using_materialized_views;
RESET optimizer;
--start_ignore
diff --git a/src/test/regress/sql/dynamic_table.sql
b/src/test/regress/sql/dynamic_table.sql
index 69a190d5a4..9e817a2767 100644
--- a/src/test/regress/sql/dynamic_table.sql
+++ b/src/test/regress/sql/dynamic_table.sql
@@ -137,6 +137,8 @@ SELECT
pg_catalog.pg_get_dynamic_table_schedule('dt_schedule'::regclass::oid);
-- not a dynamic table
SELECT pg_catalog.pg_get_dynamic_table_schedule('t2'::regclass::oid);
+SELECT * FROM pg_dynamic_tables;
+
RESET enable_answer_query_using_materialized_views;
RESET optimizer;
--start_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
