diff options
103 files changed, 4240 insertions, 438 deletions
diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c index d8ed06f420..8341a1ffef 100644 --- a/contrib/oid2name/oid2name.c +++ b/contrib/oid2name/oid2name.c @@ -444,7 +444,7 @@ sql_exec_dumpalltables(PGconn *conn, struct options * opts) " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()," " pg_catalog.pg_tablespace t " - "WHERE relkind IN ('r'%s%s) AND " + "WHERE relkind IN ('r', 'm'%s%s) AND " " %s" " t.oid = CASE" " WHEN reltablespace <> 0 THEN reltablespace" @@ -515,7 +515,7 @@ sql_exec_searchtables(PGconn *conn, struct options * opts) " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n" " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n" " pg_catalog.pg_tablespace t \n" - "WHERE relkind IN ('r', 'i', 'S', 't') AND \n" + "WHERE relkind IN ('r', 'm', 'i', 'S', 't') AND \n" " t.oid = CASE\n" " WHEN reltablespace <> 0 THEN reltablespace\n" " ELSE dattablespace\n" diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index 1905c4399d..a5aa40f625 100644 --- a/contrib/pg_upgrade/info.c +++ b/contrib/pg_upgrade/info.c @@ -282,7 +282,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) "CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid " "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n " " ON c.relnamespace = n.oid " - "WHERE relkind IN ('r', 'i'%s) AND " + "WHERE relkind IN ('r', 'm', 'i'%s) AND " /* exclude possible orphaned temp tables */ " ((n.nspname !~ '^pg_temp_' AND " " n.nspname !~ '^pg_toast_temp_' AND " diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c index cd6497c220..489b68003c 100644 --- a/contrib/pg_upgrade/pg_upgrade.c +++ b/contrib/pg_upgrade/pg_upgrade.c @@ -525,8 +525,8 @@ set_frozenxids(void) PQclear(executeQueryOrDie(conn, "UPDATE pg_catalog.pg_class " "SET relfrozenxid = '%u' " - /* only heap and TOAST are vacuumed */ - "WHERE relkind IN ('r', 't')", + /* only heap, materialized view, and TOAST are vacuumed */ + "WHERE relkind IN ('r', 'm', 't')", old_cluster.controldata.chkpnt_nxtxid)); PQfinish(conn); diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c index 4551d68ba4..e244dcf9ea 100644 --- a/contrib/pg_upgrade/version_old_8_3.c +++ b/contrib/pg_upgrade/version_old_8_3.c @@ -145,6 +145,7 @@ old_8_3_check_for_tsquery_usage(ClusterInfo *cluster) "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " + /* materialized views didn't exist in 8.3, so no need to check 'm' */ "WHERE c.relkind = 'r' AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " @@ -323,6 +324,7 @@ old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode) "FROM pg_catalog.pg_class c, " " pg_catalog.pg_namespace n, " " pg_catalog.pg_attribute a " + /* materialized views didn't exist in 8.3, so no need to check 'm' */ "WHERE c.relkind = 'r' AND " " c.oid = a.attrelid AND " " NOT a.attisdropped AND " @@ -343,6 +345,7 @@ old_8_3_rebuild_tsvector_tables(ClusterInfo *cluster, bool check_mode) "FROM pg_catalog.pg_class c, " \ " pg_catalog.pg_namespace n, " \ " pg_catalog.pg_attribute a " \ + /* materialized views didn't exist in 8.3, so no need to check 'm' */ \ "WHERE c.relkind = 'r' AND " \ " c.oid = a.attrelid AND " \ " NOT a.attisdropped AND " \ diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c index 8d4fd8b0c9..7f41ec3ad9 100644 --- a/contrib/pgstattuple/pgstattuple.c +++ b/contrib/pgstattuple/pgstattuple.c @@ -216,6 +216,7 @@ pgstat_relation(Relation rel, FunctionCallInfo fcinfo) switch (rel->rd_rel->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_TOASTVALUE: case RELKIND_SEQUENCE: return pgstat_heap(rel, fcinfo); diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c index 107eaf9fa1..607849c912 100644 --- a/contrib/vacuumlo/vacuumlo.c +++ b/contrib/vacuumlo/vacuumlo.c @@ -209,7 +209,7 @@ vacuumlo(const char *database, const struct _param * param) strcat(buf, " AND a.atttypid = t.oid "); strcat(buf, " AND c.relnamespace = s.oid "); strcat(buf, " AND t.typname in ('oid', 'lo') "); - strcat(buf, " AND c.relkind = 'r'"); + strcat(buf, " AND c.relkind in ('r', 'm')"); strcat(buf, " AND s.nspname !~ '^pg_'"); res = PQexec(conn, buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9144eec674..81c1be3567 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1597,8 +1597,8 @@ The catalog <structname>pg_class</structname> catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also - <structname>pg_index</structname>), sequences, views, composite types, - and TOAST tables; see <structfield>relkind</>. + <structname>pg_index</structname>), sequences, views, materialized + views, composite types, and TOAST tables; see <structfield>relkind</>. Below, when we mean all of these kinds of objects we speak of <quote>relations</quote>. Not all columns are meaningful for all relation types. @@ -1789,8 +1789,9 @@ <entry></entry> <entry> <literal>r</> = ordinary table, <literal>i</> = index, - <literal>S</> = sequence, <literal>v</> = view, <literal>c</> = - composite type, <literal>t</> = TOAST table, + <literal>S</> = sequence, <literal>v</> = view, + <literal>m</> = materialized view, + <literal>c</> = composite type, <literal>t</> = TOAST table, <literal>f</> = foreign table </entry> </row> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 92a79d350a..9b7e967758 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -13744,6 +13744,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); </indexterm> <indexterm> + <primary>pg_relation_is_scannable</primary> + </indexterm> + + <indexterm> <primary>pg_typeof</primary> </indexterm> @@ -13867,29 +13871,29 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <row> <entry><literal><function>pg_get_viewdef(<parameter>view_name</parameter>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get underlying <command>SELECT</command> command for view (<emphasis>deprecated</emphasis>)</entry> + <entry>get underlying <command>SELECT</command> command for view or materialized view (<emphasis>deprecated</emphasis>)</entry> </row> <row> <entry><literal><function>pg_get_viewdef(<parameter>view_name</parameter>, <parameter>pretty_bool</>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get underlying <command>SELECT</command> command for view (<emphasis>deprecated</emphasis>)</entry> + <entry>get underlying <command>SELECT</command> command for view or materialized view (<emphasis>deprecated</emphasis>)</entry> </row> <row> <entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get underlying <command>SELECT</command> command for view</entry> + <entry>get underlying <command>SELECT</command> command for view or materialized view</entry> </row> <row> <entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>, <parameter>pretty_bool</>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get underlying <command>SELECT</command> command for view</entry> + <entry>get underlying <command>SELECT</command> command for view or materialized view</entry> </row> <row> <entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>, <parameter>wrap_column_int</>)</function></literal></entry> <entry><type>text</type></entry> - <entry>get underlying <command>SELECT</command> command for view; - lines with fields are wrapped to specified number of columns, - pretty-printing is implied</entry> + <entry>get underlying <command>SELECT</command> command for view or + materialized view; lines with fields are wrapped to specified + number of columns, pretty-printing is implied</entry> </row> <row> <entry><literal><function>pg_options_to_table(<parameter>reloptions</parameter>)</function></literal></entry> @@ -13907,6 +13911,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <entry>get the path in the file system that this tablespace is located in</entry> </row> <row> + <entry><literal><function>pg_relation_is_scannable(<parameter>relation_oid</parameter>)</function></literal></entry> + <entry><type>boolean</type></entry> + <entry>is the relation scannable; a materialized view which has not been loaded will not be scannable</entry> + </row> + <row> <entry><literal><function>pg_typeof(<parameter>any</parameter>)</function></literal></entry> <entry><type>regtype</type></entry> <entry>get the data type of any value</entry> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index c61c62f228..5846974feb 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -21,6 +21,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY alterIndex SYSTEM "alter_index.sgml"> <!ENTITY alterLanguage SYSTEM "alter_language.sgml"> <!ENTITY alterLargeObject SYSTEM "alter_large_object.sgml"> +<!ENTITY alterMaterializedView SYSTEM "alter_materialized_view.sgml"> <!ENTITY alterOperator SYSTEM "alter_operator.sgml"> <!ENTITY alterOperatorClass SYSTEM "alter_opclass.sgml"> <!ENTITY alterOperatorFamily SYSTEM "alter_opfamily.sgml"> @@ -63,6 +64,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY createGroup SYSTEM "create_group.sgml"> <!ENTITY createIndex SYSTEM "create_index.sgml"> <!ENTITY createLanguage SYSTEM "create_language.sgml"> +<!ENTITY createMaterializedView SYSTEM "create_materialized_view.sgml"> <!ENTITY createOperator SYSTEM "create_operator.sgml"> <!ENTITY createOperatorClass SYSTEM "create_opclass.sgml"> <!ENTITY createOperatorFamily SYSTEM "create_opfamily.sgml"> @@ -102,6 +104,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY dropGroup SYSTEM "drop_group.sgml"> <!ENTITY dropIndex SYSTEM "drop_index.sgml"> <!ENTITY dropLanguage SYSTEM "drop_language.sgml"> +<!ENTITY dropMaterializedView SYSTEM "drop_materialized_view.sgml"> <!ENTITY dropOperator SYSTEM "drop_operator.sgml"> <!ENTITY dropOperatorClass SYSTEM "drop_opclass.sgml"> <!ENTITY dropOperatorFamily SYSTEM "drop_opfamily.sgml"> @@ -136,6 +139,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 refreshMaterializedView SYSTEM "refresh_materialized_view.sgml"> <!ENTITY reindex SYSTEM "reindex.sgml"> <!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml"> <!ENTITY reset SYSTEM "reset.sgml"> diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index 60bc747269..2dbba0c0bb 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -39,6 +39,7 @@ ALTER EXTENSION <replaceable class="PARAMETER">name</replaceable> DROP <replacea FOREIGN DATA WRAPPER <replaceable class="PARAMETER">object_name</replaceable> | FOREIGN TABLE <replaceable class="PARAMETER">object_name</replaceable> | FUNCTION <replaceable class="PARAMETER">function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) | + MATERIALIZED VIEW <replaceable class="PARAMETER">object_name</replaceable> | OPERATOR <replaceable class="PARAMETER">operator_name</replaceable> (<replaceable class="PARAMETER">left_type</replaceable>, <replaceable class="PARAMETER">right_type</replaceable>) | OPERATOR CLASS <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> | OPERATOR FAMILY <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> | diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml new file mode 100644 index 0000000000..b60451374b --- /dev/null +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -0,0 +1,167 @@ +<!-- +doc/src/sgml/ref/alter_materialized_view.sgml +PostgreSQL documentation +--> + +<refentry id="SQL-ALTERMATERIALIZEDVIEW"> + <refmeta> + <refentrytitle>ALTER MATERIALIZED VIEW</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>ALTER MATERIALIZED VIEW</refname> + <refpurpose>change the definition of a materialized view</refpurpose> + </refnamediv> + + <indexterm zone="sql-alterview"> + <primary>ALTER MATERIALIZED VIEW</primary> + </indexterm> + + <refsynopsisdiv> +<synopsis> +ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> + <replaceable class="PARAMETER">action</replaceable> [, ... ] +ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> + RENAME [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> TO <replaceable class="PARAMETER">new_column_name</replaceable> +ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> + RENAME TO <replaceable class="parameter">new_name</replaceable> +ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> + SET SCHEMA <replaceable class="parameter">new_schema</replaceable> + +<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase> + + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable> + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] ) + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } + CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable> + SET WITHOUT CLUSTER + SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) + RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] ) + OWNER TO <replaceable class="PARAMETER">new_owner</replaceable> + SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>ALTER MATERIALIZED VIEW</command> changes various auxiliary + properties of an existing materialized view. + </para> + + <para> + You must own the materialized view to use <command>ALTER MATERIALIZED + VIEW</>. To change a materialized view's schema, you must also have + <literal>CREATE</> privilege on the new schema. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have <literal>CREATE</literal> privilege on + the materialized view's schema. (These restrictions enforce that altering + the owner doesn't do anything you couldn't do by dropping and recreating the + materialized view. However, a superuser can alter ownership of any view + anyway.) + </para> + + <para> + The statement subforms and actions available for + <command>ALTER MATERIALIZED VIEW</command> are a subset of those available + for <command>ALTER TABLE</command>, and have the same meaning when used for + materialized views. See the descriptions for <xref linkend="sql-altertable"> + for details. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of an existing materialized view. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">column_name</replaceable></term> + <listitem> + <para> + Name of a new or existing column. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">new_column_name</replaceable></term> + <listitem> + <para> + New name for an existing column. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">new_owner</replaceable></term> + <listitem> + <para> + The user name of the new owner of the materialized view. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">new_name</replaceable></term> + <listitem> + <para> + The new name for the materialized view. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">new_schema</replaceable></term> + <listitem> + <para> + The new schema for the materialized view. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + To rename the materialized view <literal>foo</literal> to + <literal>bar</literal>: +<programlisting> +ALTER MATERIALIZED VIEW foo RENAME TO bar; +</programlisting></para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>ALTER MATERIALIZED VIEW</command> is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-creatematerializedview"></member> + <member><xref linkend="sql-dropmaterializedview"></member> + <member><xref linkend="sql-refreshmaterializedview"></member> + </simplelist> + </refsect1> +</refentry> diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index a03f15cd56..e94dd4b8de 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -38,6 +38,7 @@ COMMENT ON FUNCTION <replaceable class="PARAMETER">function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) | INDEX <replaceable class="PARAMETER">object_name</replaceable> | LARGE OBJECT <replaceable class="PARAMETER">large_object_oid</replaceable> | + MATERIALIZED VIEW <replaceable class="PARAMETER">object_name</replaceable> | OPERATOR <replaceable class="PARAMETER">operator_name</replaceable> (<replaceable class="PARAMETER">left_type</replaceable>, <replaceable class="PARAMETER">right_type</replaceable>) | OPERATOR CLASS <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> | OPERATOR FAMILY <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> | @@ -279,6 +280,7 @@ COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral'; COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee ID'; COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures'; COMMENT ON LARGE OBJECT 346344 IS 'Planning document'; +COMMENT ON MATERIALIZED VIEW my_matview IS 'Summary of order history'; COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts'; COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus'; COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees'; diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index d800701ff4..01faa3afcf 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -33,8 +33,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</ <title>Description</title> <para> - <command>CREATE INDEX</command> constructs an index - on the specified column(s) of the specified table. + <command>CREATE INDEX</command> constructs an index on the specified column(s) + of the specified relation, which can be a table or a materialized view. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance). </para> diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml new file mode 100644 index 0000000000..ed3bb4d3ae --- /dev/null +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -0,0 +1,154 @@ +<!-- +doc/src/sgml/ref/create_materialized_view.sgml +PostgreSQL documentation +--> + +<refentry id="SQL-CREATEMATERIALIZEDVIEW"> + <refmeta> + <refentrytitle>CREATE MATERIALIZED VIEW</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>CREATE MATERIALIZED VIEW</refname> + <refpurpose>define a new materialized view</refpurpose> + </refnamediv> + + <indexterm zone="sql-creatematerializedview"> + <primary>CREATE MATERIALIZED VIEW</primary> + </indexterm> + + <refsynopsisdiv> +<synopsis> +CREATE [ UNLOGGED ] MATERIALIZED VIEW <replaceable>table_name</replaceable> + [ (<replaceable>column_name</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 MATERIALIZED VIEW</command> defines a materialized view 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</> is used) and may be + refreshed later using <command>REFRESH MATERIALIZED VIEW</command>. + </para> + + <para> + <command>CREATE MATERIALIZED VIEW</command> is similar to + <command>CREATE TABLE AS</>, except that it also remembers the query used + to initialize the view, so that it can be refreshed later upon demand. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><literal>UNLOGGED</></term> + <listitem> + <para> + If specified, the materialized view will be unlogged. + Refer to <xref linkend="sql-createtable"> for details. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>table_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the materialized view to be + created. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>column_name</replaceable></term> + <listitem> + <para> + The name of a column in the new materialized view. If column names are + not provided, they are taken from the output column names of the query. + </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 + materialized view; see <xref linkend="sql-createtable-storage-parameters" + endterm="sql-createtable-storage-parameters-title"> for more + information. + 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 materialized view 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 <xref linkend="sql-select">, <link linkend="sql-table">TABLE</link>, + or <xref linkend="sql-values"> command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>WITH [ NO ] DATA</></term> + <listitem> + <para> + This clause specifies whether or not the materialized view should be + populated at creation time. If not, the materialized view will be + flagged as unscannable and cannot be queried until <command>REFRESH + MATERIALIZED VIEW</> is used. + </para> + </listitem> + </varlistentry> + + </variablelist> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>CREATE MATERIALIZED VIEW</command> is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-altermaterializedview"></member> + <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/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 9739417a70..29c80405bf 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -340,6 +340,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS <title>See Also</title> <simplelist type="inline"> + <member><xref linkend="sql-creatematerializedview"></member> <member><xref linkend="sql-createtable"></member> <member><xref linkend="sql-execute"></member> <member><xref linkend="sql-select"></member> diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 0745e3cdb5..aa3fc1515a 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -379,6 +379,7 @@ CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable c <title>See Also</title> <simplelist type="inline"> + <member><xref linkend="sql-creatematerializedview"></member> <member><xref linkend="sql-alterview"></member> <member><xref linkend="sql-dropview"></member> </simplelist> diff --git a/doc/src/sgml/ref/drop_materialized_view.sgml b/doc/src/sgml/ref/drop_materialized_view.sgml new file mode 100644 index 0000000000..80d8acea36 --- /dev/null +++ b/doc/src/sgml/ref/drop_materialized_view.sgml @@ -0,0 +1,114 @@ +<!-- +doc/src/sgml/ref/drop_materialized_view.sgml +PostgreSQL documentation +--> + +<refentry id="SQL-DROPMATERIALIZEDVIEW"> + <refmeta> + <refentrytitle>DROP MATERIALIZED VIEW</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>DROP MATERIALIZED VIEW</refname> + <refpurpose>remove a materialized view</refpurpose> + </refnamediv> + + <indexterm zone="sql-dropmaterializedview"> + <primary>DROP MATERIALIZED VIEW</primary> + </indexterm> + + <refsynopsisdiv> +<synopsis> +DROP MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>DROP MATERIALIZED VIEW</command> drops an existing materialized + view. To execute this command you must be the owner of the materialized + view. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><literal>IF EXISTS</literal></term> + <listitem> + <para> + Do not throw an error if the materialized view 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 materialized view to + remove. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CASCADE</literal></term> + <listitem> + <para> + Automatically drop objects that depend on the materialized view (such as + other materialized views, or regular views). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>RESTRICT</literal></term> + <listitem> + <para> + Refuse to drop the materialized view 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 materialized view called + <literal>order_summary</literal>: +<programlisting> +DROP MATERIALIZED VIEW order_summary; +</programlisting></para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>DROP MATERIALIZED VIEW</command> is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-creatematerializedview"></member> + <member><xref linkend="sql-altermaterializedview"></member> + <member><xref linkend="sql-refreshmaterializedview"></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml new file mode 100644 index 0000000000..44cff9c98e --- /dev/null +++ b/doc/src/sgml/ref/refresh_materialized_view.sgml @@ -0,0 +1,113 @@ +<!-- +doc/src/sgml/ref/refresh_materialized_view.sgml +PostgreSQL documentation +--> + +<refentry id="SQL-REFRESHMATERIALIZEDVIEW"> + <refmeta> + <refentrytitle>REFRESH MATERIALIZED VIEW</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>REFRESH MATERIALIZED VIEW</refname> + <refpurpose>replace the contents of a materialized view</refpurpose> + </refnamediv> + + <indexterm zone="sql-refreshmaterializedview"> + <primary>REFRESH MATERIALIZED VIEW</primary> + </indexterm> + + <refsynopsisdiv> +<synopsis> +REFRESH MATERIALIZED VIEW <replaceable class="PARAMETER">name</replaceable> + [ WITH [ NO ] DATA ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>REFRESH MATERIALIZED VIEW</command> completely replaces the + contents of a materialized view. 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 materialized view is left in a + scannable state. If <literal>WITH NO DATA</literal> is specified no new + data is generated and the materialized view is left in an unscannable + state. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="PARAMETER">name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the materialized view to + refresh. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + While the default index for future + <xref linkend="SQL-CLUSTER"> + operations is retained, <command>REFRESH MATERIALIZED VIEW</> does not + order the generated rows based on this property. If you want the data + to be ordered upon generation, you must use an <literal>ORDER BY</> + clause in the backing query. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + This command will replace the contents of the materialized view called + <literal>order_summary</literal> using the query from the materialized + view's definition, and leave it in a scannable state: +<programlisting> +REFRESH MATERIALIZED VIEW order_summary; +</programlisting> + </para> + + <para> + This command will free storage associated with the materialized view + <literal>annual_statistics_basis</literal> and leave it in an unscannable + state: +<programlisting> +REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA; +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>REFRESH MATERIALIZED VIEW</command> is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-creatematerializedview"></member> + <member><xref linkend="sql-altermaterializedview"></member> + <member><xref linkend="sql-dropmaterializedview"></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index d946b92e19..52cb1d16f4 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -32,6 +32,7 @@ SECURITY LABEL [ FOR <replaceable class="PARAMETER">provider</replaceable> ] ON FOREIGN TABLE <replaceable class="PARAMETER">object_name</replaceable> FUNCTION <replaceable class="PARAMETER">function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) | LARGE OBJECT <replaceable class="PARAMETER">large_object_oid</replaceable> | + MATERIALIZED VIEW <replaceable class="PARAMETER">object_name</replaceable> | [ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> | ROLE <replaceable class="PARAMETER">object_name</replaceable> | SCHEMA <replaceable class="PARAMETER">object_name</replaceable> | diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 5b0c7745e3..14e217a907 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -49,6 +49,7 @@ &alterIndex; &alterLanguage; &alterLargeObject; + &alterMaterializedView; &alterOperator; &alterOperatorClass; &alterOperatorFamily; @@ -91,6 +92,7 @@ &createGroup; &createIndex; &createLanguage; + &createMaterializedView; &createOperator; &createOperatorClass; &createOperatorFamily; @@ -130,6 +132,7 @@ &dropGroup; &dropIndex; &dropLanguage; + &dropMaterializedView; &dropOperator; &dropOperatorClass; &dropOperatorFamily; @@ -164,6 +167,7 @@ &prepare; &prepareTransaction; &reassignOwned; + &refreshMaterializedView; &reindex; &releaseSavepoint; &reset; diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 5811de7942..68a0f53445 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -893,6 +893,206 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; </sect1> +<sect1 id="rules-materializedviews"> +<title>Materialized Views</title> + +<indexterm zone="rules-materializedviews"> + <primary>rule</primary> + <secondary>and materialized views</secondary> +</indexterm> + +<indexterm zone="rules-materializedviews"> + <primary>materialized view</> + <secondary>implementation through rules</> +</indexterm> + +<indexterm zone="rules-materializedviews"> + <primary>view</> + <secondary>materialized</> +</indexterm> + +<para> + Materialized views in <productname>PostgreSQL</productname> use the + rule system like views do, but persist the results in a table-like form. + The main differences between: + +<programlisting> +CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab; +</programlisting> + + and: + +<programlisting> +CREATE TABLE mymatview AS SELECT * FROM mytab; +</programlisting> + + are that the materialized view cannot subsequently be directly updated + and that the query used to create the materialized view is stored in + exactly the same way that a view's query is stored, so that fresh data + can be generated for the materialized view with: + +<programlisting> +REFRESH MATERIALIZED VIEW mymatview; +</programlisting> + + The information about a materialized view in the + <productname>PostgreSQL</productname> system catalogs is exactly + the same as it is for a table or view. So for the parser, a + materialized view is a relation, just like a table or a view. When + a materialized view is referenced in a query, the data is returned + directly from the materialized view, like from a table; the rule is + only used for populating the materialized view. +</para> + +<para> + While access to the data stored in a materialized view is often much + faster than accessing the underlying tables directly or through a view, + the data is not always current; yet sometimes current data is not needed. + Consider a table which records sales: + +<programlisting> +CREATE TABLE invoice ( + invoice_no integer PRIMARY KEY, + seller_no integer, -- ID of salesperson + invoice_date date, -- date of sale + invoice_amt numeric(13,2) -- amount of sale +); +</programlisting> + + If people want to be able to quickly graph historical sales data, they + might want to summarize, and they may not care about the incomplete data + for the current date: + +<programlisting> +CREATE MATERIALIZED VIEW sales_summary AS + SELECT + seller_no, + invoice_date, + sum(invoice_amt)::numeric(13,2) as sales_amt + FROM invoice + WHERE invoice_date < CURRENT_DATE + GROUP BY + seller_no, + invoice_date + ORDER BY + seller_no, + invoice_date; + +CREATE UNIQUE INDEX sales_summary_seller + ON sales_summary (seller_no, invoice_date); +</programlisting> + + This materialized view might be useful for displaying a graph in the + dashboard created for salespeople. A job could be scheduled to update + the statistics each night using this SQL statement: + +<programlisting> +REFRESH MATERIALIZED VIEW sales_summary; +</programlisting> +</para> + +<para> + Another use for a materialized view is to allow faster access to data + brought across from a remote system, through a foreign data wrapper. + A simple example using <literal>file_fdw</literal> is below, with timings, + but since this is using cache on the local system the performance + difference on a foreign data wrapper to a remote system could be greater. + + Setup: + +<programlisting> +CREATE EXTENSION file_fdw; +CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw ; +CREATE FOREIGN TABLE words (word text NOT NULL) + SERVER local_file + OPTIONS (filename '/etc/dictionaries-common/words'); +CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words; +CREATE UNIQUE INDEX wrd_word ON wrd (word); +CREATE EXTENSION pg_trgm ; +CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops); +VACUUM ANALYZE wrd; +</programlisting> + + Now let's spell-check a word. Using <literal>file_fdw</literal> directly: + +<programlisting> +SELECT count(*) FROM words WHERE word = 'caterpiler'; + + count +------- + 0 +(1 row) +</programlisting> + + The plan is: + +<programlisting> + Aggregate (cost=4125.19..4125.20 rows=1 width=0) (actual time=26.013..26.014 rows=1 loops=1) + -> Foreign Scan on words (cost=0.00..4124.70 rows=196 width=0) (actual time=26.011..26.011 rows=0 loops=1) + Filter: (word = 'caterpiler'::text) + Rows Removed by Filter: 99171 + Foreign File: /etc/dictionaries-common/words + Foreign File Size: 938848 + Total runtime: 26.081 ms +</programlisting> + + If the materialized view is used instead, the query is much faster: + +<programlisting> + Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.074..0.074 rows=1 loops=1) + -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.071..0.071 rows=0 loops=1) + Index Cond: (word = 'caterpiler'::text) + Heap Fetches: 0 + Total runtime: 0.119 ms +</programlisting> + + Either way, the word is spelled wrong, so let's look for what we might + have wanted. Again using <literal>file_fdw</literal>: + +<programlisting> +SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; + + word +--------------- + cater + caterpillar + Caterpillar + caterpillars + caterpillar's + Caterpillar's + caterer + caterer's + caters + catered +(10 rows) +</programlisting> + +<programlisting> + Limit (cost=2195.70..2195.72 rows=10 width=32) (actual time=218.904..218.906 rows=10 loops=1) + -> Sort (cost=2195.70..2237.61 rows=16765 width=32) (actual time=218.902..218.904 rows=10 loops=1) + Sort Key: ((word <-> 'caterpiler'::text)) + Sort Method: top-N heapsort Memory: 25kB + -> Foreign Scan on words (cost=0.00..1833.41 rows=16765 width=32) (actual time=0.046..200.965 rows=99171 loops=1) + Foreign File: /etc/dictionaries-common/words + Foreign File Size: 938848 + Total runtime: 218.966 ms +</programlisting> + + Using the materialized view: + +<programlisting> + Limit (cost=0.28..1.02 rows=10 width=9) (actual time=24.916..25.079 rows=10 loops=1) + -> Index Scan using wrd_trgm on wrd (cost=0.28..7383.70 rows=99171 width=9) (actual time=24.914..25.076 rows=10 loops=1) + Order By: (word <-> 'caterpiler'::text) + Total runtime: 25.884 ms +</programlisting> + + If you can tolerate periodic update of the remote data to the local + database, the performance benefit can be substantial. +</para> + +</sect1> + <sect1 id="rules-update"> <title>Rules on <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</></title> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 456d7462ad..c439702a01 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -791,6 +791,7 @@ extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, Oid amoptions) case RELKIND_RELATION: case RELKIND_TOASTVALUE: case RELKIND_VIEW: + case RELKIND_MATVIEW: options = heap_reloptions(classForm->relkind, datum, false); break; case RELKIND_INDEX: @@ -1191,6 +1192,7 @@ heap_reloptions(char relkind, Datum reloptions, bool validate) } return (bytea *) rdopts; case RELKIND_RELATION: + case RELKIND_MATVIEW: return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP); case RELKIND_VIEW: return default_reloptions(reloptions, validate, RELOPT_KIND_VIEW); diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index d226726654..5250ec7f41 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -2214,7 +2214,8 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid, * If the new tuple is too big for storage or contains already toasted * out-of-line attributes from some other relation, invoke the toaster. */ - if (relation->rd_rel->relkind != RELKIND_RELATION) + if (relation->rd_rel->relkind != RELKIND_RELATION && + relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(tup)); @@ -2802,7 +2803,8 @@ l1: * because we need to look at the contents of the tuple, but it's OK to * release the content lock on the buffer first. */ - if (relation->rd_rel->relkind != RELKIND_RELATION) + if (relation->rd_rel->relkind != RELKIND_RELATION && + relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&tp)); @@ -3346,7 +3348,8 @@ l2: * We need to invoke the toaster if there are already any out-of-line * toasted values present, or if the new tuple is over-threshold. */ - if (relation->rd_rel->relkind != RELKIND_RELATION) + if (relation->rd_rel->relkind != RELKIND_RELATION && + relation->rd_rel->relkind != RELKIND_MATVIEW) { /* toast table entries should never be recursively toasted */ Assert(!HeapTupleHasExternal(&oldtup)); diff --git a/src/backend/access/heap/tuptoaster.c b/src/backend/access/heap/tuptoaster.c index 49f155346c..fc37ceb4a3 100644 --- a/src/backend/access/heap/tuptoaster.c +++ b/src/backend/access/heap/tuptoaster.c @@ -353,10 +353,11 @@ toast_delete(Relation rel, HeapTuple oldtup) bool toast_isnull[MaxHeapAttributeNumber]; /* - * We should only ever be called for tuples of plain relations --- - * recursing on a toast rel is bad news. + * We should only ever be called for tuples of plain relations or + * materialized views --- recursing on a toast rel is bad news. */ - Assert(rel->rd_rel->relkind == RELKIND_RELATION); + Assert(rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_MATVIEW); /* * Get the tuple descriptor and break down the tuple into fields. @@ -443,7 +444,8 @@ toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup, * We should only ever be called for tuples of plain relations --- * recursing on a toast rel is bad news. */ - Assert(rel->rd_rel->relkind == RELKIND_RELATION); + Assert(rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_MATVIEW); /* * Get the tuple descriptor and break down the tuple(s) into fields. diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index b3f5ba0e6e..340350fa39 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -765,6 +765,8 @@ objectsInSchemaToOids(GrantObjectType objtype, List *nspnames) objects = list_concat(objects, objs); objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW); objects = list_concat(objects, objs); + objs = getRelationsInNamespace(namespaceId, RELKIND_MATVIEW); + objects = list_concat(objects, objs); objs = getRelationsInNamespace(namespaceId, RELKIND_FOREIGN_TABLE); objects = list_concat(objects, objs); break; diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index d2037251ae..32f05bbabb 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -3024,6 +3024,10 @@ getRelationDescription(StringInfo buffer, Oid relid) appendStringInfo(buffer, _("view %s"), relname); break; + case RELKIND_MATVIEW: + appendStringInfo(buffer, _("materialized view %s"), + relname); + break; case RELKIND_COMPOSITE_TYPE: appendStringInfo(buffer, _("composite type %s"), relname); diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index db51e0b608..0ecfc78ed0 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -835,6 +835,7 @@ AddNewRelationTuple(Relation pg_class_desc, switch (relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_TOASTVALUE: /* The relation is real, but as yet empty */ @@ -858,6 +859,7 @@ AddNewRelationTuple(Relation pg_class_desc, /* Initialize relfrozenxid and relminmxid */ if (relkind == RELKIND_RELATION || + relkind == RELKIND_MATVIEW || relkind == RELKIND_TOASTVALUE) { /* @@ -1069,8 +1071,8 @@ heap_create_with_catalog(const char *relname, if (IsBinaryUpgrade && OidIsValid(binary_upgrade_next_heap_pg_class_oid) && (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || - relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE || - relkind == RELKIND_FOREIGN_TABLE)) + relkind == RELKIND_VIEW || relkind == RELKIND_MATVIEW || + relkind == RELKIND_COMPOSITE_TYPE || relkind == RELKIND_FOREIGN_TABLE)) { relid = binary_upgrade_next_heap_pg_class_oid; binary_upgrade_next_heap_pg_class_oid = InvalidOid; @@ -1096,6 +1098,7 @@ heap_create_with_catalog(const char *relname, { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: case RELKIND_FOREIGN_TABLE: relacl = get_user_default_acl(ACL_OBJECT_RELATION, ownerid, relnamespace); @@ -1139,6 +1142,7 @@ heap_create_with_catalog(const char *relname, */ if (IsUnderPostmaster && (relkind == RELKIND_RELATION || relkind == RELKIND_VIEW || + relkind == RELKIND_MATVIEW || relkind == RELKIND_FOREIGN_TABLE || relkind == RELKIND_COMPOSITE_TYPE)) new_array_oid = AssignTypeArrayOid(); @@ -1316,7 +1320,8 @@ heap_create_with_catalog(const char *relname, if (relpersistence == RELPERSISTENCE_UNLOGGED) { - Assert(relkind == RELKIND_RELATION || relkind == RELKIND_TOASTVALUE); + Assert(relkind == RELKIND_RELATION || relkind == RELKIND_MATVIEW || + relkind == RELKIND_TOASTVALUE); heap_create_init_fork(new_rel_desc); } @@ -1348,6 +1353,26 @@ heap_create_init_fork(Relation rel) } /* + * Check whether a materialized view is in an initial, unloaded state. + * + * The check here must match what is set up in heap_create_init_fork(). + * Currently the init fork is an empty file. A missing heap is also + * considered to be unloaded. + */ +bool +heap_is_matview_init_state(Relation rel) +{ + Assert(rel->rd_rel->relkind == RELKIND_MATVIEW); + + RelationOpenSmgr(rel); + + if (!smgrexists(rel->rd_smgr, MAIN_FORKNUM)) + return true; + + return (smgrnblocks(rel->rd_smgr, MAIN_FORKNUM) < 1); +} + +/* * RelationRemoveInheritance * * Formerly, this routine checked for child relations and aborted the diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index d902f9d6ba..6f60d7cad1 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -444,6 +444,7 @@ get_object_address(ObjectType objtype, List *objname, List *objargs, case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: address = get_relation_by_qualified_name(objtype, objname, @@ -816,6 +817,13 @@ get_relation_by_qualified_name(ObjectType objtype, List *objname, errmsg("\"%s\" is not a view", RelationGetRelationName(relation)))); break; + case OBJECT_MATVIEW: + if (relation->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(relation)))); + break; case OBJECT_FOREIGN_TABLE: if (relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, @@ -1073,6 +1081,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: case OBJECT_COLUMN: case OBJECT_RULE: diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index c479c23683..f727acd68f 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -94,6 +94,19 @@ CREATE VIEW pg_tables AS LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) WHERE C.relkind = 'r'; +CREATE VIEW pg_matviews AS + SELECT + N.nspname AS schemaname, + C.relname AS matviewname, + pg_get_userbyid(C.relowner) AS matviewowner, + T.spcname AS tablespace, + C.relhasindex AS hasindexes, + pg_relation_is_scannable(C.oid) AS isscannable, + pg_get_viewdef(C.oid) AS definition + FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) + LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) + WHERE C.relkind = 'm'; + CREATE VIEW pg_indexes AS SELECT N.nspname AS schemaname, @@ -105,7 +118,7 @@ CREATE VIEW pg_indexes AS JOIN pg_class I ON (I.oid = X.indexrelid) LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace) - WHERE C.relkind = 'r' AND I.relkind = 'i'; + WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i'; CREATE VIEW pg_stats AS SELECT @@ -206,6 +219,7 @@ SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN rel.relkind = 'r' THEN 'table'::text WHEN rel.relkind = 'v' THEN 'view'::text + WHEN rel.relkind = 'm' THEN 'materialized view'::text WHEN rel.relkind = 'S' THEN 'sequence'::text WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, rel.relnamespace AS objnamespace, @@ -402,7 +416,7 @@ CREATE VIEW pg_stat_all_tables AS FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE C.relkind IN ('r', 't') + WHERE C.relkind IN ('r', 't', 'm') GROUP BY C.oid, N.nspname, C.relname; CREATE VIEW pg_stat_xact_all_tables AS @@ -422,7 +436,7 @@ CREATE VIEW pg_stat_xact_all_tables AS FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE C.relkind IN ('r', 't') + WHERE C.relkind IN ('r', 't', 'm') GROUP BY C.oid, N.nspname, C.relname; CREATE VIEW pg_stat_sys_tables AS @@ -467,7 +481,7 @@ CREATE VIEW pg_statio_all_tables AS pg_class T ON C.reltoastrelid = T.oid LEFT JOIN pg_class X ON T.reltoastidxid = X.oid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE C.relkind IN ('r', 't') + WHERE C.relkind IN ('r', 't', 'm') GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid; CREATE VIEW pg_statio_sys_tables AS @@ -494,7 +508,7 @@ CREATE VIEW pg_stat_all_indexes AS pg_index X ON C.oid = X.indrelid JOIN pg_class I ON I.oid = X.indexrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE C.relkind IN ('r', 't'); + WHERE C.relkind IN ('r', 't', 'm'); CREATE VIEW pg_stat_sys_indexes AS SELECT * FROM pg_stat_all_indexes @@ -520,7 +534,7 @@ CREATE VIEW pg_statio_all_indexes AS pg_index X ON C.oid = X.indrelid JOIN pg_class I ON I.oid = X.indexrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) - WHERE C.relkind IN ('r', 't'); + WHERE C.relkind IN ('r', 't', 'm'); CREATE VIEW pg_statio_sys_indexes AS SELECT * FROM pg_statio_all_indexes diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 7c4ccbdbc0..385d64d4c0 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -84,10 +84,11 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid) rel = heap_openrv(makeRangeVar(NULL, relName, -1), AccessExclusiveLock); - if (rel->rd_rel->relkind != RELKIND_RELATION) + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table", + errmsg("\"%s\" is not a table or materialized view", relName))); /* create_toast_table does all the work */ diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile index 3c322a3441..22f116b78d 100644 --- a/src/backend/commands/Makefile +++ b/src/backend/commands/Makefile @@ -16,7 +16,7 @@ OBJS = aggregatecmds.o alter.o analyze.o async.o cluster.o comment.o \ collationcmds.o constraint.o conversioncmds.o copy.o createas.o \ dbcommands.o define.o discard.o dropcmds.o \ event_trigger.o explain.o extension.o foreigncmds.o functioncmds.o \ - indexcmds.o lockcmds.o operatorcmds.o opclasscmds.o \ + indexcmds.o lockcmds.o matview.o operatorcmds.o opclasscmds.o \ portalcmds.o prepare.o proclang.o \ schemacmds.o seclabel.o sequence.o tablecmds.o tablespace.o trigger.o \ tsearchcmds.o typecmds.o user.o vacuum.o vacuumlazy.o \ diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 269d19cea6..416a068fc7 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -317,6 +317,7 @@ ExecRenameStmt(RenameStmt *stmt) case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_INDEX: case OBJECT_FOREIGN_TABLE: return RenameRelation(stmt); @@ -393,6 +394,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt) case OBJECT_SEQUENCE: case OBJECT_TABLE: case OBJECT_VIEW: + case OBJECT_MATVIEW: return AlterTableNamespace(stmt); case OBJECT_DOMAIN: diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index d7b17a5aba..ad9c911542 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -206,11 +206,12 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy) } /* - * Check that it's a plain table or foreign table; we used to do this in - * get_rel_oids() but seems safer to check after we've locked the - * relation. + * Check that it's a plain table, materialized view, or foreign table; we + * used to do this in get_rel_oids() but seems safer to check after we've + * locked the relation. */ - if (onerel->rd_rel->relkind == RELKIND_RELATION) + if (onerel->rd_rel->relkind == RELKIND_RELATION || + onerel->rd_rel->relkind == RELKIND_MATVIEW) { /* Regular table, so we'll use the regular row acquisition function */ acquirefunc = acquire_sample_rows; diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index c0cb2f6654..8ab8c17519 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -29,6 +29,7 @@ #include "catalog/namespace.h" #include "catalog/toasting.h" #include "commands/cluster.h" +#include "commands/matview.h" #include "commands/tablecmds.h" #include "commands/vacuum.h" #include "miscadmin.h" @@ -379,6 +380,19 @@ cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, check_index_is_clusterable(OldHeap, indexOid, recheck, AccessExclusiveLock); /* + * Quietly ignore the request if the a materialized view is not scannable. + * No harm is done because there is nothing no data to deal with, and we + * don't want to throw an error if this is part of a multi-relation + * request -- for example, CLUSTER was run on the entire database. + */ + if (OldHeap->rd_rel->relkind == RELKIND_MATVIEW && + !OldHeap->rd_isscannable) + { + relation_close(OldHeap, AccessExclusiveLock); + return; + } + + /* * All predicate locks on the tuples or pages are about to be made * invalid, because we move tuples around. Promote them to relation * locks. Predicate locks on indexes will be promoted when they are @@ -901,6 +915,10 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, get_namespace_name(RelationGetNamespace(OldHeap)), RelationGetRelationName(OldHeap)))); + if (OldHeap->rd_rel->relkind == RELKIND_MATVIEW) + /* Make sure the heap looks good even if no rows are written. */ + SetRelationIsScannable(NewHeap); + /* * Scan through the OldHeap, either in OldIndex order or sequentially; * copy each tuple into the NewHeap, or transiently to the tuplesort diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c index 3ec12e7cb9..60db27c205 100644 --- a/src/backend/commands/comment.c +++ b/src/backend/commands/comment.c @@ -83,15 +83,17 @@ CommentObject(CommentStmt *stmt) case OBJECT_COLUMN: /* - * Allow comments only on columns of tables, views, composite - * types, and foreign tables (which are the only relkinds for - * which pg_dump will dump per-column comments). In particular we - * wish to disallow comments on index columns, because the naming - * of an index's columns may change across PG versions, so dumping - * per-column comments could create reload failures. + * Allow comments only on columns of tables, views, materialized + * views, composite types, and foreign tables (which are the only + * relkinds for which pg_dump will dump per-column comments). In + * particular we wish to disallow comments on index columns, + * because the naming of an index's columns may change across PG + * versions, so dumping per-column comments could create reload + * failures. */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index c651ea3028..4825bca363 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -1496,6 +1496,12 @@ BeginCopyTo(Relation rel, errmsg("cannot copy from view \"%s\"", RelationGetRelationName(rel)), errhint("Try the COPY (SELECT ...) TO variant."))); + else if (rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot copy from materialized view \"%s\"", + RelationGetRelationName(rel)), + errhint("Try the COPY (SELECT ...) TO variant."))); else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -2016,6 +2022,11 @@ CopyFrom(CopyState cstate) (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot copy to view \"%s\"", RelationGetRelationName(cstate->rel)))); + else if (cstate->rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot copy to materialized view \"%s\"", + RelationGetRelationName(cstate->rel)))); else if (cstate->rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 66a49db330..a3ff1d56c8 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -2,6 +2,8 @@ * * createas.c * Execution of CREATE TABLE ... AS, a/k/a SELECT INTO + * Since CREATE MATERIALIZED VIEW shares syntax and most behaviors, + * implement that here, too. * * We implement this by diverting the query's normal output to a * specialized DestReceiver type. @@ -27,8 +29,11 @@ #include "access/xact.h" #include "catalog/toasting.h" #include "commands/createas.h" +#include "commands/matview.h" #include "commands/prepare.h" #include "commands/tablecmds.h" +#include "commands/view.h" +#include "parser/analyze.h" #include "parser/parse_clause.h" #include "rewrite/rewriteHandler.h" #include "storage/smgr.h" @@ -43,6 +48,7 @@ typedef struct { DestReceiver pub; /* publicly-known function pointers */ IntoClause *into; /* target relation specification */ + Query *viewParse; /* the query which defines/populates data */ /* These fields are filled by intorel_startup: */ Relation rel; /* relation to write to */ CommandId output_cid; /* cmin to insert in output tuples */ @@ -57,6 +63,62 @@ static void intorel_destroy(DestReceiver *self); /* + * Common setup needed by both normal execution and EXPLAIN ANALYZE. + */ +Query * +SetupForCreateTableAs(Query *query, IntoClause *into, const char *queryString, + ParamListInfo params, DestReceiver *dest) +{ + List *rewritten; + Query *viewParse = NULL; + + Assert(query->commandType == CMD_SELECT); + + if (into->relkind == RELKIND_MATVIEW) + viewParse = (Query *) parse_analyze((Node *) copyObject(query), + queryString, NULL, 0)->utilityStmt; + + /* + * Parse analysis was done already, but we still have to run the rule + * rewriter. We do not do AcquireRewriteLocks: we assume the query either + * came straight from the parser, or suitable locks were acquired by + * plancache.c. + * + * Because the rewriter and planner tend to scribble on the input, we make + * a preliminary copy of the source querytree. This prevents problems in + * the case that CTAS is in a portal or plpgsql function and is executed + * repeatedly. (See also the same hack in EXPLAIN and PREPARE.) + */ + rewritten = QueryRewrite((Query *) copyObject(query)); + + /* SELECT should never rewrite to more or less than one SELECT query */ + if (list_length(rewritten) != 1) + elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT"); + query = (Query *) linitial(rewritten); + + Assert(query->commandType == CMD_SELECT); + + /* Save the query after rewrite but before planning. */ + ((DR_intorel *) dest)->viewParse = viewParse; + ((DR_intorel *) dest)->into = into; + + if (into->relkind == RELKIND_MATVIEW) + { + /* + * A materialized view would either need to save parameters for use in + * maintaining or loading the data or prohibit them entirely. The + * latter seems safer and more sane. + */ + if (params != NULL && params->numParams > 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialized views may not be defined using bound parameters"))); + } + + return query; +} + +/* * ExecCreateTableAs -- execute a CREATE TABLE AS command */ void @@ -66,7 +128,6 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, Query *query = (Query *) stmt->query; IntoClause *into = stmt->into; DestReceiver *dest; - List *rewritten; PlannedStmt *plan; QueryDesc *queryDesc; ScanDirection dir; @@ -90,26 +151,8 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, return; } - Assert(query->commandType == CMD_SELECT); - - /* - * Parse analysis was done already, but we still have to run the rule - * rewriter. We do not do AcquireRewriteLocks: we assume the query either - * came straight from the parser, or suitable locks were acquired by - * plancache.c. - * - * Because the rewriter and planner tend to scribble on the input, we make - * a preliminary copy of the source querytree. This prevents problems in - * the case that CTAS is in a portal or plpgsql function and is executed - * repeatedly. (See also the same hack in EXPLAIN and PREPARE.) - */ - rewritten = QueryRewrite((Query *) copyObject(stmt->query)); - /* SELECT should never rewrite to more or less than one SELECT query */ - if (list_length(rewritten) != 1) - elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT"); - query = (Query *) linitial(rewritten); - Assert(query->commandType == CMD_SELECT); + query = SetupForCreateTableAs(query, into, queryString, params, dest); /* plan the query */ plan = pg_plan_query(query, 0, params); @@ -169,15 +212,21 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, int GetIntoRelEFlags(IntoClause *intoClause) { + int flags; /* * We need to tell the executor whether it has to produce OIDs or not, * because it doesn't have enough information to do so itself (since we * can't build the target relation until after ExecutorStart). */ if (interpretOidsOption(intoClause->options)) - return EXEC_FLAG_WITH_OIDS; + flags = EXEC_FLAG_WITH_OIDS; else - return EXEC_FLAG_WITHOUT_OIDS; + flags = EXEC_FLAG_WITHOUT_OIDS; + + if (intoClause->skipData) + flags |= EXEC_FLAG_WITH_NO_DATA; + + return flags; } /* @@ -299,12 +348,38 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) if (lc != NULL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("CREATE TABLE AS specifies too many column names"))); + errmsg("too many column names are specified"))); + + /* + * Enforce validations needed for materialized views only. + */ + if (into->relkind == RELKIND_MATVIEW) + { + /* + * Prohibit a data-modifying CTE in the query used to create a + * materialized view. It's not sufficiently clear what the user would + * want to happen if the MV is refreshed or incrementally maintained. + */ + if (myState->viewParse->hasModifyingCTE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialized views must not use data-modifying statements in WITH"))); + + /* + * Check whether any temporary database objects are used in the + * creation query. It would be hard to refresh data or incrementally + * maintain it if a source disappeared. + */ + if (isQueryUsingTempRelation(myState->viewParse)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialized views must not use temporary tables or views"))); + } /* * Actually create the target table */ - intoRelationId = DefineRelation(create, RELKIND_RELATION, InvalidOid); + intoRelationId = DefineRelation(create, into->relkind, InvalidOid); /* * If necessary, create a TOAST table for the target table. Note that @@ -324,11 +399,22 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) AlterTableCreateToastTable(intoRelationId, toast_options); + /* Create the "view" part of a materialized view. */ + if (into->relkind == RELKIND_MATVIEW) + { + StoreViewQuery(intoRelationId, myState->viewParse, false); + CommandCounterIncrement(); + } + /* * Finally we can open the target table */ intoRelationDesc = heap_open(intoRelationId, AccessExclusiveLock); + if (into->relkind == RELKIND_MATVIEW && !into->skipData) + /* Make sure the heap looks good even if no rows are written. */ + SetRelationIsScannable(intoRelationDesc); + /* * Check INSERT permission on the constructed table. * @@ -338,7 +424,8 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = intoRelationId; - rte->relkind = RELKIND_RELATION; + rte->relkind = into->relkind; + rte->isResultRel = true; rte->requiredPerms = ACL_INSERT; for (attnum = 1; attnum <= intoRelationDesc->rd_att->natts; attnum++) diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 18b37537c0..596178fbda 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -67,6 +67,7 @@ static event_trigger_support_data event_trigger_support[] = { { "FUNCTION", true }, { "INDEX", true }, { "LANGUAGE", true }, + { "MATERIALIZED VIEW", true }, { "OPERATOR", true }, { "OPERATOR CLASS", true }, { "OPERATOR FAMILY", true }, @@ -217,6 +218,7 @@ check_ddl_tag(const char *tag) */ if (pg_strcasecmp(tag, "CREATE TABLE AS") == 0 || pg_strcasecmp(tag, "SELECT INTO") == 0 || + pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 || pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 || pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0) return EVENT_TRIGGER_COMMAND_TAG_OK; diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index fbad0d027a..989b52da9d 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -47,7 +47,7 @@ explain_get_index_name_hook_type explain_get_index_name_hook = NULL; #define X_NOWHITESPACE 4 static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, - const char *queryString, ParamListInfo params); + const char *queryString, DestReceiver *dest, ParamListInfo params); static void report_triggers(ResultRelInfo *rInfo, bool show_relname, ExplainState *es); static double elapsed_time(instr_time *starttime); @@ -218,7 +218,7 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString, foreach(l, rewritten) { ExplainOneQuery((Query *) lfirst(l), NULL, &es, - queryString, params); + queryString, None_Receiver, params); /* Separate plans with an appropriate separator */ if (lnext(l) != NULL) @@ -299,7 +299,8 @@ ExplainResultDesc(ExplainStmt *stmt) */ static void ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, - const char *queryString, ParamListInfo params) + const char *queryString, DestReceiver *dest, + ParamListInfo params) { /* planner will not cope with utility statements */ if (query->commandType == CMD_UTILITY) @@ -319,7 +320,7 @@ ExplainOneQuery(Query *query, IntoClause *into, ExplainState *es, plan = pg_plan_query(query, 0, params); /* run it (if needed) and produce output */ - ExplainOnePlan(plan, into, es, queryString, params); + ExplainOnePlan(plan, into, es, queryString, dest, params); } } @@ -343,19 +344,23 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, if (IsA(utilityStmt, CreateTableAsStmt)) { + DestReceiver *dest; + /* * We have to rewrite the contained SELECT and then pass it back to * ExplainOneQuery. It's probably not really necessary to copy the * contained parsetree another time, but let's be safe. */ CreateTableAsStmt *ctas = (CreateTableAsStmt *) utilityStmt; - List *rewritten; + Query *query = (Query *) ctas->query; + + dest = CreateIntoRelDestReceiver(into); Assert(IsA(ctas->query, Query)); - rewritten = QueryRewrite((Query *) copyObject(ctas->query)); - Assert(list_length(rewritten) == 1); - ExplainOneQuery((Query *) linitial(rewritten), ctas->into, es, - queryString, params); + + query = SetupForCreateTableAs(query, ctas->into, queryString, params, dest); + + ExplainOneQuery(query, ctas->into, es, queryString, dest, params); } else if (IsA(utilityStmt, ExecuteStmt)) ExplainExecuteQuery((ExecuteStmt *) utilityStmt, into, es, @@ -396,9 +401,8 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, */ void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, - const char *queryString, ParamListInfo params) + const char *queryString, DestReceiver *dest, ParamListInfo params) { - DestReceiver *dest; QueryDesc *queryDesc; instr_time starttime; double totaltime = 0; @@ -422,15 +426,6 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, PushCopiedSnapshot(GetActiveSnapshot()); UpdateActiveSnapshotCommandId(); - /* - * Normally we discard the query's output, but if explaining CREATE TABLE - * AS, we'd better use the appropriate tuple receiver. - */ - if (into) - dest = CreateIntoRelDestReceiver(into); - else - dest = None_Receiver; - /* Create a QueryDesc for the query */ queryDesc = CreateQueryDesc(plannedstmt, queryString, GetActiveSnapshot(), InvalidSnapshot, diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index c3385a113a..f855befd4d 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -355,7 +355,8 @@ DefineIndex(IndexStmt *stmt, relationId = RelationGetRelid(rel); namespaceId = RelationGetNamespace(rel); - if (rel->rd_rel->relkind != RELKIND_RELATION) + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW) { if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) @@ -1835,7 +1836,8 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user) { Form_pg_class classtuple = (Form_pg_class) GETSTRUCT(tuple); - if (classtuple->relkind != RELKIND_RELATION) + if (classtuple->relkind != RELKIND_RELATION && + classtuple->relkind != RELKIND_MATVIEW) continue; /* Skip temp tables of other backends; we can't reindex them at all */ diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c new file mode 100644 index 0000000000..e040bedb7e --- /dev/null +++ b/src/backend/commands/matview.c @@ -0,0 +1,374 @@ +/*------------------------------------------------------------------------- + * + * matview.c + * materialized view support + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/commands/matview.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/multixact.h" +#include "access/relscan.h" +#include "access/xact.h" +#include "catalog/catalog.h" +#include "catalog/heap.h" +#include "catalog/namespace.h" +#include "commands/cluster.h" +#include "commands/matview.h" +#include "commands/tablecmds.h" +#include "executor/executor.h" +#include "miscadmin.h" +#include "rewrite/rewriteHandler.h" +#include "storage/lmgr.h" +#include "storage/smgr.h" +#include "tcop/tcopprot.h" +#include "utils/snapmgr.h" + + +typedef struct +{ + DestReceiver pub; /* publicly-known function pointers */ + Oid transientoid; /* OID of new heap into which to store */ + /* These fields are filled by transientrel_startup: */ + Relation transientrel; /* relation to write to */ + CommandId output_cid; /* cmin to insert in output tuples */ + int hi_options; /* heap_insert performance options */ + BulkInsertState bistate; /* bulk insert state */ +} DR_transientrel; + +static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo); +static void transientrel_receive(TupleTableSlot *slot, DestReceiver *self); +static void transientrel_shutdown(DestReceiver *self); +static void transientrel_destroy(DestReceiver *self); +static void refresh_matview_datafill(DestReceiver *dest, Query *query, + const char *queryString); + +/* + * SetRelationIsScannable + * Make the relation appear scannable. + * + * NOTE: This is only implemented for materialized views. The heap starts out + * in a state that doesn't look scannable, and can only transition from there + * to scannable, unless a new heap is created. + * + * NOTE: caller must be holding an appropriate lock on the relation. + */ +void +SetRelationIsScannable(Relation relation) +{ + Page page; + + Assert(relation->rd_rel->relkind == RELKIND_MATVIEW); + Assert(relation->rd_isscannable == false); + + RelationOpenSmgr(relation); + page = (Page) palloc(BLCKSZ); + PageInit(page, BLCKSZ, 0); + smgrextend(relation->rd_smgr, MAIN_FORKNUM, 0, (char *) page, true); + pfree(page); + + smgrimmedsync(relation->rd_smgr, MAIN_FORKNUM); + + RelationCacheInvalidateEntry(relation->rd_id); +} + +/* + * ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command + * + * This refreshes the materialized view by creating a new table and swapping + * the relfilenodes of the new table and the old materialized view, so the OID + * of the original materialized view is preserved. Thus we do not lose GRANT + * nor references to this materialized view. + * + * If WITH NO DATA was specified, this is effectively like a TRUNCATE; + * otherwise it is like a TRUNCATE followed by an INSERT using the SELECT + * statement associated with the materialized view. The statement node's + * skipData field is used to indicate that the clause was used. + * + * Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading + * the new heap, it's better to create the indexes afterwards than to fill them + * incrementally while we load. + * + * The scannable state is changed based on whether the contents reflect the + * result set of the materialized view's query. + */ +void +ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, + ParamListInfo params, char *completionTag) +{ + Oid matviewOid; + Relation matviewRel; + RewriteRule *rule; + List *actions; + Query *dataQuery; + Oid tableSpace; + Oid OIDNewHeap; + DestReceiver *dest; + + /* + * Get a lock until end of transaction. + */ + matviewOid = RangeVarGetRelidExtended(stmt->relation, + AccessExclusiveLock, false, false, + RangeVarCallbackOwnsTable, NULL); + matviewRel = heap_open(matviewOid, NoLock); + + /* Make sure it is a materialized view. */ + if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(matviewRel)))); + + /* + * We're not using materialized views in the system catalogs. + */ + Assert(!IsSystemRelation(matviewRel)); + + Assert(!matviewRel->rd_rel->relhasoids); + + /* + * Check that everything is correct for a refresh. Problems at this point + * are internal errors, so elog is sufficient. + */ + if (matviewRel->rd_rel->relhasrules == false || + matviewRel->rd_rules->numLocks < 1) + elog(ERROR, + "materialized view \"%s\" is missing rewrite information", + RelationGetRelationName(matviewRel)); + + if (matviewRel->rd_rules->numLocks > 1) + elog(ERROR, + "materialized view \"%s\" has too many rules", + RelationGetRelationName(matviewRel)); + + rule = matviewRel->rd_rules->rules[0]; + if (rule->event != CMD_SELECT || !(rule->isInstead)) + elog(ERROR, + "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule", + RelationGetRelationName(matviewRel)); + + actions = rule->actions; + if (list_length(actions) != 1) + elog(ERROR, + "the rule for materialized view \"%s\" is not a single action", + RelationGetRelationName(matviewRel)); + + /* + * The stored query was rewritten at the time of the MV definition, but + * has not been scribbled on by the planner. + */ + dataQuery = (Query *) linitial(actions); + Assert(IsA(dataQuery, Query)); + + /* + * Check for active uses of the relation in the current transaction, such + * as open scans. + * + * NB: We count on this to protect us against problems with refreshing the + * data using HEAP_INSERT_FROZEN. + */ + CheckTableNotInUse(matviewRel, "REFRESH MATERIALIZED VIEW"); + + tableSpace = matviewRel->rd_rel->reltablespace; + + heap_close(matviewRel, NoLock); + + /* Create the transient table that will receive the regenerated data. */ + OIDNewHeap = make_new_heap(matviewOid, tableSpace); + dest = CreateTransientRelDestReceiver(OIDNewHeap); + + if (!stmt->skipData) + refresh_matview_datafill(dest, dataQuery, queryString); + + /* + * Swap the physical files of the target and transient tables, then + * rebuild the target's indexes and throw away the transient table. + */ + finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, RecentXmin, + ReadNextMultiXactId()); + + RelationCacheInvalidateEntry(matviewOid); +} + +/* + * refresh_matview_datafill + */ +static void +refresh_matview_datafill(DestReceiver *dest, Query *query, + const char *queryString) +{ + List *rewritten; + PlannedStmt *plan; + QueryDesc *queryDesc; + List *rtable; + RangeTblEntry *initial_rte; + RangeTblEntry *second_rte; + + rewritten = QueryRewrite((Query *) copyObject(query)); + + /* SELECT should never rewrite to more or less than one SELECT query */ + if (list_length(rewritten) != 1) + elog(ERROR, "unexpected rewrite result for REFRESH MATERIALIZED VIEW"); + query = (Query *) linitial(rewritten); + + /* Check for user-requested abort. */ + CHECK_FOR_INTERRUPTS(); + + /* + * Kludge here to allow refresh of a materialized view which is invalid + * (that is, it was created or refreshed WITH NO DATA. We flag the first + * two RangeTblEntry list elements, which were added to the front of the + * rewritten Query to keep the rules system happy, with the isResultRel + * flag to indicate that it is OK if they are flagged as invalid. See + * UpdateRangeTableOfViewParse() for details. + * + * NOTE: The rewrite has switched the frist two RTEs, but they are still + * in the first two positions. If that behavior changes, the asserts here + * will fail. + */ + rtable = query->rtable; + initial_rte = ((RangeTblEntry *) linitial(rtable)); + Assert(strcmp(initial_rte->alias->aliasname, "new")); + initial_rte->isResultRel = true; + second_rte = ((RangeTblEntry *) lsecond(rtable)); + Assert(strcmp(second_rte->alias->aliasname, "old")); + second_rte->isResultRel = true; + + /* Plan the query which will generate data for the refresh. */ + plan = pg_plan_query(query, 0, NULL); + + /* + * Use a snapshot with an updated command ID to ensure this query sees + * results of any previously executed queries. (This could only matter if + * the planner executed an allegedly-stable function that changed the + * database contents, but let's do it anyway to be safe.) + */ + PushCopiedSnapshot(GetActiveSnapshot()); + UpdateActiveSnapshotCommandId(); + + /* Create a QueryDesc, redirecting output to our tuple receiver */ + queryDesc = CreateQueryDesc(plan, queryString, + GetActiveSnapshot(), InvalidSnapshot, + dest, NULL, 0); + + /* call ExecutorStart to prepare the plan for execution */ + ExecutorStart(queryDesc, EXEC_FLAG_WITHOUT_OIDS); + + /* run the plan */ + ExecutorRun(queryDesc, ForwardScanDirection, 0L); + + /* and clean up */ + ExecutorFinish(queryDesc); + ExecutorEnd(queryDesc); + + FreeQueryDesc(queryDesc); + + PopActiveSnapshot(); +} + +DestReceiver * +CreateTransientRelDestReceiver(Oid transientoid) +{ + DR_transientrel *self = (DR_transientrel *) palloc0(sizeof(DR_transientrel)); + + self->pub.receiveSlot = transientrel_receive; + self->pub.rStartup = transientrel_startup; + self->pub.rShutdown = transientrel_shutdown; + self->pub.rDestroy = transientrel_destroy; + self->pub.mydest = DestTransientRel; + self->transientoid = transientoid; + + return (DestReceiver *) self; +} + +/* + * transientrel_startup --- executor startup + */ +static void +transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) +{ + DR_transientrel *myState = (DR_transientrel *) self; + Relation transientrel; + + transientrel = heap_open(myState->transientoid, NoLock); + + /* + * Fill private fields of myState for use by later routines + */ + myState->transientrel = transientrel; + myState->output_cid = GetCurrentCommandId(true); + + /* + * We can skip WAL-logging the insertions, unless PITR or streaming + * replication is in use. We can skip the FSM in any case. + */ + myState->hi_options = HEAP_INSERT_SKIP_FSM | HEAP_INSERT_FROZEN; + if (!XLogIsNeeded()) + myState->hi_options |= HEAP_INSERT_SKIP_WAL; + myState->bistate = GetBulkInsertState(); + + SetRelationIsScannable(transientrel); + + /* Not using WAL requires smgr_targblock be initially invalid */ + Assert(RelationGetTargetBlock(transientrel) == InvalidBlockNumber); +} + +/* + * transientrel_receive --- receive one tuple + */ +static void +transientrel_receive(TupleTableSlot *slot, DestReceiver *self) +{ + DR_transientrel *myState = (DR_transientrel *) self; + HeapTuple tuple; + + /* + * get the heap tuple out of the tuple table slot, making sure we have a + * writable copy + */ + tuple = ExecMaterializeSlot(slot); + + heap_insert(myState->transientrel, + tuple, + myState->output_cid, + myState->hi_options, + myState->bistate); + + /* We know this is a newly created relation, so there are no indexes */ +} + +/* + * transientrel_shutdown --- executor end + */ +static void +transientrel_shutdown(DestReceiver *self) +{ + DR_transientrel *myState = (DR_transientrel *) self; + + FreeBulkInsertState(myState->bistate); + + /* If we skipped using WAL, must heap_sync before commit */ + if (myState->hi_options & HEAP_INSERT_SKIP_WAL) + heap_sync(myState->transientrel); + + /* close transientrel, but keep lock until commit */ + heap_close(myState->transientrel, NoLock); + myState->transientrel = NULL; +} + +/* + * transientrel_destroy --- release DestReceiver object + */ +static void +transientrel_destroy(DestReceiver *self) +{ + pfree(self); +} diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index 62208eb995..c79bc020c2 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -665,7 +665,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es, PlannedStmt *pstmt = (PlannedStmt *) lfirst(p); if (IsA(pstmt, PlannedStmt)) - ExplainOnePlan(pstmt, into, es, query_string, paramLI); + ExplainOnePlan(pstmt, into, es, query_string, None_Receiver, paramLI); else ExplainOneUtility((Node *) pstmt, into, es, query_string, paramLI); diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c index c83cda1b10..3b27ac26c8 100644 --- a/src/backend/commands/seclabel.c +++ b/src/backend/commands/seclabel.c @@ -101,11 +101,12 @@ ExecSecLabelStmt(SecLabelStmt *stmt) /* * Allow security labels only on columns of tables, views, - * composite types, and foreign tables (which are the only - * relkinds for which pg_dump will dump labels). + * materialized views, composite types, and foreign tables (which + * are the only relkinds for which pg_dump will dump labels). */ if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index eeddd9a80b..2a55e02577 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -217,6 +217,12 @@ static const struct dropmsgstrings dropmsgstringarray[] = { gettext_noop("view \"%s\" does not exist, skipping"), gettext_noop("\"%s\" is not a view"), gettext_noop("Use DROP VIEW to remove a view.")}, + {RELKIND_MATVIEW, + ERRCODE_UNDEFINED_TABLE, + gettext_noop("materialized view \"%s\" does not exist"), + gettext_noop("materialized view \"%s\" does not exist, skipping"), + gettext_noop("\"%s\" is not a materialized view"), + gettext_noop("Use DROP MATERIALIZED VIEW to remove a materialized view.")}, {RELKIND_INDEX, ERRCODE_UNDEFINED_OBJECT, gettext_noop("index \"%s\" does not exist"), @@ -248,9 +254,10 @@ struct DropRelationCallbackState /* Alter table target-type flags for ATSimplePermissions */ #define ATT_TABLE 0x0001 #define ATT_VIEW 0x0002 -#define ATT_INDEX 0x0004 -#define ATT_COMPOSITE_TYPE 0x0008 -#define ATT_FOREIGN_TABLE 0x0010 +#define ATT_MATVIEW 0x0004 +#define ATT_INDEX 0x0008 +#define ATT_COMPOSITE_TYPE 0x0010 +#define ATT_FOREIGN_TABLE 0x0020 static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, char relpersistence, @@ -399,6 +406,8 @@ static void RangeVarCallbackForDropRelation(const RangeVar *rel, Oid relOid, static void RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, void *arg); +static bool isQueryUsingTempRelation_walker(Node *node, void *context); + /* ---------------------------------------------------------------- * DefineRelation @@ -735,7 +744,7 @@ DropErrorMsgWrongType(const char *relname, char wrongkind, char rightkind) /* * RemoveRelations * Implements DROP TABLE, DROP INDEX, DROP SEQUENCE, DROP VIEW, - * DROP FOREIGN TABLE + * DROP MATERIALIZED VIEW, DROP FOREIGN TABLE */ void RemoveRelations(DropStmt *drop) @@ -787,6 +796,10 @@ RemoveRelations(DropStmt *drop) relkind = RELKIND_VIEW; break; + case OBJECT_MATVIEW: + relkind = RELKIND_MATVIEW; + break; + case OBJECT_FOREIGN_TABLE: relkind = RELKIND_FOREIGN_TABLE; break; @@ -2067,12 +2080,13 @@ renameatt_check(Oid myrelid, Form_pg_class classform, bool recursing) */ if (relkind != RELKIND_RELATION && relkind != RELKIND_VIEW && + relkind != RELKIND_MATVIEW && relkind != RELKIND_COMPOSITE_TYPE && relkind != RELKIND_INDEX && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table, view, composite type, index, or foreign table", + errmsg("\"%s\" is not a table, view, materialized view, composite type, index, or foreign table", NameStr(classform->relname)))); /* @@ -2989,12 +3003,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ - ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX | ATT_FOREIGN_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_FOREIGN_TABLE); /* This command never recurses */ pass = AT_PASS_MISC; break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ - ATSimplePermissions(rel, ATT_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_MISC; @@ -3007,7 +3021,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_DROP; break; case AT_AddIndex: /* ADD INDEX */ - ATSimplePermissions(rel, ATT_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_ADD_INDEX; @@ -3054,7 +3068,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_ClusterOn: /* CLUSTER ON */ case AT_DropCluster: /* SET WITHOUT CLUSTER */ - ATSimplePermissions(rel, ATT_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); /* These commands never recurse */ /* No command-specific prep needed */ pass = AT_PASS_MISC; @@ -3081,7 +3095,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_DROP; break; case AT_SetTableSpace: /* SET TABLESPACE */ - ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX); + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX); /* This command never recurses */ ATPrepSetTableSpace(tab, rel, cmd->name, lockmode); pass = AT_PASS_MISC; /* doesn't actually matter */ @@ -3089,7 +3103,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, case AT_SetRelOptions: /* SET (...) */ case AT_ResetRelOptions: /* RESET (...) */ case AT_ReplaceRelOptions: /* reset them all, then set just these */ - ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX | ATT_VIEW); + ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX); /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_MISC; @@ -3202,7 +3216,8 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode) { AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab); - if (tab->relkind == RELKIND_RELATION) + if (tab->relkind == RELKIND_RELATION || + tab->relkind == RELKIND_MATVIEW) AlterTableCreateToastTable(tab->relid, (Datum) 0); } } @@ -3937,6 +3952,9 @@ ATSimplePermissions(Relation rel, int allowed_targets) case RELKIND_VIEW: actual_target = ATT_VIEW; break; + case RELKIND_MATVIEW: + actual_target = ATT_MATVIEW; + break; case RELKIND_INDEX: actual_target = ATT_INDEX; break; @@ -3983,18 +4001,27 @@ ATWrongRelkindError(Relation rel, int allowed_targets) case ATT_TABLE: msg = _("\"%s\" is not a table"); break; - case ATT_TABLE | ATT_INDEX: - msg = _("\"%s\" is not a table or index"); - break; case ATT_TABLE | ATT_VIEW: msg = _("\"%s\" is not a table or view"); break; + case ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX: + msg = _("\"%s\" is not a table, view, materialized view, or index"); + break; + case ATT_TABLE | ATT_MATVIEW: + msg = _("\"%s\" is not a table or materialized view"); + break; + case ATT_TABLE | ATT_MATVIEW | ATT_INDEX: + msg = _("\"%s\" is not a table, materialized view, or index"); + break; case ATT_TABLE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table or foreign table"); break; case ATT_TABLE | ATT_COMPOSITE_TYPE | ATT_FOREIGN_TABLE: msg = _("\"%s\" is not a table, composite type, or foreign table"); break; + case ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_FOREIGN_TABLE: + msg = _("\"%s\" is not a table, materialized view, composite type, or foreign table"); + break; case ATT_VIEW: msg = _("\"%s\" is not a view"); break; @@ -4147,7 +4174,8 @@ find_composite_type_dependencies(Oid typeOid, Relation origRelation, rel = relation_open(pg_depend->objid, AccessShareLock); att = rel->rd_att->attrs[pg_depend->objsubid - 1]; - if (rel->rd_rel->relkind == RELKIND_RELATION) + if (rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_MATVIEW) { if (origTypeName) ereport(ERROR, @@ -4975,11 +5003,12 @@ ATPrepSetStatistics(Relation rel, const char *colName, Node *newValue, LOCKMODE * allowSystemTableMods to be turned on. */ if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW && rel->rd_rel->relkind != RELKIND_INDEX && rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table, index, or foreign table", + errmsg("\"%s\" is not a table, materialized view, index, or foreign table", RelationGetRelationName(rel)))); /* Permissions checks */ @@ -8087,6 +8116,7 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock { case RELKIND_RELATION: case RELKIND_VIEW: + case RELKIND_MATVIEW: case RELKIND_FOREIGN_TABLE: /* ok to change owner */ break; @@ -8243,11 +8273,12 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock tuple_class->relkind == RELKIND_COMPOSITE_TYPE); /* - * If we are operating on a table, also change the ownership of any - * indexes and sequences that belong to the table, as well as the - * table's toast table (if it has one) + * If we are operating on a table or materialized view, also change + * the ownership of any indexes and sequences that belong to the + * relation, as well as its toast table (if it has one). */ if (tuple_class->relkind == RELKIND_RELATION || + tuple_class->relkind == RELKIND_MATVIEW || tuple_class->relkind == RELKIND_TOASTVALUE) { List *index_oid_list; @@ -8263,7 +8294,8 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock list_free(index_oid_list); } - if (tuple_class->relkind == RELKIND_RELATION) + if (tuple_class->relkind == RELKIND_RELATION || + tuple_class->relkind == RELKIND_MATVIEW) { /* If it has a toast table, recurse to change its ownership */ if (tuple_class->reltoastrelid != InvalidOid) @@ -8533,6 +8565,7 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, case RELKIND_RELATION: case RELKIND_TOASTVALUE: case RELKIND_VIEW: + case RELKIND_MATVIEW: (void) heap_reloptions(rel->rd_rel->relkind, newOptions, true); break; case RELKIND_INDEX: @@ -8541,7 +8574,7 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, default: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table, index, or TOAST table", + errmsg("\"%s\" is not a table, view, materialized view, index, or TOAST table", RelationGetRelationName(rel)))); break; } @@ -9824,8 +9857,9 @@ AlterTableNamespace(AlterObjectSchemaStmt *stmt) } /* - * The guts of relocating a table to another namespace: besides moving - * the table itself, its dependent objects are relocated to the new schema. + * The guts of relocating a table or materialized view to another namespace: + * besides moving the relation itself, its dependent objects are relocated to + * the new schema. */ void AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, @@ -9846,7 +9880,8 @@ AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, nspOid, false, false, objsMoved); /* Fix other dependent stuff */ - if (rel->rd_rel->relkind == RELKIND_RELATION) + if (rel->rd_rel->relkind == RELKIND_RELATION || + rel->rd_rel->relkind == RELKIND_MATVIEW) { AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved); AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, @@ -10257,10 +10292,11 @@ AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid, /* * This is intended as a callback for RangeVarGetRelidExtended(). It allows - * the table to be locked only if (1) it's a plain table or TOAST table and - * (2) the current user is the owner (or the superuser). This meets the - * permission-checking needs of both CLUSTER and REINDEX TABLE; we expose it - * here so that it can be used by both. + * the relation to be locked only if (1) it's a plain table, materialized + * view, or TOAST table and (2) the current user is the owner (or the + * superuser). This meets the permission-checking needs of CLUSTER, REINDEX + * TABLE, and REFRESH MATERIALIZED VIEW; we expose it here so that it can be + * used by all. */ void RangeVarCallbackOwnsTable(const RangeVar *relation, @@ -10280,10 +10316,11 @@ RangeVarCallbackOwnsTable(const RangeVar *relation, relkind = get_rel_relkind(relId); if (!relkind) return; - if (relkind != RELKIND_RELATION && relkind != RELKIND_TOASTVALUE) + if (relkind != RELKIND_RELATION && relkind != RELKIND_TOASTVALUE && + relkind != RELKIND_MATVIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table", relation->relname))); + errmsg("\"%s\" is not a table or materialized view", relation->relname))); /* Check permissions */ if (!pg_class_ownercheck(relId, GetUserId())) @@ -10365,6 +10402,11 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a view", rv->relname))); + if (reltype == OBJECT_MATVIEW && relkind != RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", rv->relname))); + if (reltype == OBJECT_FOREIGN_TABLE && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -10401,9 +10443,9 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, * Don't allow ALTER TABLE .. SET SCHEMA on relations that can't be moved * to a different schema, such as indexes and TOAST tables. */ - if (IsA(stmt, AlterObjectSchemaStmt) &&relkind != RELKIND_RELATION - && relkind != RELKIND_VIEW && relkind != RELKIND_SEQUENCE - && relkind != RELKIND_FOREIGN_TABLE) + if (IsA(stmt, AlterObjectSchemaStmt) && relkind != RELKIND_RELATION + && relkind != RELKIND_VIEW && relkind != RELKIND_MATVIEW + && relkind != RELKIND_SEQUENCE && relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table, view, sequence, or foreign table", @@ -10411,3 +10453,51 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid, ReleaseSysCache(tuple); } + +/* + * Returns true iff any relation underlying this query is a temporary database + * object (table, view, or materialized view). + * + */ +bool +isQueryUsingTempRelation(Query *query) +{ + return isQueryUsingTempRelation_walker((Node *) query, NULL); +} + +static bool +isQueryUsingTempRelation_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + + if (IsA(node, Query)) + { + Query *query = (Query *) node; + ListCell *rtable; + + foreach(rtable, query->rtable) + { + RangeTblEntry *rte = lfirst(rtable); + + if (rte->rtekind == RTE_RELATION) + { + Relation rel = heap_open(rte->relid, AccessShareLock); + char relpersistence = rel->rd_rel->relpersistence; + + heap_close(rel, AccessShareLock); + if (relpersistence == RELPERSISTENCE_TEMP) + return true; + } + } + + return query_tree_walker(query, + isQueryUsingTempRelation_walker, + context, + QTW_IGNORE_JOINALIASES); + } + + return expression_tree_walker(node, + isQueryUsingTempRelation_walker, + context); +} diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 0e55263d4e..1ba6d5e6e9 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -2803,7 +2803,8 @@ get_rels_with_domain(Oid domainOid, LOCKMODE lockmode) format_type_be(domainOid)); /* Otherwise we can ignore views, composite types, etc */ - if (rel->rd_rel->relkind != RELKIND_RELATION) + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW) { relation_close(rel, lockmode); continue; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 4800b43764..c984488e03 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -341,23 +341,26 @@ get_rel_oids(Oid relid, const RangeVar *vacrel) } else { - /* Process all plain relations listed in pg_class */ + /* + * Process all plain relations and materialized views listed in + * pg_class + */ Relation pgclass; HeapScanDesc scan; HeapTuple tuple; - ScanKeyData key; - - ScanKeyInit(&key, - Anum_pg_class_relkind, - BTEqualStrategyNumber, F_CHAREQ, - CharGetDatum(RELKIND_RELATION)); pgclass = heap_open(RelationRelationId, AccessShareLock); - scan = heap_beginscan(pgclass, SnapshotNow, 1, &key); + scan = heap_beginscan(pgclass, SnapshotNow, 0, NULL); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { + Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); + + if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW) + continue; + /* Make a relation list entry for this guy */ oldcontext = MemoryContextSwitchTo(vac_context); oid_list = lappend_oid(oid_list, HeapTupleGetOid(tuple)); @@ -743,6 +746,7 @@ vac_update_datfrozenxid(void) * InvalidTransactionId in relfrozenxid anyway.) */ if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW && classForm->relkind != RELKIND_TOASTVALUE) continue; @@ -1045,6 +1049,7 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound) * relation. */ if (onerel->rd_rel->relkind != RELKIND_RELATION && + onerel->rd_rel->relkind != RELKIND_MATVIEW && onerel->rd_rel->relkind != RELKIND_TOASTVALUE) { ereport(WARNING, diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 4d10f80ec4..aba6944bdf 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -36,57 +36,6 @@ static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc); -static bool isViewOnTempTable_walker(Node *node, void *context); - -/*--------------------------------------------------------------------- - * isViewOnTempTable - * - * Returns true iff any of the relations underlying this view are - * temporary tables. - *--------------------------------------------------------------------- - */ -static bool -isViewOnTempTable(Query *viewParse) -{ - return isViewOnTempTable_walker((Node *) viewParse, NULL); -} - -static bool -isViewOnTempTable_walker(Node *node, void *context) -{ - if (node == NULL) - return false; - - if (IsA(node, Query)) - { - Query *query = (Query *) node; - ListCell *rtable; - - foreach(rtable, query->rtable) - { - RangeTblEntry *rte = lfirst(rtable); - - if (rte->rtekind == RTE_RELATION) - { - Relation rel = heap_open(rte->relid, AccessShareLock); - char relpersistence = rel->rd_rel->relpersistence; - - heap_close(rel, AccessShareLock); - if (relpersistence == RELPERSISTENCE_TEMP) - return true; - } - } - - return query_tree_walker(query, - isViewOnTempTable_walker, - context, - QTW_IGNORE_JOINALIASES); - } - - return expression_tree_walker(node, - isViewOnTempTable_walker, - context); -} /*--------------------------------------------------------------------- * DefineVirtualRelation @@ -506,7 +455,7 @@ DefineView(ViewStmt *stmt, const char *queryString) */ view = copyObject(stmt->view); /* don't corrupt original command */ if (view->relpersistence == RELPERSISTENCE_PERMANENT - && isViewOnTempTable(viewParse)) + && isQueryUsingTempRelation(viewParse)) { view->relpersistence = RELPERSISTENCE_TEMP; ereport(NOTICE, @@ -530,6 +479,17 @@ DefineView(ViewStmt *stmt, const char *queryString) */ CommandCounterIncrement(); + StoreViewQuery(viewOid, viewParse, stmt->replace); + + return viewOid; +} + +/* + * Use the rules system to store the query for the view. + */ +void +StoreViewQuery(Oid viewOid, Query *viewParse, bool replace) +{ /* * The range table of 'viewParse' does not contain entries for the "OLD" * and "NEW" relations. So... add them! @@ -539,7 +499,5 @@ DefineView(ViewStmt *stmt, const char *queryString) /* * Now create the rules associated with the view. */ - DefineViewRules(viewOid, viewParse, stmt->replace); - - return viewOid; + DefineViewRules(viewOid, viewParse, replace); } diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 632644f1d8..288b29e44a 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -84,6 +84,7 @@ static char *ExecBuildSlotValueDescription(TupleTableSlot *slot, int maxfieldlen); static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree); +static bool RelationIdIsScannable(Oid relid); /* end of local decls */ @@ -493,6 +494,65 @@ ExecutorRewind(QueryDesc *queryDesc) /* + * ExecCheckRelationsScannable + * Check that relations which are to be accessed are in a scannable + * state. + * + * If not, throw error. For a materialized view, suggest refresh. + */ +static void +ExecCheckRelationsScannable(List *rangeTable) +{ + ListCell *l; + + foreach(l, rangeTable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); + + if (rte->rtekind != RTE_RELATION) + continue; + + if (!RelationIdIsScannable(rte->relid)) + { + if (rte->relkind == RELKIND_MATVIEW) + { + /* It is OK to replace the contents of an invalid matview. */ + if (rte->isResultRel) + continue; + + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("materialized view \"%s\" has not been populated", + get_rel_name(rte->relid)), + errhint("Use the REFRESH MATERIALIZED VIEW command."))); + } + else + /* This should never happen, so elog will do. */ + elog(ERROR, "relation \"%s\" is not flagged as scannable", + get_rel_name(rte->relid)); + } + } +} + +/* + * Tells whether a relation is scannable. + * + * Currently only non-populated materialzed views are not. + */ +static bool +RelationIdIsScannable(Oid relid) +{ + Relation relation; + bool result; + + relation = RelationIdGetRelation(relid); + result = relation->rd_isscannable; + RelationClose(relation); + + return result; +} + +/* * ExecCheckRTPerms * Check access permissions for all relations listed in a range table. * @@ -883,6 +943,13 @@ InitPlan(QueryDesc *queryDesc, int eflags) planstate = ExecInitNode(plan, estate, eflags); /* + * Unless we are creating a view or are creating a materialized view WITH + * NO DATA, ensure that all referenced relations are scannable. + */ + if ((eflags & EXEC_FLAG_WITH_NO_DATA) == 0) + ExecCheckRelationsScannable(rangeTable); + + /* * Get the tuple descriptor describing the type of tuples to return. */ tupType = ExecGetResultType(planstate); @@ -995,6 +1062,12 @@ CheckValidResultRel(Relation resultRel, CmdType operation) break; } break; + case RELKIND_MATVIEW: + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot change materialized view \"%s\"", + RelationGetRelationName(resultRel)))); + break; case RELKIND_FOREIGN_TABLE: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -1045,6 +1118,13 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType) errmsg("cannot lock rows in view \"%s\"", RelationGetRelationName(rel)))); break; + case RELKIND_MATVIEW: + /* Should not get here */ + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot lock rows in materialized view \"%s\"", + RelationGetRelationName(rel)))); + break; case RELKIND_FOREIGN_TABLE: /* Perhaps we can support this someday, but not today */ ereport(ERROR, diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index de8d59a8cd..cc7764dba2 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -2122,6 +2122,13 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, if (((CreateTableAsStmt *) stmt)->is_select_into) res = SPI_OK_SELINTO; } + else if (IsA(stmt, RefreshMatViewStmt)) + { + Assert(strncmp(completionTag, + "REFRESH MATERIALIZED VIEW ", 23) == 0); + _SPI_current->processed = strtoul(completionTag + 23, + NULL, 10); + } else if (IsA(stmt, CopyStmt)) { Assert(strncmp(completionTag, "COPY ", 5) == 0); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 23ec88d54c..867b0c09d9 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1032,6 +1032,7 @@ _copyIntoClause(const IntoClause *from) COPY_SCALAR_FIELD(onCommit); COPY_STRING_FIELD(tableSpaceName); COPY_SCALAR_FIELD(skipData); + COPY_SCALAR_FIELD(relkind); return newnode; } @@ -1970,6 +1971,7 @@ _copyRangeTblEntry(const RangeTblEntry *from) COPY_SCALAR_FIELD(rtekind); COPY_SCALAR_FIELD(relid); COPY_SCALAR_FIELD(relkind); + COPY_SCALAR_FIELD(isResultRel); COPY_NODE_FIELD(subquery); COPY_SCALAR_FIELD(security_barrier); COPY_SCALAR_FIELD(jointype); @@ -3228,11 +3230,23 @@ _copyCreateTableAsStmt(const CreateTableAsStmt *from) COPY_NODE_FIELD(query); COPY_NODE_FIELD(into); + COPY_SCALAR_FIELD(relkind); COPY_SCALAR_FIELD(is_select_into); return newnode; } +static RefreshMatViewStmt * +_copyRefreshMatViewStmt(const RefreshMatViewStmt *from) +{ + RefreshMatViewStmt *newnode = makeNode(RefreshMatViewStmt); + + COPY_SCALAR_FIELD(skipData); + COPY_NODE_FIELD(relation); + + return newnode; +} + static CreateSeqStmt * _copyCreateSeqStmt(const CreateSeqStmt *from) { @@ -4303,6 +4317,9 @@ copyObject(const void *from) case T_CreateTableAsStmt: retval = _copyCreateTableAsStmt(from); break; + case T_RefreshMatViewStmt: + retval = _copyRefreshMatViewStmt(from); + break; case T_CreateSeqStmt: retval = _copyCreateSeqStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 99c034ab68..085cd5bee1 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -124,6 +124,7 @@ _equalIntoClause(const IntoClause *a, const IntoClause *b) COMPARE_SCALAR_FIELD(onCommit); COMPARE_STRING_FIELD(tableSpaceName); COMPARE_SCALAR_FIELD(skipData); + COMPARE_SCALAR_FIELD(relkind); return true; } @@ -1525,12 +1526,22 @@ _equalCreateTableAsStmt(const CreateTableAsStmt *a, const CreateTableAsStmt *b) { COMPARE_NODE_FIELD(query); COMPARE_NODE_FIELD(into); + COMPARE_SCALAR_FIELD(relkind); COMPARE_SCALAR_FIELD(is_select_into); return true; } static bool +_equalRefreshMatViewStmt(const RefreshMatViewStmt *a, const RefreshMatViewStmt *b) +{ + COMPARE_SCALAR_FIELD(skipData); + COMPARE_NODE_FIELD(relation); + + return true; +} + +static bool _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) { COMPARE_NODE_FIELD(sequence); @@ -2223,6 +2234,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) COMPARE_SCALAR_FIELD(rtekind); COMPARE_SCALAR_FIELD(relid); COMPARE_SCALAR_FIELD(relkind); + COMPARE_SCALAR_FIELD(isResultRel); COMPARE_NODE_FIELD(subquery); COMPARE_SCALAR_FIELD(security_barrier); COMPARE_SCALAR_FIELD(jointype); @@ -2790,6 +2802,9 @@ equal(const void *a, const void *b) case T_CreateTableAsStmt: retval = _equalCreateTableAsStmt(a, b); break; + case T_RefreshMatViewStmt: + retval = _equalRefreshMatViewStmt(a, b); + break; case T_CreateSeqStmt: retval = _equalCreateSeqStmt(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index ffd123d506..be4e548281 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -893,6 +893,7 @@ _outIntoClause(StringInfo str, const IntoClause *node) WRITE_ENUM_FIELD(onCommit, OnCommitAction); WRITE_STRING_FIELD(tableSpaceName); WRITE_BOOL_FIELD(skipData); + WRITE_CHAR_FIELD(relkind); } static void @@ -2351,6 +2352,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) case RTE_RELATION: WRITE_OID_FIELD(relid); WRITE_CHAR_FIELD(relkind); + WRITE_BOOL_FIELD(isResultRel); break; case RTE_SUBQUERY: WRITE_NODE_FIELD(subquery); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 472c82361a..cee67f2eb9 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -395,6 +395,7 @@ _readIntoClause(void) READ_ENUM_FIELD(onCommit, OnCommitAction); READ_STRING_FIELD(tableSpaceName); READ_BOOL_FIELD(skipData); + READ_CHAR_FIELD(relkind); READ_DONE(); } @@ -1190,6 +1191,7 @@ _readRangeTblEntry(void) case RTE_RELATION: READ_OID_FIELD(relid); READ_CHAR_FIELD(relkind); + READ_BOOL_FIELD(isResultRel); break; case RTE_SUBQUERY: READ_NODE_FIELD(subquery); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 5b97cb5a24..db3d5c5018 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -3386,7 +3386,7 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid) rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = tableOid; - rte->relkind = RELKIND_RELATION; + rte->relkind = RELKIND_RELATION; /* Don't be too picky. */ rte->lateral = false; rte->inh = false; rte->inFromCl = true; diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 01f988f7c3..bff7aff593 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -409,6 +409,7 @@ estimate_rel_size(Relation rel, int32 *attr_widths, { case RELKIND_RELATION: case RELKIND_INDEX: + case RELKIND_MATVIEW: case RELKIND_TOASTVALUE: /* it has storage, ok to call the smgr */ curpages = RelationGetNumberOfBlocks(rel); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 240faca72a..d34fca5466 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -190,6 +190,7 @@ transformTopLevelStmt(ParseState *pstate, Node *parseTree) ctas->query = parseTree; ctas->into = stmt->intoClause; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = true; /* @@ -324,6 +325,11 @@ analyze_requires_snapshot(Node *parseTree) result = true; break; + case T_RefreshMatViewStmt: + /* yes, because the SELECT from pg_rewrite must be analyzed */ + result = true; + break; + default: /* other utility statements don't have any real parse analysis */ result = false; @@ -2117,7 +2123,8 @@ transformExplainStmt(ParseState *pstate, ExplainStmt *stmt) /* * transformCreateTableAsStmt - - * transform a CREATE TABLE AS (or SELECT ... INTO) Statement + * transform a CREATE TABLE AS, SELECT ... INTO, or CREATE MATERIALIZED VIEW + * Statement * * As with EXPLAIN, transform the contained statement now. */ @@ -2126,6 +2133,24 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt) { Query *result; + /* + * Set relkind in IntoClause based on statement relkind. These are + * different types, because the parser users the ObjectType enumeration + * and the executor uses RELKIND_* defines. + */ + switch (stmt->relkind) + { + case (OBJECT_TABLE): + stmt->into->relkind = RELKIND_RELATION; + break; + case (OBJECT_MATVIEW): + stmt->into->relkind = RELKIND_MATVIEW; + break; + default: + elog(ERROR, "unrecognized object relkind: %d", + (int) stmt->relkind); + } + /* transform contained query */ stmt->query = (Node *) transformStmt(pstate, stmt->query); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index d3009b67b4..0787d2f506 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -121,6 +121,13 @@ typedef struct PrivTarget #define CAS_NOT_VALID 0x10 #define CAS_NO_INHERIT 0x20 +/* + * In the IntoClause structure there is a char value which will eventually be + * set to RELKIND_RELATION or RELKIND_MATVIEW based on the relkind field in + * the statement-level structure, which is an ObjectType. Define the default + * here, which should always be overridden later. + */ +#define INTO_CLAUSE_RELKIND_DEFAULT '\0' #define parser_yyerror(msg) scanner_yyerror(msg, yyscanner) #define parser_errposition(pos) scanner_errposition(pos, yyscanner) @@ -248,6 +255,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DeallocateStmt PrepareStmt ExecuteStmt DropOwnedStmt ReassignOwnedStmt AlterTSConfigurationStmt AlterTSDictionaryStmt + CreateMatViewStmt RefreshMatViewStmt %type <node> select_no_parens select_with_parens select_clause simple_select values_clause @@ -351,7 +359,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <defelt> fdw_option %type <range> OptTempTableName -%type <into> into_clause create_as_target +%type <into> into_clause create_as_target create_mv_target %type <defelt> createfunc_opt_item common_func_opt_item dostmt_opt_item %type <fun_param> func_arg func_arg_with_default table_func_column @@ -360,6 +368,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <boolean> opt_trusted opt_restart_seqs %type <ival> OptTemp +%type <ival> OptNoLog %type <oncommit> OnCommitOption %type <ival> for_locking_strength @@ -557,7 +566,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P - MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE + MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF @@ -572,7 +581,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); QUOTE - RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX + RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE @@ -745,6 +754,7 @@ stmt : | CreateForeignTableStmt | CreateFunctionStmt | CreateGroupStmt + | CreateMatViewStmt | CreateOpClassStmt | CreateOpFamilyStmt | AlterOpFamilyStmt @@ -790,6 +800,7 @@ stmt : | IndexStmt | InsertStmt | ListenStmt + | RefreshMatViewStmt | LoadStmt | LockStmt | NotifyStmt @@ -1704,9 +1715,9 @@ DiscardStmt: /***************************************************************************** * - * ALTER [ TABLE | INDEX | SEQUENCE | VIEW ] variations + * ALTER [ TABLE | INDEX | SEQUENCE | VIEW | MATERIALIZED VIEW ] variations * - * Note: we accept all subcommands for each of the four variants, and sort + * Note: we accept all subcommands for each of the five variants, and sort * out what's really legal at execution time. *****************************************************************************/ @@ -1783,6 +1794,24 @@ AlterTableStmt: n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $4; + n->cmds = $5; + n->relkind = OBJECT_MATVIEW; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $6; + n->cmds = $7; + n->relkind = OBJECT_MATVIEW; + n->missing_ok = true; + $$ = (Node *)n; + } ; alter_table_cmds: @@ -3186,6 +3215,7 @@ CreateAsStmt: CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt); ctas->query = $6; ctas->into = $4; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = false; /* cram additional flags into the IntoClause */ $4->rel->relpersistence = $2; @@ -3204,6 +3234,7 @@ create_as_target: $$->onCommit = $4; $$->tableSpaceName = $5; $$->skipData = false; /* might get changed later */ + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; } ; @@ -3217,6 +3248,65 @@ opt_with_data: /***************************************************************************** * * QUERY : + * CREATE MATERIALIZED VIEW relname AS SelectStmt + * + *****************************************************************************/ + +CreateMatViewStmt: + CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt); + ctas->query = $7; + ctas->into = $5; + ctas->relkind = OBJECT_MATVIEW; + ctas->is_select_into = false; + /* cram additional flags into the IntoClause */ + $5->rel->relpersistence = $2; + $5->skipData = !($8); + $$ = (Node *) ctas; + } + ; + +create_mv_target: + qualified_name opt_column_list opt_reloptions OptTableSpace + { + $$ = makeNode(IntoClause); + $$->rel = $1; + $$->colNames = $2; + $$->options = $3; + $$->onCommit = ONCOMMIT_NOOP; + $$->tableSpaceName = $4; + $$->skipData = false; /* might get changed later */ + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; + } + ; + +OptNoLog: UNLOGGED { $$ = RELPERSISTENCE_UNLOGGED; } + | /*EMPTY*/ { $$ = RELPERSISTENCE_PERMANENT; } + ; + + +/***************************************************************************** + * + * QUERY : + * REFRESH MATERIALIZED VIEW qualified_name + * + *****************************************************************************/ + +RefreshMatViewStmt: + REFRESH MATERIALIZED VIEW qualified_name opt_with_data + { + RefreshMatViewStmt *n = makeNode(RefreshMatViewStmt); + n->relation = $4; + n->skipData = !($5); + $$ = (Node *) n; + } + ; + + +/***************************************************************************** + * + * QUERY : * CREATE SEQUENCE seqname * ALTER SEQUENCE seqname * @@ -3731,6 +3821,15 @@ AlterExtensionContentsStmt: n->objname = $6; $$ = (Node *)n; } + | ALTER EXTENSION name add_drop MATERIALIZED VIEW any_name + { + AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); + n->extname = $3; + n->action = $4; + n->objtype = OBJECT_MATVIEW; + n->objname = $7; + $$ = (Node *)n; + } | ALTER EXTENSION name add_drop FOREIGN TABLE any_name { AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); @@ -5057,6 +5156,7 @@ DropStmt: DROP drop_type IF_P EXISTS any_name_list opt_drop_behavior drop_type: TABLE { $$ = OBJECT_TABLE; } | SEQUENCE { $$ = OBJECT_SEQUENCE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | INDEX { $$ = OBJECT_INDEX; } | FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; } | EVENT TRIGGER { $$ = OBJECT_EVENT_TRIGGER; } @@ -5123,7 +5223,8 @@ opt_restart_seqs: * EXTENSION | ROLE | TEXT SEARCH PARSER | * TEXT SEARCH DICTIONARY | TEXT SEARCH TEMPLATE | * TEXT SEARCH CONFIGURATION | FOREIGN TABLE | - * FOREIGN DATA WRAPPER | SERVER | EVENT TRIGGER ] <objname> | + * FOREIGN DATA WRAPPER | SERVER | EVENT TRIGGER | + * MATERIALIZED VIEW] <objname> | * AGGREGATE <aggname> (arg1, ...) | * FUNCTION <funcname> (arg1, arg2, ...) | * OPERATOR <op> (leftoperand_typ, rightoperand_typ) | @@ -5297,6 +5398,7 @@ comment_type: | DOMAIN_P { $$ = OBJECT_DOMAIN; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | TABLESPACE { $$ = OBJECT_TABLESPACE; } @@ -5398,6 +5500,7 @@ security_label_type: | TABLESPACE { $$ = OBJECT_TABLESPACE; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } + | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } ; security_label: Sconst { $$ = $1; } @@ -6940,6 +7043,26 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_MATVIEW; + n->relation = $4; + n->subname = NULL; + n->newname = $7; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_MATVIEW; + n->relation = $6; + n->subname = NULL; + n->newname = $9; + n->missing_ok = true; + $$ = (Node *)n; + } | ALTER INDEX qualified_name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); @@ -7002,6 +7125,28 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name RENAME opt_column name TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_COLUMN; + n->relationType = OBJECT_MATVIEW; + n->relation = $4; + n->subname = $7; + n->newname = $9; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name RENAME opt_column name TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_COLUMN; + n->relationType = OBJECT_MATVIEW; + n->relation = $6; + n->subname = $9; + n->newname = $11; + n->missing_ok = true; + $$ = (Node *)n; + } | ALTER TABLE relation_expr RENAME CONSTRAINT name TO name { RenameStmt *n = makeNode(RenameStmt); @@ -7357,6 +7502,24 @@ AlterObjectSchemaStmt: n->missing_ok = true; $$ = (Node *)n; } + | ALTER MATERIALIZED VIEW qualified_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_MATVIEW; + n->relation = $4; + n->newschema = $7; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER MATERIALIZED VIEW IF_P EXISTS qualified_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_MATVIEW; + n->relation = $6; + n->newschema = $9; + n->missing_ok = true; + $$ = (Node *)n; + } | ALTER FOREIGN TABLE relation_expr SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); @@ -8535,6 +8698,8 @@ ExplainableStmt: | DeleteStmt | DeclareCursorStmt | CreateAsStmt + | CreateMatViewStmt + | RefreshMatViewStmt | ExecuteStmt /* by default all are $$=$1 */ ; @@ -8619,6 +8784,7 @@ ExecuteStmt: EXECUTE name execute_param_clause n->params = $8; ctas->query = (Node *) n; ctas->into = $4; + ctas->relkind = OBJECT_TABLE; ctas->is_select_into = false; /* cram additional flags into the IntoClause */ $4->rel->relpersistence = $2; @@ -9166,6 +9332,7 @@ into_clause: $$->onCommit = ONCOMMIT_NOOP; $$->tableSpaceName = NULL; $$->skipData = false; + $$->relkind = INTO_CLAUSE_RELKIND_DEFAULT; } | /*EMPTY*/ { $$ = NULL; } @@ -12652,6 +12819,7 @@ unreserved_keyword: | LOCK_P | MAPPING | MATCH + | MATERIALIZED | MAXVALUE | MINUTE_P | MINVALUE @@ -12697,6 +12865,7 @@ unreserved_keyword: | RECHECK | RECURSIVE | REF + | REFRESH | REINDEX | RELATIVE_P | RELEASE diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 10a3be59c0..8a1876c8a3 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -646,6 +646,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla if (relation->rd_rel->relkind != RELKIND_RELATION && relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_MATVIEW && relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, @@ -1999,6 +2000,11 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString, */ rel = heap_openrv(stmt->relation, AccessExclusiveLock); + if (rel->rd_rel->relkind == RELKIND_MATVIEW) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rules on materialized views are not supported"))); + /* Set up pstate */ pstate = make_parsestate(NULL); pstate->p_sourcetext = queryString; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 7ab0801887..00cb3f760d 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -1990,22 +1990,17 @@ do_autovacuum(void) * Scan pg_class to determine which tables to vacuum. * * We do this in two passes: on the first one we collect the list of plain - * relations, and on the second one we collect TOAST tables. The reason - * for doing the second pass is that during it we want to use the main - * relation's pg_class.reloptions entry if the TOAST table does not have - * any, and we cannot obtain it unless we know beforehand what's the main - * table OID. + * relations and materialized views, and on the second one we collect + * TOAST tables. The reason for doing the second pass is that during it we + * want to use the main relation's pg_class.reloptions entry if the TOAST + * table does not have any, and we cannot obtain it unless we know + * beforehand what's the main table OID. * * We need to check TOAST tables separately because in cases with short, * wide tables there might be proportionally much more activity in the * TOAST table than in its parent. */ - ScanKeyInit(&key, - Anum_pg_class_relkind, - BTEqualStrategyNumber, F_CHAREQ, - CharGetDatum(RELKIND_RELATION)); - - relScan = heap_beginscan(classRel, SnapshotNow, 1, &key); + relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL); /* * On the first pass, we collect main tables to vacuum, and also the main @@ -2021,6 +2016,10 @@ do_autovacuum(void) bool doanalyze; bool wraparound; + if (classForm->relkind != RELKIND_RELATION && + classForm->relkind != RELKIND_MATVIEW) + continue; + relid = HeapTupleGetOid(tuple); /* Fetch reloptions and the pgstat entry for this table */ @@ -2406,6 +2405,7 @@ extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc) AutoVacOpts *av; Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION || + ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW || ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE); relopts = extractRelOptions(tup, pg_class_desc, InvalidOid); diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index bdd67dd97f..12ca295422 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -1599,6 +1599,7 @@ pgstat_initstats(Relation rel) /* We only count stats for things that have storage */ if (!(relkind == RELKIND_RELATION || + relkind == RELKIND_MATVIEW || relkind == RELKIND_INDEX || relkind == RELKIND_TOASTVALUE || relkind == RELKIND_SEQUENCE)) diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c index a1a9808e5d..8963266157 100644 --- a/src/backend/rewrite/rewriteDefine.c +++ b/src/backend/rewrite/rewriteDefine.c @@ -260,6 +260,7 @@ DefineQueryRewrite(char *rulename, * Verify relation is of a type that rules can sensibly be applied to. */ if (event_relation->rd_rel->relkind != RELKIND_RELATION && + event_relation->rd_rel->relkind != RELKIND_MATVIEW && event_relation->rd_rel->relkind != RELKIND_VIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -356,7 +357,8 @@ DefineQueryRewrite(char *rulename, */ checkRuleResultList(query->targetList, RelationGetDescr(event_relation), - true); + event_relation->rd_rel->relkind != + RELKIND_MATVIEW); /* * ... there must not be another ON SELECT rule already ... @@ -414,7 +416,8 @@ DefineQueryRewrite(char *rulename, * business of converting relations to views is just a kluge to allow * dump/reload of views that participate in circular dependencies.) */ - if (event_relation->rd_rel->relkind != RELKIND_VIEW) + if (event_relation->rd_rel->relkind != RELKIND_VIEW && + event_relation->rd_rel->relkind != RELKIND_MATVIEW) { HeapScanDesc scanDesc; diff --git a/src/backend/rewrite/rewriteDefine.c.orig b/src/backend/rewrite/rewriteDefine.c.orig new file mode 100644 index 0000000000..a1a9808e5d --- /dev/null +++ b/src/backend/rewrite/rewriteDefine.c.orig @@ -0,0 +1,945 @@ +/*------------------------------------------------------------------------- + * + * rewriteDefine.c + * routines for defining a rewrite rule + * + * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/rewrite/rewriteDefine.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/heapam.h" +#include "access/htup_details.h" +#include "access/multixact.h" +#include "access/transam.h" +#include "access/xact.h" +#include "catalog/catalog.h" +#include "catalog/dependency.h" +#include "catalog/heap.h" +#include "catalog/indexing.h" +#include "catalog/namespace.h" +#include "catalog/objectaccess.h" +#include "catalog/pg_rewrite.h" +#include "catalog/storage.h" +#include "miscadmin.h" +#include "nodes/nodeFuncs.h" +#include "parser/parse_utilcmd.h" +#include "rewrite/rewriteDefine.h" +#include "rewrite/rewriteManip.h" +#include "rewrite/rewriteSupport.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/inval.h" +#include "utils/lsyscache.h" +#include "utils/rel.h" +#include "utils/syscache.h" +#include "utils/tqual.h" + + +static void checkRuleResultList(List *targetList, TupleDesc resultDesc, + bool isSelect); +static bool setRuleCheckAsUser_walker(Node *node, Oid *context); +static void setRuleCheckAsUser_Query(Query *qry, Oid userid); + + +/* + * InsertRule - + * takes the arguments and inserts them as a row into the system + * relation "pg_rewrite" + */ +static Oid +InsertRule(char *rulname, + int evtype, + Oid eventrel_oid, + AttrNumber evslot_index, + bool evinstead, + Node *event_qual, + List *action, + bool replace) +{ + char *evqual = nodeToString(event_qual); + char *actiontree = nodeToString((Node *) action); + Datum values[Natts_pg_rewrite]; + bool nulls[Natts_pg_rewrite]; + bool replaces[Natts_pg_rewrite]; + NameData rname; + Relation pg_rewrite_desc; + HeapTuple tup, + oldtup; + Oid rewriteObjectId; + ObjectAddress myself, + referenced; + bool is_update = false; + + /* + * Set up *nulls and *values arrays + */ + MemSet(nulls, false, sizeof(nulls)); + + namestrcpy(&rname, rulname); + values[Anum_pg_rewrite_rulename - 1] = NameGetDatum(&rname); + values[Anum_pg_rewrite_ev_class - 1] = ObjectIdGetDatum(eventrel_oid); + values[Anum_pg_rewrite_ev_attr - 1] = Int16GetDatum(evslot_index); + values[Anum_pg_rewrite_ev_type - 1] = CharGetDatum(evtype + '0'); + values[Anum_pg_rewrite_ev_enabled - 1] = CharGetDatum(RULE_FIRES_ON_ORIGIN); + values[Anum_pg_rewrite_is_instead - 1] = BoolGetDatum(evinstead); + values[Anum_pg_rewrite_ev_qual - 1] = CStringGetTextDatum(evqual); + values[Anum_pg_rewrite_ev_action - 1] = CStringGetTextDatum(actiontree); + + /* + * Ready to store new pg_rewrite tuple + */ + pg_rewrite_desc = heap_open(RewriteRelationId, RowExclusiveLock); + + /* + * Check to see if we are replacing an existing tuple + */ + oldtup = SearchSysCache2(RULERELNAME, + ObjectIdGetDatum(eventrel_oid), + PointerGetDatum(rulname)); + + if (HeapTupleIsValid(oldtup)) + { + if (!replace) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("rule \"%s\" for relation \"%s\" already exists", + rulname, get_rel_name(eventrel_oid)))); + + /* + * When replacing, we don't need to replace every attribute + */ + MemSet(replaces, false, sizeof(replaces)); + replaces[Anum_pg_rewrite_ev_attr - 1] = true; + replaces[Anum_pg_rewrite_ev_type - 1] = true; + replaces[Anum_pg_rewrite_is_instead - 1] = true; + replaces[Anum_pg_rewrite_ev_qual - 1] = true; + replaces[Anum_pg_rewrite_ev_action - 1] = true; + + tup = heap_modify_tuple(oldtup, RelationGetDescr(pg_rewrite_desc), + values, nulls, replaces); + + simple_heap_update(pg_rewrite_desc, &tup->t_self, tup); + + ReleaseSysCache(oldtup); + + rewriteObjectId = HeapTupleGetOid(tup); + is_update = true; + } + else + { + tup = heap_form_tuple(pg_rewrite_desc->rd_att, values, nulls); + + rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); + } + + /* Need to update indexes in either case */ + CatalogUpdateIndexes(pg_rewrite_desc, tup); + + heap_freetuple(tup); + + /* If replacing, get rid of old dependencies and make new ones */ + if (is_update) + deleteDependencyRecordsFor(RewriteRelationId, rewriteObjectId, false); + + /* + * Install dependency on rule's relation to ensure it will go away on + * relation deletion. If the rule is ON SELECT, make the dependency + * implicit --- this prevents deleting a view's SELECT rule. Other kinds + * of rules can be AUTO. + */ + myself.classId = RewriteRelationId; + myself.objectId = rewriteObjectId; + myself.objectSubId = 0; + + referenced.classId = RelationRelationId; + referenced.objectId = eventrel_oid; + referenced.objectSubId = 0; + + recordDependencyOn(&myself, &referenced, + (evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO); + + /* + * Also install dependencies on objects referenced in action and qual. + */ + recordDependencyOnExpr(&myself, (Node *) action, NIL, + DEPENDENCY_NORMAL); + + if (event_qual != NULL) + { + /* Find query containing OLD/NEW rtable entries */ + Query *qry = (Query *) linitial(action); + + qry = getInsertSelectQuery(qry, NULL); + recordDependencyOnExpr(&myself, event_qual, qry->rtable, + DEPENDENCY_NORMAL); + } + + /* Post creation hook for new rule */ + InvokeObjectAccessHook(OAT_POST_CREATE, + RewriteRelationId, rewriteObjectId, 0, NULL); + + heap_close(pg_rewrite_desc, RowExclusiveLock); + + return rewriteObjectId; +} + +/* + * DefineRule + * Execute a CREATE RULE command. + */ +Oid +DefineRule(RuleStmt *stmt, const char *queryString) +{ + List *actions; + Node *whereClause; + Oid relId; + + /* Parse analysis. */ + transformRuleStmt(stmt, queryString, &actions, &whereClause); + + /* + * Find and lock the relation. Lock level should match + * DefineQueryRewrite. + */ + relId = RangeVarGetRelid(stmt->relation, AccessExclusiveLock, false); + + /* ... and execute */ + return DefineQueryRewrite(stmt->rulename, + relId, + whereClause, + stmt->event, + stmt->instead, + stmt->replace, + actions); +} + + +/* + * DefineQueryRewrite + * Create a rule + * + * This is essentially the same as DefineRule() except that the rule's + * action and qual have already been passed through parse analysis. + */ +Oid +DefineQueryRewrite(char *rulename, + Oid event_relid, + Node *event_qual, + CmdType event_type, + bool is_instead, + bool replace, + List *action) +{ + Relation event_relation; + int event_attno; + ListCell *l; + Query *query; + bool RelisBecomingView = false; + Oid ruleId = InvalidOid; + + /* + * If we are installing an ON SELECT rule, we had better grab + * AccessExclusiveLock to ensure no SELECTs are currently running on the + * event relation. For other types of rules, it would be sufficient to + * grab ShareRowExclusiveLock to lock out insert/update/delete actions and + * to ensure that we lock out current CREATE RULE statements; but because + * of race conditions in access to catalog entries, we can't do that yet. + * + * Note that this lock level should match the one used in DefineRule. + */ + event_relation = heap_open(event_relid, AccessExclusiveLock); + + /* + * Verify relation is of a type that rules can sensibly be applied to. + */ + if (event_relation->rd_rel->relkind != RELKIND_RELATION && + event_relation->rd_rel->relkind != RELKIND_VIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table or view", + RelationGetRelationName(event_relation)))); + + if (!allowSystemTableMods && IsSystemRelation(event_relation)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(event_relation)))); + + /* + * Check user has permission to apply rules to this relation. + */ + if (!pg_class_ownercheck(event_relid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, + RelationGetRelationName(event_relation)); + + /* + * No rule actions that modify OLD or NEW + */ + foreach(l, action) + { + query = (Query *) lfirst(l); + if (query->resultRelation == 0) + continue; + /* Don't be fooled by INSERT/SELECT */ + if (query != getInsertSelectQuery(query, NULL)) + continue; + if (query->resultRelation == PRS2_OLD_VARNO) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rule actions on OLD are not implemented"), + errhint("Use views or triggers instead."))); + if (query->resultRelation == PRS2_NEW_VARNO) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rule actions on NEW are not implemented"), + errhint("Use triggers instead."))); + } + + if (event_type == CMD_SELECT) + { + /* + * Rules ON SELECT are restricted to view definitions + * + * So there cannot be INSTEAD NOTHING, ... + */ + if (list_length(action) == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("INSTEAD NOTHING rules on SELECT are not implemented"), + errhint("Use views instead."))); + + /* + * ... there cannot be multiple actions, ... + */ + if (list_length(action) > 1) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("multiple actions for rules on SELECT are not implemented"))); + + /* + * ... the one action must be a SELECT, ... + */ + query = (Query *) linitial(action); + if (!is_instead || + query->commandType != CMD_SELECT || + query->utilityStmt != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rules on SELECT must have action INSTEAD SELECT"))); + + /* + * ... it cannot contain data-modifying WITH ... + */ + if (query->hasModifyingCTE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rules on SELECT must not contain data-modifying statements in WITH"))); + + /* + * ... there can be no rule qual, ... + */ + if (event_qual != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("event qualifications are not implemented for rules on SELECT"))); + + /* + * ... the targetlist of the SELECT action must exactly match the + * event relation, ... + */ + checkRuleResultList(query->targetList, + RelationGetDescr(event_relation), + true); + + /* + * ... there must not be another ON SELECT rule already ... + */ + if (!replace && event_relation->rd_rules != NULL) + { + int i; + + for (i = 0; i < event_relation->rd_rules->numLocks; i++) + { + RewriteRule *rule; + + rule = event_relation->rd_rules->rules[i]; + if (rule->event == CMD_SELECT) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("\"%s\" is already a view", + RelationGetRelationName(event_relation)))); + } + } + + /* + * ... and finally the rule must be named _RETURN. + */ + if (strcmp(rulename, ViewSelectRuleName) != 0) + { + /* + * In versions before 7.3, the expected name was _RETviewname. For + * backwards compatibility with old pg_dump output, accept that + * and silently change it to _RETURN. Since this is just a quick + * backwards-compatibility hack, limit the number of characters + * checked to a few less than NAMEDATALEN; this saves having to + * worry about where a multibyte character might have gotten + * truncated. + */ + if (strncmp(rulename, "_RET", 4) != 0 || + strncmp(rulename + 4, RelationGetRelationName(event_relation), + NAMEDATALEN - 4 - 4) != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("view rule for \"%s\" must be named \"%s\"", + RelationGetRelationName(event_relation), + ViewSelectRuleName))); + rulename = pstrdup(ViewSelectRuleName); + } + + /* + * Are we converting a relation to a view? + * + * If so, check that the relation is empty because the storage for the + * relation is going to be deleted. Also insist that the rel not have + * any triggers, indexes, or child tables. (Note: these tests are too + * strict, because they will reject relations that once had such but + * don't anymore. But we don't really care, because this whole + * business of converting relations to views is just a kluge to allow + * dump/reload of views that participate in circular dependencies.) + */ + if (event_relation->rd_rel->relkind != RELKIND_VIEW) + { + HeapScanDesc scanDesc; + + scanDesc = heap_beginscan(event_relation, SnapshotNow, 0, NULL); + if (heap_getnext(scanDesc, ForwardScanDirection) != NULL) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("could not convert table \"%s\" to a view because it is not empty", + RelationGetRelationName(event_relation)))); + heap_endscan(scanDesc); + + if (event_relation->rd_rel->relhastriggers) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("could not convert table \"%s\" to a view because it has triggers", + RelationGetRelationName(event_relation)), + errhint("In particular, the table cannot be involved in any foreign key relationships."))); + + if (event_relation->rd_rel->relhasindex) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("could not convert table \"%s\" to a view because it has indexes", + RelationGetRelationName(event_relation)))); + + if (event_relation->rd_rel->relhassubclass) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("could not convert table \"%s\" to a view because it has child tables", + RelationGetRelationName(event_relation)))); + + RelisBecomingView = true; + } + } + else + { + /* + * For non-SELECT rules, a RETURNING list can appear in at most one of + * the actions ... and there can't be any RETURNING list at all in a + * conditional or non-INSTEAD rule. (Actually, there can be at most + * one RETURNING list across all rules on the same event, but it seems + * best to enforce that at rule expansion time.) If there is a + * RETURNING list, it must match the event relation. + */ + bool haveReturning = false; + + foreach(l, action) + { + query = (Query *) lfirst(l); + + if (!query->returningList) + continue; + if (haveReturning) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot have multiple RETURNING lists in a rule"))); + haveReturning = true; + if (event_qual != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RETURNING lists are not supported in conditional rules"))); + if (!is_instead) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RETURNING lists are not supported in non-INSTEAD rules"))); + checkRuleResultList(query->returningList, + RelationGetDescr(event_relation), + false); + } + } + + /* + * This rule is allowed - prepare to install it. + */ + event_attno = -1; + + /* discard rule if it's null action and not INSTEAD; it's a no-op */ + if (action != NIL || is_instead) + { + ruleId = InsertRule(rulename, + event_type, + event_relid, + event_attno, + is_instead, + event_qual, + action, + replace); + + /* + * Set pg_class 'relhasrules' field TRUE for event relation. + * + * Important side effect: an SI notice is broadcast to force all + * backends (including me!) to update relcache entries with the new + * rule. + */ + SetRelationRuleStatus(event_relid, true); + } + + /* --------------------------------------------------------------------- + * If the relation is becoming a view: + * - delete the associated storage files + * - get rid of any system attributes in pg_attribute; a view shouldn't + * have any of those + * - remove the toast table; there is no need for it anymore, and its + * presence would make vacuum slightly more complicated + * - set relkind to RELKIND_VIEW, and adjust other pg_class fields + * to be appropriate for a view + * + * NB: we had better have AccessExclusiveLock to do this ... + * --------------------------------------------------------------------- + */ + if (RelisBecomingView) + { + Relation relationRelation; + Oid toastrelid; + HeapTuple classTup; + Form_pg_class classForm; + + relationRelation = heap_open(RelationRelationId, RowExclusiveLock); + toastrelid = event_relation->rd_rel->reltoastrelid; + + /* drop storage while table still looks like a table */ + RelationDropStorage(event_relation); + DeleteSystemAttributeTuples(event_relid); + + /* + * Drop the toast table if any. (This won't take care of updating + * the toast fields in the relation's own pg_class entry; we handle + * that below.) + */ + if (OidIsValid(toastrelid)) + { + ObjectAddress toastobject; + + /* + * Delete the dependency of the toast relation on the main + * relation so we can drop the former without dropping the latter. + */ + deleteDependencyRecordsFor(RelationRelationId, toastrelid, + false); + + /* Make deletion of dependency record visible */ + CommandCounterIncrement(); + + /* Now drop toast table, including its index */ + toastobject.classId = RelationRelationId; + toastobject.objectId = toastrelid; + toastobject.objectSubId = 0; + performDeletion(&toastobject, DROP_RESTRICT, + PERFORM_DELETION_INTERNAL); + } + + /* + * SetRelationRuleStatus may have updated the pg_class row, so we must + * advance the command counter before trying to update it again. + */ + CommandCounterIncrement(); + + /* + * Fix pg_class entry to look like a normal view's, including setting + * the correct relkind and removal of reltoastrelid/reltoastidxid of + * the toast table we potentially removed above. + */ + classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(event_relid)); + if (!HeapTupleIsValid(classTup)) + elog(ERROR, "cache lookup failed for relation %u", event_relid); + classForm = (Form_pg_class) GETSTRUCT(classTup); + + classForm->reltablespace = InvalidOid; + classForm->relpages = 0; + classForm->reltuples = 0; + classForm->relallvisible = 0; + classForm->reltoastrelid = InvalidOid; + classForm->reltoastidxid = InvalidOid; + classForm->relhasindex = false; + classForm->relkind = RELKIND_VIEW; + classForm->relhasoids = false; + classForm->relhaspkey = false; + classForm->relfrozenxid = InvalidTransactionId; + classForm->relminmxid = InvalidMultiXactId; + + simple_heap_update(relationRelation, &classTup->t_self, classTup); + CatalogUpdateIndexes(relationRelation, classTup); + + heap_freetuple(classTup); + heap_close(relationRelation, RowExclusiveLock); + } + + /* Close rel, but keep lock till commit... */ + heap_close(event_relation, NoLock); + + return ruleId; +} + +/* + * checkRuleResultList + * Verify that targetList produces output compatible with a tupledesc + * + * The targetList might be either a SELECT targetlist, or a RETURNING list; + * isSelect tells which. (This is mostly used for choosing error messages, + * but also we don't enforce column name matching for RETURNING.) + */ +static void +checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect) +{ + ListCell *tllist; + int i; + + i = 0; + foreach(tllist, targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(tllist); + int32 tletypmod; + Form_pg_attribute attr; + char *attname; + + /* resjunk entries may be ignored */ + if (tle->resjunk) + continue; + i++; + if (i > resultDesc->natts) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + isSelect ? + errmsg("SELECT rule's target list has too many entries") : + errmsg("RETURNING list has too many entries"))); + + attr = resultDesc->attrs[i - 1]; + attname = NameStr(attr->attname); + + /* + * Disallow dropped columns in the relation. This won't happen in the + * cases we actually care about (namely creating a view via CREATE + * TABLE then CREATE RULE, or adding a RETURNING rule to a view). + * Trying to cope with it is much more trouble than it's worth, + * because we'd have to modify the rule to insert dummy NULLs at the + * right positions. + */ + if (attr->attisdropped) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert relation containing dropped columns to view"))); + + if (isSelect && strcmp(tle->resname, attname) != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("SELECT rule's target entry %d has different column name from \"%s\"", i, attname))); + + if (attr->atttypid != exprType((Node *) tle->expr)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + isSelect ? + errmsg("SELECT rule's target entry %d has different type from column \"%s\"", + i, attname) : + errmsg("RETURNING list's entry %d has different type from column \"%s\"", + i, attname))); + + /* + * Allow typmods to be different only if one of them is -1, ie, + * "unspecified". This is necessary for cases like "numeric", where + * the table will have a filled-in default length but the select + * rule's expression will probably have typmod = -1. + */ + tletypmod = exprTypmod((Node *) tle->expr); + if (attr->atttypmod != tletypmod && + attr->atttypmod != -1 && tletypmod != -1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + isSelect ? + errmsg("SELECT rule's target entry %d has different size from column \"%s\"", + i, attname) : + errmsg("RETURNING list's entry %d has different size from column \"%s\"", + i, attname))); + } + + if (i != resultDesc->natts) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + isSelect ? + errmsg("SELECT rule's target list has too few entries") : + errmsg("RETURNING list has too few entries"))); +} + +/* + * setRuleCheckAsUser + * Recursively scan a query or expression tree and set the checkAsUser + * field to the given userid in all rtable entries. + * + * Note: for a view (ON SELECT rule), the checkAsUser field of the OLD + * RTE entry will be overridden when the view rule is expanded, and the + * checkAsUser field of the NEW entry is irrelevant because that entry's + * requiredPerms bits will always be zero. However, for other types of rules + * it's important to set these fields to match the rule owner. So we just set + * them always. + */ +void +setRuleCheckAsUser(Node *node, Oid userid) +{ + (void) setRuleCheckAsUser_walker(node, &userid); +} + +static bool +setRuleCheckAsUser_walker(Node *node, Oid *context) +{ + if (node == NULL) + return false; + if (IsA(node, Query)) + { + setRuleCheckAsUser_Query((Query *) node, *context); + return false; + } + return expression_tree_walker(node, setRuleCheckAsUser_walker, + (void *) context); +} + +static void +setRuleCheckAsUser_Query(Query *qry, Oid userid) +{ + ListCell *l; + + /* Set all the RTEs in this query node */ + foreach(l, qry->rtable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); + + if (rte->rtekind == RTE_SUBQUERY) + { + /* Recurse into subquery in FROM */ + setRuleCheckAsUser_Query(rte->subquery, userid); + } + else + rte->checkAsUser = userid; + } + + /* Recurse into subquery-in-WITH */ + foreach(l, qry->cteList) + { + CommonTableExpr *cte = (CommonTableExpr *) lfirst(l); + + setRuleCheckAsUser_Query((Query *) cte->ctequery, userid); + } + + /* If there are sublinks, search for them and process their RTEs */ + if (qry->hasSubLinks) + query_tree_walker(qry, setRuleCheckAsUser_walker, (void *) &userid, + QTW_IGNORE_RC_SUBQUERIES); +} + + +/* + * Change the firing semantics of an existing rule. + */ +void +EnableDisableRule(Relation rel, const char *rulename, + char fires_when) +{ + Relation pg_rewrite_desc; + Oid owningRel = RelationGetRelid(rel); + Oid eventRelationOid; + HeapTuple ruletup; + bool changed = false; + + /* + * Find the rule tuple to change. + */ + pg_rewrite_desc = heap_open(RewriteRelationId, RowExclusiveLock); + ruletup = SearchSysCacheCopy2(RULERELNAME, + ObjectIdGetDatum(owningRel), + PointerGetDatum(rulename)); + if (!HeapTupleIsValid(ruletup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("rule \"%s\" for relation \"%s\" does not exist", + rulename, get_rel_name(owningRel)))); + + /* + * Verify that the user has appropriate permissions. + */ + eventRelationOid = ((Form_pg_rewrite) GETSTRUCT(ruletup))->ev_class; + Assert(eventRelationOid == owningRel); + if (!pg_class_ownercheck(eventRelationOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, + get_rel_name(eventRelationOid)); + + /* + * Change ev_enabled if it is different from the desired new state. + */ + if (DatumGetChar(((Form_pg_rewrite) GETSTRUCT(ruletup))->ev_enabled) != + fires_when) + { + ((Form_pg_rewrite) GETSTRUCT(ruletup))->ev_enabled = + CharGetDatum(fires_when); + simple_heap_update(pg_rewrite_desc, &ruletup->t_self, ruletup); + + /* keep system catalog indexes current */ + CatalogUpdateIndexes(pg_rewrite_desc, ruletup); + + changed = true; + } + + heap_freetuple(ruletup); + heap_close(pg_rewrite_desc, RowExclusiveLock); + + /* + * If we changed anything, broadcast a SI inval message to force each + * backend (including our own!) to rebuild relation's relcache entry. + * Otherwise they will fail to apply the change promptly. + */ + if (changed) + CacheInvalidateRelcache(rel); +} + + +/* + * Perform permissions and integrity checks before acquiring a relation lock. + */ +static void +RangeVarCallbackForRenameRule(const RangeVar *rv, Oid relid, Oid oldrelid, + void *arg) +{ + HeapTuple tuple; + Form_pg_class form; + + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + return; /* concurrently dropped */ + form = (Form_pg_class) GETSTRUCT(tuple); + + /* only tables and views can have rules */ + if (form->relkind != RELKIND_RELATION && form->relkind != RELKIND_VIEW) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table or view", rv->relname))); + + if (!allowSystemTableMods && IsSystemClass(form)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + rv->relname))); + + /* you must own the table to rename one of its rules */ + if (!pg_class_ownercheck(relid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, rv->relname); + + ReleaseSysCache(tuple); +} + +/* + * Rename an existing rewrite rule. + */ +Oid +RenameRewriteRule(RangeVar *relation, const char *oldName, + const char *newName) +{ + Oid relid; + Relation targetrel; + Relation pg_rewrite_desc; + HeapTuple ruletup; + Form_pg_rewrite ruleform; + Oid ruleOid; + + /* + * Look up name, check permissions, and acquire lock (which we will NOT + * release until end of transaction). + */ + relid = RangeVarGetRelidExtended(relation, AccessExclusiveLock, + false, false, + RangeVarCallbackForRenameRule, + NULL); + + /* Have lock already, so just need to build relcache entry. */ + targetrel = relation_open(relid, NoLock); + + /* Prepare to modify pg_rewrite */ + pg_rewrite_desc = heap_open(RewriteRelationId, RowExclusiveLock); + + /* Fetch the rule's entry (it had better exist) */ + ruletup = SearchSysCacheCopy2(RULERELNAME, + ObjectIdGetDatum(relid), + PointerGetDatum(oldName)); + if (!HeapTupleIsValid(ruletup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("rule \"%s\" for relation \"%s\" does not exist", + oldName, RelationGetRelationName(targetrel)))); + ruleform = (Form_pg_rewrite) GETSTRUCT(ruletup); + ruleOid = HeapTupleGetOid(ruletup); + + /* rule with the new name should not already exist */ + if (IsDefinedRewriteRule(relid, newName)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("rule \"%s\" for relation \"%s\" already exists", + newName, RelationGetRelationName(targetrel)))); + + /* + * We disallow renaming ON SELECT rules, because they should always be + * named "_RETURN". + */ + if (ruleform->ev_type == CMD_SELECT + '0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("renaming an ON SELECT rule is not allowed"))); + + /* OK, do the update */ + namestrcpy(&(ruleform->rulename), newName); + + simple_heap_update(pg_rewrite_desc, &ruletup->t_self, ruletup); + + /* keep system catalog indexes current */ + CatalogUpdateIndexes(pg_rewrite_desc, ruletup); + + heap_freetuple(ruletup); + heap_close(pg_rewrite_desc, RowExclusiveLock); + + /* + * Invalidate relation's relcache entry so that other backends (and this + * one too!) are sent SI message to make them rebuild relcache entries. + * (Ideally this should happen automatically...) + */ + CacheInvalidateRelcache(targetrel); + + /* + * Close rel, but keep exclusive lock! + */ + relation_close(targetrel, NoLock); + + return ruleOid; +} diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index b458de6971..83c83a6a8a 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1168,7 +1168,8 @@ rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, const char *attrname; TargetEntry *tle; - if (target_relation->rd_rel->relkind == RELKIND_RELATION) + if (target_relation->rd_rel->relkind == RELKIND_RELATION || + target_relation->rd_rel->relkind == RELKIND_MATVIEW) { /* * Emit CTID so that executor can find the row to update or delete. @@ -1591,6 +1592,23 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown) rel = heap_open(rte->relid, NoLock); /* + * Skip materialized view expansion when it is being created. + * + * NOTE: This is assuming that we cannot have gotten to this point + * with a non-scannable materialized view unless it is being + * populated, and that if it is scannable we want to use the existing + * contents. It would be nice to have some way to confirm that we're + * doing the right thing here, but rule expansion doesn't give us a + * lot to work with, so we are trusting earlier validations and + * execution steps to get it right. + */ + if (rel->rd_rel->relkind == RELKIND_MATVIEW && rel->rd_isscannable) + { + heap_close(rel, NoLock); + break; + } + + /* * Collect the RIR rules that we must apply */ rules = rel->rd_rules; diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c index 51c350797d..6029cfb78e 100644 --- a/src/backend/storage/lmgr/predicate.c +++ b/src/backend/storage/lmgr/predicate.c @@ -460,13 +460,14 @@ static void OnConflict_CheckForSerializationFailure(const SERIALIZABLEXACT *read /* * Does this relation participate in predicate locking? Temporary and system - * relations are exempt. + * relations are exempt, as are materialized views. */ static inline bool PredicateLockingNeededForRelation(Relation relation) { return !(relation->rd_id < FirstBootstrapObjectId || - RelationUsesLocalBuffers(relation)); + RelationUsesLocalBuffers(relation) || + relation->rd_rel->relkind == RELKIND_MATVIEW); } /* diff --git a/src/backend/tcop/dest.c b/src/backend/tcop/dest.c index 8767d05cc1..fb2ff32f57 100644 --- a/src/backend/tcop/dest.c +++ b/src/backend/tcop/dest.c @@ -32,6 +32,7 @@ #include "access/xact.h" #include "commands/copy.h" #include "commands/createas.h" +#include "commands/matview.h" #include "executor/functions.h" #include "executor/tstoreReceiver.h" #include "libpq/libpq.h" @@ -125,6 +126,9 @@ CreateDestReceiver(CommandDest dest) case DestSQLFunction: return CreateSQLFunctionDestReceiver(); + + case DestTransientRel: + return CreateTransientRelDestReceiver(InvalidOid); } /* should never get here */ @@ -157,6 +161,7 @@ EndCommand(const char *commandTag, CommandDest dest) case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } @@ -198,6 +203,7 @@ NullCommand(CommandDest dest) case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } @@ -241,6 +247,7 @@ ReadyForQuery(CommandDest dest) case DestIntoRel: case DestCopyOut: case DestSQLFunction: + case DestTransientRel: break; } } diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 8904c6f2da..a1c03f1f76 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -37,6 +37,7 @@ #include "commands/event_trigger.h" #include "commands/explain.h" #include "commands/extension.h" +#include "commands/matview.h" #include "commands/lockcmds.h" #include "commands/portalcmds.h" #include "commands/prepare.h" @@ -202,6 +203,7 @@ check_xact_readonly(Node *parsetree) case T_CreateSeqStmt: case T_CreateStmt: case T_CreateTableAsStmt: + case T_RefreshMatViewStmt: case T_CreateTableSpaceStmt: case T_CreateTrigStmt: case T_CompositeTypeStmt: @@ -713,6 +715,7 @@ standard_ProcessUtility(Node *parsetree, case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: + case OBJECT_MATVIEW: case OBJECT_FOREIGN_TABLE: RemoveRelations((DropStmt *) parsetree); break; @@ -1164,6 +1167,13 @@ standard_ProcessUtility(Node *parsetree, queryString, params, completionTag)); break; + case T_RefreshMatViewStmt: + if (isCompleteQuery) + EventTriggerDDLCommandStart(parsetree); + ExecRefreshMatView((RefreshMatViewStmt *) parsetree, + queryString, params, completionTag); + break; + case T_VariableSetStmt: ExecSetVariableStmt((VariableSetStmt *) parsetree); break; @@ -1290,6 +1300,7 @@ standard_ProcessUtility(Node *parsetree, ReindexIndex(stmt->relation); break; case OBJECT_TABLE: + case OBJECT_MATVIEW: ReindexTable(stmt->relation); break; case OBJECT_DATABASE: @@ -1509,9 +1520,10 @@ QueryReturnsTuples(Query *parsetree) * We assume it is invoked only on already-parse-analyzed statements * (else the contained parsetree isn't a Query yet). * - * In some cases (currently, only EXPLAIN of CREATE TABLE AS/SELECT INTO), - * potentially Query-containing utility statements can be nested. This - * function will drill down to a non-utility Query, or return NULL if none. + * In some cases (currently, only EXPLAIN of CREATE TABLE AS/SELECT INTO and + * CREATE MATERIALIZED VIEW), potentially Query-containing utility statements + * can be nested. This function will drill down to a non-utility Query, or + * return NULL if none. */ Query * UtilityContainsQuery(Node *parsetree) @@ -1655,6 +1667,9 @@ AlterObjectTypeCommandTag(ObjectType objtype) case OBJECT_VIEW: tag = "ALTER VIEW"; break; + case OBJECT_MATVIEW: + tag = "ALTER MATERIALIZED VIEW"; + break; default: tag = "???"; break; @@ -1852,6 +1867,9 @@ CreateCommandTag(Node *parsetree) case OBJECT_VIEW: tag = "DROP VIEW"; break; + case OBJECT_MATVIEW: + tag = "DROP MATERIALIZED VIEW"; + break; case OBJECT_INDEX: tag = "DROP INDEX"; break; @@ -2113,10 +2131,24 @@ CreateCommandTag(Node *parsetree) break; case T_CreateTableAsStmt: - if (((CreateTableAsStmt *) parsetree)->is_select_into) - tag = "SELECT INTO"; - else - tag = "CREATE TABLE AS"; + switch (((CreateTableAsStmt *) parsetree)->relkind) + { + case OBJECT_TABLE: + if (((CreateTableAsStmt *) parsetree)->is_select_into) + tag = "SELECT INTO"; + else + tag = "CREATE TABLE AS"; + break; + case OBJECT_MATVIEW: + tag = "CREATE MATERIALIZED VIEW"; + break; + default: + tag = "???"; + } + break; + + case T_RefreshMatViewStmt: + tag = "REFRESH MATERIALIZED VIEW"; break; case T_VariableSetStmt: @@ -2681,6 +2713,10 @@ GetCommandLogLevel(Node *parsetree) lev = LOGSTMT_DDL; break; + case T_RefreshMatViewStmt: + lev = LOGSTMT_DDL; + break; + case T_VariableSetStmt: lev = LOGSTMT_ALL; break; diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c index 11b004072f..d589d26070 100644 --- a/src/backend/utils/adt/dbsize.c +++ b/src/backend/utils/adt/dbsize.c @@ -719,6 +719,7 @@ pg_relation_filenode(PG_FUNCTION_ARGS) switch (relform->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_SEQUENCE: case RELKIND_TOASTVALUE: @@ -767,6 +768,7 @@ pg_relation_filepath(PG_FUNCTION_ARGS) switch (relform->relkind) { case RELKIND_RELATION: + case RELKIND_MATVIEW: case RELKIND_INDEX: case RELKIND_SEQUENCE: case RELKIND_TOASTVALUE: @@ -832,3 +834,25 @@ pg_relation_filepath(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(path)); } + + +/* + * Indicate whether a relation is scannable. + * + * Currently, this is always true except for a materialized view which has not + * been populated. + */ +Datum +pg_relation_is_scannable(PG_FUNCTION_ARGS) +{ + Oid relid; + Relation relation; + bool result; + + relid = PG_GETARG_OID(0); + relation = RelationIdGetRelation(relid); + result = relation->rd_isscannable; + RelationClose(relation); + + PG_RETURN_BOOL(result); +} diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c index e101ea6349..d5d48d5c06 100644 --- a/src/backend/utils/adt/xml.c +++ b/src/backend/utils/adt/xml.c @@ -2285,7 +2285,7 @@ schema_get_xml_visible_tables(Oid nspid) StringInfoData query; initStringInfo(&query); - appendStringInfo(&query, "SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = %u AND relkind IN ('r', 'v') AND pg_catalog.has_table_privilege (oid, 'SELECT') ORDER BY relname;", nspid); + appendStringInfo(&query, "SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = %u AND relkind IN ('r', 'm', 'v') AND pg_catalog.has_table_privilege (oid, 'SELECT') ORDER BY relname;", nspid); return query_to_oid_list(query.data); } @@ -2311,7 +2311,7 @@ static List * database_get_xml_visible_tables(void) { /* At the moment there is no order required here. */ - return query_to_oid_list("SELECT oid FROM pg_catalog.pg_class WHERE relkind IN ('r', 'v') AND pg_catalog.has_table_privilege (pg_class.oid, 'SELECT') AND relnamespace IN (" XML_VISIBLE_SCHEMAS ");"); + return query_to_oid_list("SELECT oid FROM pg_catalog.pg_class WHERE relkind IN ('r', 'm', 'v') AND pg_catalog.has_table_privilege (pg_class.oid, 'SELECT') AND relnamespace IN (" XML_VISIBLE_SCHEMAS ");"); } diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index e85c7a9872..ba03dfcbb2 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -37,6 +37,7 @@ #include "access/transam.h" #include "access/xact.h" #include "catalog/catalog.h" +#include "catalog/heap.h" #include "catalog/index.h" #include "catalog/indexing.h" #include "catalog/namespace.h" @@ -399,6 +400,7 @@ RelationParseRelOptions(Relation relation, HeapTuple tuple) case RELKIND_TOASTVALUE: case RELKIND_INDEX: case RELKIND_VIEW: + case RELKIND_MATVIEW: break; default: return; @@ -954,6 +956,12 @@ RelationBuildDesc(Oid targetRelId, bool insertIt) /* make sure relation is marked as having no open file yet */ relation->rd_smgr = NULL; + if (relation->rd_rel->relkind == RELKIND_MATVIEW && + heap_is_matview_init_state(relation)) + relation->rd_isscannable = false; + else + relation->rd_isscannable = true; + /* * now we can free the memory allocated for pg_class_tuple */ @@ -1523,6 +1531,7 @@ formrdesc(const char *relationName, Oid relationReltype, * initialize physical addressing information for the relation */ RelationInitPhysicalAddr(relation); + relation->rd_isscannable = true; /* * initialize the rel-has-index flag, using hardwired knowledge @@ -1747,6 +1756,7 @@ RelationReloadIndexInfo(Relation relation) heap_freetuple(pg_class_tuple); /* We must recalculate physical address in case it changed */ RelationInitPhysicalAddr(relation); + relation->rd_isscannable = true; /* * For a non-system index, there are fields of the pg_index row that are @@ -1893,6 +1903,11 @@ RelationClearRelation(Relation relation, bool rebuild) if (relation->rd_isnailed) { RelationInitPhysicalAddr(relation); + if (relation->rd_rel->relkind == RELKIND_MATVIEW && + heap_is_matview_init_state(relation)) + relation->rd_isscannable = false; + else + relation->rd_isscannable = true; if (relation->rd_rel->relkind == RELKIND_INDEX) { @@ -2681,6 +2696,12 @@ RelationBuildLocalRelation(const char *relname, RelationInitPhysicalAddr(rel); + /* materialized view not initially scannable */ + if (relkind == RELKIND_MATVIEW) + rel->rd_isscannable = false; + else + rel->rd_isscannable = true; + /* * Okay to insert into the relcache hash tables. */ @@ -4424,6 +4445,11 @@ load_relcache_init_file(bool shared) */ RelationInitLockInfo(rel); RelationInitPhysicalAddr(rel); + if (rel->rd_rel->relkind == RELKIND_MATVIEW && + heap_is_matview_init_state(rel)) + rel->rd_isscannable = false; + else + rel->rd_isscannable = true; } /* diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index b50113265b..b0b346f72a 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -2047,7 +2047,7 @@ setup_privileges(void) static char *privileges_setup[] = { "UPDATE pg_class " " SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' " - " WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n", + " WHERE relkind IN ('r', 'v', 'm', 'S') AND relacl IS NULL;\n", "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n", "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n", "REVOKE ALL ON pg_largeobject FROM PUBLIC;\n", diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 01739ab717..b8832af250 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -270,7 +270,8 @@ flagInhTables(TableInfo *tblinfo, int numTables, { /* Sequences and views never have parents */ if (tblinfo[i].relkind == RELKIND_SEQUENCE || - tblinfo[i].relkind == RELKIND_VIEW) + tblinfo[i].relkind == RELKIND_VIEW || + tblinfo[i].relkind == RELKIND_MATVIEW) continue; /* Don't bother computing anything for non-target tables, either */ @@ -315,7 +316,8 @@ flagInhAttrs(TableInfo *tblinfo, int numTables) /* Sequences and views never have parents */ if (tbinfo->relkind == RELKIND_SEQUENCE || - tbinfo->relkind == RELKIND_VIEW) + tbinfo->relkind == RELKIND_VIEW || + tbinfo->relkind == RELKIND_MATVIEW) continue; /* Don't bother computing anything for non-target tables, either */ diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 1c663cd14f..d500bfd234 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2908,7 +2908,8 @@ _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) const char *type = te->desc; /* Use ALTER TABLE for views and sequences */ - if (strcmp(type, "VIEW") == 0 || strcmp(type, "SEQUENCE") == 0) + if (strcmp(type, "VIEW") == 0 || strcmp(type, "SEQUENCE") == 0|| + strcmp(type, "MATERIALIZED VIEW") == 0) type = "TABLE"; /* objects named by a schema and name */ @@ -3140,6 +3141,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat strcmp(te->desc, "TABLE") == 0 || strcmp(te->desc, "TYPE") == 0 || strcmp(te->desc, "VIEW") == 0 || + strcmp(te->desc, "MATERIALIZED VIEW") == 0 || strcmp(te->desc, "SEQUENCE") == 0 || strcmp(te->desc, "FOREIGN TABLE") == 0 || strcmp(te->desc, "TEXT SEARCH DICTIONARY") == 0 || diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 7903b79a32..e6c85ac0ae 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -151,6 +151,7 @@ static void expand_table_name_patterns(Archive *fout, SimpleOidList *oids); static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid); static void dumpTableData(Archive *fout, TableDataInfo *tdinfo); +static void refreshMatViewData(Archive *fout, TableDataInfo *tdinfo); static void guessConstraintInheritance(TableInfo *tblinfo, int numTables); static void dumpComment(Archive *fout, const char *target, const char *namespace, const char *owner, @@ -223,6 +224,7 @@ static void addBoundaryDependencies(DumpableObject **dobjs, int numObjs, static void getDomainConstraints(Archive *fout, TypeInfo *tyinfo); static void getTableData(TableInfo *tblinfo, int numTables, bool oids); static void makeTableDataInfo(TableInfo *tbinfo, bool oids); +static void buildMatViewRefreshDependencies(Archive *fout); static void getTableDataFKConstraints(void); static char *format_function_arguments(FuncInfo *finfo, char *funcargs); static char *format_function_arguments_old(Archive *fout, @@ -723,6 +725,7 @@ main(int argc, char **argv) if (!schemaOnly) { getTableData(tblinfo, numTables, oids); + buildMatViewRefreshDependencies(fout); if (dataOnly) getTableDataFKConstraints(); } @@ -1075,9 +1078,9 @@ expand_table_name_patterns(Archive *fout, "SELECT c.oid" "\nFROM pg_catalog.pg_class c" "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" - "\nWHERE c.relkind in ('%c', '%c', '%c', '%c')\n", + "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n", RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, - RELKIND_FOREIGN_TABLE); + RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE); processSQLNamePattern(GetConnection(fout), query, cell->val, true, false, "n.nspname", "c.relname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); @@ -1638,6 +1641,49 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo) } /* + * refreshMatViewData - + * load or refresh the contents of a single materialized view + * + * Actually, this just makes an ArchiveEntry for the REFRESH MATERIALIZED VIEW + * statement. + */ +static void +refreshMatViewData(Archive *fout, TableDataInfo *tdinfo) +{ + TableInfo *tbinfo = tdinfo->tdtable; + PQExpBuffer q; + + /* If the materialized view is not flagged as scannable, skip this. */ + if (!tbinfo->isscannable) + return; + + q = createPQExpBuffer(); + + appendPQExpBuffer(q, "REFRESH MATERIALIZED VIEW %s;\n", + fmtId(tbinfo->dobj.name)); + + ArchiveEntry(fout, + tdinfo->dobj.catId, /* catalog ID */ + tdinfo->dobj.dumpId, /* dump ID */ + tbinfo->dobj.name, /* Name */ + tbinfo->dobj.namespace->dobj.name, /* Namespace */ + NULL, /* Tablespace */ + tbinfo->rolname, /* Owner */ + false, /* with oids */ + "MATERIALIZED VIEW DATA", /* Desc */ + SECTION_POST_DATA, /* Section */ + q->data, /* Create */ + "", /* Del */ + NULL, /* Copy */ + tdinfo->dobj.dependencies, /* Deps */ + tdinfo->dobj.nDeps, /* # Deps */ + NULL, /* Dumper */ + NULL); /* Dumper Arg */ + + destroyPQExpBuffer(q); +} + +/* * getTableData - * set up dumpable objects representing the contents of tables */ @@ -1691,7 +1737,10 @@ makeTableDataInfo(TableInfo *tbinfo, bool oids) /* OK, let's dump it */ tdinfo = (TableDataInfo *) pg_malloc(sizeof(TableDataInfo)); - tdinfo->dobj.objType = DO_TABLE_DATA; + if (tbinfo->relkind == RELKIND_MATVIEW) + tdinfo->dobj.objType = DO_REFRESH_MATVIEW; + else + tdinfo->dobj.objType = DO_TABLE_DATA; /* * Note: use tableoid 0 so that this object won't be mistaken for @@ -1711,6 +1760,114 @@ makeTableDataInfo(TableInfo *tbinfo, bool oids) } /* + * The refresh for a materialized view must be dependent on the refresh for + * any materialized view that this one is dependent on. + * + * This must be called after all the objects are created, but before they are + * sorted. + */ +static void +buildMatViewRefreshDependencies(Archive *fout) +{ + PQExpBuffer query = createPQExpBuffer(); + PGresult *res; + int ntups, + i; + int i_classid, + i_objid, + i_refobjid; + + /* Make sure we are in proper schema */ + selectSourceSchema(fout, "pg_catalog"); + + if (fout->remoteVersion >= 90300) + { + appendPQExpBuffer(query, "with recursive w as " + "( " + "select d1.objid, d2.refobjid, c2.relkind as refrelkind " + "from pg_depend d1 " + "join pg_class c1 on c1.oid = d1.objid " + "and c1.relkind = 'm' " + "join pg_rewrite r1 on r1.ev_class = d1.objid " + "join pg_depend d2 on d2.classid = 'pg_rewrite'::regclass " + "and d2.objid = r1.oid " + "and d2.refobjid <> d1.objid " + "join pg_class c2 on c2.oid = d2.refobjid " + "and c2.relkind in ('m','v') " + "where d1.classid = 'pg_class'::regclass " + "union " + "select w.objid, d3.refobjid, c3.relkind " + "from w " + "join pg_rewrite r3 on r3.ev_class = w.refobjid " + "join pg_depend d3 on d3.classid = 'pg_rewrite'::regclass " + "and d3.objid = r3.oid " + "and d3.refobjid <> w.refobjid " + "join pg_class c3 on c3.oid = d3.refobjid " + "and c3.relkind in ('m','v') " + ") " + "select 'pg_class'::regclass::oid as classid, objid, refobjid " + "from w " + "where refrelkind = 'm'"); + } + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + i_classid = PQfnumber(res, "classid"); + i_objid = PQfnumber(res, "objid"); + i_refobjid = PQfnumber(res, "refobjid"); + + for (i = 0; i < ntups; i++) + { + CatalogId objId; + CatalogId refobjId; + DumpableObject *dobj; + DumpableObject *refdobj; + TableInfo *tbinfo; + TableInfo *reftbinfo; + + objId.tableoid = atooid(PQgetvalue(res, i, i_classid)); + objId.oid = atooid(PQgetvalue(res, i, i_objid)); + refobjId.tableoid = objId.tableoid; + refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid)); + + dobj = findObjectByCatalogId(objId); + if (dobj == NULL) + continue; + + Assert(dobj->objType == DO_TABLE); + tbinfo = (TableInfo *) dobj; + Assert(tbinfo->relkind == RELKIND_MATVIEW); + dobj = (DumpableObject *) tbinfo->dataObj; + if (dobj == NULL) + continue; + Assert(dobj->objType == DO_REFRESH_MATVIEW); + + refdobj = findObjectByCatalogId(refobjId); + if (refdobj == NULL) + continue; + + Assert(refdobj->objType == DO_TABLE); + reftbinfo = (TableInfo *) refdobj; + Assert(reftbinfo->relkind == RELKIND_MATVIEW); + refdobj = (DumpableObject *) reftbinfo->dataObj; + if (refdobj == NULL) + continue; + Assert(refdobj->objType == DO_REFRESH_MATVIEW); + + addObjectDependency(dobj, refdobj->dumpId); + + if (!reftbinfo->isscannable) + tbinfo->isscannable = false; + } + + PQclear(res); + + destroyPQExpBuffer(query); +} + +/* * getTableDataFKConstraints - * add dump-order dependencies reflecting foreign key constraints * @@ -3953,6 +4110,7 @@ getTables(Archive *fout, int *numTables) int i_toastoid; int i_toastfrozenxid; int i_relpersistence; + int i_isscannable; int i_owning_tab; int i_owning_col; int i_reltablespace; @@ -3970,7 +4128,7 @@ getTables(Archive *fout, int *numTables) * defined to inherit from a system catalog (pretty weird, but...) * * We ignore relations that are not ordinary tables, sequences, views, - * composite types, or foreign tables. + * materialized views, composite types, or foreign tables. * * Composite-type table entries won't be dumped as such, but we have to * make a DumpableObject for them so that we can track dependencies of the @@ -3997,7 +4155,7 @@ getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " - "c.relpersistence, " + "c.relpersistence, pg_relation_is_scannable(c.oid) as isscannable, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4011,13 +4169,13 @@ getTables(Archive *fout, int *numTables) "d.objsubid = 0 AND " "d.refclassid = c.tableoid AND d.deptype = 'a') " "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " - "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c') " + "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') " "ORDER BY c.oid", username_subquery, RELKIND_SEQUENCE, RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, RELKIND_COMPOSITE_TYPE, - RELKIND_FOREIGN_TABLE); + RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE); } else if (fout->remoteVersion >= 90000) { @@ -4033,7 +4191,7 @@ getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4068,7 +4226,7 @@ getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4103,7 +4261,7 @@ getTables(Archive *fout, int *numTables) "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4139,7 +4297,7 @@ getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4174,7 +4332,7 @@ getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4205,7 +4363,7 @@ getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4231,7 +4389,7 @@ getTables(Archive *fout, int *numTables) "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4267,7 +4425,7 @@ getTables(Archive *fout, int *numTables) "0 as relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " - "'p' AS relpersistence, " + "'p' AS relpersistence, 't'::bool as isscannable, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4315,6 +4473,7 @@ getTables(Archive *fout, int *numTables) i_toastoid = PQfnumber(res, "toid"); i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_relpersistence = PQfnumber(res, "relpersistence"); + i_isscannable = PQfnumber(res, "isscannable"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); i_reltablespace = PQfnumber(res, "reltablespace"); @@ -4356,6 +4515,7 @@ getTables(Archive *fout, int *numTables) tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0); tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); + tblinfo[i].isscannable = (strcmp(PQgetvalue(res, i, i_isscannable), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); tblinfo[i].toast_frozenxid = atooid(PQgetvalue(res, i, i_toastfrozenxid)); @@ -4551,8 +4711,11 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) { TableInfo *tbinfo = &tblinfo[i]; - /* Only plain tables have indexes */ - if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex) + /* Only plain tables and materialized views have indexes. */ + if (tbinfo->relkind != RELKIND_RELATION && + tbinfo->relkind != RELKIND_MATVIEW) + continue; + if (!tbinfo->hasindex) continue; /* Ignore indexes of tables not to be dumped */ @@ -5134,12 +5297,14 @@ getRules(Archive *fout, int *numRules) if (ruleinfo[i].ruletable) { /* - * If the table is a view, force its ON SELECT rule to be sorted - * before the view itself --- this ensures that any dependencies - * for the rule affect the table's positioning. Other rules are - * forced to appear after their table. + * If the table is a view or materialized view, force its ON + * SELECT rule to be sorted before the view itself --- this + * ensures that any dependencies for the rule affect the table's + * positioning. Other rules are forced to appear after their + * table. */ - if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW && + if ((ruleinfo[i].ruletable->relkind == RELKIND_VIEW || + ruleinfo[i].ruletable->relkind == RELKIND_MATVIEW) && ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead) { addObjectDependency(&ruleinfo[i].ruletable->dobj, @@ -7345,6 +7510,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_INDEX: dumpIndex(fout, (IndxInfo *) dobj); break; + case DO_REFRESH_MATVIEW: + refreshMatViewData(fout, (TableDataInfo *) dobj); + break; case DO_RULE: dumpRule(fout, (RuleInfo *) dobj); break; @@ -12384,17 +12552,73 @@ dumpTable(Archive *fout, TableInfo *tbinfo) } /* + * Create the AS clause for a view or materialized view. The semicolon is + * stripped because a materialized view must add a WITH NO DATA clause. + * + * This returns a new buffer which must be freed by the caller. + */ +static PQExpBuffer +createViewAsClause(Archive *fout, TableInfo *tbinfo) +{ + PQExpBuffer query = createPQExpBuffer(); + PQExpBuffer result = createPQExpBuffer(); + PGresult *res; + int len; + + /* Fetch the view definition */ + if (fout->remoteVersion >= 70300) + { + /* Beginning in 7.3, viewname is not unique; rely on OID */ + appendPQExpBuffer(query, + "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) AS viewdef", + tbinfo->dobj.catId.oid); + } + else + { + appendPQExpBuffer(query, "SELECT definition AS viewdef " + "FROM pg_views WHERE viewname = "); + appendStringLiteralAH(query, tbinfo->dobj.name, fout); + appendPQExpBuffer(query, ";"); + } + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + if (PQntuples(res) != 1) + { + if (PQntuples(res) < 1) + exit_horribly(NULL, "query to obtain definition of view \"%s\" returned no data\n", + tbinfo->dobj.name); + else + exit_horribly(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n", + tbinfo->dobj.name); + } + + len = PQgetlength(res, 0, 0); + + if (len == 0) + exit_horribly(NULL, "definition of view \"%s\" appears to be empty (length zero)\n", + tbinfo->dobj.name); + + /* Strip off the trailing semicolon so that other things may follow. */ + Assert(PQgetvalue(res, 0, 0)[len-1] == ';'); + appendBinaryPQExpBuffer(result, PQgetvalue(res, 0, 0), len - 1); + + PQclear(res); + destroyPQExpBuffer(query); + + return result; +} + +/* * dumpTableSchema * write the declaration (not data) of one user-defined table or view */ static void dumpTableSchema(Archive *fout, TableInfo *tbinfo) { - PQExpBuffer query = createPQExpBuffer(); PQExpBuffer q = createPQExpBuffer(); PQExpBuffer delq = createPQExpBuffer(); PQExpBuffer labelq = createPQExpBuffer(); - PGresult *res; int numParents; TableInfo **parents; int actual_atts; /* number of attrs in this CREATE statement */ @@ -12415,44 +12639,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) /* Is it a table or a view? */ if (tbinfo->relkind == RELKIND_VIEW) { - char *viewdef; + PQExpBuffer result; reltypename = "VIEW"; - /* Fetch the view definition */ - if (fout->remoteVersion >= 70300) - { - /* Beginning in 7.3, viewname is not unique; rely on OID */ - appendPQExpBuffer(query, - "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) AS viewdef", - tbinfo->dobj.catId.oid); - } - else - { - appendPQExpBuffer(query, "SELECT definition AS viewdef " - "FROM pg_views WHERE viewname = "); - appendStringLiteralAH(query, tbinfo->dobj.name, fout); - appendPQExpBuffer(query, ";"); - } - - res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); - - if (PQntuples(res) != 1) - { - if (PQntuples(res) < 1) - exit_horribly(NULL, "query to obtain definition of view \"%s\" returned no data\n", - tbinfo->dobj.name); - else - exit_horribly(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n", - tbinfo->dobj.name); - } - - viewdef = PQgetvalue(res, 0, 0); - - if (strlen(viewdef) == 0) - exit_horribly(NULL, "definition of view \"%s\" appears to be empty (length zero)\n", - tbinfo->dobj.name); - /* * DROP must be fully qualified in case same name appears in * pg_catalog @@ -12469,49 +12659,60 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) appendPQExpBuffer(q, "CREATE VIEW %s", fmtId(tbinfo->dobj.name)); if (tbinfo->reloptions && strlen(tbinfo->reloptions) > 0) appendPQExpBuffer(q, " WITH (%s)", tbinfo->reloptions); - appendPQExpBuffer(q, " AS\n %s\n", viewdef); + result = createViewAsClause(fout, tbinfo); + appendPQExpBuffer(q, " AS\n%s;\n", result->data); + destroyPQExpBuffer(result); appendPQExpBuffer(labelq, "VIEW %s", fmtId(tbinfo->dobj.name)); - - PQclear(res); } else { - if (tbinfo->relkind == RELKIND_FOREIGN_TABLE) - { - int i_srvname; - int i_ftoptions; - - reltypename = "FOREIGN TABLE"; - - /* retrieve name of foreign server and generic options */ - appendPQExpBuffer(query, - "SELECT fs.srvname, " - "pg_catalog.array_to_string(ARRAY(" - "SELECT pg_catalog.quote_ident(option_name) || " - "' ' || pg_catalog.quote_literal(option_value) " - "FROM pg_catalog.pg_options_to_table(ftoptions) " - "ORDER BY option_name" - "), E',\n ') AS ftoptions " - "FROM pg_catalog.pg_foreign_table ft " - "JOIN pg_catalog.pg_foreign_server fs " - "ON (fs.oid = ft.ftserver) " - "WHERE ft.ftrelid = '%u'", - tbinfo->dobj.catId.oid); - res = ExecuteSqlQueryForSingleRow(fout, query->data); - i_srvname = PQfnumber(res, "srvname"); - i_ftoptions = PQfnumber(res, "ftoptions"); - srvname = pg_strdup(PQgetvalue(res, 0, i_srvname)); - ftoptions = pg_strdup(PQgetvalue(res, 0, i_ftoptions)); - PQclear(res); - } - else + switch (tbinfo->relkind) { - reltypename = "TABLE"; - srvname = NULL; - ftoptions = NULL; + case (RELKIND_FOREIGN_TABLE): + { + PQExpBuffer query = createPQExpBuffer(); + PGresult *res; + int i_srvname; + int i_ftoptions; + + reltypename = "FOREIGN TABLE"; + + /* retrieve name of foreign server and generic options */ + appendPQExpBuffer(query, + "SELECT fs.srvname, " + "pg_catalog.array_to_string(ARRAY(" + "SELECT pg_catalog.quote_ident(option_name) || " + "' ' || pg_catalog.quote_literal(option_value) " + "FROM pg_catalog.pg_options_to_table(ftoptions) " + "ORDER BY option_name" + "), E',\n ') AS ftoptions " + "FROM pg_catalog.pg_foreign_table ft " + "JOIN pg_catalog.pg_foreign_server fs " + "ON (fs.oid = ft.ftserver) " + "WHERE ft.ftrelid = '%u'", + tbinfo->dobj.catId.oid); + res = ExecuteSqlQueryForSingleRow(fout, query->data); + i_srvname = PQfnumber(res, "srvname"); + i_ftoptions = PQfnumber(res, "ftoptions"); + srvname = pg_strdup(PQgetvalue(res, 0, i_srvname)); + ftoptions = pg_strdup(PQgetvalue(res, 0, i_ftoptions)); + PQclear(res); + destroyPQExpBuffer(query); + break; + } + case (RELKIND_MATVIEW): + reltypename = "MATERIALIZED VIEW"; + srvname = NULL; + ftoptions = NULL; + break; + default: + reltypename = "TABLE"; + srvname = NULL; + ftoptions = NULL; } + numParents = tbinfo->numParents; parents = tbinfo->parents; @@ -12544,6 +12745,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->reloftype && !binary_upgrade) appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); + if (tbinfo->relkind != RELKIND_MATVIEW) + { /* Dump the attributes */ actual_atts = 0; for (j = 0; j < tbinfo->numatts; j++) @@ -12583,7 +12786,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) actual_atts++; /* Attribute name */ - appendPQExpBuffer(q, "%s ", + appendPQExpBuffer(q, "%s", fmtId(tbinfo->attnames[j])); if (tbinfo->attisdropped[j]) @@ -12593,7 +12796,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) * so we will not have gotten a valid type name; insert * INTEGER as a stopgap. We'll clean things up later. */ - appendPQExpBuffer(q, "INTEGER /* dummy */"); + appendPQExpBuffer(q, " INTEGER /* dummy */"); /* Skip all the rest, too */ continue; } @@ -12601,17 +12804,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) /* Attribute type */ if (tbinfo->reloftype && !binary_upgrade) { - appendPQExpBuffer(q, "WITH OPTIONS"); + appendPQExpBuffer(q, " WITH OPTIONS"); } else if (fout->remoteVersion >= 70100) { - appendPQExpBuffer(q, "%s", + appendPQExpBuffer(q, " %s", tbinfo->atttypnames[j]); } else { /* If no format_type, fake it */ - appendPQExpBuffer(q, "%s", + appendPQExpBuffer(q, " %s", myFormatType(tbinfo->atttypnames[j], tbinfo->atttypmod[j])); } @@ -12694,6 +12897,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (tbinfo->relkind == RELKIND_FOREIGN_TABLE) appendPQExpBuffer(q, "\nSERVER %s", fmtId(srvname)); + } if ((tbinfo->reloptions && strlen(tbinfo->reloptions) > 0) || (tbinfo->toast_reloptions && strlen(tbinfo->toast_reloptions) > 0)) @@ -12718,7 +12922,20 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (ftoptions && ftoptions[0]) appendPQExpBuffer(q, "\nOPTIONS (\n %s\n)", ftoptions); - appendPQExpBuffer(q, ";\n"); + /* + * For materialized views, create the AS clause just like a view. + */ + if (tbinfo->relkind == RELKIND_MATVIEW) + { + PQExpBuffer result; + + result = createViewAsClause(fout, tbinfo); + appendPQExpBuffer(q, " AS\n%s\n WITH NO DATA;\n", + result->data); + destroyPQExpBuffer(result); + } + else + appendPQExpBuffer(q, ";\n"); /* * To create binary-compatible heap files, we have to ensure the same @@ -12974,7 +13191,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) dumpTableConstraintComment(fout, constr); } - destroyPQExpBuffer(query); destroyPQExpBuffer(q); destroyPQExpBuffer(delq); destroyPQExpBuffer(labelq); @@ -14468,6 +14684,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, addObjectDependency(postDataBound, dobj->dumpId); break; case DO_INDEX: + case DO_REFRESH_MATVIEW: case DO_TRIGGER: case DO_EVENT_TRIGGER: case DO_DEFAULT_ACL: diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index b6dc856600..01ec27b632 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -110,7 +110,8 @@ typedef enum DO_BLOB_DATA, DO_PRE_DATA_BOUNDARY, DO_POST_DATA_BOUNDARY, - DO_EVENT_TRIGGER + DO_EVENT_TRIGGER, + DO_REFRESH_MATVIEW } DumpableObjectType; typedef struct _dumpableObject @@ -242,6 +243,7 @@ typedef struct _tableInfo bool hasrules; /* does it have any rules? */ bool hastriggers; /* does it have any triggers? */ bool hasoids; /* does it have OIDs? */ + bool isscannable; /* is valid for use in queries */ uint32 frozenxid; /* for restore frozen xid */ Oid toast_oid; /* for restore toast frozen xid */ uint32 toast_frozenxid; /* for restore toast frozen xid */ diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 955c231b1a..2c3d850f3d 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -24,9 +24,9 @@ static const char *modulename = gettext_noop("sorter"); * Objects are sorted by priority levels, and within an equal priority level * by OID. (This is a relatively crude hack to provide semi-reasonable * behavior for old databases without full dependency info.) Note: collations, - * extensions, text search, foreign-data, event trigger, and default ACL - * objects can't really happen here, so the rather bogus priorities for them - * don't matter. + * extensions, text search, foreign-data, materialized view, event trigger, + * and default ACL objects can't really happen here, so the rather bogus + * priorities for them don't matter. * * NOTE: object-type priorities must match the section assignments made in * pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUNDARY, @@ -68,7 +68,8 @@ static const int oldObjectTypePriority[] = 12, /* DO_BLOB_DATA */ 10, /* DO_PRE_DATA_BOUNDARY */ 13, /* DO_POST_DATA_BOUNDARY */ - 20 /* DO_EVENT_TRIGGER */ + 20, /* DO_EVENT_TRIGGER */ + 15 /* DO_REFRESH_MATVIEW */ }; /* @@ -115,7 +116,8 @@ static const int newObjectTypePriority[] = 24, /* DO_BLOB_DATA */ 22, /* DO_PRE_DATA_BOUNDARY */ 25, /* DO_POST_DATA_BOUNDARY */ - 32 /* DO_EVENT_TRIGGER */ + 32, /* DO_EVENT_TRIGGER */ + 33 /* DO_REFRESH_MATVIEW */ }; static DumpId preDataBoundId; @@ -1152,6 +1154,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) "INDEX %s (ID %d OID %u)", obj->name, obj->dumpId, obj->catId.oid); return; + case DO_REFRESH_MATVIEW: + snprintf(buf, bufsize, + "REFRESH MATERIALIZED VIEW %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); + return; case DO_RULE: snprintf(buf, bufsize, "RULE %s (ID %d OID %u)", diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 012cb75e52..3be7c442a4 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -355,7 +355,7 @@ exec_command(const char *cmd, success = describeTableDetails(pattern, show_verbose, show_system); else /* standard listing of interesting things */ - success = listTables("tvsE", NULL, show_verbose, show_system); + success = listTables("tvmsE", NULL, show_verbose, show_system); break; case 'a': success = describeAggregates(pattern, show_verbose, show_system); @@ -422,6 +422,7 @@ exec_command(const char *cmd, break; case 't': case 'v': + case 'm': case 'i': case 's': case 'E': diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 8064a3d702..217c3f5a3a 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -721,11 +721,20 @@ permissionsList(const char *pattern) printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" - " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END as \"%s\",\n" + " CASE c.relkind" + " WHEN 'r' THEN '%s'" + " WHEN 'v' THEN '%s'" + " WHEN 'm' THEN '%s'" + " WHEN 'S' THEN '%s'" + " WHEN 'f' THEN '%s'" + " END as \"%s\",\n" " ", gettext_noop("Schema"), gettext_noop("Name"), - gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"), + gettext_noop("table"), + gettext_noop("view"), + gettext_noop("materialized view"), + gettext_noop("sequence"), gettext_noop("foreign table"), gettext_noop("Type")); @@ -742,7 +751,7 @@ permissionsList(const char *pattern) appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n" " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" - "WHERE c.relkind IN ('r', 'v', 'S', 'f')\n"); + "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n"); /* * Unless a schema pattern is specified, we suppress system and temp @@ -1319,6 +1328,7 @@ describeOneTableDetails(const char *schemaname, * types, and foreign tables (c.f. CommentObject() in comment.c). */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'f' || tableinfo.relkind == 'c') appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)"); } @@ -1347,6 +1357,14 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&title, _("View \"%s.%s\""), schemaname, relationname); break; + case 'm': + if (tableinfo.relpersistence == 'u') + printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""), + schemaname, relationname); + else + printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""), + schemaname, relationname); + break; case 'S': printfPQExpBuffer(&title, _("Sequence \"%s.%s\""), schemaname, relationname); @@ -1389,6 +1407,7 @@ describeOneTableDetails(const char *schemaname, cols = 2; if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'f' || tableinfo.relkind == 'c') { show_modifiers = true; @@ -1408,10 +1427,12 @@ describeOneTableDetails(const char *schemaname, if (verbose) { headers[cols++] = gettext_noop("Storage"); - if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') + if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || + tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Stats target"); /* Column comments, if the relkind supports this feature. */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'c' || tableinfo.relkind == 'f') headers[cols++] = gettext_noop("Description"); } @@ -1422,8 +1443,8 @@ describeOneTableDetails(const char *schemaname, for (i = 0; i < cols; i++) printTableAddHeader(&cont, headers[i], true, 'l'); - /* Check if table is a view */ - if (tableinfo.relkind == 'v' && verbose) + /* Check if table is a view or materialized view */ + if ((tableinfo.relkind == 'v' || tableinfo.relkind == 'm') && verbose) { PGresult *result; @@ -1511,7 +1532,8 @@ describeOneTableDetails(const char *schemaname, false, false); /* Statistics target, if the relkind supports this feature */ - if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') + if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || + tableinfo.relkind == 'f') { printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1), false, false); @@ -1519,6 +1541,7 @@ describeOneTableDetails(const char *schemaname, /* Column comments, if the relkind supports this feature. */ if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || + tableinfo.relkind == 'm' || tableinfo.relkind == 'c' || tableinfo.relkind == 'f') printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2), false, false); @@ -1615,44 +1638,6 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - else if (view_def) - { - PGresult *result = NULL; - - /* Footer information about a view */ - printTableAddFooter(&cont, _("View definition:")); - printTableAddFooter(&cont, view_def); - - /* print rules */ - if (tableinfo.hasrules) - { - printfPQExpBuffer(&buf, - "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" - "FROM pg_catalog.pg_rewrite r\n" - "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", - oid); - result = PSQLexec(buf.data, false); - if (!result) - goto error_return; - - if (PQntuples(result) > 0) - { - printTableAddFooter(&cont, _("Rules:")); - for (i = 0; i < PQntuples(result); i++) - { - const char *ruledef; - - /* Everything after "CREATE RULE" is echoed verbatim */ - ruledef = PQgetvalue(result, i, 1); - ruledef += 12; - - printfPQExpBuffer(&buf, " %s", ruledef); - printTableAddFooter(&cont, buf.data); - } - } - PQclear(result); - } - } else if (tableinfo.relkind == 'S') { /* Footer information about a sequence */ @@ -1691,7 +1676,8 @@ describeOneTableDetails(const char *schemaname, */ PQclear(result); } - else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') + else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || + tableinfo.relkind == 'f') { /* Footer information about a table */ PGresult *result = NULL; @@ -1892,7 +1878,7 @@ describeOneTableDetails(const char *schemaname, } /* print rules */ - if (tableinfo.hasrules) + if (tableinfo.hasrules && tableinfo.relkind != 'm') { if (pset.sversion >= 80300) { @@ -1987,6 +1973,45 @@ describeOneTableDetails(const char *schemaname, } } + if (view_def) + { + PGresult *result = NULL; + + /* Footer information about a view */ + printTableAddFooter(&cont, _("View definition:")); + printTableAddFooter(&cont, view_def); + + /* print rules */ + if (tableinfo.hasrules) + { + printfPQExpBuffer(&buf, + "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" + "FROM pg_catalog.pg_rewrite r\n" + "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", + oid); + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + + if (PQntuples(result) > 0) + { + printTableAddFooter(&cont, _("Rules:")); + for (i = 0; i < PQntuples(result); i++) + { + const char *ruledef; + + /* Everything after "CREATE RULE" is echoed verbatim */ + ruledef = PQgetvalue(result, i, 1); + ruledef += 12; + + printfPQExpBuffer(&buf, " %s", ruledef); + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + } + } + /* * Print triggers next, if any (but only user-defined triggers). This * could apply to either a table or a view. @@ -2110,7 +2135,8 @@ describeOneTableDetails(const char *schemaname, /* * Finish printing the footer information about a table. */ - if (tableinfo.relkind == 'r' || tableinfo.relkind == 'f') + if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || + tableinfo.relkind == 'f') { PGresult *result; int tuples; @@ -2235,8 +2261,8 @@ describeOneTableDetails(const char *schemaname, printTableAddFooter(&cont, buf.data); } - /* OIDs, if verbose */ - if (verbose) + /* OIDs, if verbose and not a materialized view */ + if (verbose && tableinfo.relkind != 'm') { const char *s = _("Has OIDs"); @@ -2307,7 +2333,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind, Oid tablespace, const bool newline) { /* relkinds for which we support tablespaces */ - if (relkind == 'r' || relkind == 'i') + if (relkind == 'r' || relkind == 'm' || relkind == 'i') { /* * We ignore the database default tablespace so that users not using @@ -2589,6 +2615,7 @@ listDbRoleSettings(const char *pattern, const char *pattern2) * t - tables * i - indexes * v - views + * m - materialized views * s - sequences * E - foreign table (Note: different from 'f', the relkind value) * (any order of the above is fine) @@ -2600,6 +2627,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys bool showTables = strchr(tabtypes, 't') != NULL; bool showIndexes = strchr(tabtypes, 'i') != NULL; bool showViews = strchr(tabtypes, 'v') != NULL; + bool showMatViews = strchr(tabtypes, 'm') != NULL; bool showSeq = strchr(tabtypes, 's') != NULL; bool showForeign = strchr(tabtypes, 'E') != NULL; @@ -2608,8 +2636,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys printQueryOpt myopt = pset.popt; static const bool translate_columns[] = {false, false, true, false, false, false, false}; - if (!(showTables || showIndexes || showViews || showSeq || showForeign)) - showTables = showViews = showSeq = showForeign = true; + if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign)) + showTables = showViews = showMatViews = showSeq = showForeign = true; initPQExpBuffer(&buf); @@ -2620,12 +2648,21 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys printfPQExpBuffer(&buf, "SELECT n.nspname as \"%s\",\n" " c.relname as \"%s\",\n" - " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' WHEN 'f' THEN '%s' END as \"%s\",\n" + " CASE c.relkind" + " WHEN 'r' THEN '%s'" + " WHEN 'v' THEN '%s'" + " WHEN 'm' THEN '%s'" + " WHEN 'i' THEN '%s'" + " WHEN 'S' THEN '%s'" + " WHEN 's' THEN '%s'" + " WHEN 'f' THEN '%s'" + " END as \"%s\",\n" " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", gettext_noop("Schema"), gettext_noop("Name"), gettext_noop("table"), gettext_noop("view"), + gettext_noop("materialized view"), gettext_noop("index"), gettext_noop("sequence"), gettext_noop("special"), @@ -2671,6 +2708,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys appendPQExpBuffer(&buf, "'r',"); if (showViews) appendPQExpBuffer(&buf, "'v',"); + if (showMatViews) + appendPQExpBuffer(&buf, "'m',"); if (showIndexes) appendPQExpBuffer(&buf, "'i',"); if (showSeq) diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 43cb550bd2..819a20f18d 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -221,6 +221,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); + fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n")); fprintf(output, _(" \\do[S] [PATTERN] list operators\n")); fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index edfba67766..d2170308f7 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -435,11 +435,11 @@ static const SchemaQuery Query_for_list_of_relations = { NULL }; -static const SchemaQuery Query_for_list_of_tsvf = { +static const SchemaQuery Query_for_list_of_tsvmf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ - "c.relkind IN ('r', 'S', 'v', 'f')", + "c.relkind IN ('r', 'S', 'v', 'm', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ @@ -450,11 +450,26 @@ static const SchemaQuery Query_for_list_of_tsvf = { NULL }; -static const SchemaQuery Query_for_list_of_tf = { +static const SchemaQuery Query_for_list_of_tmf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ - "c.relkind IN ('r', 'f')", + "c.relkind IN ('r', 'm', 'f')", + /* viscondition */ + "pg_catalog.pg_table_is_visible(c.oid)", + /* namespace */ + "c.relnamespace", + /* result */ + "pg_catalog.quote_ident(c.relname)", + /* qualresult */ + NULL +}; + +static const SchemaQuery Query_for_list_of_tm = { + /* catname */ + "pg_catalog.pg_class c", + /* selcondition */ + "c.relkind IN ('r', 'm')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ @@ -480,6 +495,21 @@ static const SchemaQuery Query_for_list_of_views = { NULL }; +static const SchemaQuery Query_for_list_of_matviews = { + /* catname */ + "pg_catalog.pg_class c", + /* selcondition */ + "c.relkind IN ('m')", + /* viscondition */ + "pg_catalog.pg_table_is_visible(c.oid)", + /* namespace */ + "c.relnamespace", + /* result */ + "pg_catalog.quote_ident(c.relname)", + /* qualresult */ + NULL +}; + /* * Queries to get lists of names of various kinds of things, possibly @@ -752,6 +782,7 @@ static const pgsql_thing_t words_after_create[] = { {"GROUP", Query_for_list_of_roles}, {"LANGUAGE", Query_for_list_of_languages}, {"INDEX", NULL, &Query_for_list_of_indexes}, + {"MATERIALIZED VIEW", NULL, NULL}, {"OPERATOR", NULL, NULL}, /* Querying for this is probably not such a * good idea. */ {"OWNED", NULL, NULL, THING_NO_CREATE}, /* for DROP OWNED BY ... */ @@ -853,7 +884,7 @@ psql_completion(char *text, int start, int end) "COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE", - "REASSIGN", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", + "REASSIGN", "REFRESH", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", "TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", "WITH", NULL @@ -933,7 +964,7 @@ psql_completion(char *text, int start, int end) static const char *const list_ALTER[] = {"AGGREGATE", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION", - "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "OPERATOR", + "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "MATERIALIZED VIEW", "OPERATOR", "ROLE", "RULE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE", "USER", "USER MAPPING FOR", "VIEW", NULL}; @@ -1102,6 +1133,14 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_ALTERLARGEOBJECT); } + /* ALTER MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + { + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + } + /* ALTER USER,ROLE <name> */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && !(pg_strcasecmp(prev2_wd, "USER") == 0 && pg_strcasecmp(prev_wd, "MAPPING") == 0) && @@ -1268,6 +1307,16 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_ALTERVIEW); } + /* ALTER MATERIALIZED VIEW <name> */ + else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 && + pg_strcasecmp(prev3_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + { + static const char *const list_ALTERMATVIEW[] = + {"ALTER COLUMN", "OWNER TO", "RENAME TO", "SET SCHEMA", NULL}; + + COMPLETE_WITH_LIST(list_ALTERMATVIEW); + } /* ALTER RULE <name>, add ON */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && @@ -1746,14 +1795,14 @@ psql_completion(char *text, int start, int end) */ else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 && pg_strcasecmp(prev2_wd, "WITHOUT") != 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "UNION SELECT 'VERBOSE'"); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, "UNION SELECT 'VERBOSE'"); /* * If the previous words are CLUSTER VERBOSE produce list of tables */ else if (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "CLUSTER") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* If we have CLUSTER <sth>, then add "USING" */ else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 && @@ -1800,7 +1849,7 @@ psql_completion(char *text, int start, int end) {"CAST", "COLLATION", "CONVERSION", "DATABASE", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "SERVER", "INDEX", "LANGUAGE", "RULE", "SCHEMA", "SEQUENCE", - "TABLE", "TYPE", "VIEW", "COLUMN", "AGGREGATE", "FUNCTION", + "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE", NULL}; @@ -1845,6 +1894,13 @@ psql_completion(char *text, int start, int end) completion_info_charp = prev2_wd; COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_constraint); } + else if (pg_strcasecmp(prev4_wd, "COMMENT") == 0 && + pg_strcasecmp(prev3_wd, "ON") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + { + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + } else if ((pg_strcasecmp(prev4_wd, "COMMENT") == 0 && pg_strcasecmp(prev3_wd, "ON") == 0) || (pg_strcasecmp(prev5_wd, "COMMENT") == 0 && @@ -1974,7 +2030,7 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev2_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "CONCURRENTLY") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* If we have CREATE|UNIQUE INDEX <sth> CONCURRENTLY, then add "ON" */ else if ((pg_strcasecmp(prev3_wd, "INDEX") == 0 || pg_strcasecmp(prev2_wd, "INDEX") == 0) && @@ -2080,7 +2136,10 @@ psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && pg_strcasecmp(prev_wd, "UNLOGGED") == 0) { - COMPLETE_WITH_CONST("TABLE"); + static const char *const list_UNLOGGED[] = + {"TABLE", "MATERIALIZED VIEW", NULL}; + + COMPLETE_WITH_LIST(list_UNLOGGED); } /* CREATE TABLESPACE */ @@ -2249,6 +2308,22 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "AS") == 0) COMPLETE_WITH_CONST("SELECT"); +/* CREATE MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && + pg_strcasecmp(prev_wd, "MATERIALIZED") == 0) + COMPLETE_WITH_CONST("VIEW"); + /* Complete CREATE MATERIALIZED VIEW <name> with AS */ + else if (pg_strcasecmp(prev4_wd, "CREATE") == 0 && + pg_strcasecmp(prev3_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + COMPLETE_WITH_CONST("AS"); + /* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */ + else if (pg_strcasecmp(prev5_wd, "CREATE") == 0 && + pg_strcasecmp(prev4_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev3_wd, "VIEW") == 0 && + pg_strcasecmp(prev_wd, "AS") == 0) + COMPLETE_WITH_CONST("SELECT"); + /* DECLARE */ else if (pg_strcasecmp(prev2_wd, "DECLARE") == 0) { @@ -2375,6 +2450,20 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(drop_CREATE_FOREIGN); } + + /* DROP MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev2_wd, "DROP") == 0 && + pg_strcasecmp(prev_wd, "MATERIALIZED") == 0) + { + COMPLETE_WITH_CONST("VIEW"); + } + else if (pg_strcasecmp(prev3_wd, "DROP") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + { + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + } + else if (pg_strcasecmp(prev4_wd, "DROP") == 0 && (pg_strcasecmp(prev3_wd, "AGGREGATE") == 0 || pg_strcasecmp(prev3_wd, "FUNCTION") == 0) && @@ -2550,7 +2639,7 @@ psql_completion(char *text, int start, int end) else if ((pg_strcasecmp(prev3_wd, "GRANT") == 0 || pg_strcasecmp(prev3_wd, "REVOKE") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, " UNION SELECT 'DATABASE'" " UNION SELECT 'DOMAIN'" " UNION SELECT 'FOREIGN DATA WRAPPER'" @@ -2769,6 +2858,37 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev5_wd, "REASSIGN") == 0) COMPLETE_WITH_QUERY(Query_for_list_of_roles); +/* REFRESH MATERIALIZED VIEW */ + else if (pg_strcasecmp(prev_wd, "REFRESH") == 0) + COMPLETE_WITH_CONST("MATERIALIZED VIEW"); + else if (pg_strcasecmp(prev2_wd, "REFRESH") == 0 && + pg_strcasecmp(prev_wd, "MATERIALIZED") == 0) + COMPLETE_WITH_CONST("VIEW"); + else if (pg_strcasecmp(prev3_wd, "REFRESH") == 0 && + pg_strcasecmp(prev2_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev_wd, "VIEW") == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); + else if (pg_strcasecmp(prev4_wd, "REFRESH") == 0 && + pg_strcasecmp(prev3_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + COMPLETE_WITH_CONST("WITH"); + else if (pg_strcasecmp(prev5_wd, "REFRESH") == 0 && + pg_strcasecmp(prev4_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev3_wd, "VIEW") == 0 && + pg_strcasecmp(prev_wd, "WITH") == 0) + { + static const char *const list_WITH_DATA[] = + {"NO DATA", "DATA", NULL}; + + COMPLETE_WITH_LIST(list_WITH_DATA); + } + else if (pg_strcasecmp(prev6_wd, "REFRESH") == 0 && + pg_strcasecmp(prev5_wd, "MATERIALIZED") == 0 && + pg_strcasecmp(prev4_wd, "VIEW") == 0 && + pg_strcasecmp(prev2_wd, "WITH") == 0 && + pg_strcasecmp(prev_wd, "NO") == 0) + COMPLETE_WITH_CONST("DATA"); + /* REINDEX */ else if (pg_strcasecmp(prev_wd, "REINDEX") == 0) { @@ -2780,7 +2900,7 @@ psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "REINDEX") == 0) { if (pg_strcasecmp(prev_wd, "TABLE") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); else if (pg_strcasecmp(prev_wd, "INDEX") == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL); else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 || @@ -2812,9 +2932,9 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "ON") == 0)) { static const char *const list_SECURITY_LABEL[] = - {"LANGUAGE", "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", "COLUMN", - "AGGREGATE", "FUNCTION", "DOMAIN", "LARGE OBJECT", - NULL}; + {"LANGUAGE", "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", + "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "DOMAIN", + "LARGE OBJECT", NULL}; COMPLETE_WITH_LIST(list_SECURITY_LABEL); } @@ -3061,7 +3181,7 @@ psql_completion(char *text, int start, int end) * VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] */ else if (pg_strcasecmp(prev_wd, "VACUUM") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'FULL'" " UNION SELECT 'FREEZE'" " UNION SELECT 'ANALYZE'" @@ -3069,34 +3189,34 @@ psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && (pg_strcasecmp(prev_wd, "FULL") == 0 || pg_strcasecmp(prev_wd, "FREEZE") == 0)) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'" " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'VERBOSE'"); else if (pg_strcasecmp(prev3_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0 && (pg_strcasecmp(prev2_wd, "FULL") == 0 || pg_strcasecmp(prev2_wd, "FREEZE") == 0)) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "VERBOSE") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'ANALYZE'"); else if (pg_strcasecmp(prev2_wd, "VACUUM") == 0 && pg_strcasecmp(prev_wd, "ANALYZE") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, " UNION SELECT 'VERBOSE'"); else if ((pg_strcasecmp(prev_wd, "ANALYZE") == 0 && pg_strcasecmp(prev2_wd, "VERBOSE") == 0) || (pg_strcasecmp(prev_wd, "VERBOSE") == 0 && pg_strcasecmp(prev2_wd, "ANALYZE") == 0)) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL); /* WITH [RECURSIVE] */ @@ -3111,7 +3231,7 @@ psql_completion(char *text, int start, int end) /* ANALYZE */ /* If the previous word is ANALYZE, produce list of tables */ else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tf, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tmf, NULL); /* WHERE */ /* Simple case of the word before the where being the table name */ @@ -3123,11 +3243,11 @@ psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev_wd, "FROM") == 0 && pg_strcasecmp(prev3_wd, "COPY") != 0 && pg_strcasecmp(prev3_wd, "\\copy") != 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); /* ... JOIN ... */ else if (pg_strcasecmp(prev_wd, "JOIN") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); /* Backslash commands */ /* TODO: \dc \dd \dl */ @@ -3167,7 +3287,7 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (strncmp(prev_wd, "\\dp", strlen("\\dp")) == 0 || strncmp(prev_wd, "\\z", strlen("\\z")) == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL); else if (strncmp(prev_wd, "\\ds", strlen("\\ds")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL); else if (strncmp(prev_wd, "\\dt", strlen("\\dt")) == 0) @@ -3179,6 +3299,8 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_roles); else if (strncmp(prev_wd, "\\dv", strlen("\\dv")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL); + else if (strncmp(prev_wd, "\\dm", strlen("\\dm")) == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); /* must be at end of \d list */ else if (strncmp(prev_wd, "\\d", strlen("\\d")) == 0) diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 3a1788d792..989089a25a 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -70,6 +70,7 @@ extern Oid heap_create_with_catalog(const char *relname, bool is_internal); extern void heap_create_init_fork(Relation rel); +extern bool heap_is_matview_init_state(Relation rel); extern void heap_drop_with_catalog(Oid relid); diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 820552f013..fd97141e9e 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -153,6 +153,7 @@ DESCR(""); #define RELKIND_VIEW 'v' /* view */ #define RELKIND_COMPOSITE_TYPE 'c' /* composite type */ #define RELKIND_FOREIGN_TABLE 'f' /* foreign table */ +#define RELKIND_MATVIEW 'm' /* materialized view */ #define RELPERSISTENCE_PERMANENT 'p' /* regular table */ #define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index d9f50d22d2..0e26ebf219 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -1980,6 +1980,8 @@ DATA(insert OID = 3842 ( pg_view_is_insertable PGNSP PGUID 12 10 0 0 0 f f f f DESCR("is a view insertable-into"); DATA(insert OID = 3843 ( pg_view_is_updatable PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_view_is_updatable _null_ _null_ _null_ )); DESCR("is a view updatable"); +DATA(insert OID = 3846 ( pg_relation_is_scannable PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_relation_is_scannable _null_ _null_ _null_ )); +DESCR("is a relation scannable"); /* Deferrable unique constraint trigger */ DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h index 2ac718762b..012334b42e 100644 --- a/src/include/commands/createas.h +++ b/src/include/commands/createas.h @@ -19,6 +19,10 @@ #include "tcop/dest.h" +extern Query *SetupForCreateTableAs(Query *query, IntoClause *into, + const char *queryString, + ParamListInfo params, DestReceiver *dest); + extern void ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString, ParamListInfo params, char *completionTag); diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index ca213d7f70..24ef493115 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -67,8 +67,8 @@ extern void ExplainOneUtility(Node *utilityStmt, IntoClause *into, const char *queryString, ParamListInfo params); extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, - ExplainState *es, - const char *queryString, ParamListInfo params); + ExplainState *es, const char *queryString, + DestReceiver *dest, ParamListInfo params); extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc); diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h new file mode 100644 index 0000000000..6de3c1aa93 --- /dev/null +++ b/src/include/commands/matview.h @@ -0,0 +1,28 @@ +/*------------------------------------------------------------------------- + * + * matview.h + * prototypes for matview.c. + * + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/commands/matview.h + * + *------------------------------------------------------------------------- + */ +#ifndef MATVIEW_H +#define MATVIEW_H + +#include "nodes/params.h" +#include "tcop/dest.h" +#include "utils/relcache.h" + +extern void SetRelationIsScannable(Relation relation); + +extern void ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, + ParamListInfo params, char *completionTag); + +extern DestReceiver *CreateTransientRelDestReceiver(Oid oid); + +#endif /* MATVIEW_H */ diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 27dc5e8ebb..031c77c9ef 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -78,4 +78,6 @@ extern void AtEOSubXact_on_commit_actions(bool isCommit, extern void RangeVarCallbackOwnsTable(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); +extern bool isQueryUsingTempRelation(Query *query); + #endif /* TABLECMDS_H */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index ff80ed6a44..972c7d208c 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -18,4 +18,6 @@ extern Oid DefineView(ViewStmt *stmt, const char *queryString); +extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); + #endif /* VIEW_H */ diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index b1213a0635..aec6c7f7df 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -61,6 +61,7 @@ #define EXEC_FLAG_SKIP_TRIGGERS 0x0010 /* skip AfterTrigger calls */ #define EXEC_FLAG_WITH_OIDS 0x0020 /* force OIDs in returned tuples */ #define EXEC_FLAG_WITHOUT_OIDS 0x0040 /* force no OIDs in returned tuples */ +#define EXEC_FLAG_WITH_NO_DATA 0x0080 /* rel scannability doesn't matter */ /* diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 0854579fdf..0d5c007f93 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -361,6 +361,7 @@ typedef enum NodeTag T_AlterExtensionContentsStmt, T_CreateEventTrigStmt, T_AlterEventTrigStmt, + T_RefreshMatViewStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index d54990d39c..2229ef0f95 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -713,6 +713,7 @@ typedef struct RangeTblEntry */ Oid relid; /* OID of the relation */ char relkind; /* relation kind (see pg_class.relkind) */ + bool isResultRel; /* used in target of SELECT INTO or similar */ /* * Fields valid for a subquery RTE (else NULL): @@ -1135,6 +1136,7 @@ typedef enum ObjectType OBJECT_INDEX, OBJECT_LANGUAGE, OBJECT_LARGEOBJECT, + OBJECT_MATVIEW, OBJECT_OPCLASS, OBJECT_OPERATOR, OBJECT_OPFAMILY, @@ -2447,6 +2449,8 @@ typedef struct ExplainStmt * A query written as CREATE TABLE AS will produce this node type natively. * A query written as SELECT ... INTO will be transformed to this form during * parse analysis. + * A query written as CREATE MATERIALIZED view will produce this node type, + * during parse analysis, since it needs all the same data. * * The "query" field is handled similarly to EXPLAIN, though note that it * can be a SELECT or an EXECUTE, but not other DML statements. @@ -2457,10 +2461,22 @@ typedef struct CreateTableAsStmt NodeTag type; Node *query; /* the query (see comments above) */ IntoClause *into; /* destination table */ + ObjectType relkind; /* type of object */ bool is_select_into; /* it was written as SELECT INTO */ } CreateTableAsStmt; /* ---------------------- + * REFRESH MATERIALIZED VIEW Statement + * ---------------------- + */ +typedef struct RefreshMatViewStmt +{ + NodeTag type; + bool skipData; /* true for WITH NO DATA */ + RangeVar *relation; /* relation to insert into */ +} RefreshMatViewStmt; + +/* ---------------------- * Checkpoint Statement * ---------------------- */ @@ -2517,7 +2533,7 @@ typedef struct ConstraintsSetStmt typedef struct ReindexStmt { NodeTag type; - ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */ + ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, etc. */ RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ bool do_system; /* include system tables in database case */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 1d657669e1..27d4e4cd67 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -80,7 +80,8 @@ typedef struct RangeVar } RangeVar; /* - * IntoClause - target information for SELECT INTO and CREATE TABLE AS + * IntoClause - target information for SELECT INTO, CREATE TABLE AS, and + * CREATE MATERIALIZED VIEW */ typedef struct IntoClause { @@ -92,6 +93,7 @@ typedef struct IntoClause OnCommitAction onCommit; /* what do we do at COMMIT? */ char *tableSpaceName; /* table space to use, or NULL */ bool skipData; /* true for WITH NO DATA */ + char relkind; /* RELKIND_RELATION or RELKIND_MATVIEW */ } IntoClause; diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 6f67a65f3d..68a13b7a7b 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -232,6 +232,7 @@ PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD) PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD) PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD) PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD) +PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD) PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD) @@ -302,6 +303,7 @@ PG_KEYWORD("recheck", RECHECK, UNRESERVED_KEYWORD) PG_KEYWORD("recursive", RECURSIVE, UNRESERVED_KEYWORD) PG_KEYWORD("ref", REF, UNRESERVED_KEYWORD) PG_KEYWORD("references", REFERENCES, RESERVED_KEYWORD) +PG_KEYWORD("refresh", REFRESH, UNRESERVED_KEYWORD) PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD) PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD) PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD) diff --git a/src/include/tcop/dest.h b/src/include/tcop/dest.h index 6e260bdfa2..79002184be 100644 --- a/src/include/tcop/dest.h +++ b/src/include/tcop/dest.h @@ -93,7 +93,8 @@ typedef enum DestTuplestore, /* results sent to Tuplestore */ DestIntoRel, /* results sent to relation (SELECT INTO) */ DestCopyOut, /* results sent to COPY TO code */ - DestSQLFunction /* results sent to SQL-language func mgr */ + DestSQLFunction, /* results sent to SQL-language func mgr */ + DestTransientRel /* results sent to transient relation */ } CommandDest; /* ---------------- diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 533539ca29..c0debe400c 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -461,6 +461,7 @@ extern Datum pg_table_size(PG_FUNCTION_ARGS); extern Datum pg_indexes_size(PG_FUNCTION_ARGS); extern Datum pg_relation_filenode(PG_FUNCTION_ARGS); extern Datum pg_relation_filepath(PG_FUNCTION_ARGS); +extern Datum pg_relation_is_scannable(PG_FUNCTION_ARGS); /* genfile.c */ extern bytea *read_binary_file(const char *filename, diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index c342eaa66f..06e1531e9a 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -83,6 +83,7 @@ typedef struct RelationData BackendId rd_backend; /* owning backend id, if temporary relation */ bool rd_islocaltemp; /* rel is a temp rel of this session */ bool rd_isnailed; /* rel is nailed in cache */ + bool rd_isscannable; /* rel can be scanned */ bool rd_isvalid; /* relcache entry is valid */ char rd_indexvalid; /* state of rd_indexlist: 0 = not valid, 1 = * valid, 2 = temporarily forced */ diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index f093037741..d2e832fa67 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -1760,11 +1760,13 @@ plpgsql_parse_cwordtype(List *idents) classStruct = (Form_pg_class) GETSTRUCT(classtup); /* - * It must be a relation, sequence, view, composite type, or foreign table + * It must be a relation, sequence, view, materialized view, composite + * type, or foreign table */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && + classStruct->relkind != RELKIND_MATVIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) goto done; @@ -1982,10 +1984,14 @@ build_row_from_class(Oid classOid) classStruct = RelationGetForm(rel); relname = RelationGetRelationName(rel); - /* accept relation, sequence, view, composite type, or foreign table */ + /* + * Accept relation, sequence, view, materialized view, composite type, or + * foreign table. + */ if (classStruct->relkind != RELKIND_RELATION && classStruct->relkind != RELKIND_SEQUENCE && classStruct->relkind != RELKIND_VIEW && + classStruct->relkind != RELKIND_MATVIEW && classStruct->relkind != RELKIND_COMPOSITE_TYPE && classStruct->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index c4d5fa1700..4cd59bc3ea 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -506,6 +506,7 @@ pltcl_init_load_unknown(Tcl_Interp *interp) return; /* must be table or view, else ignore */ if (!(pmrel->rd_rel->relkind == RELKIND_RELATION || + pmrel->rd_rel->relkind == RELKIND_MATVIEW || pmrel->rd_rel->relkind == RELKIND_VIEW)) { relation_close(pmrel, AccessShareLock); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out new file mode 100644 index 0000000000..1077651b8e --- /dev/null +++ b/src/test/regress/expected/matview.out @@ -0,0 +1,406 @@ +-- create a table to use as a basis for views and materialized views in various combinations +CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); +INSERT INTO t VALUES + (1, 'x', 2), + (2, 'x', 3), + (3, 'y', 5), + (4, 'y', 7), + (5, 'z', 11); +-- we want a view based on the table, too, since views present additional challenges +CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; +SELECT * FROM tv; + type | totamt +------+-------- + y | 12 + z | 11 + x | 5 +(3 rows) + +-- create a materialized view with no data, and confirm correct behavior +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; + QUERY PLAN +--------------------- + HashAggregate + -> Seq Scan on t +(2 rows) + +CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; +SELECT pg_relation_is_scannable('tm'::regclass); + pg_relation_is_scannable +-------------------------- + f +(1 row) + +SELECT * FROM tm; +ERROR: materialized view "tm" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW tm; +SELECT pg_relation_is_scannable('tm'::regclass); + pg_relation_is_scannable +-------------------------- + t +(1 row) + +CREATE UNIQUE INDEX tm_type ON tm (type); +SELECT * FROM tm; + type | totamt +------+-------- + y | 12 + z | 11 + x | 5 +(3 rows) + +-- create various views +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; + QUERY PLAN +--------------------- + HashAggregate + -> Seq Scan on t +(2 rows) + +CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; +SELECT * FROM tvm; + type | totamt +------+-------- + y | 12 + z | 11 + x | 5 +(3 rows) + +CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; +CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm; +CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv; +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; + QUERY PLAN +--------------------------- + Aggregate + -> HashAggregate + -> Seq Scan on t +(3 rows) + +CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; +CREATE VIEW tvvmv AS SELECT * FROM tvvm; +CREATE MATERIALIZED VIEW bb AS SELECT * FROM tvvmv; +CREATE INDEX aa ON bb (grandtot); +-- check that plans seem reasonable +\d+ tvm + Materialized view "public.tvm" + Column | Type | Modifiers | Storage | Stats target | Description +--------+---------+-----------+----------+--------------+------------- + type | text | | extended | | + totamt | numeric | | main | | +View definition: + SELECT tv.type, + tv.totamt + FROM tv; + +\d+ tvm + Materialized view "public.tvm" + Column | Type | Modifiers | Storage | Stats target | Description +--------+---------+-----------+----------+--------------+------------- + type | text | | extended | | + totamt | numeric | | main | | +View definition: + SELECT tv.type, + tv.totamt + FROM tv; + +\d+ tvvm + Materialized view "public.tvvm" + Column | Type | Modifiers | Storage | Stats target | Description +----------+---------+-----------+---------+--------------+------------- + grandtot | numeric | | main | | +View definition: + SELECT tvv.grandtot + FROM tvv; + +\d+ bb + Materialized view "public.bb" + Column | Type | Modifiers | Storage | Stats target | Description +----------+---------+-----------+---------+--------------+------------- + grandtot | numeric | | main | | +Indexes: + "aa" btree (grandtot) +View definition: + SELECT tvvmv.grandtot + FROM tvvmv; + +-- test schema behavior +CREATE SCHEMA mvschema; +ALTER MATERIALIZED VIEW tvm SET SCHEMA mvschema; +\d+ tvm +\d+ tvmm + Materialized view "public.tvmm" + Column | Type | Modifiers | Storage | Stats target | Description +----------+---------+-----------+---------+--------------+------------- + grandtot | numeric | | main | | +View definition: + SELECT sum(tvm.totamt) AS grandtot + FROM mvschema.tvm; + +SET search_path = mvschema, public; +\d+ tvm + Materialized view "mvschema.tvm" + Column | Type | Modifiers | Storage | Stats target | Description +--------+---------+-----------+----------+--------------+------------- + type | text | | extended | | + totamt | numeric | | main | | +View definition: + SELECT tv.type, + tv.totamt + FROM tv; + +-- modify the underlying table data +INSERT INTO t VALUES (6, 'z', 13); +-- confirm pre- and post-refresh contents of fairly simple materialized views +SELECT * FROM tm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +SELECT * FROM tvm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +REFRESH MATERIALIZED VIEW tm; +REFRESH MATERIALIZED VIEW tvm; +SELECT * FROM tm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 24 +(3 rows) + +SELECT * FROM tvm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 24 +(3 rows) + +RESET search_path; +-- confirm pre- and post-refresh contents of nested materialized views +EXPLAIN (costs off) + SELECT * FROM tmm; + QUERY PLAN +----------------- + Seq Scan on tmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM tvmm; + QUERY PLAN +------------------ + Seq Scan on tvmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM tvvm; + QUERY PLAN +------------------ + Seq Scan on tvvm +(1 row) + +SELECT * FROM tmm; + grandtot +---------- + 28 +(1 row) + +SELECT * FROM tvmm; + grandtot +---------- + 28 +(1 row) + +SELECT * FROM tvvm; + grandtot +---------- + 28 +(1 row) + +REFRESH MATERIALIZED VIEW tmm; +REFRESH MATERIALIZED VIEW tvmm; +REFRESH MATERIALIZED VIEW tvvm; +EXPLAIN (costs off) + SELECT * FROM tmm; + QUERY PLAN +----------------- + Seq Scan on tmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM tvmm; + QUERY PLAN +------------------ + Seq Scan on tvmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM tvvm; + QUERY PLAN +------------------ + Seq Scan on tvvm +(1 row) + +SELECT * FROM tmm; + grandtot +---------- + 41 +(1 row) + +SELECT * FROM tvmm; + grandtot +---------- + 41 +(1 row) + +SELECT * FROM tvvm; + grandtot +---------- + 41 +(1 row) + +-- test diemv when the mv does not exist +DROP MATERIALIZED VIEW IF EXISTS tum; +NOTICE: materialized view "tum" does not exist, skipping +-- make sure that an unlogged materialized view works (in the absence of a crash) +CREATE UNLOGGED MATERIALIZED VIEW tum AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; +SELECT pg_relation_is_scannable('tum'::regclass); + pg_relation_is_scannable +-------------------------- + f +(1 row) + +SELECT * FROM tum; +ERROR: materialized view "tum" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW tum; +SELECT pg_relation_is_scannable('tum'::regclass); + pg_relation_is_scannable +-------------------------- + t +(1 row) + +SELECT * FROM tum; + type | totamt +------+-------- + y | 12 + z | 24 + x | 5 +(3 rows) + +REFRESH MATERIALIZED VIEW tum WITH NO DATA; +SELECT pg_relation_is_scannable('tum'::regclass); + pg_relation_is_scannable +-------------------------- + f +(1 row) + +SELECT * FROM tum; +ERROR: materialized view "tum" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW tum WITH DATA; +SELECT pg_relation_is_scannable('tum'::regclass); + pg_relation_is_scannable +-------------------------- + t +(1 row) + +SELECT * FROM tum; + type | totamt +------+-------- + y | 12 + z | 24 + x | 5 +(3 rows) + +-- test diemv when the mv does exist +DROP MATERIALIZED VIEW IF EXISTS tum; +-- make sure that dependencies are reported properly when they block the drop +DROP TABLE t; +ERROR: cannot drop table t because other objects depend on it +DETAIL: view tv depends on table t +view tvv depends on view tv +materialized view tvvm depends on view tvv +view tvvmv depends on materialized view tvvm +materialized view bb depends on view tvvmv +materialized view mvschema.tvm depends on view tv +materialized view tvmm depends on materialized view mvschema.tvm +materialized view tm depends on table t +materialized view tmm depends on materialized view tm +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- make sure dependencies are dropped and reported +-- and make sure that transactional behavior is correct on rollback +-- incidentally leaving some interesting materialized views for pg_dump testing +BEGIN; +DROP TABLE t CASCADE; +NOTICE: drop cascades to 9 other objects +DETAIL: drop cascades to view tv +drop cascades to view tvv +drop cascades to materialized view tvvm +drop cascades to view tvvmv +drop cascades to materialized view bb +drop cascades to materialized view mvschema.tvm +drop cascades to materialized view tvmm +drop cascades to materialized view tm +drop cascades to materialized view tmm +ROLLBACK; +-- some additional tests not using base tables +CREATE VIEW v_test1 AS SELECT 1 moo; +CREATE VIEW v_test2 AS SELECT moo, 2*moo FROM v_test1 UNION ALL SELECT moo, 3*moo FROM v_test1; +\d+ v_test2 + View "public.v_test2" + Column | Type | Modifiers | Storage | Description +----------+---------+-----------+---------+------------- + moo | integer | | plain | + ?column? | integer | | plain | +View definition: + SELECT v_test1.moo, + 2 * v_test1.moo + FROM v_test1 +UNION ALL + SELECT v_test1.moo, + 3 * v_test1.moo + FROM v_test1; + +CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2; +\d+ mv_test2 + Materialized view "public.mv_test2" + Column | Type | Modifiers | Storage | Stats target | Description +----------+---------+-----------+---------+--------------+------------- + moo | integer | | plain | | + ?column? | integer | | plain | | +View definition: + SELECT v_test2.moo, + 2 * v_test2.moo + FROM v_test2 +UNION ALL + SELECT v_test2.moo, + 3 * v_test2.moo + FROM v_test2; + +CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345; +SELECT pg_relation_is_scannable('mv_test3'::regclass); + pg_relation_is_scannable +-------------------------- + t +(1 row) + +DROP VIEW v_test1 CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to view v_test2 +drop cascades to materialized view mv_test2 +drop cascades to materialized view mv_test3 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 6ba984a928..a4ecfd2aea 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1325,7 +1325,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | JOIN pg_class i ON ((i.oid = x.indexrelid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + | LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) + - | WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); + | WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, + | l.database, + | l.relation, + @@ -1342,6 +1342,17 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | l.granted, + | l.fastpath + | FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); + pg_matviews | SELECT n.nspname AS schemaname, + + | c.relname AS matviewname, + + | pg_get_userbyid(c.relowner) AS matviewowner, + + | t.spcname AS tablespace, + + | c.relhasindex AS hasindexes, + + | pg_relation_is_scannable(c.oid) AS isscannable, + + | pg_get_viewdef(c.oid) AS definition + + | FROM ((pg_class c + + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + + | LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) + + | WHERE (c.relkind = 'm'::"char"); pg_prepared_statements | SELECT p.name, + | p.statement, + | p.prepare_time, + @@ -1385,6 +1396,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | CASE + | WHEN (rel.relkind = 'r'::"char") THEN 'table'::text + | WHEN (rel.relkind = 'v'::"char") THEN 'view'::text + + | WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text + | WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text + | WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text + | ELSE NULL::text + @@ -1600,7 +1612,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | JOIN pg_index x ON ((c.oid = x.indrelid))) + | JOIN pg_class i ON ((i.oid = x.indexrelid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); pg_stat_all_tables | SELECT c.oid AS relid, + | n.nspname AS schemaname, + | c.relname, + @@ -1625,7 +1637,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | FROM ((pg_class c + | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, + | pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, + @@ -1774,7 +1786,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | FROM ((pg_class c + | LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname; pg_stat_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid, + | pg_stat_xact_all_tables.schemaname, + @@ -1822,7 +1834,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | JOIN pg_index x ON ((c.oid = x.indrelid))) + | JOIN pg_class i ON ((i.oid = x.indexrelid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); pg_statio_all_sequences | SELECT c.oid AS relid, + | n.nspname AS schemaname, + | c.relname, + @@ -1847,7 +1859,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) + | LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) + | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + - | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) + + | WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) + | GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, + | pg_statio_all_indexes.indexrelid, + @@ -2119,7 +2131,15 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem | emp.location, + | (12 * emp.salary) AS annualsal + | FROM emp; -(60 rows) + tv | SELECT t.type, + + | sum(t.amt) AS totamt + + | FROM t + + | GROUP BY t.type; + tvv | SELECT sum(tv.totamt) AS grandtot + + | FROM tv; + tvvmv | SELECT tvvm.grandtot + + | FROM tvvm; +(64 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index 4353d0b1e3..2dd5b2389e 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -588,6 +588,7 @@ SELECT user_relns() AS user_relns arrtest b b_star + bb box_tbl bprime bt_f8_heap @@ -671,6 +672,7 @@ SELECT user_relns() AS user_relns student subselect_tbl suffix_text_tbl + t tenk1 tenk2 test_range_excl @@ -683,10 +685,18 @@ SELECT user_relns() AS user_relns timestamptz_tbl timetz_tbl tinterval_tbl + tm + tmm toyemp + tv + tvm + tvmm + tvv + tvvm + tvvmv varchar_tbl xacttest -(108 rows) +(118 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index d3def07f92..2af28b1502 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- -test: privileges security_label collate +test: privileges security_label collate matview # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 7059fca092..d6eaa7aa4d 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -95,6 +95,7 @@ test: prepared_xacts test: privileges test: security_label test: collate +test: matview test: alter_generic test: misc test: psql diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql new file mode 100644 index 0000000000..e1c0e1583d --- /dev/null +++ b/src/test/regress/sql/matview.sql @@ -0,0 +1,128 @@ +-- create a table to use as a basis for views and materialized views in various combinations +CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); +INSERT INTO t VALUES + (1, 'x', 2), + (2, 'x', 3), + (3, 'y', 5), + (4, 'y', 7), + (5, 'z', 11); + +-- we want a view based on the table, too, since views present additional challenges +CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type; +SELECT * FROM tv; + +-- create a materialized view with no data, and confirm correct behavior +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; +CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; +SELECT pg_relation_is_scannable('tm'::regclass); +SELECT * FROM tm; +REFRESH MATERIALIZED VIEW tm; +SELECT pg_relation_is_scannable('tm'::regclass); +CREATE UNIQUE INDEX tm_type ON tm (type); +SELECT * FROM tm; + +-- create various views +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; +CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv; +SELECT * FROM tvm; +CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm; +CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm; +CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv; +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; +CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv; +CREATE VIEW tvvmv AS SELECT * FROM tvvm; +CREATE MATERIALIZED VIEW bb AS SELECT * FROM tvvmv; +CREATE INDEX aa ON bb (grandtot); + +-- check that plans seem reasonable +\d+ tvm +\d+ tvm +\d+ tvvm +\d+ bb + +-- test schema behavior +CREATE SCHEMA mvschema; +ALTER MATERIALIZED VIEW tvm SET SCHEMA mvschema; +\d+ tvm +\d+ tvmm +SET search_path = mvschema, public; +\d+ tvm + +-- modify the underlying table data +INSERT INTO t VALUES (6, 'z', 13); + +-- confirm pre- and post-refresh contents of fairly simple materialized views +SELECT * FROM tm ORDER BY type; +SELECT * FROM tvm ORDER BY type; +REFRESH MATERIALIZED VIEW tm; +REFRESH MATERIALIZED VIEW tvm; +SELECT * FROM tm ORDER BY type; +SELECT * FROM tvm ORDER BY type; +RESET search_path; + +-- confirm pre- and post-refresh contents of nested materialized views +EXPLAIN (costs off) + SELECT * FROM tmm; +EXPLAIN (costs off) + SELECT * FROM tvmm; +EXPLAIN (costs off) + SELECT * FROM tvvm; +SELECT * FROM tmm; +SELECT * FROM tvmm; +SELECT * FROM tvvm; +REFRESH MATERIALIZED VIEW tmm; +REFRESH MATERIALIZED VIEW tvmm; +REFRESH MATERIALIZED VIEW tvvm; +EXPLAIN (costs off) + SELECT * FROM tmm; +EXPLAIN (costs off) + SELECT * FROM tvmm; +EXPLAIN (costs off) + SELECT * FROM tvvm; +SELECT * FROM tmm; +SELECT * FROM tvmm; +SELECT * FROM tvvm; + +-- test diemv when the mv does not exist +DROP MATERIALIZED VIEW IF EXISTS tum; + +-- make sure that an unlogged materialized view works (in the absence of a crash) +CREATE UNLOGGED MATERIALIZED VIEW tum AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA; +SELECT pg_relation_is_scannable('tum'::regclass); +SELECT * FROM tum; +REFRESH MATERIALIZED VIEW tum; +SELECT pg_relation_is_scannable('tum'::regclass); +SELECT * FROM tum; +REFRESH MATERIALIZED VIEW tum WITH NO DATA; +SELECT pg_relation_is_scannable('tum'::regclass); +SELECT * FROM tum; +REFRESH MATERIALIZED VIEW tum WITH DATA; +SELECT pg_relation_is_scannable('tum'::regclass); +SELECT * FROM tum; + +-- test diemv when the mv does exist +DROP MATERIALIZED VIEW IF EXISTS tum; + +-- make sure that dependencies are reported properly when they block the drop +DROP TABLE t; + +-- make sure dependencies are dropped and reported +-- and make sure that transactional behavior is correct on rollback +-- incidentally leaving some interesting materialized views for pg_dump testing +BEGIN; +DROP TABLE t CASCADE; +ROLLBACK; + +-- some additional tests not using base tables +CREATE VIEW v_test1 AS SELECT 1 moo; +CREATE VIEW v_test2 AS SELECT moo, 2*moo FROM v_test1 UNION ALL SELECT moo, 3*moo FROM v_test1; +\d+ v_test2 +CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2; +\d+ mv_test2 +CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345; +SELECT pg_relation_is_scannable('mv_test3'::regclass); + +DROP VIEW v_test1 CASCADE; |