Re: BUG #16986: reindex error on ltree index

.Hi, Mauro!

On Thu, Apr 29, 2021 at 1:33 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> reindexing a table with ltree index this error is thrown:
> ERROR:  index row size 7720 exceeds maximum 8152 for index
> "ltree_test_session_event_path_idx"
> Follow script to reproduce error:
> ---------------------------------------------------------------
> CREATE TABLE "m3_dm"."ltree_test" (
>   "session_id" varchar COLLATE "pg_catalog"."default" NOT NULL,
>   "session_event_path" "public"."ltree",
>   CONSTRAINT "ltree_test_pkey" PRIMARY KEY ("session_id")
> );
> CREATE INDEX "ltree_test_session_event_path_idx" ON "m3_dm"."ltree_test"
> USING gist (
>   "session_event_path" "public"."gist_ltree_ops"
> );
> insert into m3_dm.ltree_test
> values (
>         'aaa',
>         text2ltree(repeat('abcdefghi.',480)||'abcdefghi')
> )
> ;
> reindex table m3_dm.ltree_test;

Thank you for reporting.  Generally, GiST isn't good at controlling
maximum tuple length.  If your tuple is long error can be raised
randomly here and there.

Your particular case is related to GiST fillfactor.  Normal insertion
doesn't take fillfactor into account but index build does.  You can
resolve this case by specifying fillfactor = 100.

CREATE INDEX "ltree_test_session_event_path_idx" ON "m3_dm"."ltree_test"
USING gist (
  "session_event_path" "public"."gist_ltree_ops"
) WITH (fillfactor = 100);

Besides fact that GiST need a general rework at controlling maximum
tuple length, the error message looks really dumb.  7720 doesn't
exceed 8152.  I'm going to fix this.

------
Regards,
Alexander Korotkov