Prepared statement is not re-parsed after used type is re-created - ERROR: cache lookup failed for type NNN

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Prepared statement is not re-parsed after used type is re-created - ERROR: cache lookup failed for type NNN

Rashid Abzalov
Prepared statement is not re-parsed after used type is re-created. While the re-creation of other types of objects (for example, a table or a function) works correctly.

According to the documentation, the prepared statement was to be re-analyzed again:
PostgreSQL will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes since the previous use of the prepared statement.

Tested on 11.2 and 9.6.12.

Steps to reproduce:
1) Create type and function:
create type pg_temp.temp_type AS (field varchar(64));

create function pg_temp.test_func(param pg_temp.temp_type) returns temp_type as $$
begin
  param.field = 'qqq';
  return param;
end;
$$ language plpgsql;

2) Prepare and use them:
PREPARE testplan (pg_temp.temp_type) AS
    select * from pg_temp.test_func($1);
EXECUTE testplan(('(qqq)')::pg_temp.temp_type);

3) Drop type and function:
drop function pg_temp.test_func(param pg_temp.temp_type);
drop type pg_temp.temp_type;

4) Recreate type and function:
create type pg_temp.temp_type as (field varchar(64));

create function pg_temp.test_func(param pg_temp.temp_type) returns temp_type as $$
begin
  param.field = 'qqq';
  return param;
end;
$$ language plpgsql;

5) Trying execute prepared SQL:
EXECUTE testplan(('(qqq)')::pg_temp.temp_type);

ERROR: cache lookup failed for type 2906443
Reply | Threaded
Open this post in threaded view
|

Re: Prepared statement is not re-parsed after used type is re-created - ERROR: cache lookup failed for type NNN

Tom Lane-2
Rashid Abzalov <[hidden email]> writes:
> Steps to reproduce:
> *1)* Create type and function:
> create type pg_temp.temp_type AS (field varchar(64));

> create function pg_temp.test_func(param pg_temp.temp_type) returns
> temp_type as $$
> begin
>   param.field = 'qqq';
>   return param;
> end;
> $$ language plpgsql;

> *2)* Prepare and use them:
> PREPARE testplan (pg_temp.temp_type) AS
>     select * from pg_temp.test_func($1);
> EXECUTE testplan(('(qqq)')::pg_temp.temp_type);

> *3)* Drop type and function:
> drop function pg_temp.test_func(param pg_temp.temp_type);
> drop type pg_temp.temp_type;

So at this point you've deleted the type that is declared to be the
argument type of the prepared statement.  I don't think "reparse" is what
is called for here.  The prepared statement is busted and we shouldn't be
willing to execute it anymore.  As indeed we don't, though I'll grant you
that "cache lookup failed" is not the world's best error message for that.

If prepared statements were full-fledged database objects, we could
imagine hooking them into the DROP CASCADE logic so that you couldn't drop
the type without (implicitly or explicitly) doing "DEALLOCATE testplan".
I doubt anyone will care to do that though, as it'd make them far
heavier-weight than they are now.

My inclination, if we do anything at all about this, is just to add
something like

        if (!SearchSysCacheExists1(TYPEOID, ObjectIdGetDatum(expected_type_id)))
                ereport(ERROR, ... "prepared statement's argument type no longer exists");

in EvaluateParams.  But I can't get too excited about that.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Prepared statement is not re-parsed after used type is re-created - ERROR: cache lookup failed for type NNN

Rashid Abzalov
Why prepared statements is not busted after other used objects are re-created (table, function)?
Obviously, in such cases, the prepared statement is re-parsed before execute it again.
The similar behavior was expected for types.

ср, 1 мая 2019 г., 0:00 Tom Lane <[hidden email]>:
Rashid Abzalov <[hidden email]> writes:
> Steps to reproduce:
> *1)* Create type and function:
> create type pg_temp.temp_type AS (field varchar(64));

> create function pg_temp.test_func(param pg_temp.temp_type) returns
> temp_type as $$
> begin
>   param.field = 'qqq';
>   return param;
> end;
> $$ language plpgsql;

> *2)* Prepare and use them:
> PREPARE testplan (pg_temp.temp_type) AS
>     select * from pg_temp.test_func($1);
> EXECUTE testplan(('(qqq)')::pg_temp.temp_type);

> *3)* Drop type and function:
> drop function pg_temp.test_func(param pg_temp.temp_type);
> drop type pg_temp.temp_type;

So at this point you've deleted the type that is declared to be the
argument type of the prepared statement.  I don't think "reparse" is what
is called for here.  The prepared statement is busted and we shouldn't be
willing to execute it anymore.  As indeed we don't, though I'll grant you
that "cache lookup failed" is not the world's best error message for that.

If prepared statements were full-fledged database objects, we could
imagine hooking them into the DROP CASCADE logic so that you couldn't drop
the type without (implicitly or explicitly) doing "DEALLOCATE testplan".
I doubt anyone will care to do that though, as it'd make them far
heavier-weight than they are now.

My inclination, if we do anything at all about this, is just to add
something like

        if (!SearchSysCacheExists1(TYPEOID, ObjectIdGetDatum(expected_type_id)))
                ereport(ERROR, ... "prepared statement's argument type no longer exists");

in EvaluateParams.  But I can't get too excited about that.

                        regards, tom lane