Re: Backend crash (signal 11) in pg_trgm makesign() after ALTER TABLE ... SET STORAGE on a column with a gist_trgm_ops index - Mailing list pgsql-bugs
On Sun, 4 Oct 2026 at 02:14, Thiago Bonfante <thiago@subbase.io> wrote:
Hi Andrey,
Thanks for the quick response.
Best,
ThiagoOn Fri, Oct 2, 2026 at 4:58 AM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:пт, 2 окт. 2026 г. в 11:07, Thiago Bonfante <thiago@subbase.io>:Hello,
We found that after ALTER TABLE ... ALTER COLUMN ... SET STORAGE EXTENDED (or MAIN) on a text column that has a
gist_trgm_ops index, the next INSERT or non-HOT UPDATE into the table crashes the backend with signal 11.
It reproduces every time on a stock installation.
What's worse, REINDEX fails on this index in the same way.
--== Version ==
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
Also reproduced on 17.11 and 16.15 (same Debian PGDG packages), and first seen on Amazon Aurora PostgreSQL 16.11 and Amazon RDS PostgreSQL 16.13.
== Platform ==
Official postgres:18 Docker image, Debian GNU/Linux 13 (trixie), glibc 2.41 (Debian GLIBC 2.41-12+deb13u4)
Kernel: Linux 6.12.76-linuxkit aarch64 (Docker Desktop VM on Apple Silicon), 11 CPUs, 8 GB RAM
Configuration: the image's defaults; nothing changed in postgresql.conf, no extra start-up options.
== Steps to reproduce (psql, attached as repro.sql) ==
CREATE EXTENSION pg_trgm;
CREATE TABLE t (id int, s text);
INSERT INTO t SELECT g, 'item ' || g || ' ' || left(md5(g::text), 8) FROM generate_series(1, 10000) g;
CREATE INDEX t_s_gist ON t USING gist (s gist_trgm_ops);
SELECT attstorage FROM pg_attribute WHERE attrelid = 't_s_gist'::regclass;
ALTER TABLE t ALTER COLUMN s SET STORAGE EXTENDED;
SELECT attstorage FROM pg_attribute WHERE attrelid = 't_s_gist'::regclass;
INSERT INTO t SELECT g, 'new item ' || g FROM generate_series(10001, 11000) g;
== Actual output ==
psql (with \set VERBOSITY verbose):
CREATE EXTENSION
CREATE TABLE
INSERT 0 10000
CREATE INDEX
attstorage
------------
p
(1 row)
ALTER TABLE
attstorage
------------
x
(1 row)
Server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
Connection to server was lost.
Server log:
LOG: client backend (PID 129) was terminated by signal 11: Segmentation fault
DETAIL: Failed process was running: INSERT INTO t SELECT g, 'new item ' || g FROM generate_series(10001, 11000) g;
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
== Expected output ==
The final INSERT should succeed (INSERT 0 1000). SET STORAGE EXTENDED does not change the storage of a text
column (EXTENDED is already its default), and I would expect it to leave the index column alone: the
index column's type is gtrgm, whose typstorage is 'p', and a newly created index on the same column gets 'p'.
== Observations ==
- SET STORAGE MAIN crashes the same way (index column p -> m). SET STORAGE PLAIN does not crash (p -> p).
- REINDEX or DROP + CREATE INDEX sets the index column back to 'p' and the crash stops.
- With core's tsvector GiST opclass (key type gtsvector, typstorage 'p'), SET STORAGE EXTENDED also changes
the index column to 'x', but inserts do not crash.
Backtrace on 16.13 with the PGDG debug symbols (postgresql-16-dbgsym):
#0 makesign (sign=..., a=0xaaaaf41f9498, siglen=12) at contrib/pg_trgm/trgm_gist.c:109
len = 44914044
#1 gtrgm_penalty (fcinfo=...) at contrib/pg_trgm/trgm_gist.c:722
#3 gistpenalty () at src/backend/access/gist/gistutil.c:733
#4 gistchoose () at src/backend/access/gist/gistutil.c:458
#5 gistdoinsert () at src/backend/access/gist/gist.c:749
#6 gistinsert () at src/backend/access/gist/gist.c:184
#7 ExecInsertIndexTuples () at src/backend/executor/execIndexing.c:432
In another run, the crash was in unionkey() at contrib/pg_trgm/trgm_gist.c:555.
Bytes of the key being inserted (gdb: x/12xb a, in makesign):
b9 01 20 20 31 20 20 32 20 20 70 20
The first byte (0xb9) is a 1-byte varlena header (length 92), followed by the ARRKEY flag (0x01) and the
trigrams " 1", " 2", " p". Read with a 4-byte header, as the TRGM macros do, the length is
(0x202001b9 >> 2) and the flag byte is 0x31, which matches len = 44914044 above.
== Where this seems to come from ==
SetIndexStorageProperties() in src/backend/commands/tablecmds.c (current master) sets attstorage on every
index column whose indkey references the altered column, without comparing the index column's type with
the table column's type. With attstorage 'x' or 'm' on the gtrgm column, the new index tuple gets a short
varlena header, and gtrgm_decompress() passes it on unchanged (it uses DatumGetTextPP).
I hope this helps.
Best,
SetIndexStorageProperties(). That copied the heap attstorage even when
ConstructTupleDescriptor() had replaced the index column type with the
opclass STORAGE type.
gist_trgm_ops stores gtrgm, whose typstorage is PLAIN. After SET STORAGE
EXTENDED the gist attstorage became EXTENDED. Later inserts packed short
varlena headers. TRGM macros use VARSIZE on a four-byte header and the
backend crashed.
Skip the catalog update when the index column type differs from the heap
column. A btree index on the same column still receives the new setting.
SET COMPRESSION uses the same helper.Regards,
Rachitskiy Andrey
So, looks like all opckkeytype != opintype indexes are in danger zone here. I looked across core+contrib opclass with an index STORAGE type that differs from the heap column type.
Btree, HASH and BRIN indexes are protected. This is something which is controlled by amstorage, for btree you will get:
reshke=# CREATE OPERATOR CLASS poison_spg FOR TYPE text USING btree AS
OPERATOR 1 =(text,text),
STORAGE bytea;
ERROR: storage type cannot be different from data type for access method "btree"
OPERATOR 1 =(text,text),
STORAGE bytea;
ERROR: storage type cannot be different from data type for access method "btree"
So, looks like opckkeytype != opintype is never met in btree case, explaining lack of problem report in this area.
GIN, GiST, SpGiST are not:
For GiST + intarray we can get wrong results using index scan (lost rows)
CREATE EXTENSION IF NOT EXISTS intarray;
CREATE TABLE a(v int4[]);
INSERT INTO a SELECT ARRAY[g, g%7, g%13]
FROM generate_series(1,20000) g;
CREATE INDEX ON a USING gist (v gist__intbig_ops(siglen=8));
ALTER TABLE a ALTER COLUMN v SET STORAGE EXTENDED;
INSERT INTO a VALUES (ARRAY[42]); -- writes short-packed key
SET enable_seqscan=off;
SELECT count(*) FROM a WHERE v @> ARRAY[42];
CREATE EXTENSION
CREATE TABLE
INSERT 0 20000
CREATE INDEX
ALTER TABLE
INSERT 0 1
SET
count
-------
1
(1 row)
CREATE TABLE a(v int4[]);
INSERT INTO a SELECT ARRAY[g, g%7, g%13]
FROM generate_series(1,20000) g;
CREATE INDEX ON a USING gist (v gist__intbig_ops(siglen=8));
ALTER TABLE a ALTER COLUMN v SET STORAGE EXTENDED;
INSERT INTO a VALUES (ARRAY[42]); -- writes short-packed key
SET enable_seqscan=off;
SELECT count(*) FROM a WHERE v @> ARRAY[42];
CREATE EXTENSION
CREATE TABLE
INSERT 0 20000
CREATE INDEX
ALTER TABLE
INSERT 0 1
SET
count
-------
1
(1 row)
reshke=# drop index a_v_idx ;
DROP INDEX
reshke=# SELECT count(*) FROM a WHERE v @> ARRAY[42];
count
-------
2
(1 row)
DROP INDEX
reshke=# SELECT count(*) FROM a WHERE v @> ARRAY[42];
count
-------
2
(1 row)
For SpGiST, looks like all in-core classes are fine, but third-party extensions can segfault server in the same way, so without Andrey v1 we are not protected.
Something like this:
-- Extension code sketch
--
-- Datum
-- poison_choose(PG_FUNCTION_ARGS)
-- {
-- spgChooseIn *in = (spgChooseIn *) PG_GETARG_POINTER(0);
-- text *key = (text *) in->datum;
-- int len = VARSIZE(key); /*
-- 4-byte-header read (VARSIZE).
-- Correct code: VARSIZE_ANY(key), or
-- PG_DETOAST_DATUM, or VARATT_IS_4B_U checks. */
-- ...
-- }
--
-- Datum
-- poison_choose(PG_FUNCTION_ARGS)
-- {
-- spgChooseIn *in = (spgChooseIn *) PG_GETARG_POINTER(0);
-- text *key = (text *) in->datum;
-- int len = VARSIZE(key); /*
-- 4-byte-header read (VARSIZE).
-- Correct code: VARSIZE_ANY(key), or
-- PG_DETOAST_DATUM, or VARATT_IS_4B_U checks. */
-- ...
-- }
And then segfault in picksplit() or choose() functions
For GIN I didn't manage to get query crashing server, but there is repro for catalog lies about type attstorage:
reshke=# CREATE TABLE g(s text);
CREATE INDEX ON g USING gin (s gin_trgm_ops);
ALTER TABLE g ALTER COLUMN s SET STORAGE EXTENDED;
SELECT atttypid::regtype, attstorage, attcompression
FROM pg_attribute
WHERE attrelid = 'g_s_idx'::regclass;
CREATE TABLE
CREATE INDEX
ALTER TABLE
atttypid | attstorage | attcompression
----------+------------+----------------
integer | x |
(1 row)
CREATE INDEX ON g USING gin (s gin_trgm_ops);
ALTER TABLE g ALTER COLUMN s SET STORAGE EXTENDED;
SELECT atttypid::regtype, attstorage, attcompression
FROM pg_attribute
WHERE attrelid = 'g_s_idx'::regclass;
CREATE TABLE
CREATE INDEX
ALTER TABLE
atttypid | attstorage | attcompression
----------+------------+----------------
integer | x |
(1 row)
attstorage = x for int is nonsense?
I also tried to fix REINDEX for poisoned indexes - PFA POC patch, which resets all bogus catalog descriptions for indexes upon reindex_index
Best regards,
Kirill Reshke
Attachment
pgsql-bugs by date: