Обсуждение: Failure to create GiST on ltree column

Поиск
Список
Период
Сортировка

Failure to create GiST on ltree column

От
Victor Yegorov
Дата:
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.

--
Victor Yegorov

Re: Failure to create GiST on ltree column

От
Justin Pryzby
Дата:
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

Re: Failure to create GiST on ltree column

От
Victor Yegorov
Дата:
пн, 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
Вложения

Re: Failure to create GiST on ltree column

От
Justin Pryzby
Дата:
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

Re: Failure to create GiST on ltree column

От
Victor Yegorov
Дата:
пн, 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
Вложения