could not read block 0 in file : read only 0 of 8192 bytes when doingnasty on immutable index function - Mailing list pgsql-general

From Luca Ferrari
Subject could not read block 0 in file : read only 0 of 8192 bytes when doingnasty on immutable index function
Date
Msg-id CAKoxK+5fVodiCtMsXKV_1YAKXbzwSfp7DgDqUmcUAzeAhf=HEQ@mail.gmail.com
Whole thread Raw
Responses Re: could not read block 0 in file : read only 0 of 8192 bytes whendoing nasty on immutable index function
Re: could not read block 0 in file : read only 0 of 8192 bytes whendoing nasty on immutable index function
List 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?


pgsql-general by date:

Previous
From: Moreno Andreo
Date:
Subject: Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
Next
From: Vick Khera
Date:
Subject: Re: Database name with semicolon