<div dir="ltr">> Now, I have put the following statement in the loop<br><div><br></div><div>IMHO:  you have to adapt this [1] logic and you can calculate the "depth" </div><div><br></div><div><pre class="gmail-lang-sql gmail-s-code-block" style="margin-top:0px;border:0px;font-variant-numeric:inherit;font-variant-east-asian:inherit;font-stretch:inherit;vertical-align:baseline;box-sizing:inherit;width:auto;max-height:600px;overflow:auto"><code class="gmail-hljs gmail-language-sql" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;box-sizing:inherit;background-color:transparent;white-space:inherit"><span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">with</span> <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">recursive</span> cte (root, parent, depth) <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">as</span> (
    <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">select</span> id, parent_id, <span class="gmail-hljs-number" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">1</span>
    <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">from</span> ...
<span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">union</span> <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">all</span>
    <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">select</span> <a href="http://c.id">c.id</a>, t.parent_id, depth<span class="gmail-hljs-operator" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">+</span> <span class="gmail-hljs-number" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">1</span>
    <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">from</span> ...
    <span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">where</span> depth <span class="gmail-hljs-operator" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit"><</span> <span class="gmail-hljs-number" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">1000</span>
)
<span class="gmail-hljs-keyword" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">select</span> <span class="gmail-hljs-operator" style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;line-height:inherit;font-family:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">*</span> from<span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit"> cte;</span>
</code></pre><pre class="gmail-lang-sql gmail-s-code-block" style="margin-top:0px;border:0px;font-variant-numeric:inherit;font-variant-east-asian:inherit;font-stretch:inherit;vertical-align:baseline;box-sizing:inherit;width:auto;max-height:600px;overflow:auto"><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit">[1] <a href="https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql">https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql</a></span><br></pre></div><div>> It is not the issue of forever/endless loop.</div><div><br></div><div>the endless loop eating the memory and you have reached the hard limits of PostgreSQL! </div><div><b>" ERROR: invalid memory alloc request size 1073741824 "</b><br></div><div>1073741824 == 1 GB<br></div><div>And "PostgreSQL Limits"  field size = 1 GB  ( <a href="https://www.postgresql.org/docs/current/limits.html">https://www.postgresql.org/docs/current/limits.html</a> )</div><div>so my guess: it is a data problem!  and you need protection against the "forever/endless loop"<br></div><div><br></div><div>Imre</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com">shishaozhong@gmail.com</a>> ezt írta (időpont: 2022. máj. 8., V, 2:51):<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 dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">It is simply this recursive query.<div><br></div><div><a href="http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html" target="_blank">Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)</a><br><div><br></div><div>It is not the issue of forever/endless loop.</div><div><br></div><div>It runs at a point where it stops.<div><br></div><div>This error turned up.- ERROR:  invalid memory alloc request size 1073741824</div></div><div><br></div><div>Now, I have put the following statement in the loop</div><div><br></div><div><div><br></div><div>If (select count(*) <2500 from primary_watercourse) then</div></div><div><br></div><div><br></div><div>The idea is to skip over too big tables that are generated.</div><div><br></div><div>It keeps running at the moment.</div><div><br></div><div>This memory allocation issue was encountered before.</div><div><br></div><div>I think that it does not like large data sets.</div><div><br></div><div>Regards,</div><div><br></div><div>David</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, 8 May 2022 at 00:59, Imre Samu <<a href="mailto:pella.samu@gmail.com" target="_blank">pella.samu@gmail.com</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 dir="ltr"><div>Hi David,</div><div>> There is a loop of recursive query.  It runs at a point where it stops.</div><div>>Can anyone provide pointers how to solve this problem or work around?<br></div><div><br></div><div>Please share "this" recursive query;</div><div><br></div><div>My guess:  Probably you have a "forever/endless loop" in your data; <b><font color="#0000ff">so you have to LIMIT the recursive part ;</font></b></div><div><br></div><div>related links :</div><div>1.)  with limiting "<span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit">depth</span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">"   ( </span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">where</span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit"> depth </span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit"><</span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit"> </span><span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-align:baseline;box-sizing:inherit">1000 )</span></div><div>"Prevent infinite loop in recursive query in Postgresql"</div><div><a href="https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql" target="_blank">https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql</a><br></div><div>2.)  with LIMITING clause</div><div><a href="https://www.cybertec-postgresql.com/en/recursive-queries-postgresql/" target="_blank">https://www.cybertec-postgresql.com/en/recursive-queries-postgresql/</a> <br></div><i>"This example also demonstrates how <font color="#0000ff">an endless loop can be avoided with a LIMIT clause </font>on the parent query."</i><div><br></div><div>Regards,</div><div>  Imre</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>> ezt írta (időpont: 2022. máj. 7., Szo, 15:23):<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 dir="ltr"><div dir="ltr">There is a loop of recursive query.  It runs at a point where it stops.<div><br></div><div>This error turned up.- ERROR:  invalid memory alloc request size 1073741824</div><div><br></div><div>Can anyone provide pointers how to solve this problem or work around?</div><div><br></div><div>Regards,</div><div><br></div><div>David</div></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>