Re: invalidating cached plans

On Mon, 2005-03-14 at 20:06 -0500, Bruce Momjian wrote:
> Harald Fuchs wrote:
> > In article <6028(dot)1110785150(at)sss(dot)pgh(dot)pa(dot)us>,
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >
> > > One possible approach is to do the invalidation on a sufficiently coarse
> > > grain that we don't care. For example, I would be inclined to make any
> > > change in a table's schema invalidate all plans that use that table at
> > > all; that would then subsume the constraint problem for instance. This
> > > doesn't solve the inlined function problem however.
> >
> > How about using an even coarser grain? Whenever something in the
> > database in question changes, blindly throw away all cached plans for
> > this DB.
>
> We could, but the creation of a single temp table would invalidate all
> caches, and temp table creation might be pretty frequent.

> One idea would be to record if the function uses non-temp tables, temp
> tables, or both, and invalidate based on the type of table being
> invalidated, rather than the table name itself. I can imagine this
> hurting temp table caching, but at least functions using regular tables
> would not be affected, and functions using temp tables would work
> reliably.

Too coarse I think, especially with schemas being considered user
workspaces where they are free to add or modify their structures as they
like (for maintenance, reports, temporary storage, etc.) but there are a
significant number of prepared statements in the controlled segments of
the database.

This would cause the system to hiccup fairly regularly still when a
couple hundred connections are forced to replan their queries.

--