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 1m5oM5-0005wD-TU for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jul 2021 11:56:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m5oM4-0001m1-KF for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jul 2021 11:56:08 +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 1m5oM3-0001ls-To for pgsql-bugs@lists.postgresql.org; Tue, 20 Jul 2021 11:56:08 +0000 Received: from lahtoruutu.iki.fi ([185.185.170.37]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m5oLy-00042e-3I for pgsql-bugs@lists.postgresql.org; Tue, 20 Jul 2021 11:56:06 +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 lahtoruutu.iki.fi (Postfix) with ESMTPSA id F3E9D1B000FD; Tue, 20 Jul 2021 14:55:58 +0300 (EEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1626782159; 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=2I0We847Z1XiD6FB2vSY1kX6Xt2b3jlvocSbzS2y0M4=; b=AHouq0m0MyuOPzdp9/+fUHtRx0OjcjHZRcRchlSEg/vJpOvZV38iEtzbF+wXvwZyW99pP4 G6O+1t7waRDqKMHHrVal9xHjubWbiv0opZcmTDy69ZMzngIkmoETmPPyuknxdBa8sHbytV CMUjC/79gklJF092ZgHsDj70d01shfDpM1ckfIa2Y7msm6ilSz4UHdg3oP/vN3LavLa7ED duZH7+cM2rPQyCITonHE+LzDknAse/eRmD1HxZHIKV6332SHAsMdBOgrJONE0XO7EALmP2 aFxhMH1Fn8l+Y/QQbikiwl+DebTu660klshyOzFJbpalFiLj3Xw2ZtNiY+vqpQ== 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> <470ba78f-4784-f90d-91ba-dcc05d6d4738@iki.fi> From: Heikki Linnakangas Message-ID: <14dfcfab-36df-a92d-a42c-01065acf0036@iki.fi> Date: Tue, 20 Jul 2021 14:55:58 +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="------------4CEBC3D6E90E71112034280F" Content-Language: en-US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1626782159; 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=2I0We847Z1XiD6FB2vSY1kX6Xt2b3jlvocSbzS2y0M4=; b=RbU+T5qYRIjnQi8BD/IsNOMktQQCguFFD3lFLjwiHX/S0BzKQ8DNP1SrZaqKFpvoeX9Qwk xLsjfK5DFIlxJUyRtG/phZ7zKJtW9JzWuj+zqn/Labwe09ACgpcMKdtKMYCEbvYKqZPIjF Pp3scGu1oKe1+qGDs61LItmdQToqfJzpGhSQ6mxoDxBkJY5oenOXK9iZW0KaMbxjXzDqNL EOK7qjZieIVuqw2TuCrfVyWRFwRn/5Y36+UHqTf+K5j+mHn++uTUyqwF35ky+PyI+Q+o1M 6WYms7MUx32rbGYjwIay9uAN9c06VDKJkCudXGlM545Fgj+CIDUvrmyAY6ufwg== ARC-Authentication-Results: i=1; ORIGINATING; auth=pass smtp.auth=hlinnaka smtp.mailfrom=hlinnaka@iki.fi ARC-Seal: i=1; s=lahtoruutu; d=iki.fi; t=1626782159; a=rsa-sha256; cv=none; b=nf0w1uJNBy4ye5CJqnyoj/vzGkG7L/Mb57fEsdoN1+B1eAeVUYA0oxGRbmoUGjKWJBv5y+ vFEFYs7lGuwDtvoj8m2BfTzVuTkcEIRMmpK8zh2zk7pwL30q2IYJVeuk9NZw8MthKmG9HC FaLVYhgs/uyMYTgTTEHUekO2Oo+r3Ww3SCW5ByzFDQUmtVXRIIcGwKJkGj2LT76y/Kf2Np RXkZrGSKqm1Fk9FqNmouLEm4kEFWSqmNYRaJOLNwXj3whjLyYnyA0TSN4KMjNX4APhUeiZ rj40Q5yAaA2twr75r5XYXIkr2cN7MhEjlGZRHdD38YG8Idv88ekfnTD4nwnTHA== 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. --------------4CEBC3D6E90E71112034280F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 18/07/2021 11:27, Pawel Kudzia wrote: > I'm sending those over directly to your mail. Thanks! I looked through those logs, and I'm now not seeing anything wrong with the pages involved. The query first scans the pending list, and doesn't find any matches there. It then descends the entry tree, finds pointer to a posting tree for id 1373, then scans the posting tree. For some reason, the TIDs of those offending rows, (4002784,1) and (4002869,14) are in the posting tree for key '1373', even though the heap tuples don't contain that key. Both of those TIDs are in the same TID list, on page 255179: > postgres=# select * from (select blknum, ((gin_leafpage_items(content)).*) from ginblocks where blknum=255179) x where tids::text like '%(4002869,14)%'; > blknum | first_tid | nbytes | > tids > > > --------+--------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------- > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > ------------------------------------------------------------------------------------------- > 255179 | (4002720,16) | 87 | {"(4002720,16)","(4002721,3)","(4002723,5)","(4002739,11)","(4002743,14)","(4002744,2)","(4002784,1)","(4002784,10)","(4002790,46)","(4002790,47)","(4002792 > ,22)","(4002792,23)","(4002792,24)","(4002796,22)","(4002798,2)","(4002800,7)","(4002802,27)","(4002802,28)","(4002803,22)","(4002805,28)","(4002816,13)","(4002817,19)","(4002820,2)","(40028 > 25,17)","(4002854,7)","(4002855,13)","(4002855,14)","(4002869,14)","(4006217,3)","(4006217,11)","(4006307,11)","(4006330,4)","(4006330,8)","(4006331,6)","(4006332,9)","(4006332,10)","(400633 > 3,9)","(4006335,2)","(4006338,1)","(4006342,7)","(4006343,5)","(4006407,2)","(4008912,2)"} > (1 row) I've used pg_filedump and pageinspect on all of the pages on the path to this entry, but I don't see anything wrong with the index. So how did it end up like that? I'm afraid I have no clue :-(. Peter's hunch that it's some bad interaction between deleted pages and the pending list sounds plausible, but looking at the code, I don't see any concrete bugs like that. We have one more lead that we haven't fully explored: amcheck reported this error: ERROR: index "entity_attribute_name_ids_gin": tid exceeds parent's high key in postingTree leaf on block 321746 Give that the GIN amheck patch is still very experimental, that could well be a bug in amcheck instead of real corruption. Also, that error was *not* in the posting tree for key '1373'. I hacked amcheck on my laptop to run only on the posting tree or '1373', and it didn't find any errors there. But let's check it out. I modified 'amcheck' to print more information. I also changed it so that it doesn't stop at the first error, but keeps going, and reports all issues as warnings. Pawel: Could you try the attached version of amcheck? Please apply the attached patch (you'll have to un-apply the previous version or start from a clean source tree), run "SELECT gin_index_parent_check('entity_attribute_name_ids_gin');" again, and send over the result? It should print a bunch of INFOs and WARNINGs to the client's stderr, so a good way to capture that is something like this: psql -c "select gin_index_parent_check('entity_attribute_name_ids_gin');" 2> amcheck-log Let's see if that reveals something interesting. If not, what can we do next? 1. Let's fix the issues with the triconsistent functions that we discussed earlier. They don't explain what we're seeing here, but should be cleaned up nevertheless. 2. Let's add more sanity checks wherever we read a GIN page, to check that it's of the expected kind. Again we have no theory on what sequence of events could cause this, but having more sanity checks seems like a good idea in any case. 3. Run the test case again and wait for the issue to re-appear. This time, let's try to capture the WAL, so that we can trace back the modifications to the pages that lead to the situation. Any other ideas? - Heikki --------------4CEBC3D6E90E71112034280F Content-Type: text/x-patch; charset=UTF-8; name="v3-0001-Amcheck-for-GIN-13stable.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="v3-0001-Amcheck-for-GIN-13stable.patch"