BUG #15746: cache lookup failed for function in plpgsql block
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
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.
regards,
Roman Zharkov
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
Attachments:
PoC_partof_04fe805a17_on_11_2.patchtext/x-patch; charset=us-asciiDownload+262-30
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