diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 23 | ||||
| -rw-r--r-- | doc/src/sgml/ref/allfiles.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_extension.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_materialized_view.sgml | 167 | ||||
| -rw-r--r-- | doc/src/sgml/ref/comment.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_index.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_materialized_view.sgml | 154 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table_as.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/ref/drop_materialized_view.sgml | 114 | ||||
| -rw-r--r-- | doc/src/sgml/ref/refresh_materialized_view.sgml | 113 | ||||
| -rw-r--r-- | doc/src/sgml/ref/security_label.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/reference.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/rules.sgml | 200 |
15 files changed, 785 insertions, 13 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9144eec6747..81c1be3567f 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 92a79d350a4..9b7e9677581 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 c61c62f2286..5846974feb9 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 60bc747269c..2dbba0c0bbb 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 00000000000..b60451374b5 --- /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 a03f15cd569..e94dd4b8ded 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 d800701ff4e..01faa3afcf7 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 00000000000..ed3bb4d3ae5 --- /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 9739417a709..29c80405bf1 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 0745e3cdb59..aa3fc1515a3 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 00000000000..80d8acea36d --- /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 00000000000..44cff9c98e7 --- /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 d946b92e19d..52cb1d16f4c 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 5b0c7745e39..14e217a907c 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 5811de7942f..68a0f53445f 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> |