Hi,
On 2018-06-27 20:35:16 +0200, Luca Ferrari wrote:
> 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
That certainly isn't behaviour I'd expect. Doing nasty stuff inside an
immutable function will have bad consequences, but the permanent failure
shouldn't be there.
But I also can't reproduce it either on 10.4, 10-current, master. Did
you build from source? Packages? Any extensions? Is there anything
missing from the above instruction to reproduce this?
Greetings,
Andres Freund