This is an automated email from the ASF dual-hosted git repository.
maxyang 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 57a5320e9a Forbid users from altering the AS part of the ALTER TASK
command.
57a5320e9a is described below
commit 57a5320e9a5fd0e29694408996289222d7d2be7c
Author: Zhang Mingli <[email protected]>
AuthorDate: Fri Dec 13 16:43:16 2024 +0800
Forbid users from altering the AS part of the ALTER TASK command.
The SQL must always be a REFRESH command to maintain the integrity of
the Dynamic Table design (an auto-refreshing materialized view). Other
ALTER TASK subcommands remain allowed, enabling users to control other
aspects as needed.
Authored-by: Zhang Mingli [email protected]
---
src/backend/commands/taskcmds.c | 9 ++++++++-
src/test/regress/expected/dynamic_table.out | 29 +++++++++++++++++++++++++++--
src/test/regress/sql/dynamic_table.sql | 15 +++++++++++++--
3 files changed, 48 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/taskcmds.c b/src/backend/commands/taskcmds.c
index 160d93b054..7e07339c05 100644
--- a/src/backend/commands/taskcmds.c
+++ b/src/backend/commands/taskcmds.c
@@ -251,7 +251,14 @@ AlterTask(ParseState *pstate, AlterTaskStmt * stmt)
}
if (d_sql != NULL && d_sql->arg)
- sql = defGetString(d_sql);
+ {
+ if (strncmp(stmt->taskname, DYNAMIC_TASK_PREFIX, 25) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("can not alter REFRESH SQL of
dynamic tables")));
+ else
+ sql = defGetString(d_sql);
+ }
AlterCronJob(jobid, schedule, sql, dbname, username, d_active != NULL ?
&active : NULL);
diff --git a/src/test/regress/expected/dynamic_table.out
b/src/test/regress/expected/dynamic_table.out
index 4877841483..71a665761e 100644
--- a/src/test/regress/expected/dynamic_table.out
+++ b/src/test/regress/expected/dynamic_table.out
@@ -156,14 +156,14 @@ SELECT * FROM t1 WHERE a = 2;
(2 rows)
-- test DROP DYNAMIC TABLE
-SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0';
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0%';
schedule | command
-----------+------------------------------------------------
5 * * * * | REFRESH DYNAMIC TABLE dynamic_table_schema.dt0
(1 row)
DROP DYNAMIC TABLE dt0;
-SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0';
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0%';
schedule | command
----------+---------
(0 rows)
@@ -291,6 +291,11 @@ SELECT 'dt5'::regclass::oid AS dtoid \gset
CREATE TASK gp_dynamic_table_refresh_xxx SCHEDULE '1 second' AS 'REFRESH
DYNAMIC TABLE dt5';
ERROR: unacceptable task name "gp_dynamic_table_refresh_xxx"
DETAIL: The prefix "gp_dynamic_table_refresh_" is reserved for system tasks.
+-- can not alter the REFRESH SQL of Dynamic Tables.
+ALTER TASK gp_dynamic_table_refresh_:dtoid AS '* * * * *';
+ERROR: can not alter REFRESH SQL of dynamic tables
+ALTER TASK gp_dynamic_table_refresh_:dtoid AS '';
+ERROR: can not alter REFRESH SQL of dynamic tables
-- should fail
DROP TASK gp_dynamic_table_refresh_:dtoid;
ERROR: can not drop a internal task "gp_dynamic_table_refresh_17387" paried
with dynamic table
@@ -338,6 +343,26 @@ SELECT * FROM pg_dynamic_tables;
| | | |
| | FROM t2;
(4 rows)
+CREATE TABLE t3(a int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+CREATE DYNAMIC TABLE dt_1_min SCHEDULE '* * * * *' AS SELECT * FROM t3 WITH NO
DATA;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Cloudberry Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+INSERT INTO T3 VALUES(1);
+-- wait for backgroud refresh
+SELECT pg_sleep(80);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT * FROM dt_1_min;
+ a
+---
+ 1
+(1 row)
+
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 9e817a2767..367948de78 100644
--- a/src/test/regress/sql/dynamic_table.sql
+++ b/src/test/regress/sql/dynamic_table.sql
@@ -54,9 +54,9 @@ SELECT * FROM t1 WHERE a = 2;
SELECT * FROM t1 WHERE a = 2;
-- test DROP DYNAMIC TABLE
-SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0';
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0%';
DROP DYNAMIC TABLE dt0;
-SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0';
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%' AND command LIKE '%dt0%';
-- drop base tables will drop DYNAMIC TABLEs too.
SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
@@ -127,6 +127,10 @@ SELECT 'dt5'::regclass::oid AS dtoid \gset
-- should fail
CREATE TASK gp_dynamic_table_refresh_xxx SCHEDULE '1 second' AS 'REFRESH
DYNAMIC TABLE dt5';
+-- can not alter the REFRESH SQL of Dynamic Tables.
+ALTER TASK gp_dynamic_table_refresh_:dtoid AS '* * * * *';
+ALTER TASK gp_dynamic_table_refresh_:dtoid AS '';
+
-- should fail
DROP TASK gp_dynamic_table_refresh_:dtoid;
@@ -139,6 +143,13 @@ SELECT
pg_catalog.pg_get_dynamic_table_schedule('t2'::regclass::oid);
SELECT * FROM pg_dynamic_tables;
+CREATE TABLE t3(a int);
+CREATE DYNAMIC TABLE dt_1_min SCHEDULE '* * * * *' AS SELECT * FROM t3 WITH NO
DATA;
+INSERT INTO T3 VALUES(1);
+-- wait for backgroud refresh
+SELECT pg_sleep(80);
+SELECT * FROM dt_1_min;
+
RESET enable_answer_query_using_materialized_views;
RESET optimizer;
--start_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
