Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

This allows reads to continue without any blocking while a REFRESH runs. The new data appears atomically as part of transaction commit. Review questioned the Assert that a matview was not a system relation. This will be addressed separately. Reviewed by Hitoshi Harada, Robert Haas, Andres Freund. Merged after review with security patch f3ab5d4.
tulshiba · Jul 16, 2013 · cc1965a · cc1965a
1 parent 7f7485a
commit cc1965a
Show file tree

Hide file tree

Showing 16 changed files with 646 additions and 59 deletions.
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
@@ -928,8 +928,7 @@ ERROR:  could not serialize access due to read/write dependencies among transact
         </para>
 
         <para>
-         This lock mode is not automatically acquired on tables by any
-         <productname>PostgreSQL</productname> command.
+         Acquired by <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>.
         </para>
        </listitem>
       </varlistentry>

diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-REFRESH MATERIALIZED VIEW <replaceable class="PARAMETER">name</replaceable>
+REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="PARAMETER">name</replaceable>
     [ WITH [ NO ] DATA ]
 </synopsis>
  </refsynopsisdiv>
@@ -38,12 +38,44 @@ REFRESH MATERIALIZED VIEW <replaceable class="PARAMETER">name</replaceable>
    data is generated and the materialized view is left in an unscannable
    state.
   </para>
+  <para>
+   <literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
+   be specified together.
+  </para>
  </refsect1>
 
  <refsect1>
   <title>Parameters</title>
 
   <variablelist>
+   <varlistentry>
+    <term><literal>CONCURRENTLY</literal></term>
+    <listitem>
+     <para>
+      Refresh the materialized view without locking out concurrent selects on
+      the materialized view.  Without this option a refresh which affects a
+      lot of rows will tend to use fewer resources and complete more quickly,
+      but could block other connections which are trying to read from the
+      materialized view.  This option may be faster in cases where a small
+      number of rows are affected.
+     </para>
+     <para>
+      This option is only allowed if there is at least one
+      <literal>UNIQUE</literal> index on the materialized view which uses only
+      column names and includes all rows; that is, it must not index on any
+      expressions nor include a <literal>WHERE</literal> clause.
+     </para>
+     <para>
+      This option may not be used when the materialized view is not already
+      populated.
+     </para>
+     <para>
+      Even with this option only one <literal>REFRESH</literal> at a time may
+      run against any one materialized view.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="PARAMETER">name</replaceable></term>
     <listitem>

diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
@@ -589,7 +589,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
 	heap_close(OldHeap, NoLock);
 
 	/* Create the transient table that will receive the re-ordered data */
-	OIDNewHeap = make_new_heap(tableOid, tableSpace);
+	OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
+							   AccessExclusiveLock);
 
 	/* Copy the heap data into the new table in the desired order */
 	copy_heap_data(OIDNewHeap, tableOid, indexOid,
@@ -616,7 +617,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
  * data, then call finish_heap_swap to complete the operation.
  */
 Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
+			  LOCKMODE lockmode)
 {
 	TupleDesc	OldHeapDesc;
 	char		NewHeapName[NAMEDATALEN];
@@ -626,8 +628,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
 	HeapTuple	tuple;
 	Datum		reloptions;
 	bool		isNull;
+	Oid			namespaceid;
+	char		relpersistence;
 
-	OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock);
+	OldHeap = heap_open(OIDOldHeap, lockmode);
 	OldHeapDesc = RelationGetDescr(OldHeap);
 
 	/*
@@ -648,6 +652,17 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
 	if (isNull)
 		reloptions = (Datum) 0;
 
+	if (forcetemp)
+	{
+		namespaceid = LookupCreationNamespace("pg_temp");
+		relpersistence = RELPERSISTENCE_TEMP;
+	}
+	else
+	{
+		namespaceid = RelationGetNamespace(OldHeap);
+		relpersistence = OldHeap->rd_rel->relpersistence;
+	}
+
 	/*
 	 * Create the new heap, using a temporary name in the same namespace as
 	 * the existing table.	NOTE: there is some risk of collision with user
@@ -663,16 +678,16 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
 	snprintf(NewHeapName, sizeof(NewHeapName), "pg_temp_%u", OIDOldHeap);
 
 	OIDNewHeap = heap_create_with_catalog(NewHeapName,
-										  RelationGetNamespace(OldHeap),
+										  namespaceid,
 										  NewTableSpace,
 										  InvalidOid,
 										  InvalidOid,
 										  InvalidOid,
 										  OldHeap->rd_rel->relowner,
 										  OldHeapDesc,
 										  NIL,
-										  OldHeap->rd_rel->relkind,
-										  OldHeap->rd_rel->relpersistence,
+										  RELKIND_RELATION,
+										  relpersistence,
 										  false,
 										  RelationIsMapped(OldHeap),
 										  true,