Re: invalidating cached plans

"Neil Conway" <neilc(at)samurai(dot)com> writes
> I don't see that this is a major problem. If a plan A invokes a function
> B, then changes to B will need to invalidate A; that should be pretty
> easy to arrange. If B is a PL/PgSQL function that invokes a function C,
> it will probably cache a plan involving C. But when C changes, we need
> only flush B's cached plan, _not_ A -- as far as A is concerned, the
> operation of B is a blackbox.

This is the key point (say this is point_1) - we must make sure how deep we
have to go to check validity. So if the plan of A will not reply on any
result information of B, say returned/affected row count of B, then it is
ok.

> The only exception is when B is a SQL
> function that is inlined, but we can handle that separately.

I don't quite understand the difference between a SQL function and a
PL/PgSQL function here - since there is a overlapped functionality that we
could implement by SQL function or by PL/PgSQL function.

> Regarding performance, the important point is that a DDL command
> "pushes" changes out to backends to invalidate cached plans -- a plan
> doesn't need to poll to see if there have been any changes to objects it
> depends upon. And on a production system, DDL should usually be
> infrequent (the primary exception is temp table creation/destruction,
> but we can potentially optimize for that since it is backend-local).

Yes, it is DDL's responsibility to do invalidation, and the query should
never worry about the cached plan it will use.

So when a DDL comes, it has to know all the objects it affects directly(no
need to go deeper, based on point_1), then for each plan in the cache we
check if they are directly(based on point_1) related to these changed
objects.

Regards,
Qingqing