could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function
Date: 2018-06-27 18:35:16
Message-ID: CAKoxK+5fVodiCtMsXKV_1YAKXbzwSfp7DgDqUmcUAzeAhf=HEQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2018-06-27 19:53:13 Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function
Previous Message Jeff Janes 2018-06-27 15:02:50 Re: BUG #15253: DROP/select/update query waiting for pg_dump

Browse pgsql-general by date

  From Date Subject
Next Message Vick Khera 2018-06-27 18:54:24 Re: Database name with semicolon
Previous Message Moreno Andreo 2018-06-27 16:55:15 Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data