From: Tom Lane <tgl@sss.pgh.pa.us>
To: Luca Ferrari <fluca1978@gmail.com>
cc: Artur Zakirov <zaartur@gmail.com>,
        pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function
In-reply-to: <CAKoxK+5ek4R-6kxzJ1TALf_Dxe-dM-xjTLkWp=9_BTP7Gx8Jrg@mail.gmail.com>
References: <CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com> <CAKNkYnyPs8sNwb7Xq3sDSYRgNyHgQNibJjcyrkv596uCOLGXxQ@mail.gmail.com> <CAKoxK+5ek4R-6kxzJ1TALf_Dxe-dM-xjTLkWp=9_BTP7Gx8Jrg@mail.gmail.com>
Comments: In-reply-to Luca Ferrari <fluca1978@gmail.com>
	message dated "Wed, 12 Mar 2025 13:31:05 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1955102.1741789087.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 12 Mar 2025 10:18:07 -0400
Message-ID: <1955103.1741789087@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1955103.1741789087%40sss.pgh.pa.us>
Precedence: bulk

Luca Ferrari <fluca1978@gmail.com> writes:
> On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov <zaartur@gmail.com> wrote:
>> In your case `base/357283/365810` file is a new index file. For some
>> reason Postgres tries to read the new index. I suppose this is because
>> during reading the table `t` within the function `f_t` it tries to
>> access the new index.

> Yeah, even if it is not clear to me why it is trying to read the index
> that is under creation (i.e., not usable yet).

Yeah, this has been reported before.  While planning the "SELECT FROM
tt" query within the function, the planner tries to access all the
indexes of tt --- including the one that's only half-built.
(Specifically, it wants to know the tree heights of all the btree
indexes.)  We've dismissed this as not being something we care to fix,
because the argument that such a function is immutable is specious.
And the case can't really happen without a function referencing the
index's base table; for example, a query from another session can't
see the uncommitted index at all.

			regards, tom lane