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

On 3/12/25 14:31, Luca Ferrari wrote:
> On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov<zaartur@gmail.com> wrote:
>> 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.
>
>> 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).
>
>
>> 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.
> 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.

Test on 17.0:

It seems inconsistent to me :

creating a fresh ttt (no PK no constraint) :

amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
CREATE INDEX
amantzio@[local]/test=# drop index idx_t ;
DROP INDEX
amantzio@[local]/test=# ALTER TABLE ttt ALTER pk SET NOT NULL ;
ALTER TABLE
amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
ERROR:  could not read blocks 0..0 in file "base/17753/596558047": read 
only 0 of 8192 bytes
CONTEXT:  SQL statement "SELECT pk
                  FROM public.ttt
WHERE pk = i"
PL/pgSQL function f_ttt(integer) line 5 at SQL statement
amantzio@[local]/test=# ALTER TABLE ttt ALTER pk DROP NOT NULL ;
ALTER TABLE
amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
ERROR:  could not read blocks 0..0 in file "base/17753/596558048": read 
only 0 of 8192 bytes
CONTEXT:  SQL statement "SELECT pk
                  FROM public.ttt
WHERE pk = i"
PL/pgSQL function f_ttt(integer) line 5 at SQL statement
amantzio@[local]/test=#
\q

So yep you are definitely right as far as the error message is concerned 
plus the inconsistent pattern shown above.

>
> Luca
>
>