Hi,
We have a GIST index on an LTREE field on one of the columns in our database. Due to a bug in some of our code, the `nlevel` became quite large (~44). For some reason, when the nlevel becomes large, Postgres may fail to insert new rows with the following error message:
> failed to add item to index page in "idx_directory"
If we look at the logs, future insertions will output the follow line:
> fixing incomplete split in index "idx_directory", block #
This happened to use twice. The first time, it caused some insertions into the index to fail. We reindexed the index and that resolved the issue temporarily. The second time it happened we did not realize the index was corrupted and future reads from this index caused our database to OOM (consuming 30GB of memory in less than 2 minutes). Reindexing this index resolved the OOMs. We have temporarily removed this index from our database to ensure OOMs do not continue happening.
I have not been able to reproduce the OOMs locally but have attached a consistent repro of the issue in the form of a python script (using .txt extension so my email provider will let me attach it). The script uses psycopg2 to connect to the database and you will need to modify the connection string for your local environment. I am starting my postgres with `postgres -c logging_collector=on` so that I get logs. The repro parses out and prints the relevant line.
Our current postgres version is PostgreSQL 16.3 (Debian 16.3-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit. That said, there’s a similar looking issue from 2014 in the following thread that makes it seem like this may be a long-standing issue with the GIST index on LTREE columns.
https://postgrespro.com/list/thread-id/1547317
Thank you,
Wyatt
The content of this email is confidential, may contain proprietary information, and is solely intended for the recipient specified. If you received this message by mistake, please reply to this message and follow with its deletion, so that we can ensure such a mistake does not occur in the future.
