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

From: Artur Zakirov <[email-masked]>
Date: Wed, 12 Mar 2025 12:54:25 +0100

Hey,

On Wed, 12 Mar 2025 at 10:11, Luca Ferrari <[email-masked]> wrote:
> Now, according to the documentation, the function f_t is immutable
> since it is not modifying the database, so what is going on? And why
> is the same function working if the table has not the constraint on
> the column?

I can reproduce this with the table `t` on PG 15.10.

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.

According to the documentation, IMMUTABLE functions should not only
modify the database, but also return the same results given the same
arguments forever, which might not be true when you query a table
within such a function. Such a function should be defined as STABLE or
VOLATILE.

-- 
Kind regards,
Artur

---

From: Luca Ferrari <[email-masked]>
Date: Wed, 12 Mar 2025 13:31:05 +0100

> I can reproduce this with the table `t` on PG 15.10.

I didn't mention I'm running 16.6, but I'm pretty sure it is
reproducible on other versions too.

> during reading the table `t` within the function `f_t` it tries to

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).

> within such a function. Such a function should be defined as STABLE or

As I stated, this example is controversial, and as the documentation
states, the IMMUTABLE set of functions should not perform database
lookups, as in my example.
However, the error message is quite obscure to me, and reminds me a
disk corruption rather a stability/function/lookup problem.

Luca