From: Justin Pryzby <pryzby@telsasoft.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>
Cc: Kamigishi Rei <iijima.yun@koumakan.jp>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-bugs@lists.postgresql.org, Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
Date: Sun, 7 Nov 2021 18:28:46 -0600
Message-ID: <20211108002846.GL17618@telsasoft.com> (raw)
In-Reply-To: <CAH2-WzmWGdy7W=Kjq1rA8MAZj4CGdmjF2+dPp1nGpTKPtS=84g@mail.gmail.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: pryzby@telsasoft.com, pg@bowt.ie, andres@anarazel.de, iijima.yun@koumakan.jp, amit.kapila16@gmail.com, andrew@tao11.riddles.org.uk, alvherre@alvh.no-ip.org, thomas.munro@gmail.com
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
In-Reply-To: <20211108002846.GL17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox