Subject: Re: Re: PANIC: invalid index offnum: 186 when processing
 BRIN indexes in VACUUM
To: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Michael Paquier <michael.paquier@gmail.com>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: <20171031181053.xh7hzminpv6rvwr2@alvherre.pgsql>
 <11499.1509476951@sss.pgh.pa.us> <13135.1509479192@sss.pgh.pa.us>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <083d996a-4a8a-0e13-800a-851dd09ad8cc@2ndquadrant.com>
Date: Tue, 31 Oct 2017 23:44:36 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <13135.1509479192@sss.pgh.pa.us>
Content-Type: multipart/mixed;
 boundary="------------5F83F0ED1B296AF77FB2B2EE"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------5F83F0ED1B296AF77FB2B2EE
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit

Hi,

On 10/31/2017 08:46 PM, Tom Lane wrote:
> I wrote:
>> maybe
>> we just have some run-of-the-mill bugs to find, like the off-the-end
>> bug I spotted in brin_doupdate.  There's apparently at least one
>> more, but given the error message it must be something like not
>> checking for a page to have turned into a revmap page.  Shouldn't
>> be too hard to find...
> 
> Actually, I think it might be as simple as the attached.
> brin_getinsertbuffer checks for the old page having turned into revmap,
> but the "samepage" path in brin_doupdate does not :-(
> 
> With this applied, Alvaro's version of the test case has survived
> without error for quite a bit longer than its former MTBF.  There
> might still be some issues though in other code paths.
> 

That does fix the crashes for me - I've been unable to reproduce any
even after one hour (it took a couple of minutes to crash before).

Unfortunately, I think we still have a problem ... I've been wondering
if we end up producing correct indexes, so I've done a simple test.

1) create the table as before

2) let the insert + vacuum run for some time, to see if there are
crashes (result: no crashes after one hour, inserting ~92M rows)

3) do a bunch of random updates on the data (while still doing the
concurrent vacuum in another session)

4) run a bunch of simple queries to compare the results, essentially

   -- BRIN index
   SET enable_bitmapscan = on;
   SELECT COUNT(*) FROM brin_test WHERE a = $1;


   -- seq scan
   SET enable_bitmapscan = on;
   SELECT COUNT(*) FROM brin_test WHERE a = $1;

and unfortunately what I get is not particularly pleasant:

    test=# set enable_bitmapscan = on;
    SET
    test=# select count(*) from brin_test where a = 0;
     count
    -------
      9062
    (1 row)

    test=# set enable_bitmapscan = off;
    SET
    test=# select count(*) from brin_test where a = 0;
     count
    -------
      9175
    (1 row)

Attached is a SQL script with commands I used. You'll need to copy the
commands into multiple psql sessions, though, to simulate concurrent
activity).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--------------5F83F0ED1B296AF77FB2B2EE
Content-Type: application/sql;
 name="brin-test.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="brin-test.sql"

LS0gY3JlYXRlIHRhYmxlIGFuZCBpbmRleGVzIChvbmUgYnRyZWUgdXNlZCBi
eSB0aGUgdXBkYXRlcyBsYXRlcikKY3JlYXRlIHRhYmxlIGJyaW5fdGVzdCAo
aWQgaW50LCBhIGludCwgYiBiaWdpbnQsIGMgZmxvYXQsIGQgZG91YmxlIHBy
ZWNpc2lvbiwgZSB1dWlkKTsKY3JlYXRlIGluZGV4IG9uIGJyaW5fdGVzdCAo
aWQpOwpjcmVhdGUgaW5kZXggb24gYnJpbl90ZXN0IHVzaW5nIGJyaW4gKGEp
IHdpdGggKHBhZ2VzX3Blcl9yYW5nZT0xNik7CmNyZWF0ZSBpbmRleCBvbiBi
cmluX3Rlc3QgdXNpbmcgYnJpbiAoYikgd2l0aCAocGFnZXNfcGVyX3Jhbmdl
PTE2KTsKY3JlYXRlIGluZGV4IG9uIGJyaW5fdGVzdCB1c2luZyBicmluIChj
KSB3aXRoIChwYWdlc19wZXJfcmFuZ2U9MTYpOwpjcmVhdGUgaW5kZXggb24g
YnJpbl90ZXN0IHVzaW5nIGJyaW4gKGQpIHdpdGggKHBhZ2VzX3Blcl9yYW5n
ZT0xNik7CmNyZWF0ZSBpbmRleCBvbiBicmluX3Rlc3QgdXNpbmcgYnJpbiAo
ZSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlPTE2KTsKCi0tIGxlYXZlIHRoZXJl
IDEwJSBmcmVlIHNwYWNlIGZvciB0aGUgdXBkYXRlcwphbHRlciB0YWJsZSBi
cmluX3Rlc3Qgc2V0IChmaWxsZmFjdG9yID0gOTApOwoKLS0gaW5zZXJ0IGRh
dGEgKGxldCBpdCBydW4gZm9yIGEgd2hpbGUpCmluc2VydCBpbnRvIGJyaW5f
dGVzdCBzZWxlY3QgaSwgbW9kKGksMTAwMDApLCBtb2QoaSwxMDAwMCksIG1v
ZChpLDEwMDAwKS8yNSwgbW9kKGksMTAwMDApLzI1LCBtZDUoKG1vZChpLDEw
MDAwKS8yNSk6OnRleHQpOjp1dWlkIGZyb20gZ2VuZXJhdGVfc2VyaWVzKDEs
MTAwMDAwKSBzKGkpIFx3YXRjaCAwLjEKCi0tIHVwZGF0ZSByYW5kb20gc3Vi
c2V0cyBvZiBkYXRhIChldmVyeSB0aW1lIGFib3V0IDAuMDAxJSkKLS0gdGhl
IHVwZGF0ZSBvbmx5IHNsaWdodGx5IHR3ZWFrcyB0aGUgdmFsdWVzLCB0byBu
b3QgbWFrZSB0aGUgcmFuZ2VzIGV4dHJlbWVseSB3aWRlCnVwZGF0ZSBicmlu
X3Rlc3Qgc2V0IGEgPSBhICsgKDUwIC0gMTAwKnJhbmRvbSgpKSwgYiA9IGIg
KyAoMTAwIC0gMjAwICogcmFuZG9tKCkpLCBjID0gYyArICgxMCAtIDIwICog
cmFuZG9tKCkpLCBkID0gZCArICgyNSAtIDUwICogcmFuZG9tKCkpLCBlID0g
bWQ1KCgocmFuZG9tKCkgKiAxMDAwMCkvMjUpOjp0ZXh0KTo6dXVpZCB3aGVy
ZSBpZCA9ICgxMDAwMDAgKiByYW5kb20oKSk6OmludCBcd2F0Y2ggMTAK

--------------5F83F0ED1B296AF77FB2B2EE
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------5F83F0ED1B296AF77FB2B2EE--