Date: Sun, 7 Nov 2021 18:28:46 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>,
	Kamigishi Rei <iijima.yun@koumakan.jp>,
	Amit Kapila <amit.kapila16@gmail.com>,
	Andrew Gierth <andrew@tao11.riddles.org.uk>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
	pgsql-bugs@lists.postgresql.org,
	Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
Message-ID: <20211108002846.GL17618@telsasoft.com>
References: 
 <CAH2-WzkN5aESSLfK7-yrYgsXxYUi__VzG4XpZFwXm98LUtoWuQ@mail.gmail.com>
 <20211030204246.d6zda3sa46bqpo5l@alap3.anarazel.de>
 <CAH2-Wzk-4_raTzawWGaiqNvkpwDXxv3y1AQhQyUeHfkU=tFCeA@mail.gmail.com>
 <20211030221824.xhzfhwveam7ymqha@alap3.anarazel.de>
 <CAH2-WzmM+NjF3FfzfPau8ZmDN=qx9E+=1LAUdOB6ce89DX=3RQ@mail.gmail.com>
 <CAH2-Wz=0rbqXFu_hVViv4pF31Q3_6xd1raE0-U8seJLj=hL4gA@mail.gmail.com>
 <20211104220722.iyvpoyacqzvm2z5v@alap3.anarazel.de>
 <CAH2-WzmhtcdFiUJ=GwkPnSKWZYm8QBJ0Z11ZdNu0HPYc1wkWvA@mail.gmail.com>
 <20211104231235.3jnopbdpubn3csy7@alap3.anarazel.de>
 <CAH2-WzmWGdy7W=Kjq1rA8MAZj4CGdmjF2+dPp1nGpTKPtS=84g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAH2-WzmWGdy7W=Kjq1rA8MAZj4CGdmjF2+dPp1nGpTKPtS=84g@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211108002846.GL17618%40telsasoft.com>
Precedence: bulk

I was going to ask if it's possible to look for corrupted indexes somehow, but
already discovered two likely instances of this issue:

$ /usr/pgsql-14/bin/pg_amcheck --relation ts.child.alarms_null_alarm_clear_time_idx
btree index "ts.child.alarms_null_alarm_clear_time_idx":
    ERROR:  posting list contains misplaced TID in index "alarms_null_alarm_clear_time_idx"
    DETAIL:  Index tid=(26,10) posting list offset=88 page lsn=223E/92E3DA40.

The index in one case is now 448 kB, in the other case is 632 kB, so I suspect
they're due to this bug (for the 2nd one, a new index created from scratch with
the same definition is 144 kB).

I tried looking for duplicate TIDs like this (which I wrote to search every
suspect table before I found the above issue) - I'm not sure this is right,
though.

| SELECT format('SELECT htid, COUNT(1), %L, %L, %L FROM bt_page_items(%L||''.''||%L,%s) WHERE htid IS NOT NULL AND tids IS NOT NULL GROUP BY 1 HAVING COUNT(1)>1 LIMIT 1', relname, blk, COUNT(1)OVER(), relnamespace::regnamespace, relname, blk ) FROM pg_class c JOIN pg_am a ON c.relam=a.oid AND a.amname='btree' , generate_series(1,pg_relation_size(c.oid)/8192 - 1)blk WHERE c.relkind='i' AND relpages<99 AND (relnamespace=to_regnamespace('child') AND relname~'null') ;

But this doesn't find any issue in that index.  If I'm not wrong, "duplicate
TIDs" are just one likely symptom of the bug, but not its only consequence.
I donno how the TIDs can get out of order, though?

I thought you might like to know about another instance of this in the wild.
I'll disable those indexes and leave them around for a bit in case there's any
question about them.

-- 
Justin