[postgis-tickets] r14753 - FAQ about materialized views sometimes not restoring in backup.

[Regina Obe]

Author: robe
Date: 2016-03-05 12:36:11 -0800 (Sat, 05 Mar 2016)
New Revision: 14753

Modified:
   branches/2.2/doc/faq.xml
Log:
FAQ about materialized views sometimes not restoring in backup.
References #3490

Modified: branches/2.2/doc/faq.xml
===================================================================
--- branches/2.2/doc/faq.xml	2016-03-05 06:57:13 UTC (rev 14752)
+++ branches/2.2/doc/faq.xml	2016-03-05 20:36:11 UTC (rev 14753)
@@ -3,6 +3,30 @@
   <title>PostGIS Frequently Asked Questions</title>
 
   <qandaset>
+  
+    <qandaset>
+     <qandaentry id="faq_mat_views_restore">
+      <question>
+        <para>My materialized views involving ST_Distance geography and other functions do not restore</para>
+      </question>
+
+      <answer>
+        <para>Some materialized views involing postgis do not restore as a result of use of PostGIS functions that call other PostGIS functions.  This happens if you materialized view is in a different schema from where PostGIS is installed since they make unqualified function calls, and the pg_restore always sets schema to the schema of the view being restored. Since materialized view functions get called in order to build the data, the calls fail and therefore the creation of the view fails as well.</para>
+        <para>As of PostGIS 2.2.2
+        there is a script in share/contrib/postgis-2.2/postgis_proc_set_search_path.sql that will set the search_path of most postgis functions.  To use
+        when restoring data:</para>
+        <programlisting>
+-- make sure to install in same schema as your backup was installed in
+CREATE EXTENSION postgis;
+\i postgis_proc_set_search_path.sql
+-- then restore your data</programlisting>
+		<para>Keep in mind the search path was not set for functions relationship functions like <varname>ST_Intersects</varname>, <varname>ST_DWithin</varname> since these rely on SQL-inlining to force index usage, and such behavior would get broken if FUNCTION search_path is applied.  In these cases, we suggest if you need these in materialized views, to explicitly call the hidden _ST_.. function instead accompanied by &&.  So for example:</para>
+		<programlisting>CREATE MATERIALIZED VIEW vwmat_something AS
+SELECT ...
+FROM a INNER JOIN b ON ( a.geom && b.geom AND _ST_Intersects(a.geo, b.geom) );
+      </answer>
+    </qandaentry>
+    
   	<qandaentry id="faq_where_tutorials">
       <question>
         <para>Where can I find tutorials, guides and workshops on working with PostGIS</para>