RE: BUG #16031: Group by returns duplicate groups

Here's what it's reporting for an error:

testing=# select bt_index_check('big_table_name_id_1_id_2_idx', true);
ERROR:  high key invariant violated for index "big_table_name_id_1_id_2_idx"
DETAIL:  Index tid=(86990,140) points to heap tid=(139313,57) page lsn=0/0.
Time: 65695.359 ms (01:05.695)
testing=# select bt_index_parent_check('big_table_name_id_1_id_2_idx', true);
ERROR:  down-link lower bound invariant violated for index "big_table_name_id_1_id_2_idx"
DETAIL:  Parent block=43131 child index tid=(43197,9) parent page lsn=0/0.
Time: 1697.205 ms (00:01.697)
testing=#



-----Original Message-----
From: Peter Geoghegan <pg@bowt.ie> 
Sent: Monday, September 30, 2019 7:34 PM
To: David Raymond <David.Raymond@tomtom.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16031: Group by returns duplicate groups

On Mon, Sep 30, 2019 at 2:49 PM David Raymond <David.Raymond@tomtom.com> wrote:
> I re-created the index...
> create index on big_table (name, id_1, id_2);
>
> ...and count(*) goes back to returning 9 again.
>
> and group by sees those 9 as one group and the other 16 as a different group.

You should try running contrib/amcheck, which should be able to confirm
index corruption, and give you a specific complaint. You may then be
able to inspect the exact index page with the problem using
contrib/pageinspect. Something like this ought to do it on Postgres
11:

CREATE EXTENSION IF NOT EXISTS amcheck
SELECT bt_index_check('my_index', true);

If that doesn't show any errors, then perhaps try this:

SELECT bt_index_parent_check('my_index', true);

Let us know what you see.

--
Peter Geoghegan