Re: PREPARE/EXECUTE across backends?

On Wed, 2003-10-01 at 20:25, Jingren Zhou wrote:
> From the document, it seems that PREPARE/EXECUTE works only in the same
> session. I am wondering whether postgres can prepare a query (save the plan)
> for difference backends.

The decision to store prepared statements per-backend, rather than in
shared memory, was made deliberately. In fact, an early version of the
PREPARE/EXECUTE patch (written by Karel Zak) stored prepared statements
in shared memory. But I decided to remove this, because:

- it is more complex

- since shared memory must be allocated statically on postmaster
startup, it would make prepared statements more fragile: at some point
we would run out of room in shm, and need to either remove prepared
statements, or swap them out to disk

- it would encourage poor application design, since it wouldn't be
trivial to tell whether a given prepared query has already been prepared
by a different backend, and what name it is using

- the performance gains are not that dramatic: preparing a statement
once per active backend is not that expensive. In most of the cases
where prepared statements are useful, since the # of backends is usually
far smaller than the # of times you're executing a given prepared
statement

That's all the reasons I can think of off the top of my head for doing
things the way we do. However, I'm open to being convinced: if you think
we should store prepared statements in shm, feel free to make a case for
it.

-Neil