could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function
Luca Ferrari <fluca1978@gmail.com>
From: Luca Ferrari <fluca1978@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Date: 2018-06-27T18:35:16Z
Lists: pgsql-bugs, pgsql-general
I've found this strange (to me) behavior when doing nasty things with
indexes and immutable functions:
create table t( pk serial, t text );
insert into t( t ) values( 'hello' ), ('world');
create or replace function f_fake( i int )
returns text
as $body$
declare
v_t text;
begin
select t into strict v_t
from t where pk = i limit 1;
return v_t;
exception
when no_data_found then return 'a';
end
$body$
language plpgsql immutable;
Of course, f_fake is not immutable.
When on 10.4 or 11 beta 1 I try to create an index on this nasty
crappy function:
create index idx_fake on t ( f_fake( pk ) );
ERROR: could not read block 0 in file "base/16392/16444": read only 0
of 8192 bytes
CONTEXT: SQL statement "select t from t where pk =
i limit 1"
PL/pgSQL function f_fake(integer) line 5 at SQL statement
that is somehow correct (because the function cannot be used to build
an index), but then it goes worst:
elect * from t;
ERROR: could not open relation with OID 16444
If I then disconnect and reconnect I'm able to issue the select and
get back the results. But if I issue a reindex I got the same error
and the table "becames unreadable" for the whole session.
On 10.3 the table is never locked for the session, that is I can
create the index, I can query the table and get the results, but I
cannot reindex. However, even after a reindex, it does allow me to
select data from the table.
So my question is: why this behavior in later PostgreSQL?
Commits
-
Add table relcache invalidation to index builds.
- b3f919da0754 12.0 landed
- b9612e5cfab8 11.0 landed
- c83408aa71e0 10.5 landed
- 0a60a291c9a5 9.6.10 landed
- aca22574106e 9.5.14 landed
- 250528cec09f 9.4.19 landed
- 0229e087df2a 9.3.24 landed