Cached Plans and When They Die

Why this stage exists

Your PREPARE parses and checks a statement once. The planner still runs on every EXECUTE: choosing a scan, maybe costing joins, maybe reordering them. For a statement run ten thousand times a minute, that is ten thousand identical plans. So the plan is kept too, the last piece of work that did not depend on the values.

A kept plan is a promise about a world that can change. It says "use index t_name". Then someone drops t_name. Or it was costed with statistics from when the table had a hundred rows, and it now has a million. A plan cache that never notices runs a plan that crashes or crawls. One that re-plans every time is no cache at all. The answer is invalidation: each plan remembers what it depends on, and a change to any of those drops the plan.

What a plan depends on

A plan depends on the tables it reads or changes, their indexes and their statistics. droid keeps it simple: each plan keeps the names of its tables. Any statement that changes the shape of one of them, or its statistics, drops every plan naming it, in every session. A session that dropped an index must not leave other sessions running plans that use it.

Dropping is cheap and immediate. Planning again is lazy: only a statement that is actually executed again pays for a new plan, and its client never notices. That is the "silently" in the contract. The EXECUTE that re-plans is not a hit, because it did the planner's work.

When planning again cannot work

Sometimes the new plan can't be made: the table was dropped. The EXECUTE fails with the same error a fresh statement would get (42P01), and the statement stays prepared. If someone creates the table again, the next EXECUTE plans against the new table and works. PostgreSQL behaves exactly this way.

Sometimes the new plan can be made but answers a different question. select * from t was prepared when t had two columns. After ADD COLUMN it has three. A driver that described the statement at Parse time still expects two. Rather than surprise it, the server refuses with 0A000 (PostgreSQL's message reads "cached plan must not change result type") on every EXECUTE until the client prepares the statement again.

An aside: generic and custom plans

A plan made at PREPARE has no values, so it is generic: where name = $1 is costed for "some value of name". If the table holds a million 'x' and one 'y', the best plan for 'y' (an index) is the worst for 'x' (a scan would be better). PostgreSQL therefore plans the first five EXECUTEs with their actual values, as custom plans, and keeps the generic one only if it costs about as much. droid always keeps the generic plan. It is predictable, and its weakness is the reason PostgreSQL has the option plan_cache_mode.

Output contract for this stage

These are the exact lines and codes the tests check. The full definition is on the output contract page, section 100.

What you should see when done

$droid --db s.db
Welcome to droid!
>prepare p as select id, name from t where name = $1;
Prepare statement executed successfully
>explain execute p ('bob');
[PLAN] execute p ('bob')
[PLAN]   └── Project (cols=[id, name])
[PLAN]       └── IndexOnlyScan (table=t, index=t_name, key=$1)
>drop index t_name;
Drop index statement executed successfully
>explain execute p ('bob');
[PLAN] execute p ('bob')
[PLAN]   └── Project (cols=[id, name])
[PLAN]       └── Filter (name = $1)
[PLAN]           └── SeqScan (table=t)
>plan cache
[PLANCACHE] p plans=2 hits=0 invalidations=1

(The node lines are whatever your planner prints for these plans in earlier parts; only the $1 is new. The index holds name and the table key id, everything the query reads, so the scan is index-only.)

Common bugs in this stage

• Invalidating only the session that ran the DDL. Other sessions keep plans that use the dropped index.
• Re-planning eagerly at the DDL. It works, but it plans statements that may never run again, and it fails loudly for a dropped table at the DROP, not at the EXECUTE.
• Counting a re-planning EXECUTE as a hit. It did the planner's work.
• Deallocating a statement whose table was dropped. It stays prepared; the table may come back.
• Letting a new plan return other columns. A driver described the statement once and holds the old shape.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: a list of every prepared statement of every session, with their table names, to walk at each DDL. A NULL plan pointer is "invalidated".
• C++: std::optional<Plan> in the prepared statement; a std::unordered_multimap<std::string, Prepared*> from table name to statements makes invalidation a lookup.
• Rust: Option<Plan> in the prepared statement; plan.take() to invalidate.
• Zig: ?Plan; free the old plan's memory when you drop it.