Re: BUG #15746: cache lookup failed for function in plpgsql block

Hello.

At Wed, 10 Apr 2019 03:51:07 +0000, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote in <15746-6e0482a4c0f915cb(at)postgresql(dot)org>
> The following bug has been logged on the website:
>
> Bug reference: 15746
> Logged by: Roman Zharkov
> Email address: r(dot)zharkov(at)postgrespro(dot)ru
> PostgreSQL version: 10.7
> Operating system: centos 7, fedora 28
> Description:
>
> Hello,
> I found a problem within regression tests. The plpgsql test fails when
> running twice on the same database.
> Here is small script illustrates the problem:
>
> begin;
> create function sql_to_date(integer) returns date as $$
> select $1::text::date
> $$ language sql immutable strict;
> create cast (integer as date) with function sql_to_date(integer) as
> assignment;
> create function cast_invoker(integer) returns date as $$
> begin
> return $1;
> end$$ language plpgsql;
>
> select cast_invoker(20150717);
>
> drop function cast_invoker(integer);
> drop function sql_to_date(integer) cascade;
> commit;
>
> begin;
> create function sql_to_date(integer) returns date as $$
> select $1::text::date
> $$ language sql immutable strict;
> create cast (integer as date) with function sql_to_date(integer) as
> assignment;
> create function cast_invoker(integer) returns date as $$
> begin
> return $1;
> end$$ language plpgsql;
>
> select cast_invoker(20150717);
>
> drop function cast_invoker(integer);
> drop function sql_to_date(integer) cascade;
> commit;
>
> Results:
>
> begin;
> create function sql_to_date(integer) returns date as $$
> select $1::text::date
> $$ language sql immutable strict;
> create cast (integer as date) with function sql_to_date(integer) as
> assignment;
> create function cast_invoker(integer) returns date as $$
> begin
> return $1;
> end$$ language plpgsql;
> select cast_invoker(20150717);
> cast_invoker
> --------------
> 07-17-2015
> (1 row)
>
> drop function cast_invoker(integer);
> drop function sql_to_date(integer) cascade;
> NOTICE: drop cascades to cast from integer to date
> commit;
> begin;
> create function sql_to_date(integer) returns date as $$
> select $1::text::date
> $$ language sql immutable strict;
> create cast (integer as date) with function sql_to_date(integer) as
> assignment;
> create function cast_invoker(integer) returns date as $$
> begin
> return $1;
> end$$ language plpgsql;
> select cast_invoker(20150717);
> ERROR: cache lookup failed for function 16414
> CONTEXT: PL/pgSQL function cast_invoker(integer) while casting return value
> to function's return type
> drop function cast_invoker(integer);
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
> drop function sql_to_date(integer) cascade;
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
> commit;
>
> The problem reproduces in the 10, 11 versions.

The cause is stale cast function id in cached expression of
plpgsql. (get_cast_hashentry)

Happens since 9.5 to 11. Once happens, the symptom persists
until session-end. Doesn't happen on 9.4 since it doesn't cache
cast expressions. 12 invalidates cached cast expressions
(04fe805a17).

It seems to me possible that a cast calls a wrong function and
leads to a crash. But I don't come up with a good way to fix
this, but applying a part of the patch 04fe805a17 on 11(.2) seems
working.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center