[postgis-users] Recursive query - ERROR: invalid memory alloc request size 1073741824

[Imre Samu]

> Now, I have put the following statement in the loop

IMHO:  you have to adapt this [1] logic and you can calculate the "depth"

with recursive cte (root, parent, depth) as (
    select id, parent_id, 1
    from ...union all
    select c.id, t.parent_id, depth+ 1
    from ...
    where depth < 1000
)select * from cte;

[1] https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql

> It is not the issue of forever/endless loop.

the endless loop eating the memory and you have reached the hard limits of
PostgreSQL!
*" ERROR: invalid memory alloc request size 1073741824 "*
1073741824 == 1 GB
And "PostgreSQL Limits"  field size = 1 GB  (
https://www.postgresql.org/docs/current/limits.html )
so my guess: it is a data problem!  and you need protection against the
"forever/endless loop"

Imre


Shaozhong SHI <shishaozhong at gmail.com> ezt írta (időpont: 2022. máj. 8., V,
2:51):

> It is simply this recursive query.
>
> Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)
> <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>
> It is not the issue of forever/endless loop.
>
> It runs at a point where it stops.
>
> This error turned up.- ERROR:  invalid memory alloc request size 1073741824
>
> Now, I have put the following statement in the loop
>
>
> If (select count(*) <2500 from primary_watercourse) then
>
>
> The idea is to skip over too big tables that are generated.
>
> It keeps running at the moment.
>
> This memory allocation issue was encountered before.
>
> I think that it does not like large data sets.
>
> Regards,
>
> David
>
>
>
>
>
>
>
> On Sun, 8 May 2022 at 00:59, Imre Samu <pella.samu at gmail.com> wrote:
>
>> Hi David,
>> > There is a loop of recursive query.  It runs at a point where it stops.
>> >Can anyone provide pointers how to solve this problem or work around?
>>
>> Please share "this" recursive query;
>>
>> My guess:  Probably you have a "forever/endless loop" in your data; *so
>> you have to LIMIT the recursive part ;*
>>
>> related links :
>> 1.)  with limiting "depth"   ( where depth < 1000 )
>> "Prevent infinite loop in recursive query in Postgresql"
>>
>> https://stackoverflow.com/questions/51025607/prevent-infinite-loop-in-recursive-query-in-postgresql
>> 2.)  with LIMITING clause
>> https://www.cybertec-postgresql.com/en/recursive-queries-postgresql/
>> *"This example also demonstrates how an endless loop can be avoided with
>> a LIMIT clause on the parent query."*
>>
>> Regards,
>>   Imre
>>
>> Shaozhong SHI <shishaozhong at gmail.com> ezt írta (időpont: 2022. máj. 7.,
>> Szo, 15:23):
>>
>>> There is a loop of recursive query.  It runs at a point where it stops.
>>>
>>> This error turned up.- ERROR:  invalid memory alloc request size
>>> 1073741824
>>>
>>> Can anyone provide pointers how to solve this problem or work around?
>>>
>>> Regards,
>>>
>>> David
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220508/7d5d633d/attachment.htm>