BUG #18016: REINDEX TABLE failure - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18016
Logged by:          Richard Vesely
Email address:      richard.vesely@softea.sk
PostgreSQL version: 15.3
Operating system:   Windows 10 Enterprise 22H2
Description:

Hi,

Given a table with a TOASTed variable length attribute, REINDEX TABLE fails
to rebuild indexes when you truncate (or otherwise corrupt) relation files
for both TOAST table index and a custom index on the varlena.

Here's an error from server log with log_error_verbosity set to verbose:

ERROR:  XX001: could not read block 0 in file "base/[datoid]/[relfilenode]":
read only 0 of 8192 bytes
LOCATION:  mdread, md.c:724
STATEMENT:  reindex table t1

However, when you perform a manual reindex in the correct order - REINDEX
INDEX pg_toast.pg_toast_oid_index and then REINDEX INDEX t1_column1_idx it
works as expected. REINDEX TABLE should ensure that the TOAST index is
rebuilt first before rebuilding an index on (potentially) TOASTed values. In
this particular example when you REINDEX TOAST index first and then run the
full REINDEX TABLE you can see that it always rebuilds the custom index
first based on relation file nodes.

Best regards,
Richard Veselý

Here's a minimal repro dump:

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.3
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: bug_report; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE bug_report WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';


ALTER DATABASE bug_report OWNER TO postgres;

\connect bug_report

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--

-- *not* creating schema, since initdb creates it


ALTER SCHEMA public OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    column1 text
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t1 (column1) FROM stdin;

vkifpbzxdplzkizpaugzhlejhqmvgwmlhqlgofbvoaiowqohnmxaldkyoawdrpttppkxfratkgeyxogzdvihkssbpyvgbnbhgaezhhgyehqcduakvrahnauymfuqznthijohfbbuzitrpifmqkezjbujngzsijsquskztqypdkienyhytyergfbibasksgntabxgzgrmhtzrukjuykaqfrksqcswwbsmlmdfrpovbdlvcaofztwasbfzwyoeklbnacgtdrwjfvdpdccnyetkohmtgwdkzlnofyccxgrbojcjnruvwlbwbpxyzubwqjmfnzvzkjsdgozewauqlbmckpxztuidtdfpvbhizlbrezvkndjcodbjabxggywtqpsofdtsfyspjscrmghbbpxhuvqvxpgwfdvhhcvekncudhzbtotqxxzixoqnybzpnhvgnhdlcbctyitiqdilwuensusfcfelojvzhgrefyrqohdqiaewddpharcwipjyyijudozpkomgsstqbarykbuoxgnmjwcvkufidiozxccwtfzatxyztjmeihlzyafdafqbkkqqekasgfllfcdaelwsecayspnspvofkelkxfytrwfccuynwjlafelgnuggvejoiketoeqpxtofivpxeqahxnhdkhfwdbytqlfulogxdpjbbtioelkuxywcdvknjbllmyvuckduywllkljfpoxiwgunwjwoiokenfygsduokepxjetyjjzbnxqbvsdbrpefdlghluynoqsxkfrttsibjkdtforzhmhazyzoaanvstmqafsuynrvmknivmcvcqlwxmdgjnhuivxzwjefszyrkzmvleskghrknohfyntnsovqiquojnrzsusyvjfcogtdgrlbyemggllpyvqxclqqcmwcvrvtejmiinlmqfcznszledlavaqwnugijgevehlrydlrlluqmepaqyqlhpyxeuryqwauyfaoifsxsxxxemgidmzxzjpoecapyubvprnzlgvrlidotzluaodlwrrphgxfpcsskkaxguwajcytusnpbudvuvdjqzujgdlqnoksainpdwcfdwizvpgnhysunadzaizywtzgydpgumfedoqbhdlqynufivmqyihkfqnvavofgojzjrzpfhmqqgxqmmhkyvsloegljgjglkywqfjqcwawigxhlbmztzytlqlheghhhykttjvbqkdnuuiajqvpihyrwjnlihglgxebhalthpizkrccgnxkwfxjsjrpcsitmdounnbxoeoomstbykypoflitwvirpwdrdvrtwkqwbqlsqxkvogdsdkwffvvzalibtgtkbcmqjcpvlwpubdhykqsrqwzmaqbwndmvribafoyizgbpbavvvtivkcofijaubtpmzfgauvrgfqjlsksdtfaaimfnurstbfikildbcdfzbwzqicjwewrxzppneyrlhsrdaprgmaofulgcffstvikvwvkmprddflkudytkrlccrkivvzwvmsyeigowqoqkidzcetlnfaxlpyalzennzgexiaqduzffijgsbhshyaiephqviluzzjdfgjjgkphdkamlwzppqpvpjbgnjnmvmgyrqubvsgpivstqbydtbpakripvsvnuqwwgngwdoeeichpljrnqstcdeobubjcudjizrgxjfmcvghrlhvjseinrfkmeqhrcullxildvkcjcbozpsowddwdqusclysmaasmcgruosqqjcjurtqhnnigvpviuhwroydcxhasvqwcgeauiawnqyreaoikhbaymizkanzjyrbtftiddryylqxfhmzomlqkcqkgrapqgiiylahganeibkzahxitcwswgpqmvnlgyuxywoaqqlbqdpfexlpzpzlpucwgqxfraqwqmvwhuojbmpngdhenplmkomgwmnplwnfnlgmejgyoapkjmyvsolpiqlebfumcywfxvbgshaakujitbbgrvtqxvsfvapuejebqoknhaefyeebmlqvoifjvlnosxkvk
\.


--
-- Name: t1_column1_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX t1_column1_idx ON public.t1 USING btree (column1);


--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--