Re: BUG #19018: high memory usage and "stack depth limit exceeded", with GiST index on ltree - Mailing list pgsql-bugs

On Tue, Aug 12, 2025 at 5:44 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19018
> Logged by:          Joseph Silva
> Email address:      dull.bananas0@gmail.com
> PostgreSQL version: 17.5
> Operating system:   Fedora
> Description:
>
> If I run this, then the postgres process's memory usage approaches 6 GB, and
> the insertion when
> i=253 fails with "stack depth limit exceeded":
>
> ```
> CREATE EXTENSION ltree;
>
> CREATE TABLE comment (path ltree);
>
> CREATE INDEX ON comment USING gist (path);
>
> DO $$
>     DECLARE
>         i int := 1;
>         p text := '0';
>     BEGIN
>         WHILE i < 1000 LOOP
>             p := p || '.' || i::text;
>             i := i + 1;
>             INSERT INTO comment (path) VALUES (p::ltree);
>             COMMIT;
>         END LOOP;
>     END
> $$;
> ```
>
> If index creation is delayed until after insertions, then the insertions
> succeed but index creation
> fails.
>

Thanks for reporting, I didn't analyze it fully but here is what I
have analyzed so far.  While debugging I have noticed that it is
recursively trying to complete the previously incomplete split,
ideally there should not be any incomplete split because I am just
running this from a single sessions so there should not be any issue
in acquiring parent page lock and there is no crash so
GistFollowRight() must be cleared but it is not in some cases and its
keep recursively splitting until it hits the stack overflow [2].  So
this seems like somewhere we have missed to call
GistClearFollowRight() after splitting.

Then I tried to observed the relpages and it shows 270065 relpages for
comment_path_idx which was just 1 before executing this ANONYMOUS
block[2]


[1]
postgres[2882817]=# ANALYZE ;
ANALYZE
postgres[2882817]=# select relname, relpages from pg_class where
relname like '%comment%';
     relname      | relpages
------------------+----------
 comment          |        0
 comment_path_idx |        1
(2 rows)

postgres[2882817]=# DO $$
    DECLARE
        i int := 1;
        p text := '0';
    BEGIN
        WHILE i < 500 LOOP
            p := p || '.' || i::text;
            i := i + 1;
            INSERT INTO comment (path) VALUES (p::ltree);
            COMMIT;
        END LOOP;
    END
$$

ERROR:  54001: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth"
(currently 2048kB), after ensuring the platform's stack depth limit is
adequate.
CONTEXT:  SQL statement "INSERT INTO comment (path) VALUES (p::ltree)"
PL/pgSQL function inline_code_block line 9 at SQL statement
LOCATION:  check_stack_depth, stack_depth.c:99

postgres[2882817]=# select relname, relpages from pg_class where
relname like '%comment%';
     relname      | relpages
------------------+----------
 comment          |       35
 comment_path_idx |   270065
(2 rows)

[2]

#0  check_stack_depth () at stack_depth.c:99
#1  0x000055659812bad5 in gistSplit (r=0x7f0c1d9dd008,
page=0x7f0c1e44d000 "\001", itup=0x5567a00a1650, len=3,
giststate=0x5565d74ce9f8) at gist.c:1463
#2  0x0000556598129720 in gistplacetopage (rel=0x7f0c1d9dd008,
freespace=0, giststate=0x5565d74ce9f8, buffer=453,
itup=0x7ffd5f39b800, ntup=2, oldoffnum=2, newblkno=0x0,
    leftchildbuf=13010, splitinfo=0x7ffd5f39b790,
markfollowright=true, heapRel=0x7f0c1d9de928, is_build=false) at
gist.c:315
#3  0x000055659812b7eb in gistinserttuples (state=0x7ffd5f5993c0,
stack=0x5567a009a078, giststate=0x5565d74ce9f8, tuples=0x7ffd5f39b800,
ntup=2, oldoffnum=2

<--------clip------>
    leftchild=12971, rightchild=12974, unlockbuf=true,
unlockleftchild=true) at gist.c:1337
#18642 0x000055659812ba52 in gistfinishsplit (state=0x7ffd5f5993c0,
stack=0x5565d7726968, giststate=0x5565d74ce9f8,
splitinfo=0x5565d7768848, unlockbuf=true)
    at gist.c:1408
#18643 0x000055659812b855 in gistinserttuples (state=0x7ffd5f5993c0,
stack=0x5565d7726968, giststate=0x5565d74ce9f8, tuples=0x7ffd5f599300,
ntup=2, oldoffnum=1,
    leftchild=12972, rightchild=12973, unlockbuf=true,
unlockleftchild=false) at gist.c:1337
#18644 0x000055659812ba52 in gistfinishsplit (state=0x7ffd5f5993c0,
stack=0x5565d7758388, giststate=0x5565d74ce9f8,
splitinfo=0x5565d77593e8, unlockbuf=false)
    at gist.c:1408
#18645 0x000055659812b6e2 in gistfixsplit (state=0x7ffd5f5993c0,
giststate=0x5565d74ce9f8) at gist.c:1246
#18646 0x000055659812a61c in gistdoinsert (r=0x7f0c1d9dd008,
itup=0x5565d746c580, freespace=0, giststate=0x5565d74ce9f8,
heapRel=0x7f0c1d9de928, is_build=false)


--
Regards,
Dilip Kumar
Google