pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
15+ messages / 5 participants
[nested] [flat]
* BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2020-12-27 17:18 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: PG Bug reporting form @ 2020-12-27 17:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kudzia@gmail.com
The following bug has been logged on the website:
Bug reference: 16792
Logged by: pawel kudzia
Email address: kudzia@gmail.com
PostgreSQL version: 11.10
Operating system: Debian Buster x86_64
Description:
I'd like to ask for advice how to proceed with pin-pointing cause of the
silent corruption of GIN index that I'm facing.
Relevant part of the table structure:
CREATE TABLE public.entity (
entity_id bigint NOT NULL,
attribute_value_ids integer[] NOT NULL,
attribute_name_ids integer[] NOT NULL,
[..]
);
ALTER TABLE ONLY public.entity ADD CONSTRAINT entity_pkey PRIMARY KEY
(entity_id);
CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
(attribute_name_ids public.gin__int_ops);
CREATE INDEX entity_attribute_value_ids_gin ON public.entity USING gin
(attribute_value_ids public.gin__int_ops);
How does the issue manifest?
Queries which use GIN on integer[] column occasionally return too many rows,
including ones that actually do not match criteria expressed in WHERE.
Queries like below should never return any rows, yet - occasionally they do
return some results:
data=> SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{4980}' )
AND NOT ( (attribute_name_ids||0) && '{4980}') ;
entity_id
-----------
31213924
31195117
31209184
(3 rows)
Query plan:
QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity (cost=50.39..3068.11 rows=26923 width=22)
Recheck Cond: (attribute_name_ids && '{4980}'::integer[])
Filter: (NOT ((attribute_name_ids || 0) && '{4980}'::integer[]))
-> Bitmap Index Scan on entity_attribute_name_ids_gin (cost=0.00..50.39
rows=27058 width=0)
Index Cond: (attribute_name_ids && '{4980}'::integer[])
(5 rows)
The query was crafted intentionally to let PostgreSQL use GIN index
entity_attribute_name_ids_gin [ left side of AND ] and then filter out rows
that after inspection of their values do not actually match that criteria.
What have I done so far?
Originally the problem was discovered when running PostgreSQL 11.7 from
Debian's repo. In the first step we've upgraded to PostgreSQL 11.9 also from
Debian's repo and run:
reindex (verbose) table entity; vacuum (verbose, full, analyze) entity;
After few days of updates the problem returned.
We've upgraded to PostgreSQL 11.10 from postgresql.org repository for Debian
and reindexed / vacuumed again. After few weeks of updates problem returned
again.
Other information worth noting:
* table in question has 38M rows and is the only table created after
PostgreSQL installation
* server in question is master of streaming replication; issue occurs also
on the slave servers - this suggests corruption of the on-disk GIN index
data
* just rewriting rows falsely returned by select above - UPDATE entity SET
attribute_name_ids ='{....}' WHERE entity_id=123 - fixes the issue
* we've set up rudimentary consistency checks that allow to catch some of
the corruptions within 1h of occurring - so far corruptions only happen to
rows that have been updated since the previous check
* server in question handles heavy read/write traffic
* PostgreSQL in question runs in LXC container with Debian Buster running on
top of Debian Buster running on top of bare-metal server
* it's highly unlikely that hardware malfunction is to be blamed - the same
physical server handles also few MySQL instances with hundreds of GB of data
with heavy consistency checks cross checking content of MySQL with
PostgreSQL, unpacking gzip'ed blobs stored in MySQL databases and
de-serializing objects stored there. if there was a bit-rot / bit-flip in
memory would that's not detected or fixed by ECC - high level consistency
checks would pick it.
* despite numerous attempts i cannot reproduce the issue in test
environment, possibly due to much lower level of write traffic. it takes
days to weeks to have it re-occur on the production server.
Thank you in advance for your suggestions how to tackle this.
best regards,
Pawel
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-02-12 12:35 Pawel Kudzia <kudzia@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: Pawel Kudzia @ 2021-02-12 12:35 UTC (permalink / raw)
To: Pawel Kudzia <kudzia@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sun, Dec 27, 2020 at 6:19 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 16792
> Logged by: pawel kudzia
> Email address: kudzia@gmail.com
> PostgreSQL version: 11.10
> Operating system: Debian Buster x86_64
> Description:
>
> I'd like to ask for advice how to proceed with pin-pointing cause of the
> silent corruption of GIN index that I'm facing.
>
> Relevant part of the table structure:
>
> CREATE TABLE public.entity (
> entity_id bigint NOT NULL,
> attribute_value_ids integer[] NOT NULL,
> attribute_name_ids integer[] NOT NULL,
> [..]
> );
>
> ALTER TABLE ONLY public.entity ADD CONSTRAINT entity_pkey PRIMARY KEY
> (entity_id);
> CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
> (attribute_name_ids public.gin__int_ops);
> CREATE INDEX entity_attribute_value_ids_gin ON public.entity USING gin
> (attribute_value_ids public.gin__int_ops);
>
> How does the issue manifest?
>
> Queries which use GIN on integer[] column occasionally return too many rows,
> including ones that actually do not match criteria expressed in WHERE.
> Queries like below should never return any rows, yet - occasionally they do
> return some results:
>
> data=> SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{4980}' )
> AND NOT ( (attribute_name_ids||0) && '{4980}') ;
> entity_id
> -----------
> 31213924
> 31195117
> 31209184
> (3 rows)
>
> Query plan:
> QUERY PLAN
> -------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on entity (cost=50.39..3068.11 rows=26923 width=22)
> Recheck Cond: (attribute_name_ids && '{4980}'::integer[])
> Filter: (NOT ((attribute_name_ids || 0) && '{4980}'::integer[]))
> -> Bitmap Index Scan on entity_attribute_name_ids_gin (cost=0.00..50.39
> rows=27058 width=0)
> Index Cond: (attribute_name_ids && '{4980}'::integer[])
> (5 rows)
>
> The query was crafted intentionally to let PostgreSQL use GIN index
> entity_attribute_name_ids_gin [ left side of AND ] and then filter out rows
> that after inspection of their values do not actually match that criteria.
>
> What have I done so far?
>
> Originally the problem was discovered when running PostgreSQL 11.7 from
> Debian's repo. In the first step we've upgraded to PostgreSQL 11.9 also from
> Debian's repo and run:
>
> reindex (verbose) table entity; vacuum (verbose, full, analyze) entity;
>
> After few days of updates the problem returned.
>
> We've upgraded to PostgreSQL 11.10 from postgresql.org repository for Debian
> and reindexed / vacuumed again. After few weeks of updates problem returned
> again.
>
> Other information worth noting:
> * table in question has 38M rows and is the only table created after
> PostgreSQL installation
> * server in question is master of streaming replication; issue occurs also
> on the slave servers - this suggests corruption of the on-disk GIN index
> data
> * just rewriting rows falsely returned by select above - UPDATE entity SET
> attribute_name_ids ='{....}' WHERE entity_id=123 - fixes the issue
> * we've set up rudimentary consistency checks that allow to catch some of
> the corruptions within 1h of occurring - so far corruptions only happen to
> rows that have been updated since the previous check
> * server in question handles heavy read/write traffic
> * PostgreSQL in question runs in LXC container with Debian Buster running on
> top of Debian Buster running on top of bare-metal server
> * it's highly unlikely that hardware malfunction is to be blamed - the same
> physical server handles also few MySQL instances with hundreds of GB of data
> with heavy consistency checks cross checking content of MySQL with
> PostgreSQL, unpacking gzip'ed blobs stored in MySQL databases and
> de-serializing objects stored there. if there was a bit-rot / bit-flip in
> memory would that's not detected or fixed by ECC - high level consistency
> checks would pick it.
> * despite numerous attempts i cannot reproduce the issue in test
> environment, possibly due to much lower level of write traffic. it takes
> days to weeks to have it re-occur on the production server.
>
> Thank you in advance for your suggestions how to tackle this.
>
> best regards,
> Pawel
>
I'd like to provide more details from another occurrence of this issue:
psql (11.7 (Debian 11.7-0+deb10u1), server 11.9 (Debian 11.9-0+deb10u1))
Example where GIN is returning misleading information:
data=# SELECT entity_id,attribute_name_ids FROM entity WHERE (
attribute_name_ids && '{471}' ) AND NOT ( (attribute_name_ids||0) &&
'{471}') ;
entity_id | attribute_name_ids
-----------+------------------------------------------------------------------------------
197911914 | {2,3,4,6,7,8,9,10,13,17,35,48,66,71,3270,3273,3279,4377,4842,5379,5787,7416}
(1 row)
data=# EXPLAIN ANALYZE SELECT entity_id,attribute_name_ids FROM
entity WHERE ( attribute_name_ids && '{471}' ) AND NOT (
(attribute_name_ids||0) && '{471}') ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity (cost=71.04..3432.73 rows=29881
width=126) (actual time=64.179..65.255 rows=1 loops=1)
Recheck Cond: (attribute_name_ids && '{471}'::integer[])
Filter: (NOT ((attribute_name_ids || 0) && '{471}'::integer[]))
Rows Removed by Filter: 8814
Heap Blocks: exact=7877
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..71.04 rows=30031 width=0) (actual time=10.396..10.398
rows=13628 loops=1)
Index Cond: (attribute_name_ids && '{471}'::integer[])
Planning Time: 0.164 ms
Execution Time: 65.458 ms
(9 rows)
When GIN is not used results are as expected:
data=# EXPLAIN ANALYZE SELECT entity_id,attribute_name_ids FROM
entity WHERE entity_id=197911914 AND attribute_name_ids && '{471}';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using entity_pkey on entity (cost=0.00..0.20 rows=1
width=126) (actual time=0.090..0.091 rows=0 loops=1)
Index Cond: (entity_id = 197911914)
Filter: (attribute_name_ids && '{471}'::integer[])
Rows Removed by Filter: 1
Planning Time: 0.233 ms
Execution Time: 0.116 ms
(6 rows)
data=# SELECT entity_id,attribute_name_ids FROM entity WHERE
entity_id=197911914 AND attribute_name_ids && '{471}';
entity_id | attribute_name_ids
-----------+--------------------
(0 rows)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 06:40 Pawel Kudzia <kudzia@gmail.com>
parent: Pawel Kudzia <kudzia@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Pawel Kudzia @ 2021-06-17 06:40 UTC (permalink / raw)
To: Pawel Kudzia <kudzia@gmail.com>; pgsql-bugs@lists.postgresql.org
Based on the suggestions we've received from IRC we've:
* Moved some of our postgresql instances to another
physical server,
* Enabled data_checksums on all instances,
* Upgraded upgraded our servers to 13.3
(Debian 13.3-1.pgdg100+1) with hope that
https://github.com/postgres/postgres/commit/0d779d22a290a89b6c892137a37280b9588ad0cc
addresses the issue [ although it's ts-vector
specific, so unlikely to be related to our issue ].
Yet soon after we've observe another inconsistency.
Recap of the problem - SELECTs return rows, based on GIN,
that do not actually meet criteria expressed in WHERE.
SELECT entity_id,attribute_name_ids FROM entity WHERE
( attribute_name_ids && '{7572}' ) AND NOT
( (attribute_name_ids||0) && '{7572}') LIMIT 100 ;
entity_id | attribute_name_ids
-----------+----------------------------------------------------------------------------------------
22327791 | {1,2,3,4,6,8,9,10,11,13,14,17,19,21,35,72,366,1659,2208,2211,3270,3273,3279,5787,7650}
(1 row)
EXPLAIN ANALYZE of the query above:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=35.46..46.67 rows=100 width=132) (actual
time=307.221..335.842 rows=1 loops=1)
-> Bitmap Heap Scan on entity (cost=35.46..3705.32 rows=32724
width=132) (actual time=307.220..335.839 rows=1 loops=1)
Recheck Cond: (attribute_name_ids && '{7572}'::integer[])
Rows Removed by Index Recheck: 72012
Filter: (NOT ((attribute_name_ids || 0) && '{7572}'::integer[]))
Rows Removed by Filter: 21501
Heap Blocks: exact=8998 lossy=9257
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..35.46 rows=32889 width=0) (actual time=19.790..19.790
rows=115485 loops=1)
Index Cond: (attribute_name_ids && '{7572}'::integer[])
Planning Time: 0.068 ms
Execution Time: 335.879 ms
(11 rows)
Relevant part of the table structure:
CREATE TABLE public.entity (
entity_id bigint NOT NULL,
attribute_value_ids integer[] NOT NULL,
attribute_name_ids integer[] NOT NULL,
[..]
);
ALTER TABLE ONLY public.entity ADD CONSTRAINT entity_pkey PRIMARY KEY
(entity_id);
CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
(attribute_name_ids public.gin__int_ops);
CREATE INDEX entity_attribute_value_ids_gin ON public.entity USING gin
(attribute_value_ids public.gin__int_ops);
There are at leat tens of updates / inserts per second to the table all the
time. the issue above manifests very rarely, after few days if not weeks of
uptime. we did not find any deterministic way of reproducing it, but it's
a matter of time till it shows.
The issue persist is related to on-disk data, it replicates from
streaming replication
masters to slaves.
Thank you in advance for your suggestions how to tackle this.
--
regards,
Pawel Kudzia
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 08:00 Pawel Kudzia <kudzia@gmail.com>
parent: Pawel Kudzia <kudzia@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Pawel Kudzia @ 2021-06-17 08:00 UTC (permalink / raw)
To: Pawel Kudzia <kudzia@gmail.com>; pgsql-bugs@lists.postgresql.org
with help from IRC we've found that decreasing work_mem from 1MB to 256kB
or less makes the problem go away:
data=# show work_mem;
work_mem
----------
256kB
(1 row)
data=# SELECT entity_id,attribute_name_ids FROM entity WHERE (
attribute_name_ids && '{7572}' ) AND NOT ( (attribute_name_ids||0) &&
'{7572}') LIMIT 100 ;
entity_id | attribute_name_ids
-----------+--------------------
(0 rows)
data=# explain analyze SELECT entity_id,attribute_name_ids FROM entity
WHERE ( attribute_name_ids && '{7572}' ) AND NOT (
(attribute_name_ids||0) && '{7572}') LIMIT 100 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=26.53..38.04 rows=100 width=132) (actual
time=110.013..110.015 rows=0 loops=1)
-> Bitmap Heap Scan on entity (cost=26.53..3780.78 rows=32606
width=132) (actual time=110.011..110.011 rows=0 loops=1)
Recheck Cond: (attribute_name_ids && '{7572}'::integer[])
Rows Removed by Index Recheck: 102983
Filter: (NOT ((attribute_name_ids || 0) && '{7572}'::integer[]))
Rows Removed by Filter: 21501
Heap Blocks: exact=898 lossy=13752
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..26.53 rows=32770 width=0) (actual time=3.582..3.583
rows=21518 loops=1)
Index Cond: (attribute_name_ids && '{7572}'::integer[])
Planning Time: 0.173 ms
Execution Time: 110.220 ms
(11 rows)
problem manifests again with work_mem increased to 512kB or higher:
data=# show work_mem;
work_mem
----------
512kB
(1 row)
data=# SELECT entity_id,attribute_name_ids FROM entity WHERE (
attribute_name_ids && '{7572}' ) AND NOT ( (attribute_name_ids||0) &&
'{7572}') LIMIT 100 ;
entity_id | attribute_name_ids
-----------+----------------------------------------------------------------------------------------
22327791 | {1,2,3,4,6,8,9,10,11,13,14,17,19,21,35,72,366,1659,2208,2211,3270,3273,3279,5787,7650}
(1 row)
data=# explain analyze SELECT entity_id,attribute_name_ids FROM entity
WHERE ( attribute_name_ids && '{7572}' ) AND NOT (
(attribute_name_ids||0) && '{7572}') LIMIT 100 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=26.73..38.14 rows=100 width=132) (actual
time=112.268..119.475 rows=1 loops=1)
-> Bitmap Heap Scan on entity (cost=26.73..3748.28 rows=32606
width=132) (actual time=112.267..119.473 rows=1 loops=1)
Recheck Cond: (attribute_name_ids && '{7572}'::integer[])
Rows Removed by Index Recheck: 68905
Filter: (NOT ((attribute_name_ids || 0) && '{7572}'::integer[]))
Rows Removed by Filter: 21501
Heap Blocks: exact=5630 lossy=9012
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..26.73 rows=32770 width=0) (actual time=3.924..3.924
rows=21518 loops=1)
Index Cond: (attribute_name_ids && '{7572}'::integer[])
Planning Time: 0.113 ms
Execution Time: 119.801 ms
(11 rows)
"vacuum table entity;" did not help, neither did
"select gin_clean_pending_list('entity_attribute_name_ids_gin');"
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 14:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pawel Kudzia <kudzia@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2021-06-17 14:07 UTC (permalink / raw)
To: Pawel Kudzia <kudzia@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Pawel Kudzia <kudzia@gmail.com> writes:
> with help from IRC we've found that decreasing work_mem from 1MB to 256kB
> or less makes the problem go away:
Hmm. So that suggests that the index itself is *not* corrupt,
but the problem is associated with a bug in the indexscan
algorithms.
Have you experimented with different index opclasses? Your
original report said you were using gin__int_ops, but that's
not the default, and we sort of deprecate intarray these days.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 19:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Tom Lane @ 2021-06-17 19:49 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Pawel Kudzia <kudzia@gmail.com>; pgsql-bugs@lists.postgresql.org
I wrote:
> Pawel Kudzia <kudzia@gmail.com> writes:
>> with help from IRC we've found that decreasing work_mem from 1MB to 256kB
>> or less makes the problem go away:
> Hmm. So that suggests that the index itself is *not* corrupt,
> but the problem is associated with a bug in the indexscan
> algorithms.
After staring at the code I think there is at least one bug, and
possibly two, in shimTriConsistentFn. That's likely to be implicated
here because intarray's GIN opclass only provides a bool consistent
function. I'm not very clear on the circumstances that lead to the scan
code inventing GIN_MAYBE inputs, so I haven't been able to construct a
test case.
The definite bug is triggered because intarray relies on the API
specification that says that if the search mode is
GIN_SEARCH_MODE_DEFAULT, then the consistentFn will only be called
when there's at least one TRUE key:
case RTOverlapStrategyNumber:
/* result is not lossy */
*recheck = false;
/* at least one element in check[] is true, so result = true */
res = true;
break;
shimTriConsistentFn ignores this and calls it on all-FALSE inputs,
for which it'll incorrectly get a TRUE result, as it will also for
all the following checks. The upshot is that shimTriConsistentFn
will convert any case with a MAYBE input to a hard TRUE result,
with no recheck requirement. This'd easily explain the reported
misbehavior. So in the spot where we do this:
/* First call consistent function with all the maybe-inputs set FALSE */
for (i = 0; i < nmaybe; i++)
key->entryRes[maybeEntries[i]] = GIN_FALSE;
curResult = directBoolConsistentFn(key);
we need to add some code that checks for default searchMode, and in
that case doesn't call the consistentFn unless at least one
(non-MAYBE) input is TRUE.
The other thing that I'm unsure about, because the code is horribly
underdocumented, is that it's not very clear whether
shimTriConsistentFn is accurately converting between the bool and
the tristate APIs. That's because it's not very clear what the
tristate API actually *is*. In particular, is the end state of
key->recheckCurItem meaningful in the tristate case? If it's not,
then the short-circuit case for no MAYBE inputs is broken, because
it'll return TRUE when the bool consistentFn is trying to tell us
to recheck. But if it is meaningful, then the multiway case is broken,
because it will return the recheckCurItem result set by the last call to
the bool consistentfn; which might be false even though other calls said
true. (So in that case I think we'd need "key->recheckCurItem = recheck"
at the end.) I also wonder how any of that works correctly for real
triconsistent functions, which don't have access to the recheckCurItem
flag.
Anyway, I'm punting this to the code authors. I'd like to see
some comments clarifying what the API really is, not just a
quick-n-dirty code fix.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 21:55 Alexander Korotkov <aekorotkov@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 15+ messages in thread
From: Alexander Korotkov @ 2021-06-17 21:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, Jun 17, 2021 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > Pawel Kudzia <kudzia@gmail.com> writes:
> >> with help from IRC we've found that decreasing work_mem from 1MB to 256kB
> >> or less makes the problem go away:
>
> > Hmm. So that suggests that the index itself is *not* corrupt,
> > but the problem is associated with a bug in the indexscan
> > algorithms.
>
> After staring at the code I think there is at least one bug, and
> possibly two, in shimTriConsistentFn. That's likely to be implicated
> here because intarray's GIN opclass only provides a bool consistent
> function. I'm not very clear on the circumstances that lead to the scan
> code inventing GIN_MAYBE inputs, so I haven't been able to construct a
> test case.
>
> The definite bug is triggered because intarray relies on the API
> specification that says that if the search mode is
> GIN_SEARCH_MODE_DEFAULT, then the consistentFn will only be called
> when there's at least one TRUE key:
>
> case RTOverlapStrategyNumber:
> /* result is not lossy */
> *recheck = false;
> /* at least one element in check[] is true, so result = true */
> res = true;
> break;
>
> shimTriConsistentFn ignores this and calls it on all-FALSE inputs,
> for which it'll incorrectly get a TRUE result, as it will also for
> all the following checks. The upshot is that shimTriConsistentFn
> will convert any case with a MAYBE input to a hard TRUE result,
> with no recheck requirement. This'd easily explain the reported
> misbehavior. So in the spot where we do this:
>
> /* First call consistent function with all the maybe-inputs set FALSE */
> for (i = 0; i < nmaybe; i++)
> key->entryRes[maybeEntries[i]] = GIN_FALSE;
> curResult = directBoolConsistentFn(key);
>
> we need to add some code that checks for default searchMode, and in
> that case doesn't call the consistentFn unless at least one
> (non-MAYBE) input is TRUE.
+1,
I think in default search mode we can just start with curResult equal
to GIN_FALSE instead of calling directBoolConsistentFn().
> The other thing that I'm unsure about, because the code is horribly
> underdocumented, is that it's not very clear whether
> shimTriConsistentFn is accurately converting between the bool and
> the tristate APIs. That's because it's not very clear what the
> tristate API actually *is*. In particular, is the end state of
> key->recheckCurItem meaningful in the tristate case? If it's not,
> then the short-circuit case for no MAYBE inputs is broken, because
> it'll return TRUE when the bool consistentFn is trying to tell us
> to recheck. But if it is meaningful, then the multiway case is broken,
> because it will return the recheckCurItem result set by the last call to
> the bool consistentfn; which might be false even though other calls said
> true. (So in that case I think we'd need "key->recheckCurItem = recheck"
> at the end.) I also wonder how any of that works correctly for real
> triconsistent functions, which don't have access to the recheckCurItem
> flag.
As far as I recall, returning MAYBE for no MAYBE inputs in the
triConsistent function is equivalent to setting the recheck flag in
the bool consistent function. Thus, short-circuit case for no MAYBE
inputs should be broken.
> Anyway, I'm punting this to the code authors. I'd like to see
> some comments clarifying what the API really is, not just a
> quick-n-dirty code fix.
Yep, I'm going to have a closer look at this tomorrow.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 22:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Korotkov <aekorotkov@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Tom Lane @ 2021-06-17 22:04 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Alexander Korotkov <aekorotkov@gmail.com> writes:
> On Thu, Jun 17, 2021 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> we need to add some code that checks for default searchMode, and in
>> that case doesn't call the consistentFn unless at least one
>> (non-MAYBE) input is TRUE.
> I think in default search mode we can just start with curResult equal
> to GIN_FALSE instead of calling directBoolConsistentFn().
No, it's not that simple, because there might be other keys that are
TRUE not MAYBE. So the result could legitimately be TRUE in this case.
BTW, I think it'd be a really good idea for this function to restore
all the MAYBE entries to GIN_MAYBE before returning, so that you can
remove the caveat that it destroys the contents of entryRes[]. I have
basically zero faith that that's safe, and it seems pretty cheap to
not have to make the assumption.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-17 22:10 Alexander Korotkov <aekorotkov@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Alexander Korotkov @ 2021-06-17 22:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jun 18, 2021 at 1:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> writes:
> > On Thu, Jun 17, 2021 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> we need to add some code that checks for default searchMode, and in
> >> that case doesn't call the consistentFn unless at least one
> >> (non-MAYBE) input is TRUE.
>
> > I think in default search mode we can just start with curResult equal
> > to GIN_FALSE instead of calling directBoolConsistentFn().
>
> No, it's not that simple, because there might be other keys that are
> TRUE not MAYBE. So the result could legitimately be TRUE in this case.
Yes, that's it. Thank you for correction.
> BTW, I think it'd be a really good idea for this function to restore
> all the MAYBE entries to GIN_MAYBE before returning, so that you can
> remove the caveat that it destroys the contents of entryRes[]. I have
> basically zero faith that that's safe, and it seems pretty cheap to
> not have to make the assumption.
+1, sounds like a good idea.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-06-22 16:02 Alexander Korotkov <aekorotkov@gmail.com>
parent: Alexander Korotkov <aekorotkov@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Alexander Korotkov @ 2021-06-22 16:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jun 18, 2021 at 12:55 AM Alexander Korotkov
<aekorotkov@gmail.com> wrote:
> On Thu, Jun 17, 2021 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Anyway, I'm punting this to the code authors. I'd like to see
> > some comments clarifying what the API really is, not just a
> > quick-n-dirty code fix.
>
> Yep, I'm going to have a closer look at this tomorrow.
Sorry for the delay. I'm going to take a closer look in the next
couple of days.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-07-10 21:56 Alexander Korotkov <aekorotkov@gmail.com>
parent: Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Korotkov @ 2021-07-10 21:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
( . () w On Tue, Jun 22, 2021 at 7:02 PM Alexander Korotkov
<aekorotkov@gmail.com> wrote:
> On Fri, Jun 18, 2021 at 12:55 AM Alexander Korotkov
> <aekorotkov@gmail.com> wrote:
> > On Thu, Jun 17, 2021 at 10:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Anyway, I'm punting this to the code authors. I'd like to see
> > > some comments clarifying what the API really is, not just a
> > > quick-n-dirty code fix.
> >
> > Yep, I'm going to have a closer look at this tomorrow.
>
> Sorry for the delay. I'm going to take a closer look in the next
> couple of days.
I've closer look at shimTriConsistentFn() function. It looks to me
like the function itself has inconsistencies. But the way it's
currently used in GIN shouldn't produce the wrong query answers.
shimTriConsistentFn() is one of implementation of
GinScanKey.triConsistentFn. In turn, GinScanKey.triConsistentFn have
3 callers:
1) startScanKey() to determine required keys
2) keyGetItem() for lossy item check
3) keyGetItem() for normal item check
Let's see shimTriConsistentFn() inconsistencies and how callers handle them.
1) shimTriConsistentFn() returns result of directBoolConsistentFn()
for zero maybe entries without examining key->recheckCurItem. That
may result in returning GIN_TRUE instead of GIN_MAYBE
1.1) startScanKey() doesn't care about recheck, just looking for
GIN_FALSE result.
1.2) keyGetItem() for lossy item always implies recheck.
1.3) keyGetItem() for a normal item does the trick. Whether a current
item is rechecked is controlled by key->recheckCurItem variable (the
same which is set by directBoolConsistentFn(). The following switch
sets key->recheckCurItem for GIN_MAYBE, but leaves it untouched for
GIN_TRUE. Thus, GIN_TRUE with key->recheckCurItem works here just
like GIN_MAYBE. I think this is inconsistent by itself, but this
inconsistency compensates for inconsistency in shimTriConsistentFn().
2) shimTriConsistentFn() might call directBoolConsistentFn() with all
false inputs for GIN_SEARCH_MODE_DEFAULT. The result is intended to
be false, but opclass consistent method isn't intended to handle this
situation correctly. For instance, ginint4_consistent() returns true
without checking the input array. That could make
shimTriConsistentFn() return GIN_TRUE instead of GIN_MAYBE, or
GIN_MAYBE instead of GIN_FALSE.
2.1) In principle, this could lead startScanKey() to select less
required entries than possible. But this is definitely not the case
of ginint4_consistent() when meeting any of entries is enough for
match.
2.2) In principle, keyGetItem() could get false positives for lossy
items. But that wouldn't lead to wrong query answers. Again, this is
not the case of ginint4_consistent().
2.3) keyGetItem() for a normal item doesn't call triConsistentFn()
with any GIN_MAYBE or all GIN_FALSE.
To sum up, I don't see how inconsistencies in shimTriConsistentFn()
could lead to wrong query answers, especially in intarray GIN index.
Nevertheless, these inconsistencies should be fixed. I'm going to
propose a patch soon.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-07-11 16:14 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Heikki Linnakangas @ 2021-07-11 16:14 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Pawel Kudzia <kudzia@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
On 11/07/2021 00:56, Alexander Korotkov wrote:
> I've closer look at shimTriConsistentFn() function. It looks to me
> like the function itself has inconsistencies. But the way it's
> currently used in GIN shouldn't produce the wrong query answers.
>
> shimTriConsistentFn() is one of implementation of
> GinScanKey.triConsistentFn. In turn, GinScanKey.triConsistentFn have
> 3 callers:
> 1) startScanKey() to determine required keys
> 2) keyGetItem() for lossy item check
> 3) keyGetItem() for normal item check
>
> Let's see shimTriConsistentFn() inconsistencies and how callers handle them.
> 1) shimTriConsistentFn() returns result of directBoolConsistentFn()
> for zero maybe entries without examining key->recheckCurItem. That
> may result in returning GIN_TRUE instead of GIN_MAYBE
> 1.1) startScanKey() doesn't care about recheck, just looking for
> GIN_FALSE result.
> 1.2) keyGetItem() for lossy item always implies recheck.
> 1.3) keyGetItem() for a normal item does the trick. Whether a current
> item is rechecked is controlled by key->recheckCurItem variable (the
> same which is set by directBoolConsistentFn(). The following switch
> sets key->recheckCurItem for GIN_MAYBE, but leaves it untouched for
> GIN_TRUE. Thus, GIN_TRUE with key->recheckCurItem works here just
> like GIN_MAYBE. I think this is inconsistent by itself, but this
> inconsistency compensates for inconsistency in shimTriConsistentFn().
> 2) shimTriConsistentFn() might call directBoolConsistentFn() with all
> false inputs for GIN_SEARCH_MODE_DEFAULT. The result is intended to
> be false, but opclass consistent method isn't intended to handle this
> situation correctly. For instance, ginint4_consistent() returns true
> without checking the input array. That could make
> shimTriConsistentFn() return GIN_TRUE instead of GIN_MAYBE, or
> GIN_MAYBE instead of GIN_FALSE.
> 2.1) In principle, this could lead startScanKey() to select less
> required entries than possible. But this is definitely not the case
> of ginint4_consistent() when meeting any of entries is enough for
> match.
> 2.2) In principle, keyGetItem() could get false positives for lossy
> items. But that wouldn't lead to wrong query answers. Again, this is
> not the case of ginint4_consistent().
> 2.3) keyGetItem() for a normal item doesn't call triConsistentFn()
> with any GIN_MAYBE or all GIN_FALSE.
>
> To sum up, I don't see how inconsistencies in shimTriConsistentFn()
> could lead to wrong query answers, especially in intarray GIN index.
Agreed, I came to the same conclusion looking at the code. Which means
that we still don't have an explanation for the original bug report :-(.
> Nevertheless, these inconsistencies should be fixed. I'm going to
> propose a patch soon.
Thanks! I came up with the attached patch. I changed
directBoolConsistentFn() to return a GinTernaryValue rather than bool, I
think that makes the logic in shimTriConsistentFn() more clear.
I also tried to write a test case to expose issue 2.1 above, but
couldn't come up with an example.
- Heikki
Attachments:
fix-tri-consistent-issues-1.patch (text/x-patch, 7.1K) download
diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c
index 03191e016ce..8be0191e147 100644
--- a/src/backend/access/gin/ginget.c
+++ b/src/backend/access/gin/ginget.c
@@ -1232,11 +1232,14 @@ keyGetItem(GinState *ginstate, MemoryContext tempCtx, GinScanKey key,
res = key->triConsistentFn(key);
+ /*
+ * Convert the ternary result value into a boolean with recheck flag
+ */
switch (res)
{
case GIN_TRUE:
key->curItemMatches = true;
- /* triConsistentFn set recheckCurItem */
+ key->recheckCurItem = false;
break;
case GIN_FALSE:
@@ -1888,13 +1891,22 @@ scanPendingInsert(IndexScanDesc scan, TIDBitmap *tbm, int64 *ntids)
for (i = 0; i < so->nkeys; i++)
{
GinScanKey key = so->keys + i;
+ GinTernaryValue res;
- if (!key->boolConsistentFn(key))
+ res = key->boolConsistentFn(key);
+ if (res == GIN_TRUE)
+ continue;
+ else if (res == GIN_FALSE)
{
match = false;
break;
}
- recheck |= key->recheckCurItem;
+ else
+ {
+ Assert(res == GIN_MAYBE);
+ recheck = true;
+ continue;
+ }
}
MemoryContextSwitchTo(oldCtx);
diff --git a/src/backend/access/gin/ginlogic.c b/src/backend/access/gin/ginlogic.c
index 6bf3288f5b9..f15208d7718 100644
--- a/src/backend/access/gin/ginlogic.c
+++ b/src/backend/access/gin/ginlogic.c
@@ -50,16 +50,10 @@
#define MAX_MAYBE_ENTRIES 4
/*
- * Dummy consistent functions for an EVERYTHING key. Just claim it matches.
+ * Dummy consistent function for an EVERYTHING key. Just claim it matches.
*/
-static bool
-trueConsistentFn(GinScanKey key)
-{
- key->recheckCurItem = false;
- return true;
-}
static GinTernaryValue
-trueTriConsistentFn(GinScanKey key)
+trueConsistentFn(GinScanKey key)
{
return GIN_TRUE;
}
@@ -67,25 +61,36 @@ trueTriConsistentFn(GinScanKey key)
/*
* A helper function for calling a regular, binary logic, consistent function.
*/
-static bool
+static GinTernaryValue
directBoolConsistentFn(GinScanKey key)
{
+ bool boolResult;
+
/*
* Initialize recheckCurItem in case the consistentFn doesn't know it
* should set it. The safe assumption in that case is to force recheck.
*/
key->recheckCurItem = true;
-
- return DatumGetBool(FunctionCall8Coll(key->consistentFmgrInfo,
- key->collation,
- PointerGetDatum(key->entryRes),
- UInt16GetDatum(key->strategy),
- key->query,
- UInt32GetDatum(key->nuserentries),
- PointerGetDatum(key->extra_data),
- PointerGetDatum(&key->recheckCurItem),
- PointerGetDatum(key->queryValues),
- PointerGetDatum(key->queryCategories)));
+ boolResult = DatumGetBool(FunctionCall8Coll(key->consistentFmgrInfo,
+ key->collation,
+ PointerGetDatum(key->entryRes),
+ UInt16GetDatum(key->strategy),
+ key->query,
+ UInt32GetDatum(key->nuserentries),
+ PointerGetDatum(key->extra_data),
+ PointerGetDatum(&key->recheckCurItem),
+ PointerGetDatum(key->queryValues),
+ PointerGetDatum(key->queryCategories)));
+ if (boolResult)
+ {
+ /* TRUE with recheck is taken to mean MAYBE */
+ if (key->recheckCurItem)
+ return GIN_MAYBE;
+ else
+ return GIN_TRUE;
+ }
+ else
+ return GIN_FALSE;
}
/*
@@ -110,12 +115,10 @@ directTriConsistentFn(GinScanKey key)
* logic consistent function provided by the opclass. GIN_MAYBE return value
* is interpreted as true with recheck flag.
*/
-static bool
+static GinTernaryValue
shimBoolConsistentFn(GinScanKey key)
{
- GinTernaryValue result;
-
- result = DatumGetGinTernaryValue(FunctionCall7Coll(key->triConsistentFmgrInfo,
+ return DatumGetGinTernaryValue(FunctionCall7Coll(key->triConsistentFmgrInfo,
key->collation,
PointerGetDatum(key->entryRes),
UInt16GetDatum(key->strategy),
@@ -124,16 +127,6 @@ shimBoolConsistentFn(GinScanKey key)
PointerGetDatum(key->extra_data),
PointerGetDatum(key->queryValues),
PointerGetDatum(key->queryCategories)));
- if (result == GIN_MAYBE)
- {
- key->recheckCurItem = true;
- return true;
- }
- else
- {
- key->recheckCurItem = false;
- return result;
- }
}
/*
@@ -153,9 +146,8 @@ shimTriConsistentFn(GinScanKey key)
{
int nmaybe;
int maybeEntries[MAX_MAYBE_ENTRIES];
+ int ntrue;
int i;
- bool boolResult;
- bool recheck = false;
GinTernaryValue curResult;
/*
@@ -164,6 +156,7 @@ shimTriConsistentFn(GinScanKey key)
* test all combinations, so give up and return MAYBE.
*/
nmaybe = 0;
+ ntrue = 0;
for (i = 0; i < key->nentries; i++)
{
if (key->entryRes[i] == GIN_MAYBE)
@@ -172,22 +165,41 @@ shimTriConsistentFn(GinScanKey key)
return GIN_MAYBE;
maybeEntries[nmaybe++] = i;
}
+ if (key->entryRes[i] == GIN_TRUE)
+ ntrue++;
}
/*
- * If none of the inputs were MAYBE, so we can just call consistent
+ * If none of the inputs were MAYBE, we can just call the consistent
* function as is.
*/
if (nmaybe == 0)
return directBoolConsistentFn(key);
- /* First call consistent function with all the maybe-inputs set FALSE */
- for (i = 0; i < nmaybe; i++)
- key->entryRes[maybeEntries[i]] = GIN_FALSE;
+ /*
+ * Make the first call to the consistent function. In the default search mode,
+ * be careful to not call it with all-FALSE input.
+ */
+ if (key->searchMode == GIN_SEARCH_MODE_DEFAULT && ntrue == 0)
+ {
+ key->entryRes[maybeEntries[0]] = GIN_TRUE;
+ for (i = 1; i < nmaybe; i++)
+ key->entryRes[maybeEntries[i]] = GIN_FALSE;
+ }
+ else
+ {
+ for (i = 0; i < nmaybe; i++)
+ key->entryRes[maybeEntries[i]] = GIN_FALSE;
+ }
+
curResult = directBoolConsistentFn(key);
+ if (curResult == GIN_MAYBE)
+ return GIN_MAYBE;
for (;;)
{
+ GinTernaryValue thisResult;
+
/* Twiddle the entries for next combination. */
for (i = 0; i < nmaybe; i++)
{
@@ -202,17 +214,11 @@ shimTriConsistentFn(GinScanKey key)
if (i == nmaybe)
break;
- boolResult = directBoolConsistentFn(key);
- recheck |= key->recheckCurItem;
-
- if (curResult != boolResult)
+ thisResult = directBoolConsistentFn(key);
+ if (thisResult != curResult)
return GIN_MAYBE;
}
- /* TRUE with recheck is taken to mean MAYBE */
- if (curResult == GIN_TRUE && recheck)
- curResult = GIN_MAYBE;
-
return curResult;
}
@@ -225,7 +231,7 @@ ginInitConsistentFunction(GinState *ginstate, GinScanKey key)
if (key->searchMode == GIN_SEARCH_MODE_EVERYTHING)
{
key->boolConsistentFn = trueConsistentFn;
- key->triConsistentFn = trueTriConsistentFn;
+ key->triConsistentFn = trueConsistentFn;
}
else
{
diff --git a/src/include/access/gin_private.h b/src/include/access/gin_private.h
index 670a40b4bee..eb8261822d7 100644
--- a/src/include/access/gin_private.h
+++ b/src/include/access/gin_private.h
@@ -288,7 +288,7 @@ typedef struct GinScanKeyData
/* array of check flags, reported to consistentFn */
GinTernaryValue *entryRes;
- bool (*boolConsistentFn) (GinScanKey key);
+ GinTernaryValue (*boolConsistentFn) (GinScanKey key);
GinTernaryValue (*triConsistentFn) (GinScanKey key);
FmgrInfo *consistentFmgrInfo;
FmgrInfo *triConsistentFmgrInfo;
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-08-02 08:07 Pawel Kudzia <kudzia@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Pawel Kudzia @ 2021-08-02 08:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Heikki Linnakangas <hlinnaka@iki.fi>; Alexander Korotkov <aekorotkov@gmail.com>; Peter Geoghegan <pg@bowt.ie>
On Thu, Jun 17, 2021 at 4:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Pawel Kudzia <kudzia@gmail.com> writes:
> > with help from IRC we've found that decreasing work_mem from 1MB to 256kB
> > or less makes the problem go away:
>
> Hmm. So that suggests that the index itself is *not* corrupt,
> but the problem is associated with a bug in the indexscan
> algorithms.
>
> Have you experimented with different index opclasses? Your
> original report said you were using gin__int_ops, but that's
> not the default, and we sort of deprecate intarray these days.
Hello,
Three weeks ago we've changed from
CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
(attribute_name_ids public.gin__int_ops);
into
CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
(attribute_name_ids);
Sadly - the problems are back.
I have:
* queries that return incorrect responses:
SET enable_seqscan = OFF;
SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{1485}' )
AND NOT ( (attribute_name_ids||0) && '{1485}') LIMIT 10;
SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{144}' )
AND NOT ( (attribute_name_ids||0) && '{144}') LIMIT 10;
SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{2652}' )
AND NOT ( (attribute_name_ids||0) && '{2652}') LIMIT 10;
all three return:
entity_id | primname
-----------+--------------------
99311962 | msci_674591ltepsgt
99312880 | msci_674667mgu
data=# explain analyze SELECT entity_id FROM entity WHERE (
attribute_name_ids && '{2652}' ) AND NOT ( (attribute_name_ids||0) &&
'{2652}') LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=30.59..31.71 rows=10 width=8) (actual
time=149.988..200.749 rows=2 loops=1)
-> Bitmap Heap Scan on entity (cost=30.59..3798.81 rows=33644
width=8) (actual time=149.985..200.744 rows=2 loops=1)
Recheck Cond: (attribute_name_ids && '{2652}'::integer[])
Rows Removed by Index Recheck: 79741
Filter: (NOT ((attribute_name_ids || 0) && '{2652}'::integer[]))
Rows Removed by Filter: 16723
Heap Blocks: exact=8277 lossy=9989
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..30.59 rows=33813 width=0) (actual time=9.712..9.713
rows=37367 loops=1)
Index Cond: (attribute_name_ids && '{2652}'::integer[])
Planning Time: 0.472 ms
Execution Time: 200.977 ms
(11 rows)
* first time when hourly consistency check reported the inconsistency
[ 2021-08-01 19:04:15 for entity_id=99312880, 2021-08-01 23:04:15 for
entity_id=99311962 ]
* timestamps when affected row has been updated [ entity_id=99312880:
2021-08-01 16:51:59, 2021-08-01 19:04:06 [ most likely that update led
to inconsistency ], 2021-08-01 23:04:14, 2021-08-02 01:08:18,
2021-08-02 05:12:15, entity_id=99311962 2021-08-01 16:51:59,
2021-08-01 19:04:05, 2021-08-01 23:04:13 [ likely culprit ],
2021-08-02 01:08:16 ]
* wal files from the **master server** from the time when the
inconsistency started to occur [ from 2021-08-01 14:50:37 till
2021-08-02 04:31:00 ; sadly i don't have enough to cover the whole
period between snapshots mentioned below ]
* file-level snapshot of the database taken from streaming
**replication slave** before the problem started to occur [ 2021-08-01
12:00:01 ] and after it occured [ 2021-08-02 00:00:01 ]
I'll be happy to run any diagnostics needed, provide access to
particular wal file.
Greetings!
--
regards,
Pawel Kudzia
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2021-09-06 08:18 Pawel Kudzia <kudzia@gmail.com>
parent: Pawel Kudzia <kudzia@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Pawel Kudzia @ 2021-09-06 08:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Heikki Linnakangas <hlinnaka@iki.fi>; Alexander Korotkov <aekorotkov@gmail.com>; Peter Geoghegan <pg@bowt.ie>
On Mon, Aug 2, 2021 at 10:07 AM Pawel Kudzia <kudzia@gmail.com> wrote:
>
> On Thu, Jun 17, 2021 at 4:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Pawel Kudzia <kudzia@gmail.com> writes:
> > > with help from IRC we've found that decreasing work_mem from 1MB to 256kB
> > > or less makes the problem go away:
> >
> > Hmm. So that suggests that the index itself is *not* corrupt,
> > but the problem is associated with a bug in the indexscan
> > algorithms.
> >
> > Have you experimented with different index opclasses? Your
> > original report said you were using gin__int_ops, but that's
> > not the default, and we sort of deprecate intarray these days.
>
> Hello,
>
> Three weeks ago we've changed from
>
> CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
> (attribute_name_ids public.gin__int_ops);
>
> into
>
> CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
> (attribute_name_ids);
>
> Sadly - the problems are back.
>
> I have:
> * queries that return incorrect responses:
> SET enable_seqscan = OFF;
> SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{1485}' )
> AND NOT ( (attribute_name_ids||0) && '{1485}') LIMIT 10;
> SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{144}' )
> AND NOT ( (attribute_name_ids||0) && '{144}') LIMIT 10;
> SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{2652}' )
> AND NOT ( (attribute_name_ids||0) && '{2652}') LIMIT 10;
>
> all three return:
> entity_id | primname
> -----------+--------------------
> 99311962 | msci_674591ltepsgt
> 99312880 | msci_674667mgu
>
> data=# explain analyze SELECT entity_id FROM entity WHERE (
> attribute_name_ids && '{2652}' ) AND NOT ( (attribute_name_ids||0) &&
> '{2652}') LIMIT 10;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=30.59..31.71 rows=10 width=8) (actual
> time=149.988..200.749 rows=2 loops=1)
> -> Bitmap Heap Scan on entity (cost=30.59..3798.81 rows=33644
> width=8) (actual time=149.985..200.744 rows=2 loops=1)
> Recheck Cond: (attribute_name_ids && '{2652}'::integer[])
> Rows Removed by Index Recheck: 79741
> Filter: (NOT ((attribute_name_ids || 0) && '{2652}'::integer[]))
> Rows Removed by Filter: 16723
> Heap Blocks: exact=8277 lossy=9989
> -> Bitmap Index Scan on entity_attribute_name_ids_gin
> (cost=0.00..30.59 rows=33813 width=0) (actual time=9.712..9.713
> rows=37367 loops=1)
> Index Cond: (attribute_name_ids && '{2652}'::integer[])
> Planning Time: 0.472 ms
> Execution Time: 200.977 ms
> (11 rows)
>
>
> * first time when hourly consistency check reported the inconsistency
> [ 2021-08-01 19:04:15 for entity_id=99312880, 2021-08-01 23:04:15 for
> entity_id=99311962 ]
> * timestamps when affected row has been updated [ entity_id=99312880:
> 2021-08-01 16:51:59, 2021-08-01 19:04:06 [ most likely that update led
> to inconsistency ], 2021-08-01 23:04:14, 2021-08-02 01:08:18,
> 2021-08-02 05:12:15, entity_id=99311962 2021-08-01 16:51:59,
> 2021-08-01 19:04:05, 2021-08-01 23:04:13 [ likely culprit ],
> 2021-08-02 01:08:16 ]
> * wal files from the **master server** from the time when the
> inconsistency started to occur [ from 2021-08-01 14:50:37 till
> 2021-08-02 04:31:00 ; sadly i don't have enough to cover the whole
> period between snapshots mentioned below ]
> * file-level snapshot of the database taken from streaming
> **replication slave** before the problem started to occur [ 2021-08-01
> 12:00:01 ] and after it occured [ 2021-08-02 00:00:01 ]
>
> I'll be happy to run any diagnostics needed, provide access to
> particular wal file.
>
Hello,
We continue to run into this issue and we still do not have any
reliable method to reproduce it. Is there any diagnostic information
that we could provide to make it easier to resolve?
Thank you!
--
regards,
Pawel Kudzia
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
@ 2024-06-13 18:49 Pawel Kudzia <kudzia@gmail.com>
parent: Pawel Kudzia <kudzia@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Pawel Kudzia @ 2024-06-13 18:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Heikki Linnakangas <hlinnaka@iki.fi>; Alexander Korotkov <aekorotkov@gmail.com>; Peter Geoghegan <pg@bowt.ie>
On Mon, Sep 6, 2021 at 10:18 AM Pawel Kudzia <kudzia@gmail.com> wrote:
>
> On Mon, Aug 2, 2021 at 10:07 AM Pawel Kudzia <kudzia@gmail.com> wrote:
> >
> > On Thu, Jun 17, 2021 at 4:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >
> > > Pawel Kudzia <kudzia@gmail.com> writes:
> > > > with help from IRC we've found that decreasing work_mem from 1MB to 256kB
> > > > or less makes the problem go away:
> > >
> > > Hmm. So that suggests that the index itself is *not* corrupt,
> > > but the problem is associated with a bug in the indexscan
> > > algorithms.
> > >
> > > Have you experimented with different index opclasses? Your
> > > original report said you were using gin__int_ops, but that's
> > > not the default, and we sort of deprecate intarray these days.
> >
> > Hello,
> >
> > Three weeks ago we've changed from
> >
> > CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
> > (attribute_name_ids public.gin__int_ops);
> >
> > into
> >
> > CREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin
> > (attribute_name_ids);
> >
> > Sadly - the problems are back.
> >
> > I have:
> > * queries that return incorrect responses:
> > SET enable_seqscan = OFF;
> > SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{1485}' )
> > AND NOT ( (attribute_name_ids||0) && '{1485}') LIMIT 10;
> > SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{144}' )
> > AND NOT ( (attribute_name_ids||0) && '{144}') LIMIT 10;
> > SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{2652}' )
> > AND NOT ( (attribute_name_ids||0) && '{2652}') LIMIT 10;
> >
> > all three return:
> > entity_id | primname
> > -----------+--------------------
> > 99311962 | msci_674591ltepsgt
> > 99312880 | msci_674667mgu
> >
> > data=# explain analyze SELECT entity_id FROM entity WHERE (
> > attribute_name_ids && '{2652}' ) AND NOT ( (attribute_name_ids||0) &&
> > '{2652}') LIMIT 10;
> > QUERY PLAN
> > -----------------------------------------------------------------------------------------------------------------------------------------------------
> > Limit (cost=30.59..31.71 rows=10 width=8) (actual
> > time=149.988..200.749 rows=2 loops=1)
> > -> Bitmap Heap Scan on entity (cost=30.59..3798.81 rows=33644
> > width=8) (actual time=149.985..200.744 rows=2 loops=1)
> > Recheck Cond: (attribute_name_ids && '{2652}'::integer[])
> > Rows Removed by Index Recheck: 79741
> > Filter: (NOT ((attribute_name_ids || 0) && '{2652}'::integer[]))
> > Rows Removed by Filter: 16723
> > Heap Blocks: exact=8277 lossy=9989
> > -> Bitmap Index Scan on entity_attribute_name_ids_gin
> > (cost=0.00..30.59 rows=33813 width=0) (actual time=9.712..9.713
> > rows=37367 loops=1)
> > Index Cond: (attribute_name_ids && '{2652}'::integer[])
> > Planning Time: 0.472 ms
> > Execution Time: 200.977 ms
> > (11 rows)
> >
> >
> > * first time when hourly consistency check reported the inconsistency
> > [ 2021-08-01 19:04:15 for entity_id=99312880, 2021-08-01 23:04:15 for
> > entity_id=99311962 ]
> > * timestamps when affected row has been updated [ entity_id=99312880:
> > 2021-08-01 16:51:59, 2021-08-01 19:04:06 [ most likely that update led
> > to inconsistency ], 2021-08-01 23:04:14, 2021-08-02 01:08:18,
> > 2021-08-02 05:12:15, entity_id=99311962 2021-08-01 16:51:59,
> > 2021-08-01 19:04:05, 2021-08-01 23:04:13 [ likely culprit ],
> > 2021-08-02 01:08:16 ]
> > * wal files from the **master server** from the time when the
> > inconsistency started to occur [ from 2021-08-01 14:50:37 till
> > 2021-08-02 04:31:00 ; sadly i don't have enough to cover the whole
> > period between snapshots mentioned below ]
> > * file-level snapshot of the database taken from streaming
> > **replication slave** before the problem started to occur [ 2021-08-01
> > 12:00:01 ] and after it occured [ 2021-08-02 00:00:01 ]
> >
> > I'll be happy to run any diagnostics needed, provide access to
> > particular wal file.
> >
>
> Hello,
>
> We continue to run into this issue and we still do not have any
> reliable method to reproduce it. Is there any diagnostic information
> that we could provide to make it easier to resolve?
>
> Thank you!
>
>
Hello,
I'd like to revive this thread. In the meanwhile we've upgraded to
PostgreSQL 16.3-1.pgdg110+1, moved to new x64 hardware and upgraded to
Debian Bookworm.
And we still observe occasional GIN index corruption resulting in
'impossible' queries like below returning rows which clearly do not
match the WHERE criteria:
data=# SELECT entity_id FROM entity WHERE ( attribute_name_ids &&
'{49}' ) AND NOT ( (attribute_name_ids||0) && '{49}');
entity_id
-----------
88036166
(1 row)
data=# explain analyze SELECT entity_id FROM entity WHERE (
attribute_name_ids && '{49}' ) AND NOT ( (attribute_name_ids||0) &&
'{49}');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity (cost=59.10..361.74 rows=3011 width=8)
(actual time=41.614..46.918 rows=1 loops=1)
Recheck Cond: (attribute_name_ids && '{49}'::integer[])
Filter: (NOT ((attribute_name_ids || 0) && '{49}'::integer[]))
Rows Removed by Filter: 2798
Heap Blocks: exact=4715
-> Bitmap Index Scan on entity_attribute_name_ids_gin
(cost=0.00..59.10 rows=3026 width=0) (actual time=6.563..6.564
rows=17842 loops=1)
Index Cond: (attribute_name_ids && '{49}'::integer[])
Planning Time: 0.115 ms
Execution Time: 47.032 ms
(9 rows)
The only step needed to get into this state is to leave our update
processes running for few weeks.
Our workaround, which has been working OK for us for the past 4 years,
is to run REINDEX INDEX CONCURRENTLY entity_attribute_value_ids_gin
every 24h.
If there's any additional diagnostic data that we can provide to help
in finding and fixing the root cause - please let me know.
Thank you!
--
regards,
Pawel Kudzia
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2024-06-13 18:49 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-27 17:18 BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows PG Bug reporting form <noreply@postgresql.org>
2021-02-12 12:35 ` Pawel Kudzia <kudzia@gmail.com>
2021-06-17 06:40 ` Pawel Kudzia <kudzia@gmail.com>
2021-06-17 08:00 ` Pawel Kudzia <kudzia@gmail.com>
2021-06-17 14:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-17 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-17 21:55 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-06-17 22:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-17 22:10 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-06-22 16:02 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-07-10 21:56 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-07-11 16:14 ` Heikki Linnakangas <hlinnaka@iki.fi>
2021-08-02 08:07 ` Pawel Kudzia <kudzia@gmail.com>
2021-09-06 08:18 ` Pawel Kudzia <kudzia@gmail.com>
2024-06-13 18:49 ` Pawel Kudzia <kudzia@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox