Re: ERROR: could not read block 0 in file when creating an index out of a function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Luca Ferrari <fluca1978(at)gmail(dot)com>
Cc: Artur Zakirov <zaartur(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function
Date: 2025-03-12 14:18:07
Message-ID: 1955103.1741789087@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Luca Ferrari <fluca1978(at)gmail(dot)com> writes:
> On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov <zaartur(at)gmail(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2025-03-12 15:46:49 Re: Duplicate Key Values
Previous Message Achilleas Mantzios - cloud 2025-03-12 13:56:39 Re: ERROR: could not read block 0 in file when creating an index out of a function