PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15746
PG Version10.7
OScentos 7, fedora 28
Opened2019-04-10 03:51:07+00
Reported byRoman Zharkov
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15746
Logged by:          Roman Zharkov
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-04-10 03:51:07+00PG Bug reporting formBUG #15746: cache lookup failed for function in plpgsql block
2019-04-11 12:11:13+00Kyotaro HORIGUCHIRe: BUG #15746: cache lookup failed for function in plpgsql block
2019-04-12 03:22:09+00r(dot)zharkov(at)postgrespro(dot)ruRe: BUG #15746: cache lookup failed for function in plpgsql block
2019-04-12 16:55:04+00Tom LaneRe: BUG #15746: cache lookup failed for function in plpgsql block