<div dir="ltr">Thank you, Regina <div><br></div><div>You’re right -- this dependency alone doesn't fully resolve the issue.</div><div>
<p class="gmail-">We did receive a complaint about missing data in the <code>spatial_ref_sys</code> table, with the error:<br>
<code>ERROR:  Cannot find SRID (4283) in spatial_ref_sys</code>.</p>
<p class="gmail-">By tweaking <code>pg_dump</code>, we managed to dump data for this table <em>before</em> any user tables accessed it. However, we later encountered a new issue:<br>
<code>ERROR:  relation "public.spatial_ref_sys" does not exist</code>.</p>
<p class="gmail-">As a PostgreSQL developer, this new error came as a surprise. The table in question is part of an extension, and that extension does create it. Upon further investigation, I discovered that the issue lies in the sort order used by <code>pg_dump</code> when dumping extension and user tables, the order isn't always correct. Although <code>pg_dump</code> and <code>pg_restore</code> are generally smart enough to account for object dependencies, based on entries in the <code>pg_depend</code> catalog, this case seems to expose a gap in that logic.</p>
<p class="gmail-">Specifically, since both <code>spatial_ref_sys</code> and <code>geography</code> are part of the same extension and have a dependency relationship, this relationship is <em>not</em> reflected in the <code>pg_depend</code> table. If it were, <code>pg_dump</code> would ensure the table is dumped before the dependent type, avoiding this issue. This is what I’ve suggested as a fix.</p>
<p class="gmail-">We encountered this problem while using the latest server versions for PostgreSQL v11, v13, and v16, along with PostGIS versions 3.2.4 and 3.4.3.</p><p class="gmail-">Thanks</p></div></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Thu, May 15, 2025 at 10:03 PM <<a href="mailto:lr@pcorp.us">lr@pcorp.us</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"><div class="msg-1792571721072084611"><div lang="EN-US" style="overflow-wrap: break-word;"><div class="m_-1792571721072084611WordSection1"><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.<u></u><u></u></p><p class="MsoNormal">I found this one - <a href="https://trac.osgeo.org/postgis/ticket/4405" target="_blank">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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></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.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">That said, can you give us the output of your postgis versions from your PostgreSQL 11 and PostgreSQL 13<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">And also what versions of 11 and 13 you are running.<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Thanks,<u></u><u></u></p><p class="MsoNormal">Regina<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><div style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in 0in 0in 4pt"><div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> Jeevan Chalke <<a href="mailto:jeevan.chalke@enterprisedb.com" target="_blank">jeevan.chalke@enterprisedb.com</a>> <br><b>Sent:</b> Thursday, May 15, 2025 9:42 AM<br><b>To:</b> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><b>Subject:</b> Re: pg_upgrade fails due to lack of dependencies.<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">PostGIS hackers/developers,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Does this addition make sense?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal"><u></u> <u></u></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" target="_blank">jeevan.chalke@enterprisedb.com</a>> wrote:<u></u><u></u></p></div><blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"><div><div><p class="MsoNormal">Hello,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-bottom:12pt">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)<u></u><u></u></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><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks<u></u><u></u></p></div><p class="MsoNormal"><span class="m_-1792571721072084611gmailsignatureprefix">-- </span><u></u><u></u></p><div><div><div><div><div><div><div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><img border="0" width="200" height="37" style="width: 2.0833in; height: 0.3854in;" id="m_-1792571721072084611_x0000_i1026" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8"><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><b>Jeevan Chalke</b><br><i><span style="font-family:Arial,sans-serif;color:rgb(29,28,29)">Principal Engineer, Engineering Manager</span><br>Product Development</i><br><b><span style="font-size:11pt;font-family:Arial,sans-serif;color:rgb(255,62,0);background:white"><br><a href="https://www.enterprisedb.com" target="_blank">enterprisedb.com</a></span></b><u></u><u></u></p></div></div></div></div></div></div></div></div></div></blockquote></div><div><p class="MsoNormal"><br clear="all"><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal"><span class="m_-1792571721072084611gmailsignatureprefix">-- </span><u></u><u></u></p><div><div><div><div><div><div><div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><img border="0" width="200" height="37" style="width: 2.0833in; height: 0.3854in;" id="m_-1792571721072084611_x0000_i1025" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8"><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><b>Jeevan Chalke</b><br><i><span style="font-family:Arial,sans-serif;color:rgb(29,28,29)">Principal Engineer, Engineering Manager</span><br>Product Development</i><br><b><span style="font-size:11pt;font-family:Arial,sans-serif;color:rgb(255,62,0);background:white"><br><a href="https://www.enterprisedb.com" target="_blank">enterprisedb.com</a></span></b><u></u><u></u></p></div></div></div></div></div></div></div></div></div></div></div></div></div></blockquote></div><div><br clear="all"></div><div><br></div><span class="gmail_signature_prefix">-- </span><br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><br></div><div dir="ltr"><img width="200" height="37" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8"><br></div><div dir="ltr"><br></div><div dir="ltr"><b>Jeevan Chalke</b><br><span style="color:rgb(29,28,29);font-family:Slack-Lato,Slack-Fractions,appleLogo,sans-serif"><i>Principal Engineer, Engineering Manager</i></span><i><br>Product Development</i><br><span style="font-size:11pt;font-family:Arial;color:rgb(255,62,0);background-color:rgb(255,255,255);font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><br><a href="https://www.enterprisedb.com" target="_blank">enterprisedb.com</a></span></div></div></div></div></div></div></div></div>