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

Hi all,
this is  a little controversial, but hagin a function defined as
immutable that selects a record out of a table, it is impossible to
create an index over such function if the column has a constraint like
a primary key.

Here it is the use case:

DROP TABLE  if exists t;
drop table  if exists tt;

CREATE TABLE IF NOT EXISTS t
(
  pk int primary key
);


CREATE TABLE IF NOT EXISTS tt
(
  pk int
);


INSERT INTO t
SELECT v FROM generate_series( 1, 1000 ) v;

INSERT INTO tt
SELECT v  FROM generate_series( 1, 1000 ) v;


CREATE OR REPLACE FUNCTION
f_t( i int )
RETURNS int
AS $CODE$
DECLARE
       return_value int;
BEGIN
SELECT pk
INTO return_value
FROM t
WHERE pk = i;

RETURN return_value;
END
$CODE$
LANGUAGE plpgsql
IMMUTABLE;



CREATE OR REPLACE FUNCTION
f_tt( i int )
RETURNS int
AS $CODE$
DECLARE
       return_value int;
BEGIN
SELECT pk
INTO return_value
FROM tt
WHERE pk = i;

RETURN return_value;
END
$CODE$
LANGUAGE plpgsql
IMMUTABLE;



CREATE INDEX IF NOT EXISTS idx_tt ON tt( f_tt( pk ) );

CREATE INDEX IF NOT EXISTS idx_t ON t( f_t( pk ) );



The last index, created on table t throws the error:

ERROR:  could not read block 0 in file "base/357283/365810": read only 0
of 8192 bytes
CONTEXT:  SQL statement "SELECT pk
                          FROM t
       WHERE pk = i"
PL/pgSQL function f_t(integer) line 5 at SQL statement

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?

Moreover:

select oid, relname, relkind, pg_relation_filepath( oid ) from
pg_class where pg_relation_filepath(
oid ) = 'base/357283/365810';
oid | relname | relkind | pg_relation_filepath
-----+---------+---------+----------------------
(0 rows)


So at what is referencing the error exactly?

Thanks,
Luca

Hey,

On Wed, 12 Mar 2025 at 10:11, Luca Ferrari <fluca1978@gmail.com> 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

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.

Luca

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

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