MIME-Version: 1.0
From: Luca Ferrari <fluca1978@gmail.com>
Date: Wed, 12 Mar 2025 10:10:33 +0100
Message-ID: <CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com>
Subject: ERROR: could not read block 0 in file when creating an index out of a function
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAKoxK%2B7G%2B%3DnEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg%40mail.gmail.com>
Precedence: bulk

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