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 56632f55f7 Dynamic Table. (#725)
56632f55f7 is described below
commit 56632f55f7be21e54e377d94addea22d6499c38e
Author: Zhang Mingli <[email protected]>
AuthorDate: Mon Dec 9 13:07:58 2024 +0800
Dynamic Table. (#725)
Dynamic Table is a an auto-refreshing materialized view which could
be constructed by base tables, external tables, materialized views
and dynamic tables.
And it could be used to answer query by AQUMV.
As normal tables in CBDB, dynamic tables could also have distribution
keys.
The purpose of Dynamic Tables is to solve the problem often raised by
customers who are big fans of a lakehouse architecture: how can we run
queries on external tables as fast as internal tables?
CREATE DYNAMIC TABLE:
CREATE DYNAMIC TABLE dt0 SCHEDULE '5 * * * *' AS
SELECT a, b, sum(c) FROM t1 GROUP BY a, b WITH NO DATA DISTRIBUTED
BY(b);
CREATE DYNAMIC TABLE
\d
List of relations
Schema | Name | Type | Owner | Storage
--------+------+---------------+---------+---------
public | dt0 | dynamic table | gpadmin | heap
public | t1 | table | gpadmin | heap
(2 rows)
CREATE DYNAMIC TABLE xxx AS Query
The Query allows any valid SELECT SQL of Materialized Views: from single
or multiple relations, base tables, materialized views, and dynamic
tables as well, joins, subquery, aggregation, group by and etc.
SCHEDULE:
A string used to schedule background job which auto-refreshes the
dynamic table.
We follow the valid string of pg_cron extension which supports linux
crontab, refer https://crontab.guru
┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *
You can also use '[1-59] seconds' to schedule a job based on an
interval.
The example creates a cron job refreshing the dynamic table at minute 5
of each hour.
For convenience, SCHEDULE is optional. If user didn't specific it, a
default
schedule is provided: at every 5th minute.
WITH NO DATA:
Same as Materialized View, will create an empty Dynamic Table if
specified.
DISTRIBUTED BY:
Same as normal tables in CBDB, Dynamic Tables could support distribution
keys as materialized views.
Refresh Dynamic Table
As seen in pg_task, we put a command to auto-refresh dynamic tables.
However, if users want to do a REFRESH manually, exec command REFRESH
DYNAMIC TABLE is also supported.
REFRESH DYNAMIC TABLE dt0;
REFRESH DYNAMIC TABLE
Refresh WITH NO DATA
Same as Materialized Views, Refresh with no data will truncate the
Dynamic Table and make it unpopulated status.
REFRESH DYNAMIC TABLE dt0 WITH NO DATA;
REFRESH DYNAMIC TABLE
Drop Dynamic Table
Drop a Dynamic Table will drop its scheduler job automatically.
DROP DYNAMIC TABLE dt0;
DROP DYNAMIC TABLE
Like Materialized Views, Dynamic Tables could be used to answer query
too. This is limited by AQUMV.
Authored-by: Zhang Mingli [email protected]
---
contrib/pg_stat_statements/pg_stat_statements.c | 1 +
doc/src/sgml/ref/allfiles.sgml | 3 +
doc/src/sgml/ref/create_dynamic_table.sgml | 185 ++++++++++++++
doc/src/sgml/ref/drop_dynamic_table.sgml | 116 +++++++++
doc/src/sgml/ref/refresh_dynamic_table.sgml | 142 +++++++++++
doc/src/sgml/reference.sgml | 3 +
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 1 +
src/backend/catalog/objectaddress.c | 20 +-
src/backend/commands/createas.c | 50 ++++
src/backend/commands/explain.c | 7 +-
src/backend/commands/matview.c | 47 +++-
src/backend/commands/taskcmds.c | 15 ++
src/backend/nodes/copyfuncs.c | 4 +
src/backend/nodes/equalfuncs.c | 4 +
src/backend/nodes/outfuncs.c | 2 +
src/backend/nodes/outfuncs_common.c | 1 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/nodes/readfuncs_common.c | 1 +
src/backend/parser/gram.y | 125 ++++++++-
src/backend/tcop/utility.c | 23 +-
src/backend/utils/adt/ruleutils.c | 67 +++++
src/backend/utils/cache/lsyscache.c | 24 ++
src/backend/utils/cache/relcache.c | 2 +
src/bin/pg_dump/pg_dump.c | 75 +++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 15 ++
src/bin/psql/describe.c | 25 +-
src/bin/psql/tab-complete.c | 51 +++-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_class.h | 3 +
src/include/catalog/pg_proc.dat | 4 +
src/include/catalog/pg_task.h | 6 +
src/include/commands/matview.h | 2 +
src/include/nodes/parsenodes.h | 2 +
src/include/nodes/primnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/tcop/cmdtaglist.h | 3 +
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/dynamic_table.out | 326 ++++++++++++++++++++++++
src/test/regress/greenplum_schedule | 2 +
src/test/regress/sql/dynamic_table.sql | 144 +++++++++++
42 files changed, 1480 insertions(+), 32 deletions(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c
b/contrib/pg_stat_statements/pg_stat_statements.c
index bede662907..1e3981c7eb 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -1153,6 +1153,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char
*queryString,
rows = (qc && (qc->commandTag == CMDTAG_COPY ||
qc->commandTag == CMDTAG_FETCH ||
qc->commandTag == CMDTAG_SELECT ||
+ qc->commandTag ==
CMDTAG_REFRESH_DYNAMIC_TABLE ||
qc->commandTag ==
CMDTAG_REFRESH_MATERIALIZED_VIEW)) ?
qc->nprocessed : 0;
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 2b69b954af..5389391fbd 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -66,6 +66,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createConversion SYSTEM "create_conversion.sgml">
<!ENTITY createDatabase SYSTEM "create_database.sgml">
<!ENTITY createDomain SYSTEM "create_domain.sgml">
+<!ENTITY createDynamicTable SYSTEM "create_dynamic_table.sgml">
<!ENTITY createEventTrigger SYSTEM "create_event_trigger.sgml">
<!ENTITY createExtension SYSTEM "create_extension.sgml">
<!ENTITY createForeignDataWrapper SYSTEM "create_foreign_data_wrapper.sgml">
@@ -114,6 +115,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropConversion SYSTEM "drop_conversion.sgml">
<!ENTITY dropDatabase SYSTEM "drop_database.sgml">
<!ENTITY dropDomain SYSTEM "drop_domain.sgml">
+<!ENTITY dropDynamicTable SYSTEM "drop_dynamic_table.sgml">
<!ENTITY dropEventTrigger SYSTEM "drop_event_trigger.sgml">
<!ENTITY dropExtension SYSTEM "drop_extension.sgml">
<!ENTITY dropForeignDataWrapper SYSTEM "drop_foreign_data_wrapper.sgml">
@@ -166,6 +168,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY prepare SYSTEM "prepare.sgml">
<!ENTITY prepareTransaction SYSTEM "prepare_transaction.sgml">
<!ENTITY reassignOwned SYSTEM "reassign_owned.sgml">
+<!ENTITY refreshDynamicTable SYSTEM "refresh_dynamic_table.sgml">
<!ENTITY refreshMaterializedView SYSTEM "refresh_materialized_view.sgml">
<!ENTITY reindex SYSTEM "reindex.sgml">
<!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml">
diff --git a/doc/src/sgml/ref/create_dynamic_table.sgml
b/doc/src/sgml/ref/create_dynamic_table.sgml
new file mode 100644
index 0000000000..536aebe439
--- /dev/null
+++ b/doc/src/sgml/ref/create_dynamic_table.sgml
@@ -0,0 +1,185 @@
+<!--
+doc/src/sgml/ref/create_dynamic_table.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createdynamictable">
+ <indexterm zone="sql-createdynamictable">
+ <primary>CREATE DYNAMIC TABLE</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE DYNAMIC TABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE DYNAMIC TABLE</refname>
+ <refpurpose>define a new dynamic table</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE DYNAMIC TABLE [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
+ [ (<replaceable>column_name</replaceable> [, ...] ) ]
+ [ USING <replaceable class="parameter">method</replaceable> ]
+ [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [=
<replaceable class="parameter">value</replaceable>] [, ... ] ) ]
+ [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
+ AS <replaceable>query</replaceable>
+ [ WITH [ NO ] DATA ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>CREATE DYNAMIC TABLE</command> defines a dynamic table of
+ a query. The query is executed and used to populate the view at the time
+ the command is issued (unless <command>WITH NO DATA</command> is used) and
may be
+ refreshed later using <command>REFRESH DYNAMIC TABLE</command>.
+ </para>
+
+ <para>
+ <command>CREATE DYNAMIC TABLE</command> is similar to
+ <command>CREATE TABLE AS</command>, except that it also remembers the query
used
+ to initialize the view, so that it can be refreshed later upon demand.
+ A dynamic table has many of the same properties as a table, but there
+ is no support for temporary dynamic tables.
+ </para>
+
+ <para>
+ <command>CREATE DYNAMIC TABLE</command> requires
+ <literal>CREATE</literal> privilege on the schema used for the dynamic
+ table.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if a dynamic table with the same name already
+ exists. A notice is issued in this case. Note that there is no guarantee
+ that the existing dynamic table is anything like the one that would
+ have been created.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>table_name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the dynamic table to be
+ created.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>column_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a column in the new dynamic table. If column names are
+ not provided, they are taken from the output column names of the query.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>USING <replaceable
class="parameter">method</replaceable></literal></term>
+ <listitem>
+ <para>
+ This optional clause specifies the table access method to use to store
+ the contents for the new dynamic table; the method needs be an
+ access method of type <literal>TABLE</literal>. See <xref
+ linkend="tableam"/> for more information. If this option is not
+ specified, the default table access method is chosen for the new
+ dynamic table. See <xref linkend="guc-default-table-access-method"/>
+ for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>WITH ( <replaceable
class="parameter">storage_parameter</replaceable> [= <replaceable
class="parameter">value</replaceable>] [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ This clause specifies optional storage parameters for the new
+ dynamic table; see
+ <xref linkend="sql-createtable-storage-parameters"/> in the
+ <xref linkend="sql-createtable"/> documentation for more
+ information. All parameters supported for <literal>CREATE
+ TABLE</literal> are also supported for <literal>CREATE DYNAMIC
+ TABLE</literal>.
+ See <xref linkend="sql-createtable"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TABLESPACE <replaceable
class="parameter">tablespace_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <replaceable class="parameter">tablespace_name</replaceable> is the
name
+ of the tablespace in which the new dynamic table is to be created.
+ If not specified, <xref linkend="guc-default-tablespace"/> is consulted.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>query</replaceable></term>
+ <listitem>
+ <para>
+ A <link linkend="sql-select"><command>SELECT</command></link>, <link
linkend="sql-table"><command>TABLE</command></link>,
+ or <link linkend="sql-values"><command>VALUES</command></link> command.
This query will run within a
+ security-restricted operation; in particular, calls to functions that
+ themselves create temporary tables will fail.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>WITH [ NO ] DATA</literal></term>
+ <listitem>
+ <para>
+ This clause specifies whether or not the dynamic table should be
+ populated at creation time. If not, the dynamic table will be
+ flagged as unscannable and cannot be queried until <command>REFRESH
+ DYNAMIC TABLE</command> is used. Also, if the view is IMMV,
+ triggers for maintaining the view are not created.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE DYNAMIC TABLE</command> is a
+ <productname>Cloudberry</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createtableas"/></member>
+ <member><xref linkend="sql-createview"/></member>
+ <member><xref linkend="sql-dropmaterializedview"/></member>
+ <member><xref linkend="sql-refreshmaterializedview"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_dynamic_table.sgml
b/doc/src/sgml/ref/drop_dynamic_table.sgml
new file mode 100644
index 0000000000..005fee9b53
--- /dev/null
+++ b/doc/src/sgml/ref/drop_dynamic_table.sgml
@@ -0,0 +1,116 @@
+<!--
+doc/src/sgml/ref/drop_dynamic_table.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropdynamictable">
+ <indexterm zone="sql-dropdynamictable">
+ <primary>DROP DYNAMIC TABLE</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP DYNAMIC TABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP DYNAMIC TABLE</refname>
+ <refpurpose>remove a dynamic table</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP DYNAMIC TABLE [ IF EXISTS ] <replaceable
class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP DYNAMIC TABLE</command> drops an existing dynamic
+ table. To execute this command you must be the owner of the dynamic
+ table. Since every dynamic table has a auto refresh process of pg_task
+ job, drop a dynamic table will drop that job too.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the dynamic table does not exist. A notice
+ is issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the dynamic table to
+ remove.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the dynamic table (such as
+ other materialized views, or regular views or pg_task jobs),
+ and in turn all objects that depend on those objects
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the dynamic table if any objects depend on it. This
+ is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ This command will remove the dynamic table called
+ <literal>order_summary</literal>:
+<programlisting>
+DROP DYNAMIC TABLE order_summary;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>DROP DYNAMIC TABLE</command> is a
+ <productname>Cloudberry</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createdynamictable"/></member>
+ <member><xref linkend="sql-refreshdynamictable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/refresh_dynamic_table.sgml
b/doc/src/sgml/ref/refresh_dynamic_table.sgml
new file mode 100644
index 0000000000..57506fd0f8
--- /dev/null
+++ b/doc/src/sgml/ref/refresh_dynamic_table.sgml
@@ -0,0 +1,142 @@
+<!--
+doc/src/sgml/ref/refresh_dynamic_table.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-refreshdynamictable">
+ <indexterm zone="sql-refreshdynamictable">
+ <primary>REFRESH DYNAMIC TABLE</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>REFRESH DYNAMIC TABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>REFRESH DYNAMIC TABLE</refname>
+ <refpurpose>replace the contents of a dynamic table</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+REFRESH DYNAMIC TABLE [ CONCURRENTLY ] <replaceable
class="parameter">name</replaceable>
+ [ WITH [ NO ] DATA ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>REFRESH DYNAMIC TABLE</command> completely replaces the
+ contents of a dynamic table. To execute this command you must be the
+ owner of the dynamic table. The old contents are discarded. If
+ <literal>WITH DATA</literal> is specified (or defaults) the backing query
+ is executed to provide the new data, and the dynamic table is left in a
+ scannable state. If <literal>WITH NO DATA</literal> is specified no new
+ data is generated and the dynamic table is left in an unscannable
+ state. If the view is IMMV, the triggers are dropped.
+ </para>
+ <para>
+ <literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
+ be specified together.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>CONCURRENTLY</literal></term>
+ <listitem>
+ <para>
+ Refresh the dynamic table without locking out concurrent selects on
+ the dynamic table. Without this option a refresh which affects a
+ lot of rows will tend to use fewer resources and complete more quickly,
+ but could block other connections which are trying to read from the
+ dynamic table. This option may be faster in cases where a small
+ number of rows are affected.
+ </para>
+ <para>
+ This option is only allowed if there is at least one
+ <literal>UNIQUE</literal> index on the dynamic table which uses only
+ column names and includes all rows; that is, it must not be an
+ expression index or include a <literal>WHERE</literal> clause.
+ </para>
+ <para>
+ This option may not be used when the dynamic table is not already
+ populated.
+ </para>
+ <para>
+ Even with this option only one <literal>REFRESH</literal> at a time may
+ run against any one dynamic table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the dynamic table to
+ refresh.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ If there is an <literal>ORDER BY</literal> clause in the dynamic
+ table's defining query, the original contents of the dynamic table
+ will be ordered that way; but <command>REFRESH DYNAMIC
+ TABLE</command> does not guarantee to preserve that ordering.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ This command will replace the contents of the dynamic table called
+ <literal>order_summary</literal> using the query from the dynamic
+ table's definition, and leave it in a scannable state:
+<programlisting>
+REFRESH DYNAMIC TABLE order_summary;
+</programlisting>
+ </para>
+
+ <para>
+ This command will free storage associated with the dynamic table
+ <literal>annual_statistics_basis</literal> and leave it in an unscannable
+ state:
+<programlisting>
+REFRESH DYNAMIC TABLE annual_statistics_basis WITH NO DATA;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>REFRESH DYNAMIC TABLE</command> is a
+ <productname>Cloudberry</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createdynamictable"/></member>
+ <member><xref linkend="sql-dropdynamictable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index dff7a42645..e341f5e00a 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -93,6 +93,7 @@
&createConversion;
&createDatabase;
&createDomain;
+ &createDynamicTable;
&createEventTrigger;
&createExtension;
&createForeignDataWrapper;
@@ -140,6 +141,7 @@
&dropConversion;
&dropDatabase;
&dropDomain;
+ &dropDynamicTable;
&dropEventTrigger;
&dropExtension;
&dropForeignDataWrapper;
@@ -191,6 +193,7 @@
&prepare;
&prepareTransaction;
&reassignOwned;
+ &refreshDynamicTable;
&refreshMaterializedView;
&reindex;
&releaseSavepoint;
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index b33d0c2ecc..d877752ca2 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1327,6 +1327,7 @@ InsertPgClassTuple(Relation pg_class_desc,
values[Anum_pg_class_relfrozenxid - 1] =
TransactionIdGetDatum(rd_rel->relfrozenxid);
values[Anum_pg_class_relminmxid - 1] =
MultiXactIdGetDatum(rd_rel->relminmxid);
values[Anum_pg_class_relisivm - 1] = BoolGetDatum(rd_rel->relisivm);
+ values[Anum_pg_class_relisdynamic - 1] =
BoolGetDatum(rd_rel->relisdynamic);
if (relacl != (Datum) 0)
values[Anum_pg_class_relacl - 1] = relacl;
else
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 35089deef1..af5d24cc13 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1074,6 +1074,7 @@ index_create_internal(Relation heapRelation,
indexRelation->rd_rel->relam = accessMethodObjectId;
indexRelation->rd_rel->relispartition = OidIsValid(parentIndexRelid);
indexRelation->rd_rel->relisivm = false;
+ indexRelation->rd_rel->relisdynamic = false;
/*
* store index's pg_class entry
diff --git a/src/backend/catalog/objectaddress.c
b/src/backend/catalog/objectaddress.c
index 8f0ba5ebf9..3ff481e0f5 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -758,6 +758,9 @@ static const struct object_type_map
{
"materialized view", OBJECT_MATVIEW
},
+ {
+ "dynamic table", OBJECT_MATVIEW
+ },
{
"composite type", -1
}, /* unmapped */
@@ -4384,8 +4387,16 @@ getRelationDescription(StringInfo buffer, Oid relid,
bool missing_ok)
relname);
break;
case RELKIND_MATVIEW:
- appendStringInfo(buffer, _("materialized view %s"),
- relname);
+ if (relForm->relisdynamic)
+ {
+ appendStringInfo(buffer, _("dynamic table %s"),
+ relname);
+ }
+ else
+ {
+ appendStringInfo(buffer, _("materialized view
%s"),
+ relname);
+ }
break;
case RELKIND_COMPOSITE_TYPE:
appendStringInfo(buffer, _("composite type %s"),
@@ -4954,7 +4965,10 @@ getRelationTypeDescription(StringInfo buffer, Oid relid,
int32 objectSubId,
appendStringInfoString(buffer, "view");
break;
case RELKIND_MATVIEW:
- appendStringInfoString(buffer, "materialized view");
+ if (relForm->relisdynamic)
+ appendStringInfoString(buffer, "dynamic table");
+ else
+ appendStringInfoString(buffer, "materialized
view");
break;
case RELKIND_COMPOSITE_TYPE:
appendStringInfoString(buffer, "composite type");
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 19f89ad4dd..6483985b40 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -44,6 +44,7 @@
#include "commands/prepare.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/taskcmds.h"
#include "commands/trigger.h"
#include "commands/view.h"
#include "miscadmin.h"
@@ -74,6 +75,7 @@
#include "catalog/gp_matview_aux.h"
#include "catalog/oid_dispatch.h"
+#include "catalog/pg_task.h"
#include "cdb/cdbappendonlyam.h"
#include "cdb/cdbaocsam.h"
#include "cdb/cdbdisp_query.h"
@@ -119,6 +121,8 @@ static bool check_ivm_restriction_walker(Node *node,
check_ivm_restriction_conte
static Bitmapset *get_primary_key_attnos_from_query(Query *query, List
**constraintList);
static bool check_aggregate_supports_ivm(Oid aggfnoid);
+static void create_dynamic_table_auto_refresh_task(ParseState *pstate,
Relation DynamicTableRel, char *schedule);
+
/*
* create_ctas_internal
*
@@ -537,6 +541,14 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt
*stmt,
CreateIvmTriggersOnBaseTables(query_immv,
matviewOid);
}
}
+
+ /* Set Dynamic Tables. */
+ if (into->dynamicTbl)
+ {
+ SetDynamicTableState(matviewRel);
+ create_dynamic_table_auto_refresh_task(pstate,
matviewRel, into->schedule);
+ }
+
table_close(matviewRel, NoLock);
}
@@ -1808,3 +1820,41 @@ get_primary_key_attnos_from_query(Query *query, List
**constraintList)
return keys;
}
+
+/*
+ * Create auto-refresh task for Dynamic Tables.
+ */
+static void
+create_dynamic_table_auto_refresh_task(ParseState *pstate, Relation
DynamicTableRel, char *schedule)
+{
+ ObjectAddress refaddr;
+ ObjectAddress address;
+ StringInfoData buf;
+ char *dtname = NULL;
+
+ if (schedule == NULL)
+ schedule = DYNAMIC_TABLE_DEFAULT_REFRESH_INTERVAL;
+
+ /* Create auto refresh task. */
+ CreateTaskStmt *task_stmt = makeNode(CreateTaskStmt);
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "gp_dynamic_table_refresh_%u",
RelationGetRelid(DynamicTableRel));
+ task_stmt->taskname = pstrdup(buf.data);
+ task_stmt->schedule = pstrdup(schedule);
+ task_stmt->if_not_exists = false; /* report error if failed. */
+ dtname =
quote_qualified_identifier(get_namespace_name(RelationGetNamespace(DynamicTableRel)),
+
RelationGetRelationName(DynamicTableRel));
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "REFRESH DYNAMIC TABLE %s", dtname);
+ task_stmt->sql = pstrdup(buf.data);
+ bool saved_allowSystemTableMods = allowSystemTableMods;
+ allowSystemTableMods = true;
+ address = DefineTask(pstate, task_stmt);
+ allowSystemTableMods = saved_allowSystemTableMods;
+
+ refaddr.classId = RelationRelationId;
+ refaddr.objectId = RelationGetRelid(DynamicTableRel);
+ refaddr.objectSubId = 0;
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_INTERNAL);
+}
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 9e3ccd76bb..865edf4ac2 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -550,7 +550,12 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into,
ExplainState *es,
if (ctas->objtype == OBJECT_TABLE)
ExplainDummyGroup("CREATE TABLE AS", NULL, es);
else if (ctas->objtype == OBJECT_MATVIEW)
- ExplainDummyGroup("CREATE MATERIALIZED VIEW",
NULL, es);
+ {
+ if(ctas->into && ctas->into->dynamicTbl)
+ ExplainDummyGroup("CREATE DYNAMIC
TABLE", NULL, es);
+ else
+ ExplainDummyGroup("CREATE MATERIALIZED
VIEW", NULL, es);
+ }
else
elog(ERROR, "unexpected object type: %d",
(int) ctas->objtype);
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 8b14f1c9f5..67cde11028 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -289,6 +289,46 @@ MakeRefreshClause(bool concurrent, bool skipData, RangeVar
*relation)
return refreshClause;
}
+/*
+ * SetDynamicTableState
+ * Mark a materialized view as Dynamic Table, or not.
+ *
+ * NOTE: caller must be holding an appropriate lock on the relation.
+ */
+void
+SetDynamicTableState(Relation relation)
+{
+ Relation pgrel;
+ HeapTuple tuple;
+
+ Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Update relation's pg_class entry. Crucial side-effect: other
backends
+ * (and this one too!) are sent SI message to make them rebuild relcache
+ * entries.
+ */
+ pgrel = table_open(RelationRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopy1(RELOID,
+
ObjectIdGetDatum(RelationGetRelid(relation)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(relation));
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relisdynamic = true;
+
+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+
+ heap_freetuple(tuple);
+ table_close(pgrel, RowExclusiveLock);
+
+ /*
+ * Advance command counter to make the updated pg_class row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+}
+
/*
* SetMatViewIVMState
* Mark a materialized view as IVM, or not.
@@ -702,7 +742,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char
*queryString,
* completion tag output might break applications using it.
*/
if (qc)
- SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW,
processed);
+ {
+ if (stmt->isdynamic)
+ SetQueryCompletion(qc, CMDTAG_REFRESH_DYNAMIC_TABLE,
processed);
+ else
+ SetQueryCompletion(qc,
CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
+ }
return address;
}
diff --git a/src/backend/commands/taskcmds.c b/src/backend/commands/taskcmds.c
index 6e5757ec04..026f05b9be 100644
--- a/src/backend/commands/taskcmds.c
+++ b/src/backend/commands/taskcmds.c
@@ -109,6 +109,14 @@ DefineTask(ParseState *pstate, CreateTaskStmt *stmt)
}
}
+ if (!allowSystemTableMods && strncmp(stmt->taskname,
DYNAMIC_TASK_PREFIX, 25) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("unacceptable task name \"%s\"",
stmt->taskname),
+ errdetail("The prefix \"%s\" is reserved for
system tasks.",
+ DYNAMIC_TASK_PREFIX)));
+ }
jobid = ScheduleCronJob(cstring_to_text(stmt->schedule),
cstring_to_text(stmt->sql),
cstring_to_text(dbname), cstring_to_text(username),
true, cstring_to_text(stmt->taskname));
@@ -278,6 +286,13 @@ DropTask(ParseState *pstate, DropTaskStmt *stmt)
/* delete from pg_task_run_history according to the jobid */
if (OidIsValid(jobid))
{
+ if (!allowSystemTableMods && strncmp(stmt->taskname,
DYNAMIC_TASK_PREFIX, 25) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("can not drop a internal task
\"%s\" paried with dynamic table", stmt->taskname),
+ errdetail("please drop the dynamic
table instead")));
+ }
RemoveTaskRunHistoryByJobId(jobid);
ObjectAddressSet(address, TaskRelationId, jobid);
/* Clean up dependencies */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ed54da0526..defdca28a3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1808,6 +1808,8 @@ _copyIntoClause(const IntoClause *from)
COPY_SCALAR_FIELD(ivm);
COPY_SCALAR_FIELD(matviewOid);
COPY_STRING_FIELD(enrname);
+ COPY_SCALAR_FIELD(dynamicTbl);
+ COPY_STRING_FIELD(schedule);
return newnode;
}
@@ -4218,6 +4220,7 @@ _copyDropStmt(const DropStmt *from)
COPY_SCALAR_FIELD(behavior);
COPY_SCALAR_FIELD(missing_ok);
COPY_SCALAR_FIELD(concurrent);
+ COPY_SCALAR_FIELD(isdynamic);
return newnode;
}
@@ -4786,6 +4789,7 @@ _copyRefreshMatViewStmt(const RefreshMatViewStmt *from)
COPY_SCALAR_FIELD(concurrent);
COPY_SCALAR_FIELD(skipData);
COPY_NODE_FIELD(relation);
+ COPY_SCALAR_FIELD(isdynamic);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 1617dd3896..7c104efd26 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -176,6 +176,8 @@ _equalIntoClause(const IntoClause *a, const IntoClause *b)
COMPARE_SCALAR_FIELD(ivm);
COMPARE_SCALAR_FIELD(matviewOid);
COMPARE_STRING_FIELD(enrname);
+ COMPARE_SCALAR_FIELD(dynamicTbl);
+ COMPARE_STRING_FIELD(schedule);
return true;
}
@@ -1480,6 +1482,7 @@ _equalDropStmt(const DropStmt *a, const DropStmt *b)
COMPARE_SCALAR_FIELD(behavior);
COMPARE_SCALAR_FIELD(missing_ok);
COMPARE_SCALAR_FIELD(concurrent);
+ COMPARE_SCALAR_FIELD(isdynamic);
return true;
}
@@ -1962,6 +1965,7 @@ _equalRefreshMatViewStmt(const RefreshMatViewStmt *a,
const RefreshMatViewStmt *
COMPARE_SCALAR_FIELD(concurrent);
COMPARE_SCALAR_FIELD(skipData);
COMPARE_NODE_FIELD(relation);
+ COMPARE_SCALAR_FIELD(isdynamic);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c5f7423b2e..bc0e8b35bf 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1210,6 +1210,8 @@ _outIntoClause(StringInfo str, const IntoClause *node)
WRITE_BOOL_FIELD(ivm);
WRITE_OID_FIELD(matviewOid);
WRITE_STRING_FIELD(enrname);
+ WRITE_BOOL_FIELD(dynamicTbl);
+ WRITE_STRING_FIELD(schedule);
}
static void
diff --git a/src/backend/nodes/outfuncs_common.c
b/src/backend/nodes/outfuncs_common.c
index 4cf0c1b828..c1dd774491 100644
--- a/src/backend/nodes/outfuncs_common.c
+++ b/src/backend/nodes/outfuncs_common.c
@@ -647,6 +647,7 @@ _outDropStmt(StringInfo str, const DropStmt *node)
WRITE_ENUM_FIELD(behavior, DropBehavior);
WRITE_BOOL_FIELD(missing_ok);
WRITE_BOOL_FIELD(concurrent);
+ WRITE_BOOL_FIELD(isdynamic);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 6a3eaec5f4..da2da74925 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -569,6 +569,8 @@ _readIntoClause(void)
READ_BOOL_FIELD(ivm);
READ_OID_FIELD(matviewOid);
READ_STRING_FIELD(enrname);
+ READ_BOOL_FIELD(dynamicTbl);
+ READ_STRING_FIELD(schedule);
READ_DONE();
}
diff --git a/src/backend/nodes/readfuncs_common.c
b/src/backend/nodes/readfuncs_common.c
index 96b8c9f869..886d49da51 100644
--- a/src/backend/nodes/readfuncs_common.c
+++ b/src/backend/nodes/readfuncs_common.c
@@ -1132,6 +1132,7 @@ _readDropStmt(void)
READ_ENUM_FIELD(behavior,DropBehavior);
READ_BOOL_FIELD(missing_ok);
READ_BOOL_FIELD(concurrent);
+ READ_BOOL_FIELD(isdynamic);
/* Force 'missing_ok' in QEs */
#ifdef COMPILING_BINARY_FUNCS
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 13de187649..9f98286c55 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -764,7 +764,7 @@ static void
check_expressions_in_partition_key(PartitionSpec *spec, core_yyscan_
DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH
DESC
DETACH DICTIONARY DIRECTORY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P
DOMAIN_P
- DOUBLE_P DROP
+ DOUBLE_P DROP DYNAMIC
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENDPOINT ENUM_P ESCAPE
EVENT EXCEPT
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
@@ -7139,6 +7139,74 @@ CreateMatViewStmt:
ctas->into->distributedBy = $13;
$$ = (Node *) ctas;
}
+/*****************************************************************************
+ *
+ * QUERY :
+ * CREATE DYNAMIC TABLE relname AS SelectStmt
+ *
+ *****************************************************************************/
+ | CREATE OptNoLog DYNAMIC TABLE create_mv_target SCHEDULE
task_schedule AS SelectStmt opt_with_data OptDistributedBy
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+ ctas->query = $9;
+ ctas->into = $5;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = false;
+ /* cram additional flags into the
IntoClause */
+ $5->rel->relpersistence = $2;
+ $5->skipData = !($10);
+ $5->dynamicTbl = true;
+ $5->schedule = $7;
+ ctas->into->distributedBy = $11;
+ $$ = (Node *) ctas;
+ }
+ | CREATE OptNoLog DYNAMIC TABLE create_mv_target AS SelectStmt
opt_with_data OptDistributedBy
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+ ctas->query = $7;
+ ctas->into = $5;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = false;
+ /* cram additional flags into the
IntoClause */
+ $5->rel->relpersistence = $2;
+ $5->skipData = !($8);
+ $5->dynamicTbl = true;
+ ctas->into->distributedBy = $9;
+ $$ = (Node *) ctas;
+ }
+ | CREATE OptNoLog DYNAMIC TABLE create_mv_target IF_P NOT
EXISTS SCHEDULE task_schedule AS SelectStmt opt_with_data OptDistributedBy
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+ ctas->query = $12;
+ ctas->into = $5;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = true;
+ /* cram additional flags into the
IntoClause */
+ $5->rel->relpersistence = $2;
+ $5->skipData = !($13);
+ $5->dynamicTbl = true;
+ $5->schedule = $10;
+ ctas->into->distributedBy = $14;
+ $$ = (Node *) ctas;
+ }
+ | CREATE OptNoLog DYNAMIC TABLE create_mv_target IF_P NOT
EXISTS AS SelectStmt opt_with_data OptDistributedBy
+ {
+ CreateTableAsStmt *ctas =
makeNode(CreateTableAsStmt);
+ ctas->query = $10;
+ ctas->into = $5;
+ ctas->objtype = OBJECT_MATVIEW;
+ ctas->is_select_into = false;
+ ctas->if_not_exists = true;
+ /* cram additional flags into the
IntoClause */
+ $5->rel->relpersistence = $2;
+ $5->skipData = !($11);
+ $5->dynamicTbl = true;
+ ctas->into->distributedBy = $12;
+ $$ = (Node *) ctas;
+ }
;
create_mv_target:
@@ -7154,6 +7222,8 @@ create_mv_target:
$$->viewQuery = NULL; /*
filled at analysis time */
$$->skipData = false; /*
might get changed later */
$$->ivm = false;
+ $$->dynamicTbl = false;
+ $$->schedule = NULL;
$$->accessMethod =
greenplumLegacyAOoptions($$->accessMethod, &$$->options);
}
@@ -7167,11 +7237,11 @@ OptNoLog: UNLOGGED
{ $$ = RELPERSISTENCE_UNLOGGED; }
| /*EMPTY*/ { $$ =
RELPERSISTENCE_PERMANENT; }
;
-
/*****************************************************************************
*
* QUERY :
* REFRESH MATERIALIZED VIEW qualified_name
+ * REFRESH DYNAMIC TABLE qualified_name
*
*****************************************************************************/
@@ -7182,6 +7252,16 @@ RefreshMatViewStmt:
n->concurrent = $4;
n->relation = $5;
n->skipData = !($6);
+ n->isdynamic = false;
+ $$ = (Node *) n;
+ }
+ | REFRESH DYNAMIC TABLE opt_concurrently qualified_name
opt_with_data
+ {
+ RefreshMatViewStmt *n =
makeNode(RefreshMatViewStmt);
+ n->concurrent = $4;
+ n->relation = $5;
+ n->skipData = !($6);
+ n->isdynamic = true;
$$ = (Node *) n;
}
;
@@ -9440,6 +9520,7 @@ DropOpClassStmt:
n->behavior = $7;
n->missing_ok = false;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP OPERATOR CLASS IF_P EXISTS any_name USING name
opt_drop_behavior
@@ -9450,6 +9531,7 @@ DropOpClassStmt:
n->behavior = $9;
n->missing_ok = true;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
;
@@ -9463,6 +9545,7 @@ DropOpFamilyStmt:
n->behavior = $7;
n->missing_ok = false;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP OPERATOR FAMILY IF_P EXISTS any_name USING name
opt_drop_behavior
@@ -9473,6 +9556,7 @@ DropOpFamilyStmt:
n->behavior = $9;
n->missing_ok = true;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
;
@@ -9523,6 +9607,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $5;
n->behavior = $6;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *)n;
}
| DROP object_type_any_name any_name_list
opt_drop_behavior
@@ -9533,6 +9618,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $3;
n->behavior = $4;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *)n;
}
| DROP drop_type_name IF_P EXISTS name_list
opt_drop_behavior
@@ -9543,6 +9629,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $5;
n->behavior = $6;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *)n;
}
| DROP drop_type_name name_list opt_drop_behavior
@@ -9553,6 +9640,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $3;
n->behavior = $4;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *)n;
}
| DROP object_type_name_on_any_name name ON any_name
opt_drop_behavior
@@ -9563,6 +9651,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->behavior = $6;
n->missing_ok = false;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP object_type_name_on_any_name IF_P EXISTS name ON
any_name opt_drop_behavior
@@ -9573,6 +9662,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->behavior = $8;
n->missing_ok = true;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP TYPE_P type_name_list opt_drop_behavior
@@ -9583,6 +9673,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $3;
n->behavior = $4;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP TYPE_P IF_P EXISTS type_name_list
opt_drop_behavior
@@ -9593,6 +9684,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $5;
n->behavior = $6;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP DOMAIN_P type_name_list opt_drop_behavior
@@ -9603,6 +9695,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $3;
n->behavior = $4;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP DOMAIN_P IF_P EXISTS type_name_list
opt_drop_behavior
@@ -9613,6 +9706,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $5;
n->behavior = $6;
n->concurrent = false;
+ n->isdynamic = false;
$$ = (Node *) n;
}
| DROP INDEX CONCURRENTLY any_name_list
opt_drop_behavior
@@ -9623,6 +9717,7 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $4;
n->behavior = $5;
n->concurrent = true;
+ n->isdynamic = false;
$$ = (Node *)n;
}
| DROP INDEX CONCURRENTLY IF_P EXISTS any_name_list
opt_drop_behavior
@@ -9633,6 +9728,30 @@ DropStmt: DROP object_type_any_name IF_P EXISTS
any_name_list opt_drop_behavior
n->objects = $6;
n->behavior = $7;
n->concurrent = true;
+ n->isdynamic = false;
+ $$ = (Node *)n;
+ }
+/* DROP DYNAMIC TABLE */
+ | DROP DYNAMIC TABLE IF_P EXISTS any_name_list
opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = OBJECT_MATVIEW;
+ n->missing_ok = true;
+ n->objects = $6;
+ n->behavior = $7;
+ n->concurrent = false;
+ n->isdynamic = true;
+ $$ = (Node *)n;
+ }
+ | DROP DYNAMIC TABLE any_name_list opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = OBJECT_MATVIEW;
+ n->missing_ok = false;
+ n->objects = $4;
+ n->behavior = $5;
+ n->concurrent = false;
+ n->isdynamic = true;
$$ = (Node *)n;
}
;
@@ -19384,6 +19503,7 @@ unreserved_keyword:
| DOUBLE_P
| DROP
| DXL
+ | DYNAMIC
| EACH
| ENABLE_P
| ENCODING
@@ -20317,6 +20437,7 @@ bare_label_keyword:
| DOUBLE_P
| DROP
| DXL
+ | DYNAMIC
| EACH
| ELSE
| ENABLE_P
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 7c4c7f729d..4592399b18 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -3281,8 +3281,14 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_DROP_VIEW;
break;
case OBJECT_MATVIEW:
- tag = CMDTAG_DROP_MATERIALIZED_VIEW;
+ {
+ if (((DropStmt *) parsetree)->isdynamic)
+ tag = CMDTAG_DROP_DYNAMIC_TABLE;
+ else
+ tag =
CMDTAG_DROP_MATERIALIZED_VIEW;
+
break;
+ }
case OBJECT_INDEX:
tag = CMDTAG_DROP_INDEX;
break;
@@ -3642,15 +3648,26 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_CREATE_TABLE_AS;
break;
case OBJECT_MATVIEW:
- tag = CMDTAG_CREATE_MATERIALIZED_VIEW;
+ {
+ if (((CreateTableAsStmt *)
parsetree)->into->dynamicTbl)
+ tag =
CMDTAG_CREATE_DYNAMIC_TABLE;
+ else
+ tag =
CMDTAG_CREATE_MATERIALIZED_VIEW;
+
break;
+ }
default:
tag = CMDTAG_UNKNOWN;
}
break;
case T_RefreshMatViewStmt:
- tag = CMDTAG_REFRESH_MATERIALIZED_VIEW;
+ {
+ if (((RefreshMatViewStmt*)
parsetree)->isdynamic)
+ tag = CMDTAG_REFRESH_DYNAMIC_TABLE;
+ else
+ tag = CMDTAG_REFRESH_MATERIALIZED_VIEW;
+ }
break;
case T_AlterSystemStmt:
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index a90852798f..4ec87638a7 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -37,11 +37,13 @@
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_task.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "common/fe_memutils.h"
#include "common/keywords.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -12351,6 +12353,71 @@ flatten_reloptions(Oid relid)
return result;
}
+/*
+ * Get dynamic table's corresponding task SCHEDULE.
+ */
+Datum
+pg_get_dynamic_table_schedule(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ Relation pg_task;
+ StringInfoData buf;
+ char *username;
+ SysScanDesc scanDescriptor = NULL;
+ ScanKeyData scanKey[2];
+ HeapTuple heapTuple = NULL;
+ Form_pg_task task = NULL;
+
+ if (!get_rel_relisdynamic(relid))
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("relation of oid \"%u\" is not dynamic
table", relid)));
+ PG_RETURN_TEXT_P(cstring_to_text(""));
+ }
+
+ pg_task = table_open(TaskRelationId, AccessShareLock);
+ if (!pg_task)
+ {
+ table_close(pg_task, AccessShareLock);
+ PG_RETURN_TEXT_P(cstring_to_text(""));
+ }
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "%s%u", DYNAMIC_TASK_PREFIX, relid);
+
+ /* FIXME: is it possible that supersuers try to dump task? */
+ username = GetUserNameFromId(GetUserId(), false);
+
+ ScanKeyInit(&scanKey[0], Anum_pg_task_jobname,
+ BTEqualStrategyNumber, F_TEXTEQ,
CStringGetTextDatum(buf.data));
+ ScanKeyInit(&scanKey[1], Anum_pg_task_username,
+ BTEqualStrategyNumber, F_TEXTEQ,
CStringGetTextDatum(username));
+
+ scanDescriptor = systable_beginscan(pg_task,
TaskJobNameUserNameIndexId, false,
+
NULL, 2, scanKey);
+
+ heapTuple = systable_getnext(scanDescriptor);
+ if (!HeapTupleIsValid(heapTuple))
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("task \"%s\" does not exist",
buf.data)));
+ table_close(pg_task, AccessShareLock);
+ PG_RETURN_TEXT_P(cstring_to_text(""));
+ }
+
+ task = (Form_pg_task) GETSTRUCT(heapTuple);
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "%s", text_to_cstring(&task->schedule));
+
+ systable_endscan(scanDescriptor);
+ table_close(pg_task, AccessShareLock);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
/* ----------
* get_table_distributedby - Get the DISTRIBUTED BY definition of
a table.
* ----------
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index 9799f5b220..1a64d3870b 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2069,6 +2069,30 @@ get_rel_relisivm(Oid relid)
return false;
}
+/*
+ * get_rel_relisdynamic
+ *
+ * Returns the relisdynamic flag associated with a given relation.
+ */
+bool
+get_rel_relisdynamic(Oid relid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_class reltup = (Form_pg_class) GETSTRUCT(tp);
+ bool result;
+
+ result = reltup->relisdynamic;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return false;
+}
+
/*
* get_rel_tablespace
*
diff --git a/src/backend/utils/cache/relcache.c
b/src/backend/utils/cache/relcache.c
index 527aa9e18c..30532663b4 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1986,6 +1986,8 @@ formrdesc(const char *relationName, Oid relationReltype,
relation->rd_rel->relispopulated = true;
/* ... and they're always no ivm, too */
relation->rd_rel->relisivm = false;
+ /* ... and they're always not dynamic, too */
+ relation->rd_rel->relisdynamic = false;
relation->rd_rel->relreplident = REPLICA_IDENTITY_NOTHING;
relation->rd_rel->relpages = 0;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a2f8b4b91c..bfdf20beab 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -389,6 +389,7 @@ static bool testAttributeEncodingSupport(Archive *fout);
static char *nextToken(register char **stringp, register const char *delim);
static void addDistributedBy(Archive *fout, PQExpBuffer q, const TableInfo
*tbinfo, int actual_atts);
static void addDistributedByOld(Archive *fout, PQExpBuffer q, const TableInfo
*tbinfo, int actual_atts);
+static void addSchedule(Archive *fout, PQExpBuffer q, const TableInfo *tbinfo);
static bool isGPDB4300OrLater(Archive *fout);
static bool isGPDB(Archive *fout);
static bool isGPDB5000OrLater(Archive *fout);
@@ -2930,8 +2931,16 @@ refreshMatViewData(Archive *fout, const TableDataInfo
*tdinfo)
q = createPQExpBuffer();
- appendPQExpBuffer(q, "REFRESH MATERIALIZED VIEW %s;\n",
- fmtQualifiedDumpable(tbinfo));
+ if (tbinfo->isdynamic)
+ {
+ appendPQExpBuffer(q, "REFRESH DYNAMIC TABLE %s;\n",
+ fmtQualifiedDumpable(tbinfo));
+ }
+ else
+ {
+ appendPQExpBuffer(q, "REFRESH MATERIALIZED VIEW %s;\n",
+ fmtQualifiedDumpable(tbinfo));
+ }
if (tdinfo->dobj.dump & DUMP_COMPONENT_DATA)
ArchiveEntry(fout,
@@ -2940,7 +2949,7 @@ refreshMatViewData(Archive *fout, const TableDataInfo
*tdinfo)
ARCHIVE_OPTS(.tag = tbinfo->dobj.name,
.namespace =
tbinfo->dobj.namespace->dobj.name,
.owner =
tbinfo->rolname,
- .description
= "MATERIALIZED VIEW DATA",
+ .description
= tbinfo->isdynamic ? "DYNAMIC TABLE DATA": "MATERIALIZED VIEW DATA",
.section =
SECTION_POST_DATA,
.createStmt =
q->data,
.deps =
tdinfo->dobj.dependencies,
@@ -7415,6 +7424,7 @@ getTables(Archive *fout, int *numTables)
int i_amname;
int i_amoid;
int i_isivm;
+ int i_isdynamic;
/*
* Find all the tables and table-like objects.
@@ -7535,7 +7545,8 @@ getTables(Archive *fout, int *numTables)
"%s AS partkeydef, "
"%s AS ispartition, "
"%s AS partbound, "
- "c.relisivm AS isivm "
+ "c.relisivm AS isivm, "
+ "c.relisdynamic AS isdynamic "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -8106,6 +8117,7 @@ getTables(Archive *fout, int *numTables)
i_amname = PQfnumber(res, "amname");
i_amoid = PQfnumber(res, "amoid");
i_isivm = PQfnumber(res, "isivm");
+ i_isdynamic = PQfnumber(res, "isdynamic");
if (dopt->lockWaitTimeout)
{
@@ -8238,6 +8250,7 @@ getTables(Archive *fout, int *numTables)
tblinfo[i].ispartition = (strcmp(PQgetvalue(res, i,
i_ispartition), "t") == 0);
tblinfo[i].partbound = pg_strdup(PQgetvalue(res, i,
i_partbound));
tblinfo[i].isivm = (strcmp(PQgetvalue(res, i, i_isivm), "t") ==
0);
+ tblinfo[i].isdynamic = (strcmp(PQgetvalue(res, i, i_isdynamic),
"t") == 0);
/* foreign server */
tblinfo[i].foreign_server = atooid(PQgetvalue(res, i,
i_foreignserver));
@@ -18081,7 +18094,10 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
break;
}
case RELKIND_MATVIEW:
- reltypename = "MATERIALIZED VIEW";
+ if (tbinfo->isdynamic)
+ reltypename = "DYNAMIC TABLE";
+ else
+ reltypename = "MATERIALIZED VIEW";
break;
default:
reltypename = "TABLE";
@@ -18154,14 +18170,23 @@ dumpTableSchema(Archive *fout, const TableInfo
*tbinfo)
}
}
- appendPQExpBuffer(q, "CREATE %s%s%s %s",
- tbinfo->relpersistence ==
RELPERSISTENCE_UNLOGGED ?
- "UNLOGGED " : "",
- tbinfo->relkind ==
RELKIND_MATVIEW && tbinfo->isivm ?
- "INCREMENTAL " : "",
- reltypename,
- qualrelname);
+ if (tbinfo->relkind == RELKIND_MATVIEW && tbinfo->isdynamic)
+ {
+ /* We'r sure there is no UNLOGGED and this is a DYNAMIC
TABLE. */
+ appendPQExpBuffer(q, "CREATE DYNAMIC TABLE %s",
qualrelname);
+ addSchedule(fout, q, tbinfo);
+ }
+ else
+ {
+ appendPQExpBuffer(q, "CREATE %s%s%s %s",
+
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
+ "UNLOGGED " : "",
+ tbinfo->relkind ==
RELKIND_MATVIEW && tbinfo->isivm ?
+ "INCREMENTAL " : "",
+ reltypename,
+ qualrelname);
+ }
/*
* Attach to type, if reloftype; except in case of a binary
upgrade,
* we dump the table normally and attach it to the type
afterward.
@@ -21436,6 +21461,32 @@ testExtProtocolSupport(Archive *fout)
return isSupported;
}
+/*
+ * addSchedule
+ *
+ * find the SCHEDULE of the job in pg_task with dynamic table
+ * and append the SCHEDULE clause to the passed in dump buffer (q).
+ */
+static void
+addSchedule(Archive *fout, PQExpBuffer q, const TableInfo *tbinfo)
+{
+ PQExpBuffer query = createPQExpBuffer();
+ PGresult *res;
+ char *dby;
+
+ appendPQExpBuffer(query,
+ "SELECT
pg_catalog.pg_get_dynamic_table_schedule(%u)",
+ tbinfo->dobj.catId.oid);
+
+ res = ExecuteSqlQueryForSingleRow(fout, query->data);
+
+ dby = PQgetvalue(res, 0, 0);
+ if (strcmp(dby, "") != 0)
+ appendPQExpBuffer(q, " SCHEDULE \'%s\'", PQgetvalue(res, 0, 0));
+
+ PQclear(res);
+ destroyPQExpBuffer(query);
+}
/*
* addDistributedBy
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index a907a9af16..8a79ea2d60 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -398,6 +398,7 @@ typedef struct _tableInfo
int numTriggers; /* number of triggers for table
*/
struct _triggerInfo *triggers; /* array of TriggerInfo structs */
bool isivm; /* is incrementally
maintainable materialized view? */
+ bool isdynamic; /* is dynamic table? */
} TableInfo;
typedef struct _tableAttachInfo
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a06a994ed3..8dc9086cb9 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2186,6 +2186,21 @@ my %tests = (
unlike => { exclude_dump_test_schema => 1, },
},
+ 'CREATE DYNAMIC TABLE dynamic_table' => {
+ create_order => 28,
+ create_sql => 'CREATE DYNAMIC TABLE dump_test.dynamic_table
(col1) AS
+ SELECT col1 FROM
dump_test.test_table;',
+ regexp => qr/^
+ \QCREATE DYNAMIC TABLE dump_test.dynamic_table AS\E
+ \n\s+\QSELECT test_table.col1\E
+ \n\s+\QFROM dump_test.test_table\E
+ \n\s+\QWITH NO DATA;\E
+ /xm,
+ like =>
+ { %full_runs, %dump_test_schema_runs, section_pre_data => 1,
},
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
'CREATE POLICY p1 ON test_table' => {
create_order => 22,
create_sql => 'CREATE POLICY p1 ON dump_test.test_table
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index db0c3c4f74..ca48d71f6d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1332,7 +1332,7 @@ permissionsList(const char *pattern)
" WHEN "
CppAsString2(RELKIND_RELATION) " THEN '%s'"
" WHEN "
CppAsString2(RELKIND_DIRECTORY_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_VIEW) "
THEN '%s'"
- " WHEN "
CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
+ " WHEN "
CppAsString2(RELKIND_MATVIEW) " THEN CASE c.relisdynamic WHEN true THEN '%s'
ELSE '%s' END"
" WHEN "
CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
" WHEN "
CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
" WHEN "
CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
@@ -1343,6 +1343,7 @@ permissionsList(const char *pattern)
gettext_noop("table"),
gettext_noop("directory table"),
gettext_noop("view"),
+ gettext_noop("dynamic table"),
gettext_noop("materialized view"),
gettext_noop("sequence"),
gettext_noop("foreign table"),
@@ -1927,6 +1928,7 @@ describeOneTableDetails(const char *schemaname,
char relreplident;
char *relam;
bool isivm;
+ bool isdynamic;
char *compressionType;
char *compressionLevel;
@@ -1959,7 +1961,8 @@ describeOneTableDetails(const char *schemaname,
"false AS relhasoids,
c.relispartition, %s, c.reltablespace, "
"CASE WHEN c.reloftype = 0
THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
"c.relpersistence,
c.relreplident, am.amname, "
- "c.relisivm\n"
+ "c.relisivm, "
+ "c.relisdynamic \n"
"FROM pg_catalog.pg_class c\n
"
"LEFT JOIN
pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
"LEFT JOIN pg_catalog.pg_am
am ON (c.relam = am.oid)\n"
@@ -2155,6 +2158,7 @@ describeOneTableDetails(const char *schemaname,
else
tableinfo.relam = NULL;
tableinfo.isivm = strcmp(PQgetvalue(res, 0, 15), "t") == 0;
+ tableinfo.isdynamic = strcmp(PQgetvalue(res, 0, 16), "t") == 0;
/* GPDB Only: relstorage */
if (pset.sversion < 120000 && isGPDB())
@@ -2484,8 +2488,18 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&title, _("Unlogged
materialized view \"%s.%s\""),
schemaname,
relationname);
else
- printfPQExpBuffer(&title, _("Materialized view
\"%s.%s\""),
- schemaname,
relationname);
+ {
+ /*
+ * Postgres has forbidden UNLOGGED materialized
view,
+ * only consider below cases.
+ */
+ if (!tableinfo.isdynamic)
+ printfPQExpBuffer(&title,
_("Materialized view \"%s.%s\""),
+
schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Dynamic
table \"%s.%s\""),
+
schemaname, relationname);
+ }
break;
case RELKIND_INDEX:
if (tableinfo.relpersistence == 'u')
@@ -5005,7 +5019,7 @@ listTables(const char *tabtypes, const char *pattern,
bool verbose, bool showSys
" WHEN "
CppAsString2(RELKIND_RELATION) " THEN '%s'"
" WHEN "
CppAsString2(RELKIND_DIRECTORY_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_VIEW) "
THEN '%s'"
- " WHEN "
CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
+ " WHEN "
CppAsString2(RELKIND_MATVIEW) " THEN CASE c.relisdynamic WHEN true THEN '%s'
ELSE '%s' END"
" WHEN " CppAsString2(RELKIND_INDEX)
" THEN '%s'"
" WHEN "
CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
" WHEN "
CppAsString2(RELKIND_TOASTVALUE) " THEN '%s'"
@@ -5019,6 +5033,7 @@ listTables(const char *tabtypes, const char *pattern,
bool verbose, bool showSys
gettext_noop("table"),
gettext_noop("directory table"),
gettext_noop("view"),
+ gettext_noop("dynamic table"),
gettext_noop("materialized view"),
gettext_noop("index"),
gettext_noop("sequence"),
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7af41799f4..5d0081c811 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1121,6 +1121,7 @@ static const pgsql_thing_t words_after_create[] = {
{"DICTIONARY", Query_for_list_of_ts_dictionaries, NULL, NULL,
THING_NO_SHOW},
{"DIRECTORY TABLE", NULL, NULL, &Query_for_list_of_directory_tables},
{"DOMAIN", NULL, NULL, &Query_for_list_of_domains},
+ {"DYNAMIC TABLE", NULL, NULL, &Query_for_list_of_matviews,
THING_NO_ALTER},
{"EVENT TRIGGER", NULL, NULL, NULL},
{"EXTENSION", Query_for_list_of_extensions},
{"FOREIGN DATA WRAPPER", NULL, NULL, NULL},
@@ -1573,7 +1574,7 @@ psql_completion(const char *text, int start, int end)
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE",
"EXPLAIN",
"FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO",
"LISTEN", "LOAD", "LOCK",
"MOVE", "NOTIFY", "PREPARE",
- "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE",
+ "REASSIGN", "REFRESH MATERIALIZED VIEW", "REFRESH DYNAMIC
TABLE", "REINDEX", "RELEASE",
"RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START",
"TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES",
"WITH",
@@ -2480,7 +2481,7 @@ psql_completion(const char *text, int start, int end)
"FOREIGN DATA WRAPPER", "FOREIGN
TABLE", "SERVER",
"INDEX", "LANGUAGE", "POLICY",
"PUBLICATION", "RULE",
"SCHEMA", "SEQUENCE", "STATISTICS",
"SUBSCRIPTION",
- "TABLE", "TYPE", "VIEW",
"MATERIALIZED VIEW",
+ "TABLE", "TYPE", "VIEW",
"MATERIALIZED VIEW", "DYNAMIC TABLE",
"COLUMN", "AGGREGATE", "FUNCTION",
"STORAGE SERVER",
"PROCEDURE", "PROFILE", "ROUTINE",
"OPERATOR", "TRIGGER", "CONSTRAINT",
"DOMAIN",
@@ -2849,7 +2850,7 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
/* Complete "CREATE UNLOGGED" with TABLE or MATVIEW */
else if (TailMatches("CREATE", "UNLOGGED"))
- COMPLETE_WITH("TABLE", "MATERIALIZED VIEW", "INCREMENTAL
MATERIALIZED VIEW");
+ COMPLETE_WITH("TABLE", "MATERIALIZED VIEW", "INCREMENTAL
MATERIALIZED VIEW", "DYNAMIC TABLE");
/* Complete PARTITION BY with RANGE ( or LIST ( or ... */
else if (TailMatches("PARTITION", "BY"))
COMPLETE_WITH("RANGE (", "LIST (", "HASH (");
@@ -3199,6 +3200,21 @@ psql_completion(const char *text, int start, int end)
Matches("CREATE", "INCREMENTAL", "MATERIALIZED",
"VIEW", MatchAny, "AS"))
COMPLETE_WITH("SELECT");
+/* CREATE DYNAMIC TABLE */
+ else if (Matches("CREATE", "DYNAMIC"))
+ COMPLETE_WITH("TABLE");
+ /* Complete CREATE DYNAMIC TABLE <name> with AS */
+ /* Complete CREATE DYNAMIC TABLE <name> SCHEDULE <schedule> with AS */
+ else if (Matches("CREATE", "DYNAMIC", "TABLE", MatchAny))
+ COMPLETE_WITH("SCHEDULE", "AS");
+ else if (Matches("CREATE", "DYNAMIC", "TABLE", MatchAny, "SCHEDULE",
MatchAny))
+ COMPLETE_WITH("AS");
+ /* Complete "CREATE DYNAMIC TABLE <sth> AS with "SELECT" */
+ /* Complete "CREATE DYNAMIC TABLE <sth> SCHEDULE <schedule> AS with
"SELECT" */
+ else if (Matches("CREATE", "DYNAMIC", "TABLE", MatchAny, "AS") ||
+ Matches("CREATE", "DYNAMIC", "TABLE", MatchAny,
"SCHEDULE", MatchAny,"AS"))
+ COMPLETE_WITH("SELECT");
+
/* CREATE EVENT TRIGGER */
else if (Matches("CREATE", "EVENT"))
COMPLETE_WITH("TRIGGER");
@@ -3342,6 +3358,12 @@ psql_completion(const char *text, int start, int end)
else if (Matches("DROP", "MATERIALIZED", "VIEW"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+ /* DROP DYNAMIC TABLE */
+ else if (Matches("DROP", "DYNAMIC"))
+ COMPLETE_WITH("TABLE");
+ else if (Matches("DROP", "DYNAMIC", "TABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+
/* DROP STORAGE */
else if (Matches("DROP", "STORAGE"))
COMPLETE_WITH("SERVER", "USER MAPPING");
@@ -3825,7 +3847,7 @@ psql_completion(const char *text, int start, int end)
/* REFRESH MATERIALIZED VIEW */
else if (Matches("REFRESH"))
- COMPLETE_WITH("MATERIALIZED VIEW");
+ COMPLETE_WITH("MATERIALIZED VIEW", "DYNAMIC TABLE");
else if (Matches("REFRESH", "MATERIALIZED"))
COMPLETE_WITH("VIEW");
else if (Matches("REFRESH", "MATERIALIZED", "VIEW"))
@@ -3846,6 +3868,27 @@ psql_completion(const char *text, int start, int end)
else if (Matches("REFRESH", "MATERIALIZED", "VIEW", "CONCURRENTLY",
MatchAny, "WITH", "NO"))
COMPLETE_WITH("DATA");
+/* REFRESH DYNAMIC TABLE */
+ else if (Matches("REFRESH", "DYNAMIC"))
+ COMPLETE_WITH("TABLE");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews,
+ " UNION
SELECT 'CONCURRENTLY'");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", "CONCURRENTLY"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", MatchAny))
+ COMPLETE_WITH("WITH");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", "CONCURRENTLY",
MatchAny))
+ COMPLETE_WITH("WITH");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", MatchAny, "WITH"))
+ COMPLETE_WITH("NO DATA", "DATA");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", "CONCURRENTLY",
MatchAny, "WITH"))
+ COMPLETE_WITH("NO DATA", "DATA");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", MatchAny, "WITH", "NO"))
+ COMPLETE_WITH("DATA");
+ else if (Matches("REFRESH", "DYNAMIC", "TABLE", "CONCURRENTLY",
MatchAny, "WITH", "NO"))
+ COMPLETE_WITH("DATA");
+
/* REINDEX */
else if (Matches("REINDEX") ||
Matches("REINDEX", "(*)"))
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 8ac4a14a7c..1370f78a11 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -56,6 +56,6 @@
*/
/* 3yyymmddN */
-#define CATALOG_VERSION_NO 302412051
+#define CATALOG_VERSION_NO 302412061
#endif
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index ec525930d5..64a45b1793 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -122,6 +122,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP
BKI_ROWTYPE_OID(83,Relat
/* is relation a matview with ivm? */
bool relisivm BKI_DEFAULT(f);
+ /* is relation a dynamic table? */
+ bool relisdynamic BKI_DEFAULT(f);
+
/* link to original rel during table rewrite; otherwise 0 */
Oid relrewrite BKI_DEFAULT(0)
BKI_LOOKUP_OPT(pg_class);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c21af38c99..9941c64988 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12601,3 +12601,7 @@
proargtypes => '_oid text', proallargtypes => '{_oid,text,oid,int8}',
proargmodes => '{i,i,o,o}', proargnames => '{reloids,forkname,reloid,size}',
prosrc => 'cbdb_relation_size' },
+
+{ oid => 8693, descr => 'deparse SCHEDULE clause for a given dynamic table',
+ proname => 'pg_get_dynamic_table_schedule', provolatile => 's', prorettype
=> 'text',
+ proargtypes => 'oid', prosrc => 'pg_get_dynamic_table_schedule' },
diff --git a/src/include/catalog/pg_task.h b/src/include/catalog/pg_task.h
index 1b870b925e..30c9c6613c 100644
--- a/src/include/catalog/pg_task.h
+++ b/src/include/catalog/pg_task.h
@@ -55,4 +55,10 @@ extern Oid GetTaskJobId(const char *jobname, const char
*username);
extern char * GetTaskNameById(Oid jobid);
+/* Reversed prefix for Dynamic Tables. */
+#define DYNAMIC_TASK_PREFIX "gp_dynamic_table_refresh_"
+
+/* Default Dynamic Table Schedule */
+#define DYNAMIC_TABLE_DEFAULT_REFRESH_INTERVAL "*/5 * * * *"
+
#endif /* PG_TASK_H */
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index 2a0cef359e..8cfb1f55af 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -26,6 +26,8 @@ extern void SetMatViewPopulatedState(Relation relation, bool
newstate);
extern void SetMatViewIVMState(Relation relation, bool newstate);
+extern void SetDynamicTableState(Relation relation);
+
extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char
*queryString,
ParamListInfo params, QueryCompletion *qc);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0e416ca0da..1c2ecca128 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3438,6 +3438,7 @@ typedef struct DropStmt
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
bool missing_ok; /* skip error if object is
missing? */
bool concurrent; /* drop index concurrently? */
+ bool isdynamic; /* drop a dynamic table? */
} DropStmt;
/* ----------------------
@@ -4121,6 +4122,7 @@ typedef struct RefreshMatViewStmt
bool concurrent; /* allow concurrent access? */
bool skipData; /* true for WITH NO DATA */
RangeVar *relation; /* relation to insert into */
+ bool isdynamic; /* relation is dynamic table? */
} RefreshMatViewStmt;
/* ----------------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 68b47e7669..91def3cd71 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -125,6 +125,9 @@ typedef struct IntoClause
bool ivm; /* true for WITH IVM */
Oid matviewOid; /* matview oid */
char *enrname; /* ENR name for materialized
view delta */
+ bool dynamicTbl; /* true for Dynamic Tables. */
+ /* pg_task cron schedule, used for Dynamic Tables. */
+ char *schedule;
} IntoClause;
typedef struct CopyIntoClause
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a083093ebc..aef5b14483 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -160,6 +160,7 @@ PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("dxl", DXL, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index b0346e2b9b..21f27de42a 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -103,6 +103,7 @@ PG_CMDTAG(CMDTAG_CREATE_CONVERSION, "CREATE CONVERSION",
true, false, false)
PG_CMDTAG(CMDTAG_CREATE_DATABASE, "CREATE DATABASE", false, false, false)
PG_CMDTAG(CMDTAG_CREATE_DIRECTORY_TABLE, "CREATE DIRECTORY TABLE", true,
false, false)
PG_CMDTAG(CMDTAG_CREATE_DOMAIN, "CREATE DOMAIN", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_DYNAMIC_TABLE, "CREATE DYNAMIC TABLE", true, false,
false)
PG_CMDTAG(CMDTAG_CREATE_EVENT_TRIGGER, "CREATE EVENT TRIGGER", false, false,
false)
PG_CMDTAG(CMDTAG_CREATE_EXTENSION, "CREATE EXTENSION", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_EXTERNAL, "CREATE EXTERNAL TABLE", true, false, false)
@@ -173,6 +174,7 @@ PG_CMDTAG(CMDTAG_DROP_CONVERSION, "DROP CONVERSION", true,
false, false)
PG_CMDTAG(CMDTAG_DROP_DATABASE, "DROP DATABASE", false, false, false)
PG_CMDTAG(CMDTAG_DROP_DIRECTORY_TABLE, "DROP DIRECTORY TABLE", true, false,
false)
PG_CMDTAG(CMDTAG_DROP_DOMAIN, "DROP DOMAIN", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_DYNAMIC_TABLE, "DROP DYNAMIC TABLE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_EVENT_TRIGGER, "DROP EVENT TRIGGER", false, false, false)
PG_CMDTAG(CMDTAG_DROP_EXTENSION, "DROP EXTENSION", true, false, false)
PG_CMDTAG(CMDTAG_DROP_FOREIGN_DATA_WRAPPER, "DROP FOREIGN DATA WRAPPER", true,
false, false)
@@ -243,6 +245,7 @@ PG_CMDTAG(CMDTAG_FTS_MSG_PROMOTE, "PROMOTE", false, false,
false)
PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
PG_CMDTAG(CMDTAG_DTX_RECOVERY_ABORT_PREPARED, "Recovery Abort
Prepared", false, false, false)
PG_CMDTAG(CMDTAG_DTX_RECOVERY_COMMIT_PREPARED, "Recovery Commit
Prepared", false, false, false)
+PG_CMDTAG(CMDTAG_REFRESH_DYNAMIC_TABLE, "REFRESH DYNAMIC TABLE", true, false,
false)
PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED
VIEW", true, false, false)
PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index aa2d4014de..b176b746b4 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -172,6 +172,7 @@ extern Oid get_rel_type_id(Oid relid);
extern char get_rel_relkind(Oid relid);
extern bool get_rel_relispartition(Oid relid);
extern bool get_rel_relisivm(Oid relid);
+extern bool get_rel_relisdynamic(Oid relid);
extern Oid get_rel_tablespace(Oid relid);
extern char get_rel_persistence(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
diff --git a/src/test/regress/expected/dynamic_table.out
b/src/test/regress/expected/dynamic_table.out
new file mode 100644
index 0000000000..d95af994ff
--- /dev/null
+++ b/src/test/regress/expected/dynamic_table.out
@@ -0,0 +1,326 @@
+-- start_matchsubs
+-- m/ERROR: can not drop a internal task "gp_dynamic_table_refresh_.*/
+-- s/ERROR: can not drop a internal task "gp_dynamic_table_refresh_.*/ERROR:
can not drop a internal task "gp_dynamic_table_refresh_xxx"/g
+-- m/WARNING: relation of oid "\d+" is not dynamic table/
+-- s/WARNING: relation of oid "\d+" is not dynamic table/WARNING: relation
of oid "XXX" is not dynamic table/g
+-- end_matchsubs
+CREATE SCHEMA dynamic_table_schema;
+SET search_path TO dynamic_table_schema;
+SET optimizer = OFF;
+CREATE TABLE t1(a int, b int, c int) DISTRIBUTED BY (b);
+INSERT INTO t1 SELECT i, i + 1, i + 2 FROM GENERATE_SERIES(1, 10) i;
+INSERT INTO t1 SELECT i, i + 1, i + 2 FROM GENERATE_SERIES(1, 5) i;
+ANALYZE t1;
+CREATE DYNAMIC TABLE dt0 SCHEDULE '5 * * * *' AS
+ SELECT a, b, sum(c) FROM t1 GROUP BY a, b DISTRIBUTED BY(b);
+\d+ dt0
+ Dynamic table "dynamic_table_schema.dt0"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ sum | bigint | | | | plain | |
+View definition:
+ SELECT t1.a,
+ t1.b,
+ sum(t1.c) AS sum
+ FROM t1
+ GROUP BY t1.a, t1.b;
+Distributed by: (b)
+
+ANALYZE dt0;
+-- test backgroud auto-refresh
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
+ schedule | command
+-----------+------------------------------------------------
+ 5 * * * * | REFRESH DYNAMIC TABLE dynamic_table_schema.dt0
+(1 row)
+
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT a, b, sum(c) FROM t1 GROUP BY a, b;
+ QUERY PLAN
+-------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: a, b, (sum(c))
+ -> HashAggregate
+ Output: a, b, sum(c)
+ Group Key: t1.a, t1.b
+ -> Seq Scan on dynamic_table_schema.t1
+ Output: a, b, c
+ Settings: optimizer = 'off'
+ Optimizer: Postgres query optimizer
+(9 rows)
+
+SELECT a, b, sum(c) FROM t1 GROUP BY a, b;
+ a | b | sum
+----+----+-----
+ 9 | 10 | 11
+ 8 | 9 | 10
+ 5 | 6 | 14
+ 10 | 11 | 12
+ 4 | 5 | 12
+ 1 | 2 | 6
+ 2 | 3 | 8
+ 7 | 8 | 9
+ 3 | 4 | 10
+ 6 | 7 | 8
+(10 rows)
+
+SELECT * FROM dt0;
+ a | b | sum
+----+----+-----
+ 1 | 2 | 6
+ 2 | 3 | 8
+ 7 | 8 | 9
+ 3 | 4 | 10
+ 6 | 7 | 8
+ 9 | 10 | 11
+ 8 | 9 | 10
+ 5 | 6 | 14
+ 10 | 11 | 12
+ 4 | 5 | 12
+(10 rows)
+
+-- test join on distributed keys
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT * FROM dt0 JOIN t1 USING(b);
+ QUERY PLAN
+--------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: dt0.b, dt0.a, dt0.sum, t1.a, t1.c
+ -> Hash Join
+ Output: dt0.b, dt0.a, dt0.sum, t1.a, t1.c
+ Hash Cond: (t1.b = dt0.b)
+ -> Seq Scan on dynamic_table_schema.t1
+ Output: t1.a, t1.b, t1.c
+ -> Hash
+ Output: dt0.b, dt0.a, dt0.sum
+ -> Seq Scan on dynamic_table_schema.dt0
+ Output: dt0.b, dt0.a, dt0.sum
+ Settings: optimizer = 'off'
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+-- Create Dynamic Table without SCHEDULE.
+CREATE DYNAMIC TABLE dt1 AS
+ SELECT * FROM t1 WHERE a = 1 DISTRIBUTED BY(b);
+ANALYZE dt1;
+-- Create Dynamic Table without DISTRIBUTION KEYS.
+CREATE DYNAMIC TABLE dt2 AS
+ SELECT * FROM t1 WHERE a = 2 WITH NO DATA;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+-- Refresh Dynamic Table WITH NO DATA
+REFRESH DYNAMIC TABLE dt0 WITH NO DATA;
+-- Refresh Dynamic Table
+REFRESH DYNAMIC TABLE dt2;
+ANALYZE dt2;
+-- Test Answer Query using Dynamic Tables.
+SET enable_answer_query_using_materialized_views = ON;
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT * FROM t1 WHERE a = 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: a, b, c
+ -> Seq Scan on dynamic_table_schema.dt1
+ Output: a, b, c
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+SELECT * FROM t1 WHERE a = 1;
+ a | b | c
+---+---+---
+ 1 | 2 | 3
+ 1 | 2 | 3
+(2 rows)
+
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT * FROM t1 WHERE a = 2;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: a, b, c
+ -> Seq Scan on dynamic_table_schema.dt2
+ Output: a, b, c
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+SELECT * FROM t1 WHERE a = 2;
+ a | b | c
+---+---+---
+ 2 | 3 | 4
+ 2 | 3 | 4
+(2 rows)
+
+-- test DROP DYNAMIC TABLE
+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';
+ schedule | command
+----------+---------
+(0 rows)
+
+-- drop base tables will drop DYNAMIC TABLEs too.
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
+ schedule | command
+-------------+------------------------------------------------
+ */5 * * * * | REFRESH DYNAMIC TABLE dynamic_table_schema.dt1
+ */5 * * * * | REFRESH DYNAMIC TABLE dynamic_table_schema.dt2
+(2 rows)
+
+DROP TABLE t1 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to dynamic table dt1
+drop cascades to dynamic table dt2
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
+ schedule | command
+----------+---------
+(0 rows)
+
+-- construct dynamic table
+CREATE TABLE t2(a int, b int, c int) DISTRIBUTED BY (b);
+CREATE MATERIALIZED VIEW mv_t2 AS
+ SELECT * FROM t2 WHERE a > 1;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+-- construct dynamic table from materialized view
+CREATE DYNAMIC TABLE dt3 AS
+ SELECT * FROM mv_t2 WHERE a = 2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+-- construct dynamic table from dynamic table
+CREATE DYNAMIC TABLE dt4 AS
+ SELECT * FROM dt3 WHERE b = 3;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+-- construct dynamic table from joins
+CREATE DYNAMIC TABLE dt5 AS
+ SELECT * FROM dt3 natural join t2 natural join mv_t2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+-- construct dynamic table from external table
+begin;
+--start_ignore
+CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as
'$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE NO SQL;
+CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as
'$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL;
+DROP PROTOCOL IF EXISTS demoprot;
+NOTICE: protocol "demoprot" does not exist, skipping
+--end_ignore
+CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc =
'write_to_file'); -- should succeed
+CREATE WRITABLE EXTERNAL TABLE ext_w(id int)
+ LOCATION('demoprot://dynamic_table_text_file.txt')
+FORMAT 'text'
+DISTRIBUTED BY (id);
+INSERT INTO ext_w SELECT * FROM generate_series(1, 10);
+CREATE READABLE EXTERNAL TABLE ext_r(id int)
+ LOCATION('demoprot://dynamic_table_text_file.txt')
+FORMAT 'text';
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT sum(id) FROM ext_r where id > 5;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(id)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL sum(id))
+ -> Partial Aggregate
+ Output: PARTIAL sum(id)
+ -> Foreign Scan on dynamic_table_schema.ext_r
+ Output: id
+ Filter: (ext_r.id > 5)
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+SELECT sum(id) FROM ext_r where id > 5;
+ sum
+-----
+ 40
+(1 row)
+
+CREATE DYNAMIC TABLE dt_external AS
+ SELECT * FROM ext_r where id > 5;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'id' 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.
+ANALYZE dt_external;
+SHOW optimizer;
+ optimizer
+-----------
+ off
+(1 row)
+
+SET LOCAL enable_answer_query_using_materialized_views = ON;
+SET LOCAL aqumv_allow_foreign_table = ON;
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT sum(id) FROM ext_r where id > 5;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(id)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL sum(id))
+ -> Partial Aggregate
+ Output: PARTIAL sum(id)
+ -> Seq Scan on dynamic_table_schema.dt_external
+ Output: id
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(10 rows)
+
+SELECT sum(id) FROM ext_r where id > 5;
+ sum
+-----
+ 40
+(1 row)
+
+DROP FOREIGN TABLE ext_r CASCADE;
+NOTICE: drop cascades to dynamic table dt_external
+DROP FOREIGN TABLE ext_w;
+ABORT;
+-- Test resevered job name for Dynamic Tables.
+SELECT 'dt5'::regclass::oid AS dtoid \gset
+-- should fail
+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.
+-- 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
+DETAIL: please drop the dynamic table instead
+\unset dtoid
+CREATE DYNAMIC TABLE dt_schedule SCHEDULE '1 2 3 4 5' AS SELECT * FROM t2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b' 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.
+SELECT pg_catalog.pg_get_dynamic_table_schedule('dt_schedule'::regclass::oid);
+ pg_get_dynamic_table_schedule
+-------------------------------
+ 1 2 3 4 5
+(1 row)
+
+-- not a dynamic table
+SELECT pg_catalog.pg_get_dynamic_table_schedule('t2'::regclass::oid);
+WARNING: relation of oid "XXX" is not dynamic table
+ pg_get_dynamic_table_schedule
+-------------------------------
+
+(1 row)
+
+RESET enable_answer_query_using_materialized_views;
+RESET optimizer;
+--start_ignore
+DROP SCHEMA dynamic_table_schema cascade;
+NOTICE: drop cascades to 5 other objects
+DETAIL: drop cascades to table t2
+drop cascades to materialized view mv_t2
+drop cascades to dynamic table dt3
+drop cascades to dynamic table dt4
+drop cascades to dynamic table dt5
+--end_ignore
diff --git a/src/test/regress/greenplum_schedule
b/src/test/regress/greenplum_schedule
index dbc67d4944..3a1f7d9dd5 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -340,6 +340,8 @@ test: uao_dml/ao_unique_index_build_row
uao_dml/ao_unique_index_build_column
test: bfv_copy
+test: dynamic_table
+
# run this at the end of the schedule for more chance to catch abnormalities
# different CI env with GPDB
# test: gp_check_files
diff --git a/src/test/regress/sql/dynamic_table.sql
b/src/test/regress/sql/dynamic_table.sql
new file mode 100644
index 0000000000..69a190d5a4
--- /dev/null
+++ b/src/test/regress/sql/dynamic_table.sql
@@ -0,0 +1,144 @@
+-- start_matchsubs
+-- m/ERROR: can not drop a internal task "gp_dynamic_table_refresh_.*/
+-- s/ERROR: can not drop a internal task "gp_dynamic_table_refresh_.*/ERROR:
can not drop a internal task "gp_dynamic_table_refresh_xxx"/g
+-- m/WARNING: relation of oid "\d+" is not dynamic table/
+-- s/WARNING: relation of oid "\d+" is not dynamic table/WARNING: relation
of oid "XXX" is not dynamic table/g
+-- end_matchsubs
+CREATE SCHEMA dynamic_table_schema;
+SET search_path TO dynamic_table_schema;
+SET optimizer = OFF;
+
+CREATE TABLE t1(a int, b int, c int) DISTRIBUTED BY (b);
+INSERT INTO t1 SELECT i, i + 1, i + 2 FROM GENERATE_SERIES(1, 10) i;
+INSERT INTO t1 SELECT i, i + 1, i + 2 FROM GENERATE_SERIES(1, 5) i;
+ANALYZE t1;
+CREATE DYNAMIC TABLE dt0 SCHEDULE '5 * * * *' AS
+ SELECT a, b, sum(c) FROM t1 GROUP BY a, b DISTRIBUTED BY(b);
+\d+ dt0
+ANALYZE dt0;
+-- test backgroud auto-refresh
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
+
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT a, b, sum(c) FROM t1 GROUP BY a, b;
+SELECT a, b, sum(c) FROM t1 GROUP BY a, b;
+SELECT * FROM dt0;
+
+-- test join on distributed keys
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT * FROM dt0 JOIN t1 USING(b);
+
+-- Create Dynamic Table without SCHEDULE.
+CREATE DYNAMIC TABLE dt1 AS
+ SELECT * FROM t1 WHERE a = 1 DISTRIBUTED BY(b);
+ANALYZE dt1;
+
+-- Create Dynamic Table without DISTRIBUTION KEYS.
+CREATE DYNAMIC TABLE dt2 AS
+ SELECT * FROM t1 WHERE a = 2 WITH NO DATA;
+
+-- Refresh Dynamic Table WITH NO DATA
+REFRESH DYNAMIC TABLE dt0 WITH NO DATA;
+
+-- Refresh Dynamic Table
+REFRESH DYNAMIC TABLE dt2;
+ANALYZE dt2;
+
+-- Test Answer Query using Dynamic Tables.
+SET enable_answer_query_using_materialized_views = ON;
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 1;
+EXPLAIN(COSTS OFF, VERBOSE)
+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';
+DROP DYNAMIC TABLE 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%';
+DROP TABLE t1 CASCADE;
+SELECT schedule, command FROM pg_task WHERE jobname LIKE
'gp_dynamic_table_refresh%';
+
+-- construct dynamic table
+CREATE TABLE t2(a int, b int, c int) DISTRIBUTED BY (b);
+CREATE MATERIALIZED VIEW mv_t2 AS
+ SELECT * FROM t2 WHERE a > 1;
+
+-- construct dynamic table from materialized view
+CREATE DYNAMIC TABLE dt3 AS
+ SELECT * FROM mv_t2 WHERE a = 2;
+
+-- construct dynamic table from dynamic table
+CREATE DYNAMIC TABLE dt4 AS
+ SELECT * FROM dt3 WHERE b = 3;
+
+-- construct dynamic table from joins
+CREATE DYNAMIC TABLE dt5 AS
+ SELECT * FROM dt3 natural join t2 natural join mv_t2;
+
+-- construct dynamic table from external table
+begin;
+
+--start_ignore
+CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as
'$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE NO SQL;
+CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as
'$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL;
+DROP PROTOCOL IF EXISTS demoprot;
+--end_ignore
+CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc =
'write_to_file'); -- should succeed
+
+CREATE WRITABLE EXTERNAL TABLE ext_w(id int)
+ LOCATION('demoprot://dynamic_table_text_file.txt')
+FORMAT 'text'
+DISTRIBUTED BY (id);
+
+INSERT INTO ext_w SELECT * FROM generate_series(1, 10);
+
+CREATE READABLE EXTERNAL TABLE ext_r(id int)
+ LOCATION('demoprot://dynamic_table_text_file.txt')
+FORMAT 'text';
+
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT sum(id) FROM ext_r where id > 5;
+SELECT sum(id) FROM ext_r where id > 5;
+
+CREATE DYNAMIC TABLE dt_external AS
+ SELECT * FROM ext_r where id > 5;
+
+ANALYZE dt_external;
+
+SHOW optimizer;
+SET LOCAL enable_answer_query_using_materialized_views = ON;
+SET LOCAL aqumv_allow_foreign_table = ON;
+
+EXPLAIN(COSTS OFF, VERBOSE)
+SELECT sum(id) FROM ext_r where id > 5;
+SELECT sum(id) FROM ext_r where id > 5;
+DROP FOREIGN TABLE ext_r CASCADE;
+DROP FOREIGN TABLE ext_w;
+ABORT;
+
+-- Test resevered job name for Dynamic Tables.
+SELECT 'dt5'::regclass::oid AS dtoid \gset
+
+-- should fail
+CREATE TASK gp_dynamic_table_refresh_xxx SCHEDULE '1 second' AS 'REFRESH
DYNAMIC TABLE dt5';
+
+-- should fail
+DROP TASK gp_dynamic_table_refresh_:dtoid;
+
+\unset dtoid
+
+CREATE DYNAMIC TABLE dt_schedule SCHEDULE '1 2 3 4 5' AS SELECT * FROM t2;
+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);
+
+RESET enable_answer_query_using_materialized_views;
+RESET optimizer;
+--start_ignore
+DROP SCHEMA dynamic_table_schema cascade;
+--end_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
