BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

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

BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      19720
Logged by:          Nate Clark
Email address:      natec425@gmail.com
PostgreSQL version: 18.6
Operating system:   macOS 26 (aarch64)
Description:        

Hey all,

I experienced index corruption for a trigram GiST index at work. I believe
it is due to an invalid `TRGM->flag` state.

Currently, `gtrgm_union` sets `result->flag = ALLISTRUE` when it determines
that the signature is all true, but this drops the `SIGNKEY` bit. Downstream
of this, `unionkey` branches on the flag and defaults to the array handling
else branch. This array branch interprets the state as a 0 length array and
produces an empty signature. These two pieces together mean that an insert
in this bad flag state will produce a downlink with only the signature bits
for that new inserted value rather than the true union of the children.

I believe the fix is to change it to `|= ALLISTRUE` (similar to the other
GiST op classes). I've tested this change locally against the following
repro script.

I get the following output against `master` and 18.6 (with some small jitter
in total count):

         what          | count
-----------------------+-------
 tests (total minus 3) |     0
 total                 | 33120

With the `|=` patch I get:

         what          | count
-----------------------+-------
 tests (total minus 3) | 33130
 total                 | 33133

Thanks so much for your time,
Nate Clark

-- repro script
create extension if not exists pg_trgm;
create extension if not exists pageinspect;
drop table if exists t;
create table t (v text);
create index t_idx on t
  -- siglen 4 to make it simpler to hit ALLISTRUE
  using gist (v gist_trgm_ops(siglen=4));

-- Insert until the root splits as an internal node
-- so we get signature, not array, logic.
do $$
begin
  -- insert a null to hit the null handling branch involving gtrgm_union
  insert into t values (null);
  -- insert an ALLISTRUE input
  insert into t select string_agg(i::text, ' ')
                from generate_series(1, 100) i;
  -- insert until the root splits
  loop
    insert into t values ('test');
    exit when exists (
      select from gist_page_items_bytea(get_raw_page('t_idx', 0)) r,
        gist_page_opaque_info(get_raw_page('t_idx',
          (r.ctid::text::point)[0]::int)) c
      where r.itemoffset = 1 and not 'leaf' = any(c.flags));
  end loop;
end $$;

-- Insert one row whose trigrams set 0 bits.
-- This causes all children to be unreachable.
insert into t values ('');

select 'total' as what, count(*) from t
union
select 'tests (total minus 3)', count(*) from t where v = 'test';




Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Kirill Reshke <reshkekirill@gmail.com>
Дата:
On Sat, 26 Sept 2026 at 12:15, PG Bug reporting form
 wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19720
> Logged by:          Nate Clark
> Email address:      natec425@gmail.com
> PostgreSQL version: 18.6
> Operating system:   macOS 26 (aarch64)
> Description:
>
> Hey all,
>
> I experienced index corruption for a trigram GiST index at work. I believe
> it is due to an invalid `TRGM->flag` state.
>
> Currently, `gtrgm_union` sets `result->flag = ALLISTRUE` when it determines
> that the signature is all true, but this drops the `SIGNKEY` bit. Downstream
> of this, `unionkey` branches on the flag and defaults to the array handling
> else branch. This array branch interprets the state as a 0 length array and
> produces an empty signature. These two pieces together mean that an insert
> in this bad flag state will produce a downlink with only the signature bits
> for that new inserted value rather than the true union of the children.
>
> I believe the fix is to change it to `|= ALLISTRUE` (similar to the other
> GiST op classes). I've tested this change locally against the following
> repro script.
>
> I get the following output against `master` and 18.6 (with some small jitter
> in total count):
>
>          what          | count
> -----------------------+-------
>  tests (total minus 3) |     0
>  total                 | 33120
>
> With the `|=` patch I get:
>
>          what          | count
> -----------------------+-------
>  tests (total minus 3) | 33130
>  total                 | 33133
>
> Thanks so much for your time,
> Nate Clark
>
> -- repro script
> create extension if not exists pg_trgm;
> create extension if not exists pageinspect;
> drop table if exists t;
> create table t (v text);
> create index t_idx on t
>   -- siglen 4 to make it simpler to hit ALLISTRUE
>   using gist (v gist_trgm_ops(siglen=4));
>
> -- Insert until the root splits as an internal node
> -- so we get signature, not array, logic.
> do $$
> begin
>   -- insert a null to hit the null handling branch involving gtrgm_union
>   insert into t values (null);
>   -- insert an ALLISTRUE input
>   insert into t select string_agg(i::text, ' ')
>                 from generate_series(1, 100) i;
>   -- insert until the root splits
>   loop
>     insert into t values ('test');
>     exit when exists (
>       select from gist_page_items_bytea(get_raw_page('t_idx', 0)) r,
>         gist_page_opaque_info(get_raw_page('t_idx',
>           (r.ctid::text::point)[0]::int)) c
>       where r.itemoffset = 1 and not 'leaf' = any(c.flags));
>   end loop;
> end $$;
>
> -- Insert one row whose trigrams set 0 bits.
> -- This causes all children to be unreachable.
> insert into t values ('');
>
> select 'total' as what, count(*) from t
> union
> select 'tests (total minus 3)', count(*) from t where v = 'test';
>
>
>


Hi!
I checked this report  at 1a846a55, and it looks like there is indeed
corruption.

```
reshke=# set enable_seqscan to off;
SET
reshke=# select 'total' as what, count(*) from t
union
select 'tests (total minus 3)', count(*) from t where v = 'test';
         what          | count
-----------------------+-------
 tests (total minus 3) |     0
 total                 | 33117
(2 rows)

reshke=# set enable_seqscan to on;
SET
reshke=# select 'total' as what, count(*) from t
union
select 'tests (total minus 3)', count(*) from t where v = 'test';
         what          | count
-----------------------+-------
 tests (total minus 3) | 33114
 total                 | 33117
(2 rows)
```

Your analysis also looks correct for me, would you share a patch with
result->flag = ALLISTRUE; -> result->flag |= ALLISTRUE; ?

I also used patch from [0] and it complans with  ERROR:  index "t_idx"
has inconsistent records on page 293 offset 1


[0] https://commitfest.postgresql.org/patch/5879/
-- 
Best regards,
Kirill Reshke


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Kirill Reshke <reshkekirill@gmail.com>
Дата:
> Your analysis also looks correct for me, would you share a patch with
> result->flag = ALLISTRUE; -> result->flag |= ALLISTRUE; ?
>

This looks like oversight of [0]

[0] https://git.postgresql.org/cgit/postgresql.git/diff/contrib/pg_trgm/trgm_gist.c?id=911e7020



-- 
Best regards,
Kirill Reshke


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Nate Clark <natec425@gmail.com>
Дата:
Вложения:
> Your analysis also looks correct for me, would you share a patch with
> result->flag = ALLISTRUE; -> result->flag |= ALLISTRUE; ?

Of course. I've attached a patch with that one line change.

Please let me know if there is anything else I should do, and thanks
for your help!

Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Nate Clark <natec425@gmail.com>
Дата:
I went ahead and created a commitfest entry for this:
https://commitfest.postgresql.org/patch/7385/

Please let me know if I've done anything incorrectly!

On Sat, Sep 26, 2026 at 9:26 AM Nate Clark  wrote:
>
> > Your analysis also looks correct for me, would you share a patch with
> > result->flag = ALLISTRUE; -> result->flag |= ALLISTRUE; ?
>
> Of course. I've attached a patch with that one line change.
>
> Please let me know if there is anything else I should do, and thanks
> for your help!


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Дата:
Hi,

On Sat, 26 Sept 2026 at 19:56, Nate Clark  wrote:
>
> > Your analysis also looks correct for me, would you share a patch with
> > result->flag = ALLISTRUE; -> result->flag |= ALLISTRUE; ?
>
> Of course. I've attached a patch with that one line change.

Thanks for the report and patch. The fix looks right to me.

I wonder if we should add a regression test for this too. Since we
have the reproducer above, I'll defer to the committer on whether to
include one..

Marking it RfC.

Regards,
Ayush


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
On Mon, Oct 5, 2026 at 12:37 PM Ayush Tiwari
 wrote:
> Thanks for the report and patch. The fix looks right to me.

This should be backpatched to all supported versions?

The fix doesn't repair existing indexes. Right? If so, we should advise users
to REINDEX all existing gist_trgm_ops indexes after upgrading to a minor
release containing the fix?

Regards,

-- 
Fujii Masao


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Дата:
Hi,

On Tue, 3 Nov 2026 at 15:03, Fujii Masao  wrote:
>
> On Mon, Oct 5, 2026 at 12:37 PM Ayush Tiwari
>  wrote:
> > Thanks for the report and patch. The fix looks right to me.
>
> This should be backpatched to all supported versions?

Yes, this needs to be backpatched till PG 14.

> The fix doesn't repair existing indexes. Right? If so, we should advise users
> to REINDEX all existing gist_trgm_ops indexes after upgrading to a minor
> release containing the fix?

Right, I still needed REINDEX after updating the library. I agree
we should mention that in the release notes.

Regards,
Ayush


Re: BUG #19720: pg_trgm GiST index corruption from gtrgm_union() dropping SIGNKEY

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
On Tue, Nov 3, 2026 at 7:36 PM Ayush Tiwari  wrote:
> Yes, this needs to be backpatched till PG 14.

Ok, I've pushed the patch and backpatched it to v14. Thanks!

Regards,

-- 
Fujii Masao


FAQ