0

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-postgresql-cache-statements-and-data

The question I have is what will destroy/re-write/clear postgres has cached? Naturally, any DDL statement (such as drop, alter etc.) would do. But are there other things?

  • I think you are searching for DISCARD statement; – Sahap Asci Jul 10 at 19:45
  • DISCARD is an example of drops a cache. But the general rule is what I was looking for, as Laurenz has answered. – Hadi Jul 11 at 15:06
2

Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment:

 * Currently, we track exactly the dependencies of plans on relations and
 * user-defined functions.  On relcache invalidation events or pg_proc
 * syscache invalidation events, we invalidate just those plans that depend
 * on the particular object being modified.  (Note: this scheme assumes
 * that any table modification that requires replanning will generate a
 * relcache inval event.)  We also watch for inval events on certain other
 * system catalogs, such as pg_namespace; but for them, our response is
 * just to invalidate all plans.  We expect updates on those catalogs to
 * be infrequent enough that more-detailed tracking is not worth the effort.

In the case of tables:

git grep CacheInvalidateRelcache

on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:

  • contraints are added, removed, modified or renamed
  • attaching or detaching a partition
  • an index is added or dropped
  • REINDEX
  • a table is added to or removed from a publication
  • CLUSTER and VACUUM (FULL)
  • a row level security policy is added, modified or dropped
  • an inheritance child is added
  • ALTER TABLE
  • a trigger is added, dropped, renamed, disabled or enabled
  • a query rewrite rule is added or removed
  • TRUNCATE

Other actions that lead to any such action will of course also invalidate plans.

  • Is this the same for Plan cache? Like git grep CacheInvalidatePlancache – Hadi Jul 10 at 19:55
  • I must have confused you. a "cache invalidation message" means that an object has changed, and all cached information about that object has to be discarded. That causes all plans that use the object to be invalidated. I'll add something to the answer. – Laurenz Albe Jul 10 at 20:02
  • Ty, with git grep CacheInvaidateRelcache I can somehow see the list you provided with postgres's code. How about, as the comments you put, a keyword to search for syscache. I tried CacheInvalidate*, but I don't see something like CacheInvalidateSyscache. – Hadi Jul 10 at 21:05
  • I found the others, CacheInvalidatePartcache etc. all drive an invalidation message that effect an object id. – Hadi Jul 11 at 18:23
  • How does system catalog affect the cache? E.g. pg_proc. – Hadi Jul 16 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.