Add to PREPARE archive.

1 files changed, 1250 insertions, 0 deletions

diff --git a/doc/TODO.detail/prepare b/doc/TODO.detail/prepare
index 0ab2e4f256e..5d665126908 100644
--- a/doc/TODO.detail/prepare
+++ b/doc/TODO.detail/prepare
@@ -445,3 +445,1253 @@ query cache in shared memory...etc. Too much queries, but less answers :-)
 TIP 2: you can get off all lists at once with the unregister command
     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
 
+From pgsql-hackers-owner+M21218@postgresql.org Fri Apr 12 04:52:19 2002
+Return-path: <pgsql-hackers-owner+M21218@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3C8qIS25666

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 04:52:18 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id AE2FA4769F1; Fri, 12 Apr 2002 03:54:34 -0400 (EDT)

+Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])

+	by postgresql.org (Postfix) with ESMTP id A05A94769DC

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 03:51:27 -0400 (EDT)

+Received: from ara.zf.jcu.cz (LOCALHOST [127.0.0.1])

+	by ara.zf.jcu.cz (8.12.1/8.12.1/Debian -5) with ESMTP id g3C7pHBK012031;

+	Fri, 12 Apr 2002 09:51:17 +0200

+Received: (from zakkr@localhost)

+	by ara.zf.jcu.cz (8.12.1/8.12.1/Debian -5) id g3C7pGum012030;

+	Fri, 12 Apr 2002 09:51:16 +0200

+Date: Fri, 12 Apr 2002 09:51:16 +0200

+From: Karel Zak <zakkr@zf.jcu.cz>

+To: pgsql-hackers@postgresql.org

+cc: Hiroshi Inoue <Inoue@tpf.co.jp>

+Subject: Re: [HACKERS] 7.3 schedule

+Message-ID: <20020412095116.B6370@zf.jcu.cz>

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au> <3CB52C54.4020507@freaky-namuh.com> <20020411115434.201ff92f.nconway@klamath.dyndns.org> <3CB61DAB.5010601@freaky-namuh.com> <24184.1018581907@sss.pgh.pa.us> <3CB65B49.93F2F790@tpf.co.jp> <20020412004134.5d35a2dd.nconway@klamath.dyndns.org>

+MIME-Version: 1.0

+Content-Type: text/plain; charset=us-ascii

+Content-Disposition: inline

+User-Agent: Mutt/1.2.5i

+In-Reply-To: <20020412004134.5d35a2dd.nconway@klamath.dyndns.org>; from nconway@klamath.dyndns.org on Fri, Apr 12, 2002 at 12:41:34AM -0400

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+On Fri, Apr 12, 2002 at 12:41:34AM -0400, Neil Conway wrote:
+> On Fri, 12 Apr 2002 12:58:01 +0900
+> "Hiroshi Inoue" <Inoue@tpf.co.jp> wrote:
+> > 
+> > Just a confirmation.
+> > Someone is working on PREPARE/EXECUTE ?
+> > What about Karel's work ?
+
+ Right question :-)
+ 
+> I am. My work is based on Karel's stuff -- at the moment I'm still
+> basically working on getting Karel's patch to play nicely with
+> current sources; once that's done I'll be addressing whatever
+> issues are stopping the code from getting into CVS.
+
+ My patch (qcache) for PostgreSQL 7.0 is available at 
+ ftp://ftp2.zf.jcu.cz/users/zakkr/pg/.
+ 
+ I very look forward to Neil's work on this. 
+
+ Notes:
+
+    * It's experimental patch, but usable. All features below mentioned 
+      works.
+
+    * PREPARE/EXECUTE is not only SQL statements, I think good idea is
+      create something common and robus for query-plan caching,
+      beacuse there is for example SPI too. The RI triggers are based 
+      on SPI_saveplan(). 
+ 
+    * My patch knows EXECUTE INTO feature:
+
+ PREPARE foo AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text;
+
+ EXECUTE foo USING 'pg%';        <-- standard select
+
+ EXECUTE foo INTO TEMP newtab USING 'pg%';  <-- select into
+ 
+ 
+    * The patch allows store query-planns to shared memory and is
+      possible EXECUTE it at more backends (over same DB) and planns
+      are persistent across connetions. For this feature I create special 
+      memory context subsystem (like current aset.c, but it works with 
+      IPC shared memory).
+      
+      This is maybe too complex solution and (maybe) sufficient is cache 
+      query in one backend only. I know unbelief about this shared
+      memory solution (Tom?). 
+      
+    
+    Karel
+      
+      
+ My experimental patch README (excuse my English):
+
+ Implementation
+ ~~~~~~~~~~~~~~
+
+   The qCache allows save queryTree and queryPlan. There is available are 
+   two space for data caching. 
+  
+   LOCAL  - data are cached in backend non-shared memory and data aren't
+            available in other backends.                  
+  
+   SHARE  - data are cached in backend shared memory and data are 
+            visible in all backends.
+  
+   Because size of share memory pool is limited and it is set during
+   postmaster start up, the qCache must remove all old planns if pool is 
+   full. You can mark each entry as "REMOVEABLE" or "NOTREMOVEABLE". 
+  
+   A removeable entry is removed if pool is full.
+  
+   A not-removeable entry must be removed via qCache_Remove() or 
+   the other routines. The qCache not remove this entry itself.
+  
+   All records in qCache are cached (in the hash table) under some key.
+   The qCache knows two alternate of key --- "KEY_STRING" and "KEY_BINARY". 
+  
+   The qCache API not allows access to shared memory, all cached planns that 
+   API returns are copy to CurrentMemoryContext. All (qCache_ ) routines lock 
+   shmem itself (exception is qCache_RemoveOldest_ShareRemoveAble()).
+
+        - for locking is used spin lock.
+
+   Memory management
+   ~~~~~~~~~~~~~~~~~
+   The qCache use for qCache's shared pool its memory context independent on
+   standard aset/mcxt, but use compatible API --- it allows to use standard
+   palloc() (it is very needful for basic plan-tree operations, an example 
+   for copyObject()). The qCache memory management is very simular to current
+   aset.c code. It is chunk-ed blocks too, but the block is smaller - 1024b.
+
+   The number of blocks is available set in postmaster 'argv' via option
+   '-Z'.
+
+   For plan storing is used separate MemoryContext for each plan, it 
+   is good idea (Hiroshi's ?), bucause create new context is simple and 
+   inexpensive and allows easy destroy (free) cached plan. This method is 
+   used in my SPI overhaul instead TopMemoryContext feeding.
+
+   Postmaster
+   ~~~~~~~~~~
+   The query cache memory is init during potmaster startup. The size of
+   query cache pool is set via '-Z <number-of-blocks>' switch --- default 
+   is 100 blocks where 1 block = 1024b, it is sufficient for 20-30 cached
+   planns. One query needs somewhere 3-10 blocks, for example query like
+
+        PREPARE sel AS SELECT * FROM pg_class;
+
+   needs 10Kb, because table pg_class has very much columns. 
+ 
+   Note: for development I add SQL function: "SELECT qcache_state();",
+         this routine show usage of qCache.
+
+ SPI
+ ~~~
+    I a little overwrite SPI save plan method and remove TopMemoryContext
+    "feeding".
+
+    Standard SPI:
+
+        SPI_saveplan() - save each plan to separate standard memory context.
+
+        SPI_freeplan() - free plan.
+
+    By key SPI:
+
+        It is SPI interface for query cache and allows save planns to SHARED
+        or LOCAL cache 'by' arbitrary key (string or binary). Routines:
+
+        SPI_saveplan_bykey()    - save plan to query cache
+
+        SPI_freeplan_bykey()    - remove plan from query cache
+
+        SPI_fetchplan_bykey()   - fetch plan saved in query cache
+
+        SPI_execp_bykey()       - execute (via SPI) plan saved in query
+                                  cache 
+
+        - now, users can write functions that save planns to shared memory 
+        and planns are visible in all backend and are persistent arcoss 
+        connection. 
+
+        Example:
+        ~~~~~~~
+        /* ----------
+         * Save/exec query from shared cache via string key
+         * ----------
+         */
+        int     keySize = 0;        
+                flag = SPI_BYKEY_SHARE | SPI_BYKEY_STRING;
+        char    *key = "my unique key";
+        
+        res = SPI_execp_bykey(values, nulls, tcount, key, flag, keySize);
+        
+        if (res == SPI_ERROR_PLANNOTFOUND) 
+        {
+                /* --- not plan in cache - must create it --- */
+                
+                void *plan;
+
+                plan = SPI_prepare(querystr, valnum, valtypes);
+                SPI_saveplan_bykey(plan, key, keySize, flag);
+                
+                res = SPI_execute(plan, values, Nulls, tcount);
+        }
+        
+        elog(NOTICE, "Processed: %d", SPI_processed);
+
+
+ PREPARE/EXECUTE
+ ~~~~~~~~~~~~~~~
+   * Syntax:
+        
+        PREPARE <name> AS <query> 
+                [ USING type, ... typeN ] 
+                [ NOSHARE | SHARE | GLOBAL ]
+        
+        EXECUTE <name> 
+                [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
+                [ USING val, ... valN ]
+                       [ NOSHARE | SHARE | GLOBAL ]
+
+        DEALLOCATE PREPARE 
+                [ <name> [ NOSHARE | SHARE | GLOBAL ]]
+                [ ALL | ALL INTERNAL ]
+
+
+     I know that it is a little out of SQL92... (use CREATE/DROP PLAN instead
+     this?) --- what mean SQL standard guru?
+
+   * Where:
+ 
+        NOSHARE --- cached in local backend query cache - not accessable
+                    from the others backends and not is persisten a across
+                    conection.
+
+        SHARE   --- cached in shared query cache and accessable from
+                    all backends which work over same database.
+
+        GLOBAL  --- cached in shared query cache and accessable from
+                    all backends and all databases. 
+
+        - default is 'SHARE'
+   
+        Deallocate:
+                
+                ALL          --- deallocate all users's plans
+
+                ALL INTERNAL --- deallocate all internal plans, like planns
+                                 cached via SPI. It is needful if user
+                                 alter/drop table ...etc.
+
+   * Parameters:
+        
+        "USING" part in the prepare statement is for datetype setting for
+        paremeters in the query. For example:
+
+        PREPARE sel AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text;
+
+        EXECUTE sel USING 'pg%';
+        
+
+   * Limitation:
+        
+        - prepare/execute allow use full statement of SELECT/INSERT/DELETE/
+          UPDATE. 
+        - possible is use union, subselects, limit, ofset, select-into
+
+
+ Performance:
+ ~~~~~~~~~~~
+ * the SPI
+
+        - I for my tests a little change RI triggers to use SPI by_key API
+        and save planns to shared qCache instead to internal RI hash table.
+
+        The RI use very simple (for parsing) queries and qCache interest is 
+        not visible. It's better if backend very often startup and RI check 
+        always same tables. In this situation speed go up --- 10-12%. 
+        (This snapshot not include this RI change.)
+
+        But all depend on how much complicate for parser is query in 
+        trigger.
+
+ * PREPARE/EXECUTE
+        
+        - For tests I use query that not use some table (the executor is 
+        in boredom state), but is difficult for the parser. An example:
+
+        SELECT 'a text ' || (10*10+(100^2))::text || ' next text ' || cast 
+        (date_part('year', timestamp 'now') AS text );
+                  
+        - (10000 * this query):
+
+        standard select:        54 sec
+        via prepare/execute:     4 sec   (93% better)
+
+        IMHO it is nod bad.
+ 
+        - For standard query like:
+
+        SELECT u.usename, r.relname FROM pg_class r, pg_user u WHERE 
+        r.relowner = u.usesysid;
+
+        it is with PREPARE/EXECUTE 10-20% faster.
+
+-- 
+ Karel Zak  <zakkr@zf.jcu.cz>
+ http://home.zf.jcu.cz/~zakkr/
+ 
+ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
+
+---------------------------(end of broadcast)---------------------------
+TIP 4: Don't 'kill -9' the postmaster
+
+From pgsql-hackers-owner+M21228@postgresql.org Fri Apr 12 10:15:34 2002
+Return-path: <pgsql-hackers-owner+M21228@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CEFXS29835

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 10:15:33 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 7BFE1475A55; Fri, 12 Apr 2002 10:15:27 -0400 (EDT)

+Received: from sss.pgh.pa.us (unknown [192.204.191.242])

+	by postgresql.org (Postfix) with ESMTP id 5659B474E71

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 10:14:31 -0400 (EDT)

+Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])

+	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3CEEQF27238;

+	Fri, 12 Apr 2002 10:14:26 -0400 (EDT)

+To: Karel Zak <zakkr@zf.jcu.cz>

+cc: pgsql-hackers@postgresql.org, Neil Conway <nconway@klamath.dyndns.org>

+Subject: Re: [HACKERS] 7.3 schedule 

+In-Reply-To: <20020412095116.B6370@zf.jcu.cz> 

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au> <3CB52C54.4020507@freaky-namuh.com> <20020411115434.201ff92f.nconway@klamath.dyndns.org> <3CB61DAB.5010601@freaky-namuh.com> <24184.1018581907@sss.pgh.pa.us> <3CB65B49.93F2F790@tpf.co.jp> <20020412004134.5d35a2dd.nconway@klamath.dyndns.org> <20020412095116.B6370@zf.jcu.cz>

+Comments: In-reply-to Karel Zak <zakkr@zf.jcu.cz>

+	message dated "Fri, 12 Apr 2002 09:51:16 +0200"

+Date: Fri, 12 Apr 2002 10:14:26 -0400

+Message-ID: <27235.1018620866@sss.pgh.pa.us>

+From: Tom Lane <tgl@sss.pgh.pa.us>

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: ORr

+

+Karel Zak <zakkr@zf.jcu.cz> writes:
+>     * The patch allows store query-planns to shared memory and is
+>       possible EXECUTE it at more backends (over same DB) and planns
+>       are persistent across connetions. For this feature I create special 
+>       memory context subsystem (like current aset.c, but it works with 
+>       IPC shared memory).
+>       This is maybe too complex solution and (maybe) sufficient is cache 
+>       query in one backend only. I know unbelief about this shared
+>       memory solution (Tom?). 
+
+Yes, that is the part that was my sticking point last time around.
+(1) Because shared memory cannot be extended on-the-fly, I think it is
+a very bad idea to put data structures in there without some well
+thought out way of predicting/limiting their size.  (2) How the heck do
+you get rid of obsoleted cached plans, if the things stick around in
+shared memory even after you start a new backend?  (3) A shared cache
+requires locking; contention among multiple backends to access that
+shared resource could negate whatever performance benefit you might hope
+to realize from it.
+
+A per-backend cache kept in local memory avoids all of these problems,
+and I have seen no numbers to make me think that a shared plan cache
+would achieve significantly more performance benefit than a local one.
+
+			regards, tom lane
+
+---------------------------(end of broadcast)---------------------------
+TIP 5: Have you checked our extensive FAQ?
+
+http://www.postgresql.org/users-lounge/docs/faq.html
+
+From pgsql-hackers-owner+M21233@postgresql.org Fri Apr 12 12:26:32 2002
+Return-path: <pgsql-hackers-owner+M21233@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CGQVS11018

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 12:26:31 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 38DBB475B20; Fri, 12 Apr 2002 12:22:08 -0400 (EDT)

+Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])

+	by postgresql.org (Postfix) with ESMTP id 0DA70475B9E

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 12:21:15 -0400 (EDT)

+Received: (from pgman@localhost)

+	by candle.pha.pa.us (8.11.6/8.10.1) id g3CGL4310492;

+	Fri, 12 Apr 2002 12:21:04 -0400 (EDT)

+From: Bruce Momjian <pgman@candle.pha.pa.us>

+Message-ID: <200204121621.g3CGL4310492@candle.pha.pa.us>

+Subject: Re: [HACKERS] 7.3 schedule

+In-Reply-To: <27235.1018620866@sss.pgh.pa.us>

+To: Tom Lane <tgl@sss.pgh.pa.us>

+Date: Fri, 12 Apr 2002 12:21:04 -0400 (EDT)

+cc: Karel Zak <zakkr@zf.jcu.cz>, pgsql-hackers@postgresql.org,

+   Neil Conway <nconway@klamath.dyndns.org>

+X-Mailer: ELM [version 2.4ME+ PL97 (25)]

+MIME-Version: 1.0

+Content-Transfer-Encoding: 7bit

+Content-Type: text/plain; charset=US-ASCII

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+Tom Lane wrote:
+> Karel Zak <zakkr@zf.jcu.cz> writes:
+> >     * The patch allows store query-planns to shared memory and is
+> >       possible EXECUTE it at more backends (over same DB) and planns
+> >       are persistent across connetions. For this feature I create special 
+> >       memory context subsystem (like current aset.c, but it works with 
+> >       IPC shared memory).
+> >       This is maybe too complex solution and (maybe) sufficient is cache 
+> >       query in one backend only. I know unbelief about this shared
+> >       memory solution (Tom?). 
+> 
+> Yes, that is the part that was my sticking point last time around.
+> (1) Because shared memory cannot be extended on-the-fly, I think it is
+> a very bad idea to put data structures in there without some well
+> thought out way of predicting/limiting their size.  (2) How the heck do
+> you get rid of obsoleted cached plans, if the things stick around in
+> shared memory even after you start a new backend?  (3) A shared cache
+> requires locking; contention among multiple backends to access that
+> shared resource could negate whatever performance benefit you might hope
+> to realize from it.
+> 
+> A per-backend cache kept in local memory avoids all of these problems,
+> and I have seen no numbers to make me think that a shared plan cache
+> would achieve significantly more performance benefit than a local one.
+
+Certainly a shared cache would be good for apps that connect to issue a
+single query frequently.  In such cases, there would be no local cache
+to use.
+
+-- 
+  Bruce Momjian                        |  http://candle.pha.pa.us
+  pgman@candle.pha.pa.us               |  (610) 853-3000
+  +  If your life is a hard drive,     |  830 Blythe Avenue
+  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
+
+---------------------------(end of broadcast)---------------------------
+TIP 4: Don't 'kill -9' the postmaster
+
+From pgsql-hackers-owner+M21234@postgresql.org Fri Apr 12 12:44:12 2002
+Return-path: <pgsql-hackers-owner+M21234@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CGiBS12385

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 12:44:12 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id AEAA7475C6C; Fri, 12 Apr 2002 12:43:17 -0400 (EDT)

+Received: from barry.xythos.com (h-64-105-36-191.SNVACAID.covad.net [64.105.36.191])

+	by postgresql.org (Postfix) with ESMTP id CE58C47598E

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 12:42:48 -0400 (EDT)

+Received: from xythos.com (localhost.localdomain [127.0.0.1])

+	by barry.xythos.com (8.11.6/8.11.6) with ESMTP id g3CGgaI02920;

+	Fri, 12 Apr 2002 09:42:36 -0700

+Message-ID: <3CB70E7C.3090801@xythos.com>

+Date: Fri, 12 Apr 2002 09:42:36 -0700

+From: Barry Lind <barry@xythos.com>

+User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020310

+X-Accept-Language: en-us, en

+MIME-Version: 1.0

+To: Tom Lane <tgl@sss.pgh.pa.us>

+cc: Karel Zak <zakkr@zf.jcu.cz>, pgsql-hackers@postgresql.org,

+   Neil Conway <nconway@klamath.dyndns.org>

+Subject: Re: [HACKERS] 7.3 schedule

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au> <3CB52C54.4020507@freaky-namuh.com> <20020411115434.201ff92f.nconway@klamath.dyndns.org> <3CB61DAB.5010601@freaky-namuh.com> <24184.1018581907@sss.pgh.pa.us> <3CB65B49.93F2F790@tpf.co.jp> <20020412004134.5d35a2dd.nconway@klamath.dyndns.org> <20020412095116.B6370@zf.jcu.cz> <27235.1018620866@sss.pgh.pa.us>

+Content-Type: text/plain; charset=us-ascii; format=flowed

+Content-Transfer-Encoding: 7bit

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: ORr

+

+
+
+Tom Lane wrote:
+> Yes, that is the part that was my sticking point last time around.
+> (1) Because shared memory cannot be extended on-the-fly, I think it is
+> a very bad idea to put data structures in there without some well
+> thought out way of predicting/limiting their size.  (2) How the heck do
+> you get rid of obsoleted cached plans, if the things stick around in
+> shared memory even after you start a new backend?  (3) A shared cache
+> requires locking; contention among multiple backends to access that
+> shared resource could negate whatever performance benefit you might hope
+> to realize from it.
+> 
+> A per-backend cache kept in local memory avoids all of these problems,
+> and I have seen no numbers to make me think that a shared plan cache
+> would achieve significantly more performance benefit than a local one.
+> 
+
+Oracle's implementation is a shared cache for all plans.  This was 
+introduced in Oracle 6 or 7 (I don't remember which anymore).  The net 
+effect was that in general there was a significant performance 
+improvement with the shared cache.  However poorly written apps can now 
+bring the Oracle database to its knees because of the locking issues 
+associated with the shared cache.  For example if the most frequently 
+run sql statements are coded poorly (i.e. they don't use bind variables, 
+eg.  'select bar from foo where foobar = $1' vs. 'select bar from foo 
+where foobar =  || somevalue'  (where somevalue is likely to be 
+different on every call)) the shared cache doesn't help and its overhead 
+becomes significant.
+
+thanks,
+--Barry
+
+
+
+---------------------------(end of broadcast)---------------------------
+TIP 3: if posting/reading through Usenet, please send an appropriate
+subscribe-nomail command to majordomo@postgresql.org so that your
+message can get through to the mailing list cleanly
+
+From pgsql-hackers-owner+M21237@postgresql.org Fri Apr 12 12:50:28 2002
+Return-path: <pgsql-hackers-owner+M21237@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CGoRS13005

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 12:50:28 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 32A28475BA1; Fri, 12 Apr 2002 12:50:15 -0400 (EDT)

+Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])

+	by postgresql.org (Postfix) with ESMTP id 07F1E475892

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 12:49:43 -0400 (EDT)

+Received: (from pgman@localhost)

+	by candle.pha.pa.us (8.11.6/8.10.1) id g3CGnbw12950;

+	Fri, 12 Apr 2002 12:49:37 -0400 (EDT)

+From: Bruce Momjian <pgman@candle.pha.pa.us>

+Message-ID: <200204121649.g3CGnbw12950@candle.pha.pa.us>

+Subject: Re: [HACKERS] 7.3 schedule

+In-Reply-To: <3CB70E7C.3090801@xythos.com>

+To: Barry Lind <barry@xythos.com>

+Date: Fri, 12 Apr 2002 12:49:37 -0400 (EDT)

+cc: Tom Lane <tgl@sss.pgh.pa.us>, Karel Zak <zakkr@zf.jcu.cz>,

+   pgsql-hackers@postgresql.org, Neil Conway <nconway@klamath.dyndns.org>

+X-Mailer: ELM [version 2.4ME+ PL97 (25)]

+MIME-Version: 1.0

+Content-Transfer-Encoding: 7bit

+Content-Type: text/plain; charset=US-ASCII

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+Barry Lind wrote:
+> Oracle's implementation is a shared cache for all plans.  This was 
+> introduced in Oracle 6 or 7 (I don't remember which anymore).  The net 
+> effect was that in general there was a significant performance 
+> improvement with the shared cache.  However poorly written apps can now 
+> bring the Oracle database to its knees because of the locking issues 
+> associated with the shared cache.  For example if the most frequently 
+> run sql statements are coded poorly (i.e. they don't use bind variables, 
+> eg.  'select bar from foo where foobar = $1' vs. 'select bar from foo 
+> where foobar =  || somevalue'  (where somevalue is likely to be 
+> different on every call)) the shared cache doesn't help and its overhead 
+> becomes significant.
+
+This is very interesting.  We have always been concerned that shared
+cache invalidation could cause more of a performance problem that the
+shared cache gives benefit, and it sounds like you are saying exactly
+that.
+
+-- 
+  Bruce Momjian                        |  http://candle.pha.pa.us
+  pgman@candle.pha.pa.us               |  (610) 853-3000
+  +  If your life is a hard drive,     |  830 Blythe Avenue
+  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
+
+---------------------------(end of broadcast)---------------------------
+TIP 2: you can get off all lists at once with the unregister command
+    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
+
+From pgsql-hackers-owner+M21238@postgresql.org Fri Apr 12 12:51:55 2002
+Return-path: <pgsql-hackers-owner+M21238@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CGptS13119

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 12:51:55 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id C599D475BC6; Fri, 12 Apr 2002 12:51:47 -0400 (EDT)

+Received: from sss.pgh.pa.us (unknown [192.204.191.242])

+	by postgresql.org (Postfix) with ESMTP id C9F94475892

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 12:51:26 -0400 (EDT)

+Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])

+	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3CGpQF27967;

+	Fri, 12 Apr 2002 12:51:27 -0400 (EDT)

+To: Bruce Momjian <pgman@candle.pha.pa.us>

+cc: Karel Zak <zakkr@zf.jcu.cz>, pgsql-hackers@postgresql.org,

+   Neil Conway <nconway@klamath.dyndns.org>

+Subject: Re: [HACKERS] 7.3 schedule 

+In-Reply-To: <200204121621.g3CGL4310492@candle.pha.pa.us> 

+References: <200204121621.g3CGL4310492@candle.pha.pa.us>

+Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>

+	message dated "Fri, 12 Apr 2002 12:21:04 -0400"

+Date: Fri, 12 Apr 2002 12:51:26 -0400

+Message-ID: <27964.1018630286@sss.pgh.pa.us>

+From: Tom Lane <tgl@sss.pgh.pa.us>

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+Bruce Momjian <pgman@candle.pha.pa.us> writes:
+> Certainly a shared cache would be good for apps that connect to issue a
+> single query frequently.  In such cases, there would be no local cache
+> to use.
+
+We have enough other problems with the single-query-per-connection
+scenario that I see no reason to believe that a shared plan cache will
+help materially.  The correct answer for those folks will *always* be
+to find a way to reuse the connection.
+
+			regards, tom lane
+
+---------------------------(end of broadcast)---------------------------
+TIP 4: Don't 'kill -9' the postmaster
+
+From pgsql-hackers-owner+M21241@postgresql.org Fri Apr 12 16:25:46 2002
+Return-path: <pgsql-hackers-owner+M21241@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CKPkS03078

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 16:25:46 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 9C3BD475CC6; Fri, 12 Apr 2002 16:25:42 -0400 (EDT)

+Received: from klamath.dyndns.org (CPE002078144ae0.cpe.net.cable.rogers.com [24.102.202.35])

+	by postgresql.org (Postfix) with ESMTP id B06D8475909

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 16:24:52 -0400 (EDT)

+Received: from jiro (jiro [192.168.40.7])

+	by klamath.dyndns.org (Postfix) with SMTP

+	id C05557013; Fri, 12 Apr 2002 16:24:53 -0400 (EDT)

+Date: Fri, 12 Apr 2002 16:24:48 -0400

+From: Neil Conway <nconway@klamath.dyndns.org>

+To: "Bruce Momjian" <pgman@candle.pha.pa.us>

+cc: tgl@sss.pgh.pa.us, zakkr@zf.jcu.cz, pgsql-hackers@postgresql.org

+Subject: Re: [HACKERS] 7.3 schedule

+Message-ID: <20020412162448.4d46d747.nconway@klamath.dyndns.org>

+In-Reply-To: <200204121621.g3CGL4310492@candle.pha.pa.us>

+References: <27235.1018620866@sss.pgh.pa.us>

+	<200204121621.g3CGL4310492@candle.pha.pa.us>

+X-Mailer: Sylpheed version 0.7.4 (GTK+ 1.2.10; i386-debian-linux-gnu)

+MIME-Version: 1.0

+Content-Type: text/plain; charset=US-ASCII

+Content-Transfer-Encoding: 7bit

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: ORr

+

+On Fri, 12 Apr 2002 12:21:04 -0400 (EDT)
+"Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
+> Tom Lane wrote:
+> > A per-backend cache kept in local memory avoids all of these problems,
+> > and I have seen no numbers to make me think that a shared plan cache
+> > would achieve significantly more performance benefit than a local one.
+> 
+> Certainly a shared cache would be good for apps that connect to issue a
+> single query frequently.  In such cases, there would be no local cache
+> to use.
+
+One problem with this kind of scenario is: what to do if the plan no
+longer exists for some reason? (e.g. the code that was supposed to be
+PREPARE-ing your statements failed to execute properly, or the cached
+plan has been evicted from shared memory, or the database was restarted,
+etc.) -- EXECUTE in and of itself won't have enough information to do
+anything useful. We could perhaps provide a means for an application
+to test for the existence of a cached plan (in which case the
+application developer will need to add logic to their application
+to re-prepare the query if necessary, which could get complicated).
+
+Cheers,
+
+Neil
+
+-- 
+Neil Conway <neilconway@rogers.com>
+PGP Key ID: DB3C29FC
+
+---------------------------(end of broadcast)---------------------------
+TIP 5: Have you checked our extensive FAQ?
+
+http://www.postgresql.org/users-lounge/docs/faq.html
+
+From pgsql-hackers-owner+M21242@postgresql.org Fri Apr 12 17:27:24 2002
+Return-path: <pgsql-hackers-owner+M21242@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CLRNS14410

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 17:27:23 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id E05A1475D30; Fri, 12 Apr 2002 17:26:40 -0400 (EDT)

+Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])

+	by postgresql.org (Postfix) with ESMTP id 36BBB475858

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 17:25:44 -0400 (EDT)

+Received: (from pgman@localhost)

+	by candle.pha.pa.us (8.11.6/8.10.1) id g3CLPVa14231;

+	Fri, 12 Apr 2002 17:25:31 -0400 (EDT)

+From: Bruce Momjian <pgman@candle.pha.pa.us>

+Message-ID: <200204122125.g3CLPVa14231@candle.pha.pa.us>

+Subject: Re: [HACKERS] 7.3 schedule

+In-Reply-To: <20020412162448.4d46d747.nconway@klamath.dyndns.org>

+To: Neil Conway <nconway@klamath.dyndns.org>

+Date: Fri, 12 Apr 2002 17:25:31 -0400 (EDT)

+cc: tgl@sss.pgh.pa.us, zakkr@zf.jcu.cz, pgsql-hackers@postgresql.org

+X-Mailer: ELM [version 2.4ME+ PL97 (25)]

+MIME-Version: 1.0

+Content-Transfer-Encoding: 7bit

+Content-Type: text/plain; charset=US-ASCII

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+Neil Conway wrote:
+> On Fri, 12 Apr 2002 12:21:04 -0400 (EDT)
+> "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
+> > Tom Lane wrote:
+> > > A per-backend cache kept in local memory avoids all of these problems,
+> > > and I have seen no numbers to make me think that a shared plan cache
+> > > would achieve significantly more performance benefit than a local one.
+> > 
+> > Certainly a shared cache would be good for apps that connect to issue a
+> > single query frequently.  In such cases, there would be no local cache
+> > to use.
+> 
+> One problem with this kind of scenario is: what to do if the plan no
+> longer exists for some reason? (e.g. the code that was supposed to be
+> PREPARE-ing your statements failed to execute properly, or the cached
+> plan has been evicted from shared memory, or the database was restarted,
+> etc.) -- EXECUTE in and of itself won't have enough information to do
+> anything useful. We could perhaps provide a means for an application
+> to test for the existence of a cached plan (in which case the
+> application developer will need to add logic to their application
+> to re-prepare the query if necessary, which could get complicated).
+
+Oh, are you thinking that one backend would do the PREPARE and another
+one the EXECUTE?  I can't see that working at all.  I thought there
+would some way to quickly test if the submitted query was in the cache,
+but maybe that is too much of a performance penalty to be worth it.
+
+-- 
+  Bruce Momjian                        |  http://candle.pha.pa.us
+  pgman@candle.pha.pa.us               |  (610) 853-3000
+  +  If your life is a hard drive,     |  830 Blythe Avenue
+  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
+
+---------------------------(end of broadcast)---------------------------
+TIP 3: if posting/reading through Usenet, please send an appropriate
+subscribe-nomail command to majordomo@postgresql.org so that your
+message can get through to the mailing list cleanly
+
+From tgl@sss.pgh.pa.us Fri Apr 12 17:36:17 2002
+Return-path: <tgl@sss.pgh.pa.us>

+Received: from sss.pgh.pa.us (root@[192.204.191.242])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CLaGS16061

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 17:36:17 -0400 (EDT)

+Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])

+	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3CLaGF10813;

+	Fri, 12 Apr 2002 17:36:16 -0400 (EDT)

+To: Bruce Momjian <pgman@candle.pha.pa.us>

+cc: Neil Conway <nconway@klamath.dyndns.org>, zakkr@zf.jcu.cz,

+   pgsql-hackers@postgresql.org

+Subject: Re: [HACKERS] 7.3 schedule 

+In-Reply-To: <200204122125.g3CLPVa14231@candle.pha.pa.us> 

+References: <200204122125.g3CLPVa14231@candle.pha.pa.us>

+Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>

+	message dated "Fri, 12 Apr 2002 17:25:31 -0400"

+Date: Fri, 12 Apr 2002 17:36:16 -0400

+Message-ID: <10810.1018647376@sss.pgh.pa.us>

+From: Tom Lane <tgl@sss.pgh.pa.us>

+Status: ORr

+

+Bruce Momjian <pgman@candle.pha.pa.us> writes:
+> Oh, are you thinking that one backend would do the PREPARE and another
+> one the EXECUTE?  I can't see that working at all.
+
+Uh, why exactly were you advocating a shared cache then?  Wouldn't that
+be exactly the *point* of a shared cache?
+
+			regards, tom lane
+
+From pgsql-hackers-owner+M21245@postgresql.org Fri Apr 12 17:39:13 2002
+Return-path: <pgsql-hackers-owner+M21245@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CLdCS16515

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 17:39:12 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id A904B475E15; Fri, 12 Apr 2002 17:39:09 -0400 (EDT)

+Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])

+	by postgresql.org (Postfix) with ESMTP id B1A3F4758DE

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 17:38:25 -0400 (EDT)

+Received: (from pgman@localhost)

+	by candle.pha.pa.us (8.11.6/8.10.1) id g3CLcFX16347;

+	Fri, 12 Apr 2002 17:38:15 -0400 (EDT)

+From: Bruce Momjian <pgman@candle.pha.pa.us>

+Message-ID: <200204122138.g3CLcFX16347@candle.pha.pa.us>

+Subject: Re: [HACKERS] 7.3 schedule

+In-Reply-To: <10810.1018647376@sss.pgh.pa.us>

+To: Tom Lane <tgl@sss.pgh.pa.us>

+Date: Fri, 12 Apr 2002 17:38:15 -0400 (EDT)

+cc: Neil Conway <nconway@klamath.dyndns.org>, zakkr@zf.jcu.cz,

+   pgsql-hackers@postgresql.org

+X-Mailer: ELM [version 2.4ME+ PL97 (25)]

+MIME-Version: 1.0

+Content-Transfer-Encoding: 7bit

+Content-Type: text/plain; charset=US-ASCII

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+Tom Lane wrote:
+> Bruce Momjian <pgman@candle.pha.pa.us> writes:
+> > Oh, are you thinking that one backend would do the PREPARE and another
+> > one the EXECUTE?  I can't see that working at all.
+> 
+> Uh, why exactly were you advocating a shared cache then?  Wouldn't that
+> be exactly the *point* of a shared cache?
+
+I thought it would somehow compare the SQL query string to the cached
+plans and if it matched, it would use that plan rather than make a new
+one.  Any DDL statement would flush the cache.
+
+-- 
+  Bruce Momjian                        |  http://candle.pha.pa.us
+  pgman@candle.pha.pa.us               |  (610) 853-3000
+  +  If your life is a hard drive,     |  830 Blythe Avenue
+  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
+
+---------------------------(end of broadcast)---------------------------
+TIP 5: Have you checked our extensive FAQ?
+
+http://www.postgresql.org/users-lounge/docs/faq.html
+
+From pgsql-hackers-owner+M21246@postgresql.org Fri Apr 12 17:56:58 2002
+Return-path: <pgsql-hackers-owner+M21246@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3CLuvS19021

+	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 17:56:58 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 1B4D6475E2C; Fri, 12 Apr 2002 17:56:55 -0400 (EDT)

+Received: from voyager.corporate.connx.com (unknown [209.20.248.131])

+	by postgresql.org (Postfix) with ESMTP id 059F1475858

+	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 17:56:13 -0400 (EDT)

+X-MimeOLE: Produced By Microsoft Exchange V6.0.4712.0

+content-class: urn:content-classes:message

+MIME-Version: 1.0

+Content-Type: text/plain;

+	charset="iso-8859-1"

+Subject: Re: [HACKERS] 7.3 schedule

+Date: Fri, 12 Apr 2002 14:59:15 -0700

+Message-ID: <D90A5A6C612A39408103E6ECDD77B82906F42C@voyager.corporate.connx.com>

+Thread-Topic: [HACKERS] 7.3 schedule

+Thread-Index: AcHia2aODSpgXEd4Tluz/N0jN5fJOQAAC//w

+From: "Dann Corbit" <DCorbit@connx.com>

+To: "Bruce Momjian" <pgman@candle.pha.pa.us>, "Tom Lane" <tgl@sss.pgh.pa.us>

+cc: "Neil Conway" <nconway@klamath.dyndns.org>, <zakkr@zf.jcu.cz>,

+   <pgsql-hackers@postgresql.org>

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Content-Transfer-Encoding: 8bit

+X-MIME-Autoconverted: from quoted-printable to 8bit by candle.pha.pa.us id g3CLuvS19021

+Status: OR

+

+-----Original Message-----
+From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
+Sent: Friday, April 12, 2002 2:38 PM
+To: Tom Lane
+Cc: Neil Conway; zakkr@zf.jcu.cz; pgsql-hackers@postgresql.org
+Subject: Re: [HACKERS] 7.3 schedule
+
+
+Tom Lane wrote:
+> Bruce Momjian <pgman@candle.pha.pa.us> writes:
+> > Oh, are you thinking that one backend would do the PREPARE and
+another
+> > one the EXECUTE?  I can't see that working at all.
+> 
+> Uh, why exactly were you advocating a shared cache then?  Wouldn't
+that
+> be exactly the *point* of a shared cache?
+
+I thought it would somehow compare the SQL query string to the cached
+plans and if it matched, it would use that plan rather than make a new
+one.  Any DDL statement would flush the cache.
+>>-------------------------------------------------------------------
+Many applications will have similar queries coming from lots of
+different end-users.  Imagine an order-entry program where people are
+ordering parts.  Many of the queries might look like this:
+
+SELECT part_number FROM parts WHERE part_id = 12324 AND part_cost
+< 12.95
+
+In order to cache this query, we first parse it to replace the data
+fields with paramter markers.
+Then it looks like this:
+SELECT part_number FROM parts WHERE part_id = ? AND part_cost < ?
+{in the case of a 'LIKE' query or some other query where you can use
+key information, you might have a symbolic replacement like this:
+WHERE field LIKE '{D}%' to indicate that the key can be used}
+Then, we make sure that the case is consistent by either capitalizing
+the whole query or changing it all into lower case:
+select part_number from parts where part_id = ? and part_cost < ?
+Then, we run a checksum on the parameterized string.
+The checksum might be used as a hash table key, where we keep some
+additional information like how stale the entry is, and a pointer to
+the actual parameterized SQL (in case the hash key has a collision
+it would be simply wrong to run an incorrect query for obvious enough
+reasons).
+Now, if there are a huge number of users of the same application, it 
+makes sense that the probabilities of reusing queries goes up with
+the number of users of the same application.  Therefore, I would 
+advocate that the cache be kept in shared memory.
+
+Consider a single application with 100 different queries.  Now, add
+one user, ten users, 100 users, ... 10,000 users and you can see
+that the benefit would be greater and greater as we add users.
+<<-------------------------------------------------------------------
+
+---------------------------(end of broadcast)---------------------------
+TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
+
+From pgsql-hackers-owner+M21270@postgresql.org Sat Apr 13 02:30:47 2002
+Return-path: <pgsql-hackers-owner+M21270@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3D6UkS07169

+	for <pgman@candle.pha.pa.us>; Sat, 13 Apr 2002 02:30:46 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 23FEC475D1E; Sat, 13 Apr 2002 02:30:38 -0400 (EDT)

+Received: from mail.iinet.net.au (symphony-01.iinet.net.au [203.59.3.33])

+	by postgresql.org (Postfix) with SMTP id A08A4475C6C

+	for <pgsql-hackers@postgresql.org>; Sat, 13 Apr 2002 02:29:37 -0400 (EDT)

+Received: (qmail 11594 invoked by uid 666); 13 Apr 2002 06:29:36 -0000

+Received: from unknown (HELO SOL) (203.59.103.193)

+  by mail.iinet.net.au with SMTP; 13 Apr 2002 06:29:36 -0000

+Message-ID: <002301c1e2b3$804bd000$0200a8c0@SOL>

+From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>

+To: "Barry Lind" <barry@xythos.com>, "Tom Lane" <tgl@sss.pgh.pa.us>

+cc: "Karel Zak" <zakkr@zf.jcu.cz>, <pgsql-hackers@postgresql.org>,

+   "Neil Conway" <nconway@klamath.dyndns.org>

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au> <3CB52C54.4020507@freaky-namuh.com> <20020411115434.201ff92f.nconway@klamath.dyndns.org> <3CB61DAB.5010601@freaky-namuh.com> <24184.1018581907@sss.pgh.pa.us> <3CB65B49.93F2F790@tpf.co.jp> <20020412004134.5d35a2dd.nconway@klamath.dyndns.org> <20020412095116.B6370@zf.jcu.cz> <27235.1018620866@sss.pgh.pa.us> <3CB70E7C.3090801@xythos.com>

+Subject: Re: [HACKERS] 7.3 schedule

+Date: Sat, 13 Apr 2002 14:21:50 +0800

+MIME-Version: 1.0

+Content-Type: text/plain;

+	charset="iso-8859-1"

+Content-Transfer-Encoding: 7bit

+X-Priority: 3

+X-MSMail-Priority: Normal

+X-Mailer: Microsoft Outlook Express 5.50.4522.1200

+X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+> > thought out way of predicting/limiting their size.  (2) How the heck do
+> > you get rid of obsoleted cached plans, if the things stick around in
+> > shared memory even after you start a new backend?  (3) A shared cache
+> > requires locking; contention among multiple backends to access that
+> > shared resource could negate whatever performance benefit you might hope
+> > to realize from it.
+
+I don't understand all these locking problems?  Surely the only lock a
+transaction would need on a stored query is one that prevents the cache
+invalidation mechanism from deleting it out from under it?  Surely this
+means that there would be tonnes of readers on the cache - none of them
+blocking each other, and the odd invalidation event that needs a complete
+lock?
+
+Also, as for invalidation, there probably could be just two reasons to
+invalidate a query in the cache.  (1)  The cache is running out of space and
+you use LRU or something to remove old queries, or (2) someone runs ANALYZE,
+in which case all cached queries should just be flushed?  If they specify an
+actual table to analyze, then just drop all queries on the table.
+
+Could this cache mechanism be used to make views fast as well?  You could
+cache the queries that back views on first use, and then they can follow the
+above rules for flushing...
+
+Chris
+
+
+
+---------------------------(end of broadcast)---------------------------
+TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
+
+From pgsql-hackers-owner+M21276@postgresql.org Sat Apr 13 11:48:51 2002
+Return-path: <pgsql-hackers-owner+M21276@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3DFmoS27879

+	for <pgman@candle.pha.pa.us>; Sat, 13 Apr 2002 11:48:51 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 9EB81475C5C; Sat, 13 Apr 2002 11:46:52 -0400 (EDT)

+Received: from sss.pgh.pa.us (unknown [192.204.191.242])

+	by postgresql.org (Postfix) with ESMTP id 0FE0B474E78

+	for <pgsql-hackers@postgresql.org>; Sat, 13 Apr 2002 11:46:09 -0400 (EDT)

+Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])

+	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3DFk2F15743;

+	Sat, 13 Apr 2002 11:46:02 -0400 (EDT)

+To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>

+cc: "Barry Lind" <barry@xythos.com>, "Karel Zak" <zakkr@zf.jcu.cz>,

+   pgsql-hackers@postgresql.org, "Neil Conway" <nconway@klamath.dyndns.org>

+Subject: Re: [HACKERS] 7.3 schedule 

+In-Reply-To: <002301c1e2b3$804bd000$0200a8c0@SOL> 

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au> <3CB52C54.4020507@freaky-namuh.com> <20020411115434.201ff92f.nconway@klamath.dyndns.org> <3CB61DAB.5010601@freaky-namuh.com> <24184.1018581907@sss.pgh.pa.us> <3CB65B49.93F2F790@tpf.co.jp> <20020412004134.5d35a2dd.nconway@klamath.dyndns.org> <20020412095116.B6370@zf.jcu.cz> <27235.1018620866@sss.pgh.pa.us> <3CB70E7C.3090801@xythos.com> <002301c1e2b3$804bd000$0200a8c0@SOL>

+Comments: In-reply-to "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>

+	message dated "Sat, 13 Apr 2002 14:21:50 +0800"

+Date: Sat, 13 Apr 2002 11:46:01 -0400

+Message-ID: <15740.1018712761@sss.pgh.pa.us>

+From: Tom Lane <tgl@sss.pgh.pa.us>

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
+> thought out way of predicting/limiting their size.  (2) How the heck do
+> you get rid of obsoleted cached plans, if the things stick around in
+> shared memory even after you start a new backend?  (3) A shared cache
+> requires locking; contention among multiple backends to access that
+> shared resource could negate whatever performance benefit you might hope
+> to realize from it.
+
+> I don't understand all these locking problems?
+
+Searching the cache and inserting/deleting entries in the cache probably
+have to be mutually exclusive; concurrent insertions probably won't work
+either (at least not without a remarkably intelligent data structure).
+Unless the cache hit rate is remarkably high, there are going to be lots
+of insertions --- and, at steady state, an equal rate of deletions ---
+leading to lots of contention.
+
+This could possibly be avoided if the cache is not used for all query
+plans but only for explicitly PREPAREd plans, so that only explicit
+EXECUTEs would need to search it.  But that approach also makes a
+sizable dent in the usefulness of the cache to begin with.
+
+			regards, tom lane
+
+---------------------------(end of broadcast)---------------------------
+TIP 2: you can get off all lists at once with the unregister command
+    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
+
+From pgsql-hackers-owner+M21280@postgresql.org Sat Apr 13 14:36:34 2002
+Return-path: <pgsql-hackers-owner+M21280@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3DIaYS10293

+	for <pgman@candle.pha.pa.us>; Sat, 13 Apr 2002 14:36:34 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id AA151475BB1; Sat, 13 Apr 2002 14:36:17 -0400 (EDT)

+Received: from klamath.dyndns.org (CPE002078144ae0.cpe.net.cable.rogers.com [24.102.202.35])

+	by postgresql.org (Postfix) with ESMTP id 42993475BCB

+	for <pgsql-hackers@postgresql.org>; Sat, 13 Apr 2002 14:35:42 -0400 (EDT)

+Received: from jiro (jiro [192.168.40.7])

+	by klamath.dyndns.org (Postfix) with SMTP

+	id 82B84700C; Sat, 13 Apr 2002 14:35:42 -0400 (EDT)

+Date: Sat, 13 Apr 2002 14:35:39 -0400

+From: Neil Conway <nconway@klamath.dyndns.org>

+To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>

+cc: barry@xythos.com, tgl@sss.pgh.pa.us, zakkr@zf.jcu.cz,

+   pgsql-hackers@postgresql.org

+Subject: Re: [HACKERS] 7.3 schedule

+Message-ID: <20020413143539.7818bf7d.nconway@klamath.dyndns.org>

+In-Reply-To: <002301c1e2b3$804bd000$0200a8c0@SOL>

+References: <GNELIHDDFBOCMGBFGEFOGEBHCCAA.chriskl@familyhealth.com.au>

+	<3CB52C54.4020507@freaky-namuh.com>

+	<20020411115434.201ff92f.nconway@klamath.dyndns.org>

+	<3CB61DAB.5010601@freaky-namuh.com>

+	<24184.1018581907@sss.pgh.pa.us>

+	<3CB65B49.93F2F790@tpf.co.jp>

+	<20020412004134.5d35a2dd.nconway@klamath.dyndns.org>

+	<20020412095116.B6370@zf.jcu.cz>

+	<27235.1018620866@sss.pgh.pa.us>

+	<3CB70E7C.3090801@xythos.com>

+	<002301c1e2b3$804bd000$0200a8c0@SOL>

+X-Mailer: Sylpheed version 0.7.4 (GTK+ 1.2.10; i386-debian-linux-gnu)

+MIME-Version: 1.0

+Content-Type: text/plain; charset=US-ASCII

+Content-Transfer-Encoding: 7bit

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+On Sat, 13 Apr 2002 14:21:50 +0800
+"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> wrote:
+> Could this cache mechanism be used to make views fast as well?
+
+The current PREPARE/EXECUTE code will speed up queries that use
+rules of any kind, including views: the query plan is cached after
+it has been rewritten as necessary, so (AFAIK) this should mean
+that rules will be evaluated once when the query is PREPAREd, and
+then cached for subsequent EXECUTE commands.
+
+Cheers,
+
+Neil
+
+-- 
+Neil Conway <neilconway@rogers.com>
+PGP Key ID: DB3C29FC
+
+---------------------------(end of broadcast)---------------------------
+TIP 2: you can get off all lists at once with the unregister command
+    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
+
+From pgsql-hackers-owner+M21309@postgresql.org Sun Apr 14 15:22:44 2002
+Return-path: <pgsql-hackers-owner+M21309@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3EJMiS24239

+	for <pgman@candle.pha.pa.us>; Sun, 14 Apr 2002 15:22:44 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 44BAC475E05; Sun, 14 Apr 2002 15:22:42 -0400 (EDT)

+Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])

+	by postgresql.org (Postfix) with ESMTP id 3CD03475925

+	for <pgsql-hackers@postgresql.org>; Sun, 14 Apr 2002 15:21:58 -0400 (EDT)

+Received: from ara.zf.jcu.cz (LOCALHOST [127.0.0.1])

+	by ara.zf.jcu.cz (8.12.1/8.12.1/Debian -5) with ESMTP id g3EJLiBK012612;

+	Sun, 14 Apr 2002 21:21:44 +0200

+Received: (from zakkr@localhost)

+	by ara.zf.jcu.cz (8.12.1/8.12.1/Debian -5) id g3EJLi3k012611;

+	Sun, 14 Apr 2002 21:21:44 +0200

+Date: Sun, 14 Apr 2002 21:21:44 +0200

+From: Karel Zak <zakkr@zf.jcu.cz>

+To: Tom Lane <tgl@sss.pgh.pa.us>

+cc: Bruce Momjian <pgman@candle.pha.pa.us>, pgsql-hackers@postgresql.org,

+   Neil Conway <nconway@klamath.dyndns.org>

+Subject: Re: [HACKERS] 7.3 schedule

+Message-ID: <20020414212144.A12196@zf.jcu.cz>

+References: <200204121621.g3CGL4310492@candle.pha.pa.us> <27964.1018630286@sss.pgh.pa.us>

+MIME-Version: 1.0

+Content-Type: text/plain; charset=us-ascii

+Content-Disposition: inline

+User-Agent: Mutt/1.2.5i

+In-Reply-To: <27964.1018630286@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Fri, Apr 12, 2002 at 12:51:26PM -0400

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+On Fri, Apr 12, 2002 at 12:51:26PM -0400, Tom Lane wrote:
+> Bruce Momjian <pgman@candle.pha.pa.us> writes:
+> > Certainly a shared cache would be good for apps that connect to issue a
+> > single query frequently.  In such cases, there would be no local cache
+> > to use.
+> 
+> We have enough other problems with the single-query-per-connection
+> scenario that I see no reason to believe that a shared plan cache will
+> help materially.  The correct answer for those folks will *always* be
+> to find a way to reuse the connection.
+
+ My query cache was write for 7.0. If some next release will use
+ pre-forked backend and after a client disconnection the backend will 
+ still alives and waits for new client the shared cache is (maybe:-) not
+ needful. The current backend fork model is killer of all possible 
+ caching.
+
+ We have more caches. I hope persistent backend help will help to all 
+ and I'm sure that speed will grow up with persistent backend and 
+ persistent caches without shared memory usage. There I can agree with
+ Tom :-)
+
+    Karel
+
+-- 
+ Karel Zak  <zakkr@zf.jcu.cz>
+ http://home.zf.jcu.cz/~zakkr/
+ 
+ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
+
+---------------------------(end of broadcast)---------------------------
+TIP 4: Don't 'kill -9' the postmaster
+
+From pgsql-hackers-owner+M21321@postgresql.org Sun Apr 14 20:40:08 2002
+Return-path: <pgsql-hackers-owner+M21321@postgresql.org>

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3F0e7S29723

+	for <pgman@candle.pha.pa.us>; Sun, 14 Apr 2002 20:40:07 -0400 (EDT)

+Received: from postgresql.org (postgresql.org [64.49.215.8])

+	by postgresql.org (Postfix) with SMTP

+	id 3B5FB475DC5; Sun, 14 Apr 2002 20:40:03 -0400 (EDT)

+Received: from localhost.localdomain (bgp01077650bgs.wanarb01.mi.comcast.net [68.40.135.112])

+	by postgresql.org (Postfix) with ESMTP id 7B1D3474E71

+	for <pgsql-hackers@postgresql.org>; Sun, 14 Apr 2002 20:39:18 -0400 (EDT)

+Received: from localhost (camber@localhost)

+	by localhost.localdomain (8.11.6/8.11.6) with ESMTP id g3F0cmD10631;

+	Sun, 14 Apr 2002 20:38:48 -0400

+X-Authentication-Warning: localhost.localdomain: camber owned process doing -bs

+Date: Sun, 14 Apr 2002 20:38:48 -0400 (EDT)

+From: Brian Bruns <camber@ais.org>

+X-X-Sender: <camber@localhost.localdomain>

+To: Hannu Krosing <hannu@tm.ee>

+cc: <pgsql-hackers@postgresql.org>

+Subject: Re: [HACKERS] 7.3 schedule

+In-Reply-To: <1018704763.1784.1.camel@taru.tm.ee>

+Message-ID: <Pine.LNX.4.33.0204142027180.9523-100000@localhost.localdomain>

+MIME-Version: 1.0

+Content-Type: TEXT/PLAIN; charset=US-ASCII

+Precedence: bulk

+Sender: pgsql-hackers-owner@postgresql.org

+Status: OR

+

+On 13 Apr 2002, Hannu Krosing wrote:
+
+> On Fri, 2002-04-12 at 03:04, Brian Bruns wrote:
+> > On 11 Apr 2002, Hannu Krosing wrote:
+> > 
+> > > IIRC someone started work on modularising the network-related parts with
+> > > a goal of supporting DRDA (DB2 protocol) and others in future.
+> > 
+> > That was me, although I've been bogged down lately, and haven't been able 
+> > to get back to it.
+> 
+> Has any of your modularisation work got into CVS yet ?
+
+No, Bruce didn't like the way I did certain things, and had some qualms 
+about the value of supporting multiple wire protocols IIRC.  Plus the 
+patch was not really ready for primetime yet.  
+
+I'm hoping to get back to it soon and sync it with the latest CVS, and 
+clean up the odds and ends.
+
+> > DRDA, btw, is not just a DB2 protocol but an opengroup 
+> > spec that hopefully will someday be *the* standard on the wire database 
+> > protocol.  DRDA handles prepare/execute and is completely binary in 
+> > representation, among other advantages.
+> 
+> What about extensibility - is there some predefined way of adding new
+> types ?
+
+Not really, there is some ongoing standards activity adding some new 
+features.  The list of supported types is pretty impressive, anything in 
+particular you are looking for?
+
+> Also, does it handle NOTIFY ?
+
+I don't know the answer to this.  The spec is pretty huge, so it may, but 
+I haven't seen it.
+
+Even if it is supported as a secondary protocol, I believe there is alot 
+of value in having a single database protocol standard. (why else would I 
+be doing it!).  I'm also looking into what it will take to do the same for 
+MySQL and Firebird.  Hopefully they will be receptive to the idea as well.
+
+> ----------------
+> Hannu
+
+Cheers,
+
+Brian
+
+
+---------------------------(end of broadcast)---------------------------
+TIP 5: Have you checked our extensive FAQ?
+
+http://www.postgresql.org/users-lounge/docs/faq.html
+