Failure to create GiST on ltree column

On Sun, May 24, 2020 at 09:30:15PM +0300, Victor Yegorov wrote:

Greetings.

I am getting random failures in `CREATE INDEX USING gist` over ltree column
while performing pg_restore.
I get either
ERROR: stack depth limit exceeded
or
ERROR: failed to add item to index page

Thing is — if I retry index creation manually, I get it successfully built
in ~50% of the cases.

I would like to find out what's the real cause here, but I am not sure how
to do it.
If anybody could provide some guidance, I am open to investigate this case.

I'm on PostgreSQL 11.8 (Debian 11.8-1.pgdg90+1), debugging symbols
installed.

I think you'd want to attach a debugger to the backend's PID and set breakpoint
on errfinish() or pg_re_throw() and reproduce the problem to get a stacktrace
(separate stacks for both errors).
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

What is value of maintenance_work_mem ?

What's the definition of the index and relevant table columns ?

Do you know if that was that an issue under 11.7 as well ?

Are you running on any interesting hardware ?

--
Justin

пн, 25 мая 2020 г. в 01:52, Justin Pryzby <pryzby@telsasoft.com>:

I think you'd want to attach a debugger to the backend's PID and set
breakpoint
on errfinish() or pg_re_throw() and reproduce the problem to get a
stacktrace
(separate stacks for both errors).

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

Thanks. I'm attaching 2 backtraces.
Big one is for the “stack depth limit exceeded” case.

After I set max_stack_depth to the maximum possible value,
I am getting “failed to add item to index page” errors.

What is value of maintenance_work_mem ?

1GB

What's the definition of the index and relevant table columns ?

# \d comments.mp_comments
Table
"comments.mp_comments"
Column | Type | Collation |
Nullable | Default
---------------------------+--------------------------+-----------+----------+--------------------------------------------------------------
obj_id | bigint | | not
null | (nextval('comments.comments_obj_id_seq'::regclass))::numeric
obj_created | timestamp with time zone | | not
null | now()
obj_modified | timestamp with time zone | | not
null | now()
obj_status_did | smallint | | not
null | 1
c_comment | character varying | | not
null |
mpath | ltree | | not
null |
c_person_obj_id | bigint | | not
null |
c_lcid | character varying | |
| 32
c_rating | double precision | | not
null | 0
c_mpath_level | bigint | | not
null | 1
c_root_obj_id | bigint | | not
null |
c_root_obj_type | smallint | |
|
c_parent_obj_id | bigint | |
|
c_root_obj_vislvl_content | smallint | | not
null | 9
c_forecast_bias | smallint | |
|
mpath_array | bigint[] | |
|
anonymous | boolean | | not
null | false
c_edited | smallint | |
|
c_edited_at | timestamp with time zone | |
|
c_image | character varying(255) | |
|
c_from_mobile | boolean | |
|
Indexes:
"mpath_pkey" PRIMARY KEY, btree (obj_id)
"i_mp_comments_mpath_btree" UNIQUE, btree (mpath)
"i_comment_c_comment_ts_vector" gin (make_tsvector(c_comment::text,
'russian'::text))
"i_comment_mp_comments_person_created" btree (c_person_obj_id,
obj_status_did, obj_created)
Inherits: obj_base

New index to be created:
CREATE INDEX i_mp_comments_mpath_gist ON comments.mp_comments USING gist
(mpath);

Do you know if that was that an issue under 11.7 as well ?

It was an issue on the 11.2, I've updated to the latest minor release, no
changes.

Are you running on any interesting hardware ?

Nothing fancy, no VM.

--
Victor Yegorov

On Mon, May 25, 2020 at 04:41:49PM +0300, Victor Yegorov wrote:

New index to be created:
CREATE INDEX i_mp_comments_mpath_gist ON comments.mp_comments USING gist (mpath);

I wonder if/how that fails if you create the index before adding data:

CREATE TABLE test_path(path ltree);
CREATE INDEX ON test_path USING GIST(path);
INSERT INTO test_path SELECT * FROM comments.mp_comments;

Does that fail on a particular row ?

How many paths do you have and how long? How big is the table?

--
Justin

пн, 25 мая 2020 г. в 18:25, Justin Pryzby <pryzby@telsasoft.com>:

I wonder if/how that fails if you create the index before adding data:

CREATE TABLE test_path(path ltree);
CREATE INDEX ON test_path USING GIST(path);
INSERT INTO test_path SELECT * FROM comments.mp_comments;

Does that fail on a particular row ?

How many paths do you have and how long? How big is the table?

Yes, it fails.

I got permission and created a partial dump of the data with:
CREATE TABLE lc AS SELECT id, path FROM comments.mp_comments WHERE
length(path::text)>=500;

Attached. It is reproduces the error I get. One needs to create ltree
extension first.

I understand, that issue most likely comes from the length of the ltree
data stored in the columns.
But error is a bit misleading…

--
Victor Yegorov