Re: IRe: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows

On 16/07/2021 10:06, Pawel Kudzia wrote:
> 
> 
> On Thu, Jul 15, 2021 at 8:49 PM Heikki Linnakangas <hlinnaka@iki.fi 
> <mailto:hlinnaka@iki.fi>> wrote:
> 
> thank you for the hint. i've messed up patching. now i can get one step 
> further:
> 
> data=# CREATE EXTENSION amcheck;
> CREATE EXTENSION
> data=# gin_index_parent_check('entity_attribute_name_ids_gin');
> ERROR:  syntax error at or near "gin_index_parent_check"
> LINE 1: gin_index_parent_check('entity_attribute_name_ids_gin');
>          ^

Ah, sorry, you need to call it with SELECT, like:

SELECT gin_index_parent_check('entity_attribute_name_ids_gin');

> i've also applied trace-gin-readbuffer-2.patch

Darn, I missed one function that's used to read a page when descending 
the GIN tree. That explains the seemingly nonsensical accesses in the 
log - the trace left out some crucial accesses.

Attached is a new version of that debugging patch. Please repeat the 
same steps as before with this:

1. Apply the patch (remove previous patch first if necessary)

2. Compile Postgres, "make install"

3. Clear the log, start postgres

4. Run the query again:

SELECT ctid, entity_id FROM entity WHERE
( attribute_name_ids && '{1737}' ) AND NOT ( (attribute_name_ids||0)
&& '{1737}') LIMIT 10;

5. Stop the server.

6. Extract the content of the accessed index blocks:

cat <path to postgres log> | perl -ne '/.*read gin blk (\d+)/ && print 
"$1\n" ' | sort -n |uniq > /tmp/blocknums

psql data -c "create temp table blocknums(blknum int); copy blocknums 
from '/tmp/blocknums'; copy (select blknum, 
get_raw_page('entity_attribute_name_ids_gin', blknum) from blocknums) to 
'/tmp/block-contents';"

7. Send over /tmp/blocknums, /tmp/block-contents and the postgres log again.

Thank you for your patience!

- Heikki