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@postgresql.org> wrote in <15746-6e0482a4c0f915cb@postgresql.org>

The following bug has been logged on the website:

Bug reference: 15746
Logged by: Roman Zharkov
Email address: r.zharkov@postgrespro.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

On 2019-04-11 19:11, Kyotaro HORIGUCHI wrote:

Hello.

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.

Hello,

Thank you! The patch works.

--
regards,
Roman Zharkov

Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:

It seems to me possible that a cast calls a wrong function and
leads to a crash.

I don't see a good reason to think that a crash is possible.

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.

FWIW, I intentionally did not back-patch 04fe805a17. I judged
that before the domain-related changes in that patch, such cases
wouldn't come up often enough to justify inserting poorly-tested
code into the back branches. I still think that.

regards, tom lane