From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian xu <jamesxu@outlook.com>
cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: creating gist index on ltree column failed
In-reply-to: 
 <MN2PR05MB68321416773DB46AFCAA323EA1380@MN2PR05MB6832.namprd05.prod.outlook.com>
References: 
 <MN2PR05MB68321416773DB46AFCAA323EA1380@MN2PR05MB6832.namprd05.prod.outlook.com>
Comments: In-reply-to jian xu <jamesxu@outlook.com>
	message dated "Wed, 23 Sep 2020 13:31:27 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1427098.1600871837.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 23 Sep 2020 10:37:17 -0400
Message-ID: <1427099.1600871837@sss.pgh.pa.us>
Precedence: bulk

jian xu <jamesxu@outlook.com> writes:
>            I tried to create gist index on a ltree column, first I got e=
rror
> ERROR:  stack depth limit exceeded

Hm.  What PG version is this?  If it's up-to-date, can you provide some
sample data that causes such problems?

> one thing I notice is, there are some rows with many levels(more than 1k=
 levels) in the ltree, if I delete those rows, creating gist index works

I tried to reproduce this using just that information, but all I got was

regression=3D# create table t (f1 ltree);
CREATE TABLE
regression=3D# create index on t using gist(f1);
CREATE INDEX
regression=3D# insert into t select ('0.' || (select string_agg(g::text,'.=
') from generate_series(1,100) g))::ltree;
INSERT 0 1
regression=3D# insert into t select ('0.' || (select string_agg(g::text,'.=
') from generate_series(1,1000) g))::ltree;
ERROR:  index row requires 16048 bytes, maximum size is 8191

which is perhaps an annoying limitation, but it's not a bug.

(I'm kind of wondering about the use-case for such long ltrees, anyway.)

			regards, tom lane