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?
