Re: [HACKERS] PREPARE/EXECUTE across backends?
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. The shared version require define new type of memory managment routines which is full compatible with the standard and abstract PostgreSQL mmgr tier. I worked on this because it was interesting experiment and now we know that write something like this is possible :-) I think final and right solution are persisten pre-forked backends which know to keep cached PREPARE/EXECUTE stuff (and a lot of others things) in own memory. It's nice and simple solution than use shared memory. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PREPARE/EXECUTE across backends?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PREPARE/EXECUTE across backends?
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PREPARE/EXECUTE across backends?
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
