Thread: BUG #18577: Miss out part invalItems in saved plansource of one CallStmt
BUG #18577: Miss out part invalItems in saved plansource of one CallStmt
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18577 Logged by: haiyang li Email address: ocean_li_996@163.com PostgreSQL version: 14.12 Operating system: centos7 5.10.84 x86_64 Description: With the following procedures, I always get the error "ERROR: cache lookup failed for function xxx" if calling the DO procedure in this session. ### create or replace procedure p1(in n1 numeric, out r1 integer) as $$ begin call p2(n1, r1); raise notice 'r1 of p1: %', r1; end; $$ language plpgsql; create or replace procedure p2(in n1 numeric, out r1 numeric) as $$ begin r1 := n1; raise notice 'r1 of p2: %', r1; end; $$ language plpgsql; -- another error do $$ declare a numeric := 100; b integer; begin call p1(a, b); raise notice 'b is: %', b; end; $$ language plpgsql; -- recreate the procedure p2 drop procedure p2; create or replace procedure p2(in n1 numeric, out r1 numeric) as $$ begin r1 := n1; raise notice 'r1 of p2: %', r1; end; $$ language plpgsql; -- cache lookup failed for function xxx do $$ declare a numeric := 100; b integer; begin call p1(a, b); raise notice 'b is: %', b; end; $$ language plpgsql; ### My analysis: For one CallStmt, a saved plansource will be generated in _SPI_prepare_plan(). But, plansource->invalItems of 'call p1(a, b)' only contains p1 itself in this case. If we do some DDL on p2, only the plansource of 'call p2(n1, r1)' is invalidated and plansource of 'call p1(a, b)' is still valid. Then, we can get the same error until plansource of 'call p1(a, b)' invalidated. In my opinion, the PROCOID reported in 'Cached lookup falied for function' error is stable which means plansource of 'call p1(a, b)' record the PROCOID. And we have a chance to detect p2 proc and add it to plansource->invalItems of 'call p1(a, b)'. However, I'm not quite sure how extract_query_dependencies() works. So, I have no idea how to fix it. Any ideas? regards Haiyang Li