<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.16843"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>Jason,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>CREATE OR REPLACE is done for the other functions, so
you won't have issues there unless signatures changed, which they
didn't.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>We have to drop aggregates if they change because
PostgreSQL has no CREATE OR REPLACE AGGREGATE technically because an AGGREGATE
is mostly a structure thing so if the structure changed you'd need to drop and
recreate anyway. </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>We've been lazy in past though and always just dropped
and recreated them <SPAN class=320415000-20032014> so we don't have to
keep track of the book keeping of what version they changed in if they
changed.</SPAN> Our assumption was that few people use them in views
so no real harm to most. </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>strk put his foot down one day when he was sick
because he uses them in views. He had been threatening to put his foot
down for a couple of years and his fever made him brave. No point trying
to fight with a feverish man. Take a bow strk :).</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>Thanks,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014>Regina</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=320415000-20032014></SPAN></FONT> </DIV><BR>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Jason [mailto:jmathis@redzonesoftware.com]
<BR><B>Sent:</B> Wednesday, March 19, 2014 10:32 AM<BR><B>To:</B>
lr@pcorp.us<BR><B>Subject:</B> Private message regarding: [postgis-users] soft
upgrade 2.0.3 > 2.1.1<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Awesome thanks Regina!
<DIV><BR></DIV>
<DIV>We will just wait; no rush on our end. </DIV>
<DIV><BR></DIV>
<DIV>Although I am assuming if some functions have changed and we have
referenced them in views that we will run into the same problem. Why not do a
"create or replace" instead of drop and create? </DIV>
<DIV><BR></DIV>
<DIV>thanks again for the quick response. <BR><BR>On Tuesday, March 18,
2014 6:23:52 PM UTC-6, Paragon Corporation wrote:
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN>Jason,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN>You migth
want to wait til 2.1.2 is out which hopefully will be in next 2
weeks. It has a couple of important geography fixes and also
we changed upgrade process to not drop aggregates unless absolutely necessary.
ST_Union agg did not change between 2.0 and 2.1 so no need to drop
it.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN>Alternatively you can edit the extension script in
share/extension (postgis-2.0.3--2.1.1.sql)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN>and remove
the following lines from it:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN>DROP
AGGREGATE IF EXISTS ST_Union(geometry);<BR>CREATE AGGREGATE ST_Union
(<BR> basetype = geometry,<BR> sfunc =
pgis_geometry_accum_transfn,<BR> stype = pgis_abs,<BR> finalfunc =
pgis_geometry_union_finalfn<BR> );</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial>Then run as you
did:</FONT></SPAN></DIV>
<DIV><SPAN>psql -c "ALTER EXTENSION postgis UPDATE TO '2.1.1';"</SPAN></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial>after you are
done:</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial>run this: (since script drops
all aggs from extension as I recall )</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial> ALTER EXTENSION postgis
ADD AGGREGATE ST_Union(geometry);</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial>Hope that
helps,</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial><A
onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fwww.postgis.us\46sa\75D\46sntz\0751\46usg\75AFQjCNGMcJWBqmMNQeGhTHCVYvcQJ5ysXw';return true;"
onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fwww.postgis.us\46sa\75D\46sntz\0751\46usg\75AFQjCNGMcJWBqmMNQeGhTHCVYvcQJ5ysXw';return true;"
href="http://www.postgis.us"
target=_blank>http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV><SPAN><FONT color=#0000ff size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><BR></DIV>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT size=2 face=Tahoma><B>From:</B> <A
onclick="this.href='javascript:';return true;"
onmousedown="this.href='javascript:';return true;" href="javascript:"
target=_blank
gdf-obfuscated-mailto="XYDmpDEsyxsJ">postgis-us...@lists.<WBR>osgeo.org</A>
[mailto:<A onclick="this.href='javascript:';return true;"
onmousedown="this.href='javascript:';return true;" href="javascript:"
target=_blank
gdf-obfuscated-mailto="XYDmpDEsyxsJ">postgis-us...@<WBR>lists.osgeo.org</A>]
<B>On Behalf Of </B>Jason<BR><B>Sent:</B> Tuesday, March 18, 2014 5:52
PM<BR><B>To:</B> <A onclick="this.href='javascript:';return true;"
onmousedown="this.href='javascript:';return true;" href="javascript:"
target=_blank
gdf-obfuscated-mailto="XYDmpDEsyxsJ">postgi...@googlegroups.com</A><BR><B>Subject:</B>
[postgis-users] soft upgrade 2.0.3 > 2.1.1<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>
<DIV style="WORD-WRAP: break-word"><SPAN
style="FONT-FAMILY: arial,sans-serif">Hi All,</SPAN>
<DIV style="FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-FAMILY: arial,sans-serif">OS: centos 6.5</DIV>
<DIV style="FONT-FAMILY: arial,sans-serif">Postgres: 9.2.7</DIV>
<DIV style="FONT-FAMILY: arial,sans-serif">PostGIS: 2.0.3<BR>
<DIV><BR></DIV>
<DIV>We have recently finished cleaning up and consolidating apps/servers and
upgraded to postgres 9.2.7. Now that postgres is upgraded we decided to get
postgis upgraded as well; currently we are at 2.0.3. According to postgis docs
we can do the "soft upgrade" path, which sounds wonderful. We used "create
extension" for all our 2.0.3 databases.We are on centos 6.5 and I am using yum
to update the postgis packages. I did a quick test on a vm that had a empty
database and the postgis update went fine. After a little research I figured
it was time to run the process on our dev node. The steps are:</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>yum install postgis2_92 #2.1.1</DIV>
<DIV>psql -c "ALTER EXTENSION postgis UPDATE TO '2.1.1';"<BR></DIV>
<DIV><BR></DIV>
<DIV>The yum installed went fine but the "alter extension" failed
with: </DIV>
<DIV>ERROR: cannot drop function st_union(geometry) because other
objects depend on it</DIV>
<DIV>...</DIV>
<DIV><BR></DIV>
<DIV>It went on to list a few views that used that function. I tried dropping
those views but it just moved on to other views that had postgis functions in
them. Dropping all views is not worth it:) </DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Does anyone know if soft upgrading from 2.0.3 > 2.1.1 is possible?
Could it be something in our setup that is stopping us from upgrading.
Otherwise it looks like a hard upgrade is in our future, and its hard to get
excited about that. </DIV>
<DIV><BR></DIV>
<DIV>Thanks! </DIV>
<DIV><BR></DIV></DIV></DIV></DIV><BR>
<P><FONT size=2 face=Arial>This transmission contains confidential and
privileged information intended solely for the party identified above. If you
receive this message in error, you must not use it or convey it to others.
Please destroy it immediately and contact the sender at (303) 386-3955 or by
return e-mail to the sender.</FONT></P></DIV></BLOCKQUOTE></DIV></DIV><BR>
<P><FONT size=2 face=Arial>This transmission contains confidential and
privileged information intended solely for the party identified above. If you
receive this message in error, you must not use it or convey it to others.
Please destroy it immediately and contact the sender at (303) 386-3955 or by
return e-mail to the sender.</FONT></P></BODY></HTML>