public inbox for [email protected]  

help / color / mirror / Atom feed

From: Luca Ferrari <[email protected]>
To: pgsql-general <[email protected]>
Subject: ERROR: could not read block 0 in file when creating an index out of a function
Date: Wed, 12 Mar 2025 10:10:33 +0100
Message-ID: <CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function
  In-Reply-To: <CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox