Обсуждение: Prepared statement is not re-parsed after used type is re-created -ERROR: cache lookup failed for type NNN
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
Rashid Abzalov <rashid.abzalov@gmail.com> 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
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 <tgl@sss.pgh.pa.us>:
Rashid Abzalov <rashid.abzalov@gmail.com> 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