<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)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@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;}
@font-face
{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.gmailsignatureprefix
{mso-style-name:gmail_signature_prefix;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@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 style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>I think we have a tickets somewhere complaining about issues with pg_upgrade and geography, but they have always involved just data missing in spatial_ref_sys.<o:p></o:p></p><p class=MsoNormal>I found this one - <a href="https://trac.osgeo.org/postgis/ticket/4405">https://trac.osgeo.org/postgis/ticket/4405</a> but these cases have always been issues with data not present in spatial_ref_sys rather than the table being missing.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>What puzzles me is how this dependency even helps, cause that still wouldn’t guarantee data in the spatial_ref_sys table is loaded and ultimately I would assume it is checking spatial_ref_sys to make sure 4283 is a valid longlat projection and will fail if it can’t find it.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I thought extensions are always created before user tables so should only be an issue with the data in them and why spatial_ref_sys doesn’t even exist when you load the user table makes no sense.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I don’t see how putting dependency between geography type and spatial_ref_sys would help here and I’ve never seen this particular error before.<o:p></o:p></p><p class=MsoNormal>I suppose it’s possibly most people just use 4326 which geography doesn’t need to verify cause that’s the default srid used for geography when none is specified so is probably hard-coded in our system and that might explain why we’ve never seen this issue.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>That said, can you give us the output of your postgis versions from your PostgreSQL 11 and PostgreSQL 13<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>And also what versions of 11 and 13 you are running.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Thanks,<o:p></o:p></p><p class=MsoNormal>Regina<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><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'> Jeevan Chalke <jeevan.chalke@enterprisedb.com> <br><b>Sent:</b> Thursday, May 15, 2025 9:42 AM<br><b>To:</b> postgis-devel@lists.osgeo.org<br><b>Subject:</b> Re: pg_upgrade fails due to lack of dependencies.<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>PostGIS hackers/developers,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Does this addition make sense?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Wed, May 14, 2025 at 11:07<span style='font-family:"Arial",sans-serif'> </span>AM Jeevan Chalke <<a href="mailto:jeevan.chalke@enterprisedb.com">jeevan.chalke@enterprisedb.com</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal>Hello,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'>While working with one of our customers, we observed that <span style='font-family:"Courier New"'>pg_upgrade</span> is failing with PostGIS.<br><br>What is happening is (SQL attached below), the user table <span style='font-family:"Courier New"'>"prosch"."MyTab"</span> has a column of type <span style='font-family:"Courier New"'>"public"."geography"</span>, which, in turn, references a row in <span style='font-family:"Courier New"'>public.spatial_ref_sys</span>. While there is a recorded dependency between <span style='font-family:"Courier New"'>"procsch"."MyTab"</span> and <span style='font-family:"Courier New"'>"public"."geography"</span>, no such dependency exists between <span style='font-family:"Courier New"'>"public"."geography"</span> and <span style='font-family:"Courier New"'>public.spatial_ref_sys</span>.<br><br>As a result, while the type <span style='font-family:"Courier New"'>"public"."geography"</span> is created before the user table, <span style='font-family:"Courier New"'>spatial_ref_sys</span> is not. Due to sorting (in <span style='font-family:"Courier New"'>pg_dump</span>), the user table is dumped before <span style='font-family:"Courier New"'>spatial_ref_sys</span>, leading to an error during restoration.<br><br>Since PostgreSQL does not maintain dependencies at the row level (and thus not for tables containing such rows), the extension itself should explicitly define this dependency. This would ensure that <span style='font-family:"Courier New"'>public.spatial_ref_sys</span> is dumped before the type <span style='font-family:"Courier New"'>"public"."geography"</span>, preventing issues during dump and restore.<br><br>If we manually add the dependency in the source database, <span style='font-family:"Courier New"'>pg_upgrade</span> will work well. The query to achieve this is:<br><br><span style='font-family:"Courier New"'>INSERT INTO pg_depend VALUES (<br> 'pg_catalog.pg_type'::regclass::oid, 'public.geography'::regtype::oid, 0,<br> 'pg_catalog.pg_class'::regclass::oid, 'public.spatial_ref_sys'::regclass::oid, 0,<br> 'n');</span><br><br>Can this be considered a valid request and added to the extension SQL file itself?<br><br>---<br><br>Here is the SQL sequence that yields an error (shared by one of our engineers)<o:p></o:p></p></div><div><p class=MsoNormal><span style='font-family:"Courier New"'>CREATE SCHEMA procsch;<br>CREATE TABLE "procsch"."MyTab" (<br> "id" bigint,<br> "loc" "public"."geography"(Point,4283)<br>);<br><br>INSERT INTO procsch.MyTab VALUES (1, ST_GeomFromText('POINT(152.138672 -30.689888)', 4283));<br><br>$ strings /PATH/bin/pg_dump | grep POSTGIS_spatial<br>POSTGIS_spatial_ref_sys<br>$<br><br>/PATH/bin/initdb -D /srv/13/data/pg_data<br>/PATH11/bin/pg_ctl -D /srv/11/data/pg_data -l logfile stop<br>/PATH/bin/pg_upgrade --check --link --old-datadir=/srv/11/data/pg_data --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin --new-bindir=/PATH/bin --old-port=5432 --new-port=5434<br># for real, no --check:<br>/PATH/bin/pg_upgrade --link --old-datadir=/srv/11/data/pg_data --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin --new-bindir=/PATH/bin --old-port=5432 --new-port=5434</span><br><br><br>And it did blow up with : <br><br><br><span style='font-family:"Courier New"'>[..]<br>pg_restore: creating TABLE "procsch.MyTab"<br>pg_restore: while PROCESSING TOC:<br>pg_restore: from TOC entry 371; 1259 17425 TABLE MyTab u1<br>pg_restore: error: could not execute query: ERROR: relation "public.spatial_ref_sys" does not exist<br>LINE 21: "loc" "public"."geography"(Point,4283)<br> ^<br>QUERY: SELECT proj4text, auth_name, auth_srid, srtext FROM public.spatial_ref_sys WHERE srid = 4283 LIMIT 1<br>[..]<br>CREATE TABLE "procsch"."MyTab" (<br> "id" bigint,<br> "loc" "public"."geography"(Point,4283)<br>);<br>[..]</span><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thanks<o:p></o:p></p></div><p class=MsoNormal><span class=gmailsignatureprefix>-- </span><o:p></o:p></p><div><div><div><div><div><div><div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><img border=0 width=200 height=37 style='width:2.0833in;height:.3854in' id="_x0000_i1026" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8"><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><b>Jeevan Chalke</b><br><i><span style='font-family:"Arial",sans-serif;color:#1D1C1D'>Principal Engineer, Engineering Manager</span><br>Product Development</i><br><b><span style='font-size:11.0pt;font-family:"Arial",sans-serif;color:#FF3E00;background:white'><br><a href="https://www.enterprisedb.com" target="_blank">enterprisedb.com</a></span></b><o:p></o:p></p></div></div></div></div></div></div></div></div></div></blockquote></div><div><p class=MsoNormal><br clear=all><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><span class=gmailsignatureprefix>-- </span><o:p></o:p></p><div><div><div><div><div><div><div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><img border=0 width=200 height=37 style='width:2.0833in;height:.3854in' id="_x0000_i1025" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8"><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><b>Jeevan Chalke</b><br><i><span style='font-family:"Arial",sans-serif;color:#1D1C1D'>Principal Engineer, Engineering Manager</span><br>Product Development</i><br><b><span style='font-size:11.0pt;font-family:"Arial",sans-serif;color:#FF3E00;background:white'><br><a href="https://www.enterprisedb.com" target="_blank">enterprisedb.com</a></span></b><o:p></o:p></p></div></div></div></div></div></div></div></div></div></div></div></body></html>