[postgis-tickets] r14752 - Script to set search path for raster and postgis functions
Regina Obe
lr at pcorp.us
Fri Mar 4 22:57:13 PST 2016
Author: robe
Date: 2016-03-04 22:57:13 -0800 (Fri, 04 Mar 2016)
New Revision: 14752
Added:
branches/2.2/utils/postgis_proc_set_search_path.pl
Modified:
branches/2.2/NEWS
branches/2.2/doc/faq_raster.xml
branches/2.2/postgis/Makefile.in
branches/2.2/raster/rt_pg/Makefile.in
Log:
Script to set search path for raster and postgis functions
To fix database restore issues and materialized views
Closes #3490
Closes #3485
Closes #3277
Closes #3012
Modified: branches/2.2/NEWS
===================================================================
--- branches/2.2/NEWS 2016-03-05 06:01:03 UTC (rev 14751)
+++ branches/2.2/NEWS 2016-03-05 06:57:13 UTC (rev 14752)
@@ -14,6 +14,9 @@
innerBoundaryIs and no outerBoundaryIs
- #3429, upgrading to 2.3 or from 2.1 can cause loop/hang on some
platforms
+ - #3490, Raster data restore issues, materialized views
+ Scripts postgis_proc_set_search_path.sql, rtpostgis_proc_set_search_path.sql
+ refere to http://postgis.net/docs/manual-2.2/RT_FAQ.html#faq_raster_data_not_restore
PostGIS 2.2.1
2016/01/06
Modified: branches/2.2/doc/faq_raster.xml
===================================================================
--- branches/2.2/doc/faq_raster.xml 2016-03-05 06:01:03 UTC (rev 14751)
+++ branches/2.2/doc/faq_raster.xml 2016-03-05 06:57:13 UTC (rev 14752)
@@ -3,6 +3,22 @@
<title>PostGIS Raster Frequently Asked Questions</title>
<qandaset>
+ <qandaentry id="faq_raster_data_not_restore">
+ <question>
+ <para>My raster data does not restore.</para>
+ </question>
+
+ <answer>
+ <para>Raster data does not restore as a result of constraints that rely on schema postgis is installed in to be in search path. 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 all raster 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 rtpostgis_proc_set_search_path.sql
+-- then restore your data</programlisting>
+ </answer>
+ </qandaentry>
<qandaentry id="faq_security_change_1">
<question>
Modified: branches/2.2/postgis/Makefile.in
===================================================================
--- branches/2.2/postgis/Makefile.in 2016-03-05 06:01:03 UTC (rev 14751)
+++ branches/2.2/postgis/Makefile.in 2016-03-05 06:57:13 UTC (rev 14752)
@@ -14,7 +14,7 @@
MODULEDIR=contrib/$(MODULE_big)
# Files to be copied to the contrib/ directory
-SQL_built=postgis.sql uninstall_postgis.sql postgis_upgrade.sql legacy.sql uninstall_legacy.sql legacy_minimal.sql legacy_gist.sql
+SQL_built=postgis.sql uninstall_postgis.sql postgis_upgrade.sql postgis_proc_set_search_path.sql legacy.sql uninstall_legacy.sql legacy_minimal.sql legacy_gist.sql
DATA=../spatial_ref_sys.sql
# SQL objects (files requiring pre-processing)
@@ -164,6 +164,9 @@
echo "BEGIN;" > $@
cat $^ >> $@
echo "COMMIT;" >> $@
+
+postgis_proc_set_search_path.sql: postgis.sql ../utils/postgis_proc_set_search_path.pl
+ $(PERL) ../utils/postgis_proc_set_search_path.pl $< UNUSED > $@
sfcgal_upgrade.sql.in: sfcgal.sql ../utils/postgis_proc_upgrade.pl
$(PERL) ../utils/postgis_proc_upgrade.pl $< UNUSED > $@
Modified: branches/2.2/raster/rt_pg/Makefile.in
===================================================================
--- branches/2.2/raster/rt_pg/Makefile.in 2016-03-05 06:01:03 UTC (rev 14751)
+++ branches/2.2/raster/rt_pg/Makefile.in 2016-03-05 06:57:13 UTC (rev 14752)
@@ -118,6 +118,8 @@
echo 'BEGIN;' > $@
cat $^ | sed -e 's/^BEGIN;$$//' -e 's/^COMMIT;$$//' >> $@
echo 'COMMIT;' >> $@
+
+rtpostgis_proc_set_search_path.sql: rtpostgis.sql ../../utils/postgis_proc_set_search_path.pl
uninstall_rtpostgis.sql: rtpostgis.sql ../../utils/create_undef.pl
$(PERL) ../../utils/create_undef.pl $< $(POSTGIS_PGSQL_VERSION) > $@
Added: branches/2.2/utils/postgis_proc_set_search_path.pl
===================================================================
--- branches/2.2/utils/postgis_proc_set_search_path.pl (rev 0)
+++ branches/2.2/utils/postgis_proc_set_search_path.pl 2016-03-05 06:57:13 UTC (rev 14752)
@@ -0,0 +1,163 @@
+#!/usr/bin/perl -w
+
+#
+# PostGIS - Spatial Types for PostgreSQL
+# http://postgis.net
+#
+# Copyright (C) 2016 Regina Obe <lr at pcorp.us>
+#
+# This is free software; you can redistribute and/or modify it under
+# the terms of the GNU General Public Licence. See the COPYING file.
+#
+
+#
+# This script produces an .sql file containing
+# ALTER FUNCTION SET PATH calls for each function
+# in postgis.sql
+
+# Having postgis functions force the search path
+# to just where postgis is installed is needed
+# so that functions that call other functions during
+# database restore, materialized view creation, foreign table calls
+# will always be able to find the companion functions
+#
+
+eval "exec perl -w $0 $@"
+ if (0);
+
+use strict;
+use warnings;
+
+#
+# Commandline argument handling
+#
+($#ARGV == 0) ||
+die "Usage: perl postgis_proc_set_path.pl <postgis.sql> <version_from> [<schema>]\nCreates a new SQL script
+to set search path for all functions in input script file.\n"
+ if ( @ARGV < 1 || @ARGV > 3 );
+
+my $sql_file = $ARGV[0];
+my $module = 'postgis';
+my $soname = '';
+my $version_to = "";
+my $version_to_num = 0;
+my $version_from = $ARGV[1];
+my $version_from_num = 0;
+my $schema = "";
+$schema = $ARGV[2] if @ARGV > 2;
+
+die "Unable to open input SQL file $sql_file\n"
+ if ( ! -f $sql_file );
+
+## Header of do
+print 'DO language plpgsql $$';
+my $dofunc_start = <<"EOF";
+DECLARE param_postgis_schema text;
+BEGIN
+-- check if PostGIS is already installed
+param_postgis_schema = (SELECT n.nspname from pg_extension e join pg_namespace n on e.extnamespace = n.oid WHERE extname = 'postgis');
+
+-- if in middle install, it will be the current_schema or what was there already
+param_postgis_schema = COALESCE(param_postgis_schema, current_schema());
+
+IF param_postgis_schema != current_schema() THEN
+ EXECUTE 'set search_path TO ' || quote_ident(param_postgis_schema);
+END IF;
+
+-- PostGIS set search path of functions
+EOF
+print $dofunc_start;
+
+#
+# Search the SQL file for the target version number (the
+# version we are upgrading *to*.
+#
+open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n";
+
+
+print qq{
+--
+-- ALTER FUNCTION script
+--
+
+};
+
+#print "BEGIN;\n";
+print "SET search_path TO $schema;\n" if $schema;
+
+#
+# Go through the SQL file and find all functions
+# for each create an ALTER FUNCTION statement
+# to set the search_path to schema postgis is installed in
+open( INPUT, $sql_file ) || die "Couldn't open file: $sql_file\n";
+while(<INPUT>)
+{
+
+ if ( /^create or replace function([^\)]+)([\)]{0,1})/i )
+ {
+ my $funchead = $1; #contains function header except the end )
+ my $endhead = 0;
+ my $endfunchead = $2;
+ my $search_path_safe = 0; # we can put a search path on it without disrupting spatial index use
+ if ($2 eq ')') ## reached end of header
+ {
+ $endhead = 1;
+ }
+
+ #raster folks decided to break their func head in multiple lines
+ # so we need to do this crazy thing
+ if ($endhead != 1)
+ {
+ while(<INPUT>)
+ {
+ #look for expressions with no ( and optionally ending in )
+ if ( /^([^\)]*)([\)]{0,1})/i )
+ {
+ $funchead .= $1;
+ $endfunchead = $2;
+ if ($2 eq ')') ## reached end of header
+ {
+ $endhead = 1;
+
+ }
+ }
+ last if ( $endhead );
+
+ }
+ }
+ #strip quoted , trips up the default strip
+ $funchead =~ s/(',')+//ig;
+ #strip off default args from the function header
+ $funchead =~ s/(default\s+[A-Za-z\.\+\-0-9\'\[\]\:\s]*)//ig;
+
+ #check to see if function is STRICT or c or plpgsql
+ # we can't put search path on non-STRICT sql since search path breaks SQL inlining
+ # breaking sql inlining will break use of spatial index
+ my $endfunc = 0;
+ while(<INPUT>)
+ {
+ $endfunc = 1 if /^\s*(\$\$\s*)?LANGUAGE /i;
+ if ( $endfunc == 1 ){
+ $search_path_safe = 1 if /LANGUAGE\s+[\']*(c|plpgsql)/i;
+ $search_path_safe = 1 if /STRICT/i;
+ }
+ last if ( $endfunc && /\;/ );
+ }
+
+
+ if ($search_path_safe == 1)
+ {
+ print "EXECUTE 'ALTER FUNCTION $funchead $endfunchead SET search_path=' || quote_ident(param_postgis_schema) || ';';\n";
+ }
+ }
+
+}
+
+close( INPUT );
+
+## End of DO
+print 'END;';
+print '$$;';
+
+__END__
+
More information about the postgis-tickets
mailing list