<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Hi Paul:</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">We have many directory/files for the SQL so it's easier to manage what changed and what did not changed in a PR:</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/tree/develop/sql">https://github.com/pgRouting/pgrouting/tree/develop/sql</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">And we have some rules:<br></div></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Starting from version 3.0.0, we do not change signatures within the same minor.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Only new signatures are allowed on a bigger minor (This is enforced for users facing function, anything can happen for functions starting with underscore.<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">We have an action that test these requirements:<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/.github/workflows/check-files.yml#L8">https://github.com/pgRouting/pgrouting/blob/develop/.github/workflows/check-files.yml#L8</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/tools/scripts/test_signatures.sh">https://github.com/pgRouting/pgrouting/blob/develop/tools/scripts/test_signatures.sh</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">There are a couple of scripts that are used to build the extension file and the updates files:<br><a href="https://github.com/pgRouting/pgrouting/tree/develop/sql/scripts">https://github.com/pgRouting/pgrouting/tree/develop/sql/scripts</a><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">We have indications of when the function was added, (I think) that are used on the script:</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/bdDijkstra/bdDijkstra.sql#L34">https://github.com/pgRouting/pgrouting/blob/develop/sql/bdDijkstra/bdDijkstra.sql#L34</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/coloring/bipartite.sql#L33">https://github.com/pgRouting/pgrouting/blob/develop/sql/coloring/bipartite.sql#L33</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">CREATE FUNCTION ...<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">will be converted to<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">CREATE OR UPDATE FUNCTION ...</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"> on an update file if the signature exists on the previous version, or remain as CREATE if it is a new function</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">some time it fails so there are some hacks when needed and those are special cases: (basically if everything is done correctly the hacks won't be needed, but unfortunately is not the case yet)<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">There are hacks for 3.0 and 2.6<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/scripts/build-extension-update-files.pl#L361">https://github.com/pgRouting/pgrouting/blob/develop/sql/scripts/build-extension-update-files.pl#L361</a><br><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/scripts/build-extension-update-files.pl#L396">https://github.com/pgRouting/pgrouting/blob/develop/sql/scripts/build-extension-update-files.pl#L396</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">We use MODULE_PATHNAME<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/dijkstra/_dijkstra.sql#L57">https://github.com/pgRouting/pgrouting/blob/develop/sql/dijkstra/_dijkstra.sql#L57</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">after installing 3.2.0, doing a:<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">CREATE EXTENSION pgrouting with version '3.1.0' CASCADE;</span><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><span style="font-family:arial,sans-serif">will create a database with 3.1.0 SQL code but will use 3.2 library that comes from 3.2.0 </span><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">SELECT version, library FROM pgr_full_version(); </span><br> version | library <br>---------+-----------------
<br> 3.1.0 | pgrouting-3.2.0
<br>(1 row)<br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">So a bug fix on the C/C++ will be catched, but if there was a bug fix on 3.2 in the SQL, they would be running without that fix until UPDATE EXTENSION is used.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">If the database was created before 3.2.0 was installed, UPDATE EXTENSION is needed to catch bug fixes in both C & C++</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">I haven't tried to have a DB, created with, for example, 3.1.0 before 3.2.0 installed, dump the db, install 3.2.0 create a new DB with pgRouting 3.1.0 (using 3.2.0 library) and restore the DB.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">So because of that we take special care on keeping the C connection,</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/src/dijkstra/dijkstra.c#L253">https://github.com/pgRouting/pgrouting/blob/develop/src/dijkstra/dijkstra.c#L253</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><a href="https://github.com/pgRouting/pgrouting/blob/develop/sql/dijkstra/_dijkstra.sql">https://github.com/pgRouting/pgrouting/blob/develop/sql/dijkstra/_dijkstra.sql</a></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Things will be cleaned up on version 4.0.0 (whenever that happens.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">I hope this help<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Regards</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Vicky<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Example hack:<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span style="font-family:monospace"><span style="color:rgb(84,255,255);background-color:rgb(255,255,255)">---------------------------------------------</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br><span style="color:rgb(84,255,255);background-color:rgb(255,255,255)">-- Updating from version 2.6</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br><span style="color:rgb(84,255,255);background-color:rgb(255,255,255)">---------------------------------------------</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">ALTER</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> EXTENSION pgrouting </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_costresult; </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_costresult CASCADE;
</span><br><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">ALTER</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> EXTENSION pgrouting </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_costresult3; </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_costresult3 CASCADE;
</span><br><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">ALTER</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> EXTENSION pgrouting </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_geomresult; </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">DROP</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">TYPE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pgr_geomresult CASCADE;
</span><br><br>
<br>
<br><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">UPDATE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> pg_proc </span><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">SET</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br>proargnames = <span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(255,84,255);background-color:rgb(255,255,255)">{"","","",directed,seq,path_seq,node,edge,cost,agg_cost}</span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">WHERE</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> proname = </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(255,84,255);background-color:rgb(255,255,255)">pgr_dijkstra</span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">
</span><br><span style="color:rgb(255,255,84);background-color:rgb(255,255,255)">AND</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)"> proargnames = </span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(255,84,255);background-color:rgb(255,255,255)">{edges_sql,start_vid,end_vid,directed,seq,path_seq,node,edge,cost,agg_cost}</span><span style="color:rgb(255,215,215);background-color:rgb(255,255,255)">'</span><span style="color:rgb(0,0,0);background-color:rgb(255,255,255)">;</span><br>
<br><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Jun 11, 2021 at 9:58 AM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
<br>
> On Jun 10, 2021, at 1:47 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>
> <br>
>> Hi all!<br>
>> What kind of care do you take to ensure that library symbols don't change<br>
> over<br>
>> time? So that the library symbols in pgrouting30 match up to pgrouting31 ?<br>
> I ask<br>
>> because having non-changing names makes upgrade/install/failovers simpler.<br>
>> P.<br>
>> _______________________________________________<br>
>> pgrouting-dev mailing list<br>
>> <a href="mailto:pgrouting-dev@lists.osgeo.org" target="_blank">pgrouting-dev@lists.osgeo.org</a><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/pgrouting-dev</a><br>
> [Regina Obe] <br>
> In prior releases I think symlinking did work fine but there is a lot going<br>
> on with pgRouting 3.2 that that might not be the case. I haven't tried<br>
> doing that recently.<br>
> <br>
> FWIW it's much less of an issue with pgRouting than it is with PostGIS<br>
> because pgRouting is not tied to any data. <br>
> So even if pg_upgrade fails, you can just drop the extension and install it<br>
> after the upgrade. <br>
<br>
This is what I'm mostly thinking as well... things like backup and restore and replication are less fraught when data types are not involved. <br>
<br>
<br>
> <br>
> Vicky can correct me if I misspoke but I don't think she's that concerned<br>
> about it right now, but I don't think C++ functions bound via SQL api don't<br>
> change that often. Mostly just additions.<br>
> <br>
> I did mention about keeping the .so name the same as part of this maintain<br>
> and that those should go hand in hand with keeping symbols the same.<br>
> Otherwise it's still a symlinking pain that few people would understand how<br>
> to do. DROP EXTENSION / CREATE EXTENSION after pg upgrade would still be<br>
> easier for most unless the .so name doesn't change.<br>
> <br>
> <br>
> Thanks,<br>
> Regina<br>
> <br>
> _______________________________________________<br>
> pgrouting-dev mailing list<br>
> <a href="mailto:pgrouting-dev@lists.osgeo.org" target="_blank">pgrouting-dev@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/pgrouting-dev</a><br>
<br>
_______________________________________________<br>
pgrouting-dev mailing list<br>
<a href="mailto:pgrouting-dev@lists.osgeo.org" target="_blank">pgrouting-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/pgrouting-dev</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><pre>Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@<a href="http://georepublic.de" target="_blank">georepublic.de</a>
Web: <a href="https://georepublic.info" target="_blank">https://georepublic.info</a>
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
<span></span></pre></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>