Thread: Failure to create GiST on ltree column
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
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.
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
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.
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
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);
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
Attachment
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