From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Pawel Kudzia <kudzia@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: IRe: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
Date: Thu, 15 Jul 2021 21:49:31 +0300
Message-ID: <d4f4d5e2-35ef-58e4-4f31-5fdfb2c146a0@iki.fi> (raw)
In-Reply-To: <CAJYBUS_nx9oehEkTi+phrAvEcdQAcTGkofLOhGdGoLLHK=kGAg@mail.gmail.com>
References: <16792-b1913b6b4e098331@postgresql.org>
<CAJYBUS9AaEgFTXnZnAWir993-MOVLnXefQmyLd9erXewZhXiHA@mail.gmail.com>
<CAJYBUS_5Z+9aHyNQ7wkTEkZF+c_RMn7hMjPkTGU0Uc7OHMU3sQ@mail.gmail.com>
<CAJYBUS8p3aYh5ZWJHHk1QGnGkUepMHQMMm87kODoXOFS+VWVGQ@mail.gmail.com>
<970432.1623938874@sss.pgh.pa.us>
<1039976.1623959387@sss.pgh.pa.us>
<efe09893-8e6d-9a34-8351-3ea3c6ab202a@iki.fi>
<CAJYBUS9mmVvVPWKM4rNKJqaq85bQG0Rw20LvT05Fc3zOWt2MaQ@mail.gmail.com>
<CAJYBUS84aSgCNhcusOJQfeWFy1V3U55AOmRUWcpd+SsF9_qp=A@mail.gmail.com>
<920041ab-e975-afe3-77d0-934a7c50965d@iki.fi>
<CAJYBUS8S-k7r-VQ3Vv7Asz9KiW1u50VT5k7fq5BSgVXJ_W4KYA@mail.gmail.com>
<06cedb17-6cbe-6586-caa6-2ff618a35517@iki.fi>
<CAH2-Wz=ocXi1NeaWVAO5JndKkppF4WNLBXU=MmCd__B5en3pWw@mail.gmail.com>
<CAJYBUS8aBQQL22oHsAwjHdwYfdB_NMzt7-sZxhxiOdEdn7cOkw@mail.gmail.com>
<e31fc916-1aee-18bc-ba9a-c612b3cf4f82@iki.fi>
<CAJYBUS_nx9oehEkTi+phrAvEcdQAcTGkofLOhGdGoLLHK=kGAg@mail.gmail.com>
On 15/07/2021 12:38, Pawel Kudzia wrote:
>>> How can I identify relevant pages to make selective dump of them for
>>> you, using method proposed by Peter?
>>
>> It's a bit hard without knowing which index pages are affected. But the
>> attached script (dump-gin-page-info.sql) dumps some summary information
>> about the kind of pages there are. That's a start.
>
> I'm attaching output of the proposed commands.
Thanks!
>> Let's try one thing first: I rebased a patch to add GIN support to the
>> amcheck extension [1]. I kind of doubt that it will catch the corruption
>> in this case, but you never know. So please apply the attached
>> v2-0001-Amcheck-for-GIN-13stable.patch patch. It is the same patch I
>> posted at [1], but the installation script is slightly different to make
>> it work on PostgreSQL v13. Then compile install it:
>>
>> cd contrib/amcheck; make install
>>
>> Then in psql:
>>
>> CREATE EXTENSION amcheck;
>> gin_index_parent_check('entity_attribute_name_ids_gin');
>
> Sadly i'm getting this when executing CREATE EXTENSION amcheck;
>
> ERROR: extension "amcheck" has no installation script nor update path
> for version "1.2.1"
Hmm, that's odd, it worked for me. Did "make install" copy the
"amcheck--1.2--1.2.1.sql" file to the right place? It should be
installed the same directory as "amcheck.control".
Anyway, let's try a different tack. Here's another debugging patch. It
causes a LOG message to be printed whenever a GIN index page is
accessed, like this:
2021-07-15 21:22:20.119 EEST [972708] LOG: ReadBuffer 1663/12678/27035
read gin blk 0 (ginget.c:1832 scanPendingInsert)
2021-07-15 21:22:20.119 EEST [972708] STATEMENT: explain analyze select
* from test_intarray where a @> '{2}';
2021-07-15 21:22:20.119 EEST [972708] LOG: ReadBuffer 1663/12678/27035
read gin blk 1 (ginbtree.c:89 ginFindLeafPage)
2021-07-15 21:22:20.119 EEST [972708] STATEMENT: explain analyze select
* from test_intarray where a @> '{2}';
Please apply this patch, run the query again, and copy the resulting
log. I'm also interested in the contents of the pages that are accessed,
so if you could then run these commands to dump the contents of those
index pages:
# extract just the block numbers from the log
cat <path to postgres log> | perl -ne '/.*read gin blk (\d+)/ && print
"$1\n" ' | sort -n |uniq > /tmp/blocknums
# dump contents of each of those index pages to '/tmp/block-contents'
psql postgres -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';"
Please send over the resulting PostgreSQL log, and
'/tmp/block-contents', and I'll take a closer look into the contents to
see if there's something funny. (A private email is OK if you don't feel
comfortable sharing it with the world).
One more thing, please also add the 'ctid' virtual column to the query,
like this:
SELECT ctid, entity_id FROM entity WHERE
( attribute_name_ids && '{1737}' ) AND NOT ( (attribute_name_ids||0)
&& '{1737}') LIMIT 10;
Also, did you happen to keep the WAL log from around the time that the
query started producing wrong results? That would also be useful
evidence for how it got into this situation.
Thanks in advance! We might need a few more round-trips, but we'll get
there.
- Heikki
Attachments:trace-gin-readbuffer-2.patch (text/x-patch, 2.3K) downloaddiff --git a/src/backend/access/gin/ginbtree.c b/src/backend/access/gin/ginbtree.cindex 8d08b05f51..8960f2bbf2 100644--- a/src/backend/access/gin/ginbtree.c+++ b/src/backend/access/gin/ginbtree.c@@ -22,6 +22,8 @@
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "access/gin_trace.h"+
static void ginFindParents(GinBtree btree, GinBtreeStack *stack);
static bool ginPlaceToPage(GinBtree btree, GinBtreeStack *stack,
void *insertdata, BlockNumber updateblkno,
diff --git a/src/backend/access/gin/ginfast.c b/src/backend/access/gin/ginfast.cindex 2e41b34d8d..830c01ec04 100644--- a/src/backend/access/gin/ginfast.c+++ b/src/backend/access/gin/ginfast.c@@ -35,6 +35,8 @@
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "access/gin_trace.h"+
/* GUC parameter */
int gin_pending_list_limit = 0;
diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.cindex 2cfccdedcf..a1430a3b08 100644--- a/src/backend/access/gin/ginget.c+++ b/src/backend/access/gin/ginget.c@@ -22,6 +22,8 @@
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "access/gin_trace.h"+
/* GUC parameter */
int GinFuzzySearchLimit = 0;
diff --git a/src/backend/access/gin/ginutil.c b/src/backend/access/gin/ginutil.cindex a400f1fedb..51d5673d9a 100644--- a/src/backend/access/gin/ginutil.c+++ b/src/backend/access/gin/ginutil.c@@ -29,6 +29,7 @@
#include "utils/index_selfuncs.h"
#include "utils/typcache.h"
+#include "access/gin_trace.h"
/*
* GIN handler function: return IndexAmRoutine with access method parameters
diff --git a/src/include/access/gin_trace.h b/src/include/access/gin_trace.hnew file mode 100644index 0000000000..d858d5889f--- /dev/null+++ b/src/include/access/gin_trace.h@@ -0,0 +1,16 @@++static inline Buffer+GinReadBuffer(Relation reln, BlockNumber blockNum,+ const char *filename, int lineno, const char *funcname)+{+ if (reln->rd_rel->relam == GIN_AM_OID)+ {+ elog(LOG, "ReadBuffer %u/%u/%u read gin blk %u (%s:%d %s)",+ (reln)->rd_node.spcNode, (reln)->rd_node.dbNode, (reln)->rd_node.relNode, blockNum,+ filename, lineno, funcname);+ }+ return ReadBuffer(reln, blockNum);+}++#define ReadBuffer(reln, blockNum) \+ GinReadBuffer(reln, blockNum, __FILE__, __LINE__, PG_FUNCNAME_MACRO)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: hlinnaka@iki.fi, kudzia@gmail.com, pg@bowt.ie, aekorotkov@gmail.com, pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: IRe: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
In-Reply-To: <d4f4d5e2-35ef-58e4-4f31-5fdfb2c146a0@iki.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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