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