The following bug has been logged on the website:
Bug reference: 15431
Logged by: Sergei Kornilov
Email address: sk@zsrv.org
PostgreSQL version: 10.5
Operating system: Linux Debian
Description:
Hello
I have such testcase for ltree with gist index:
create extension if not exists ltree;
create table gist_ltree_test(id int, tree ltree);
create index on gist_ltree_test using gist(tree);
insert into gist_ltree_test (id, tree) select 1,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
insert into gist_ltree_test (id, tree) select 2,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
insert into gist_ltree_test (id, tree) select 3,
string_agg('segment_'||i,'.')::ltree from generate_series(1,320) i;
insert into gist_ltree_test (id, tree) select 4,
string_agg('segment_'||i,'.')::ltree from generate_series(1,24) i;
insert into gist_ltree_test (id, tree) select 5,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
select id as gist_ltree_test from gist_ltree_test;
drop table gist_ltree_test;
create table gist_ltree_test2(id int, tree ltree);
create index on gist_ltree_test2 using gist(tree);
insert into gist_ltree_test2 (id, tree) select 1,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
insert into gist_ltree_test2 (id, tree) select 2,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
insert into gist_ltree_test2 (id, tree) select 4,
string_agg('segment_'||i,'.')::ltree from generate_series(1,24) i;
insert into gist_ltree_test2 (id, tree) select 3,
string_agg('segment_'||i,'.')::ltree from generate_series(1,320) i;
insert into gist_ltree_test2 (id, tree) select 5,
string_agg('segment_'||i,'.')::ltree from generate_series(1,90) i;
select id as gist_ltree_test2 from gist_ltree_test2;
drop table gist_ltree_test2;
Please note: data are the same, but row id 3 and 4 are in different order.
Both tables gives ERROR: failed to add item to index page in
"gist_ltree_test2_tree_idx" - but on different lines. First testcase
successfull insert very long ltree (3731 bytes) and give error on next much
shorter row. Second testcase inserts row 4 and rejects row 3. Rows 1,2 and 5
was written in both cases.
ltree documentation prefer keeping path length under 2Kb. It seems this is
known limitation. But dependency on insert order seems as bug. Maybe we need
restrict ltree size?
I use postgresql 10.5 from PGDG repository. Also i found at least one old
similar bug:
https://www.postgresql.org/message-id/flat/20151023203503.3021.6784%40wrigleys.postgresql.org
I think bug exists at long time.
regards, Sergei