Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m46QZ-0002yS-N6 for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Jul 2021 18:49:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m46QY-0007Ql-Lq for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Jul 2021 18:49:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m46QY-0007Qc-72 for pgsql-bugs@lists.postgresql.org; Thu, 15 Jul 2021 18:49:42 +0000 Received: from meesny.iki.fi ([2001:67c:2b0:1c1::201]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m46QV-0002Ps-KE for pgsql-bugs@lists.postgresql.org; Thu, 15 Jul 2021 18:49:41 +0000 Received: from [192.168.1.113] (dsl-hkibng22-54faa4-119.dhcp.inet.fi [84.250.164.119]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: hlinnaka) by meesny.iki.fi (Postfix) with ESMTPSA id 961BA2004E; Thu, 15 Jul 2021 21:49:31 +0300 (EEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=meesny; t=1626374971; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=U07aQ6PxvTokBrfQ4JuPvWgpOI9XD0j33aSPDNwqwkw=; b=x/7LWL8WhbXOR/4KdbdvWBIEqeiOhJG7cEW0iW4IVV8tKWOCUpqpOaK+CCxLB/HUvUtJYo XbTBpAQ7RDtezz5V04vIVRUBMF35aAhHIpdDSQehaKKG3q7+VhgZz3jkK1uQiPnYi+9316 CKiTofCbY9/xVpAtkp3lEAVTvCk2G2A= Subject: Re: IRe: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows To: Pawel Kudzia Cc: Peter Geoghegan , Alexander Korotkov , PostgreSQL mailing lists , Tom Lane References: <16792-b1913b6b4e098331@postgresql.org> <970432.1623938874@sss.pgh.pa.us> <1039976.1623959387@sss.pgh.pa.us> <920041ab-e975-afe3-77d0-934a7c50965d@iki.fi> <06cedb17-6cbe-6586-caa6-2ff618a35517@iki.fi> From: Heikki Linnakangas Message-ID: Date: Thu, 15 Jul 2021 21:49:31 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------3E288C65038C13415C5BF87B" Content-Language: en-US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=meesny; t=1626374971; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=U07aQ6PxvTokBrfQ4JuPvWgpOI9XD0j33aSPDNwqwkw=; b=ClHGtr9bprhB9EHfk2bLPMdm0R70MbE/V7tZBOQxOl/ZMlmV1oU3VNo+eXLB2GMMxr8Wyh 7tFsn2CQpQr2f7FyGoFmROg1pvk7erRSIs8AzTQ+yn40klu9j1214coqLMcWp6X4+0/3fI iXo0TOQlLjBdTQIPtDsTvZyQBFyJqRo= ARC-Seal: i=1; s=meesny; d=iki.fi; t=1626374971; a=rsa-sha256; cv=none; b=Yc+QoqrLcbCzogM8TkYZ+3ZgSs97UnWuguc1gaBpzu/OJ40rnpB2U8qRTiOkklzYZR1yKG ezdaJB/ssfHTb5j4rVrz0O62adKQgiG3z/TZYQl0CK+fHxmtTZKK2ElDYidYHKpCsYY0eL rOpYm5rsyXxCwuOz37xOnpOtG27Td+M= ARC-Authentication-Results: i=1; ORIGINATING; auth=pass smtp.auth=hlinnaka smtp.mailfrom=hlinnaka@iki.fi List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------3E288C65038C13415C5BF87B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 | 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 --------------3E288C65038C13415C5BF87B Content-Type: text/x-patch; charset=UTF-8; name="trace-gin-readbuffer-2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="trace-gin-readbuffer-2.patch" diff --git a/src/backend/access/gin/ginbtree.c b/src/backend/access/gin/ginbtree.c index 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.c index 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.c index 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.c index 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.h new file mode 100644 index 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) --------------3E288C65038C13415C5BF87B--