<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I actually don't care so much about enforcing non-relocatability. The only benefit of doing that is then we can have a single schema to set the search_path and/or we can schema qualify all our PostGIS function calls – which is a big exercise in monotonous labor. I'd still go with just setting search_path for each function since we can automate that and will be valuable if we DO in future decide to enforce PostGIS installed in schema postgis.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The main issue is that whether we go down the path of making it non-relocatable or not, we are still going to have to set the search_path of each function that calls other functions and tables or schema qualify all our calls.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For example – let's hypothesize we were to enforce postgis be always installed in postgis schema. If someone puts their data in another schema say public, they still won't be able to restore their data cleanly if they have raster constraints or use ST_Distance (and other functions that call other PostGIS functions) functions in their materialized views or use postgres_fdw and use distance functions.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So in my mind FUNCTION search_path is both a current fixing and future proofing way to go. The only thing that would change if we force non-relocatability is our search_path would have just pg_catalog (this one isn't even necessary since its always added) and postgis instead of also accounting for the other search_paths people may add.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Paul Ramsey [mailto:pramsey@cleverelephant.ca] <br><b>Sent:</b> Sunday, March 15, 2015 12:17 PM<br><b>To:</b> PostGIS Development Discussion; Paragon Corporation<br><b>Subject:</b> Re: [postgis-devel] PSC Vote: set schema search_path for select functions<o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div id="bloop_customfont"><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'>I’d like to figure out what the long-term solution is before committing to the short-term hacks.<o:p></o:p></span></p></div><div id="bloop_customfont"><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'>Is it to make postgis non-relocateable and stick it into its own schema? The mechanics of having it relocatable seem intractable given the complexity of our machinery. Opinions on this very much welcome.<o:p></o:p></span></p></div><div id="bloop_customfont"><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'>P.<o:p></o:p></span></p></div><div id="bloop_sign_1426435959155503104"><div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'><o:p> </o:p></span></p></div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'>-- <br>Paul Ramsey<br><a href="http://cleverelephant.ca">http://cleverelephant.ca</a><o:p></o:p></span></p><div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'><a href="http://postgis.net">http://postgis.net</a> <o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'><o:p> </o:p></span></p><p style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif;color:black'>On March 14, 2015 at 6:38:38 PM, Paragon Corporation (<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>) wrote:<o:p></o:p></span></p><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That would be a long term solution, but unfortunately that would break too many people's installs and cause an upgrade nightmare, so not going to enforce that at least not in the short-term. This proposal at least ensures people who choose to install in postgis schema won't be screwed during database restore.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b> <span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@lists.osgeo.org</a> [<a href="mailto:postgis-devel-bounces@lists.osgeo.org">mailto:postgis-devel-bounces@lists.osgeo.org</a>] <b>On Behalf Of</b> Rémi Cura<br><b>Sent:</b> Saturday, March 14, 2015 8:31 PM<br><b>To:</b> PostGIS Development Discussion<br><b>Subject:</b> Re: [postgis-devel] PSC Vote: set schema search_path for select functions</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I personnaly would really prefer that all postgis matter is in a postgis schema by default.<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>It is confusing and annoying that everything gets into public schema<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>For instance with postigs topology everything is in the topology schema.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><br>Cheers,<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Rémi-C<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>2015-03-14 5:53 GMT+01:00 Paragon Corporation <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>>:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I'm thinking of doing something somewhat controversial which I should<br>probably get PSC vote on before I go thru the effort of putting it in and<br>testing out.<br><br>We've been getting a lot of complaints of this sort:<br><br><a href="http://trac.osgeo.org/postgis/ticket/3076" target="_blank">http://trac.osgeo.org/postgis/ticket/3076</a><br><br><a href="http://trac.osgeo.org/postgis/ticket/2485" target="_blank">http://trac.osgeo.org/postgis/ticket/2485</a><br><br><br>With the rise of materialized views, postgres_fdw, PostGIS raster, general<br>database users (with more purist ways of organizing data starting to use<br>PostGIS) we are going to hear a lot more screams of this sort coming down<br>the pike of the worst case being people not being able to restore their data<br>without some workarounds. I in fact helped a guy just last month and felt<br>so bad I didn't charge him much for the consultation. He was having<br>problems restoring data from his production to his identical dev setup and<br>spent hours scratching his head.<br><br><br>So anyway explaining the issue. This issue arises whenever we have<br>functions in PostGIS that call other PostGIS functions or tables and people<br>either put their data in a schema other than where they installed PostGIS or<br>in case of postgres_fdw where schema is not as controllable.<br><br>This is the case with a lot of the raster constraint functions, many of our<br>ST_Distance functions, and ST_Transform to name the main ones that bite<br>people.<br><br>My proposition is to in these functions, set the function schema search_path<br>to include the common schemas people decide to install PostGIS in.<br>So for example for :<br><br>ALTER FUNCTION st_distance(text, text) SET<br>search_path=postgis,contrib,extensions,public;<br><br>I would put this in a separate file to be included just so it doesn't mess<br>up our postgis parsers and can be easily pulled out if we find the need to.<br><br><br>This is a bit of a breaking change in that if people don't install PostGIS<br>in one of the common schemas we assume they would, then functions will just<br>not work for them since the Function search_path will override whatever<br>search path they set.<br><br>The above seems like a less bothersome issue than people not being able to<br>restore their data without having a doctorate in PostGIS.<br><br>Does anyone have an issue with this change? I was going to put it in<br>PostGIS 2.2 and then backport it to 2.1 after doing some more extensive<br>tests.<br><br><br>Thanks,<br>Regina<br><br><br><br><br><br><br><br><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.0pt;font-family:"Helvetica",sans-serif'>_______________________________________________ <br>postgis-devel mailing list <br><a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a> <br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel</a><o:p></o:p></span></p></div></div></blockquote></div></body></html>