From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | ERROR: could not read block 0 in file when creating an index out of a function |
Date: | 2025-03-12 09:10:33 |
Message-ID: | CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Foerster | 2025-03-12 11:01:09 | Re: Moving from Linux to Linux? |
Previous Message | Ron Johnson | 2025-03-12 02:03:50 | Re: hide data from admins |