Обсуждение: plan cache doesn't clean plans with references to dropped procedures
Hi
I am playing with fixing the speed of CALL statement in a non atomic context, and when I tested my patch I found another issue of CALL statement - an invalidation of plans doesn't work for CALL statement (in atomic context).
CREATE OR REPLACE FUNCTION public.fx(a integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
return a;
end;
$function$
create or replace function fxo(a int)
returns int as $$
begin
return fx(a);
end;
$$ language plpgsql;
drop function fx;
-- create fx again
create or replace function fx(a int)
returns int as $$
begin
return a;
end;
$$ language plpgsql;
-- should be ok
select fxo(10);
-- but
create procedure pe(a int)
as $$
begin
end;
$$ language plpgsql;
create or replace function fxo(a int)
returns int as $$
begin
call pe(a);
return fx(a);
end;
$$ language plpgsql;
-- ok
select fxo(10);
postgres=# drop procedure pe;
DROP PROCEDURE
postgres=# create procedure pe(a int)
as $$
begin
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# select fxo(10);
ERROR: cache lookup failed for function 16389
CONTEXT: SQL statement "CALL pe(a)"
PL/pgSQL function fxo(integer) line 2 at CALL
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
return a;
end;
$function$
create or replace function fxo(a int)
returns int as $$
begin
return fx(a);
end;
$$ language plpgsql;
drop function fx;
-- create fx again
create or replace function fx(a int)
returns int as $$
begin
return a;
end;
$$ language plpgsql;
-- should be ok
select fxo(10);
-- but
create procedure pe(a int)
as $$
begin
end;
$$ language plpgsql;
create or replace function fxo(a int)
returns int as $$
begin
call pe(a);
return fx(a);
end;
$$ language plpgsql;
-- ok
select fxo(10);
postgres=# drop procedure pe;
DROP PROCEDURE
postgres=# create procedure pe(a int)
as $$
begin
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# select fxo(10);
ERROR: cache lookup failed for function 16389
CONTEXT: SQL statement "CALL pe(a)"
PL/pgSQL function fxo(integer) line 2 at CALL
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I am playing with fixing the speed of CALL statement in a non atomic
> context, and when I tested my patch I found another issue of CALL statement
> - an invalidation of plans doesn't work for CALL statement (in atomic
> context).
Yeah, that's not the plancache's fault. CALL doesn't register any
dependencies for the parsed expression it keeps in its parsetree.
I remain of the opinion that we need to decide whether CALL is
a utility command or an optimizable statement, and then make it
follow the relevant set of rules. It can't live halfway between,
especially not when none of the required infrastructure has been
built to allow it to act like an optimizable statement. (Hm,
I could swear we discussed this before, but searching the archives
doesn't immediately turn up the thread. Anyway, you don't get to
do parse analysis in advance of execution when you are a utility
command.)
Probably the only feasible fix for the back branches is to go in the
utility-command direction, which means ripping out the pre-parsed
expression in CallStmt. Somebody could look at making it act like an
optimizable statement in the future; but that'll involve touching a
nontrivial amount of code, and I'm not sure how much performance it'll
really buy.
regards, tom lane
čt 15. 10. 2020 v 20:17 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I am playing with fixing the speed of CALL statement in a non atomic
> context, and when I tested my patch I found another issue of CALL statement
> - an invalidation of plans doesn't work for CALL statement (in atomic
> context).
Yeah, that's not the plancache's fault. CALL doesn't register any
dependencies for the parsed expression it keeps in its parsetree.
I remain of the opinion that we need to decide whether CALL is
a utility command or an optimizable statement, and then make it
follow the relevant set of rules. It can't live halfway between,
especially not when none of the required infrastructure has been
built to allow it to act like an optimizable statement. (Hm,
I could swear we discussed this before, but searching the archives
doesn't immediately turn up the thread. Anyway, you don't get to
do parse analysis in advance of execution when you are a utility
command.)
Probably the only feasible fix for the back branches is to go in the
utility-command direction, which means ripping out the pre-parsed
expression in CallStmt. Somebody could look at making it act like an
optimizable statement in the future; but that'll involve touching a
nontrivial amount of code, and I'm not sure how much performance it'll
really buy.
Maybe I wrote necessary code (or some part) for LET statement
Anyway, I think another related issue will be in work with optimized (cached) target.
regards, tom lane