Thread: PREPARE/EXECUTE across backends?
Hi, 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. I am working on a project which requires executing "psql -c 'query'" in command line multiple times. Since the performance is critical, it would be nice to prepare the same query first to avoid being parsed/optimized each time. But psql opens a new backend each time, it looks like that PREPARE/EXECUTE doesn't work. Is there any workaround? Thanks _________________________________________________________________ Instant message with integrated webcam using MSN Messenger 6.0. Try it now FREE! http://msnmessenger-download.com
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
On Wed, 1 Oct 2003, Jingren Zhou wrote: > Hi, > > >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. > > I am working on a project which requires executing "psql -c 'query'" in > command line multiple times. Since the performance is critical, it would be > nice to prepare the same query first to avoid being parsed/optimized each > time. But psql opens a new backend each time, it looks like that > PREPARE/EXECUTE doesn't work. Is there any workaround? Your real overhead here isn't from having to prepare the query each time, it's from having to start psql and open a new connection each time. Perhaps you need to rethink your design and go with something that will maintain a persistent connection. Kris Jurka
Neil Conway <neilc@samurai.com> writes:
> 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:
> [ several good reasons ]
Another issue is that we currently don't have a mechanism for flushing
query plans when they become obsolete (eg, an index is added or
removed). Locally-cached plans are relatively easy to refresh: just
start a fresh session. A shared plan cache would retain bogus plans
forever, short of a postmaster restart.
Obviously we need a mechanism for detecting and handling cached-plan
invalidations, and I hope someone will get around to that soon.
But we *cannot* consider a shared plan cache until that mechanism
exists.
If I recall correctly, Karel's original shared plan cache also triggered
a lot of concern about contention for the shared data structure ...
I'm not convinced that it would be a big bottleneck, but there's
definitely an issue to think about there ...
regards, tom lane
On Wed, 2003-10-01 at 22:43, Tom Lane wrote: > Another issue is that we currently don't have a mechanism for flushing > query plans when they become obsolete (eg, an index is added or > removed). Locally-cached plans are relatively easy to refresh: just > start a fresh session. A shared plan cache would retain bogus plans > forever, short of a postmaster restart. Well, keep in mind we already have DEALLOCATE for removing prepared statements, which would continue to be available if we switched to storing prepared statements in shared memory. However, using DEALLOCATE to get around invalid cached plans is obviously not a good solution. > Obviously we need a mechanism for detecting and handling cached-plan > invalidations, and I hope someone will get around to that soon. Agreed. > But we *cannot* consider a shared plan cache until that mechanism > exists. Given the presence of DEALLOCATE, I think this overstates the case somewhat: longer-lived prepared statements that are stored in shared memory makes handling invalidated plans more of an issue, of course. -Neil
On Wed, Oct 01, 2003 at 09:01:23PM -0400, Neil Conway wrote: > 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: > 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. I think the current non-shared PREPARE/EXECUTE is right solution. Theshared version require define new type of memory managment routineswhich is full compatible with the standard and abstract PostgreSQL mmgrtier. I worked on thisbecause it was interesting experiment and nowwe know that write something like this is possible :-) I think final and right solution are persisten pre-forked backendswhich know to keep cached PREPARE/EXECUTE stuff(and a lot of othersthings) in own memory. It's nice and simple solution than use sharedmemory. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/