path: root/doc/TODO.detail/prepare

From owner-pgsql-hackers@hub.org Wed Nov 18 14:40:49 1998
Received: from hub.org (majordom@hub.org [209.47.148.200])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA29743
	for <maillist@candle.pha.pa.us>; Wed, 18 Nov 1998 14:40:36 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.1/8.9.1) with SMTP id OAA03716;
	Wed, 18 Nov 1998 14:37:04 -0500 (EST)
	(envelope-from owner-pgsql-hackers@hub.org)
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Wed, 18 Nov 1998 14:34:39 +0000 (EST)
Received: (from majordom@localhost)
	by hub.org (8.9.1/8.9.1) id OAA03395
	for pgsql-hackers-outgoing; Wed, 18 Nov 1998 14:34:37 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8])
	by hub.org (8.9.1/8.9.1) with SMTP id OAA03381
	for <pgsql-hackers@hub.org>; Wed, 18 Nov 1998 14:34:31 -0500 (EST)
	(envelope-from wieck@sapserv.debis.de)
Received: by orion.SAPserv.Hamburg.dsh.de 
	for pgsql-hackers@hub.org 
	id m0zgDnj-000EBTC; Wed, 18 Nov 98 21:02 MET
Message-Id: <m0zgDnj-000EBTC@orion.SAPserv.Hamburg.dsh.de>
From: jwieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] PREPARE
To: meskes@usa.net (Michael Meskes)
Date: Wed, 18 Nov 1998 21:02:06 +0100 (MET)
Cc: pgsql-hackers@hub.org
Reply-To: jwieck@debis.com (Jan Wieck)
In-Reply-To: <19981118084843.B869@usa.net> from "Michael Meskes" at Nov 18, 98 08:48:43 am
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text
Sender: owner-pgsql-hackers@postgreSQL.org
Precedence: bulk
Status: RO

Michael Meskes wrote:

>
> On Wed, Nov 18, 1998 at 03:23:30AM +0000, Thomas G. Lockhart wrote:
> > > I didn't get this one completly. What input do you mean?
> >
> > Just the original string/query to be prepared...
>
> I see. But wouldn't it be more useful to preprocess the query and store the
> resulting nodes instead? We don't want to parse the statement everytime a
> variable binding comes in.

    Right.  A real improvement would only be to have the prepared
    execution plan in the backend and just giving  the  parameter
    values.

    I can think of the following construct:

        PREPARE optimizable-statement;

    That one will run parser/rewrite/planner, create a new memory
    context with a unique identifier and  saves  the  querytree's
    and  plan's  in  it.   Parameter values are identified by the
    usual $n notation. The command returns the identifier.

        EXECUTE QUERY identifier [value [, ...]];

    then get's back the prepared plan and querytree  by  the  id,
    creates  an  executor  context  with  the given values in the
    parameter array and calls ExecutorRun() for them.

    The PREPARE needs to analyze the resulting parsetrees to  get
    the  datatypes  (and maybe atttypmod's) of the parameters, so
    EXECUTE QUERY can convert the values into Datum's  using  the
    types  input  functions.  And  the  EXECUTE has to be handled
    special in tcop (it's something between a regular  query  and
    an utility statement). But it's not too hard to implement.

    Finally a

        FORGET QUERY identifier;

    (don't  remember  how  the  others  named it) will remove the
    prepared plan etc. simply by destroying  the  memory  context
    and dropping the identifier from the id->mcontext+prepareinfo
    mapping.

    This all  restricts  the  usage  of  PREPARE  to  optimizable
    statements.  Is  it  required  to  be able to prepare utility
    statements (like CREATE TABLE or so) too?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #




From pgsql-hackers-owner+M67@postgresql.org Tue Oct 31 19:18:16 2000
Received: from mail.postgresql.org ([216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id TAA08916
	for <pgman@candle.pha.pa.us>; Tue, 31 Oct 2000 19:18:15 -0500 (EST)
Received: from mail.postgresql.org ([216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eA10IOl60635;
	Tue, 31 Oct 2000 19:18:24 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M67@postgresql.org)
Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA10H8l60400
	for <pgsql-hackers@postgresql.org>; Tue, 31 Oct 2000 19:17:08 -0500 (EST)
	(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
	by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id BAA32036;
	Wed, 1 Nov 2000 01:16:42 +0100
Date: Wed, 1 Nov 2000 01:16:42 +0100 (CET)
From: Karel Zak <zakkr@zf.jcu.cz>
To: Alfred Perlstein <bright@wintelcom.net>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Query cache import?
In-Reply-To: <20001031151144.F22110@fw.wintelcom.net>
Message-ID: <Pine.LNX.3.96.1001101005110.31713B-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


On Tue, 31 Oct 2000, Alfred Perlstein wrote:

> I never saw much traffic regarding Karel's work on making stored
> proceedures:
>
> http://people.freebsd.org/~alfred/karel-pgsql.txt
> 
> What happened with this?  It looked pretty interesting. :(

 It's probably a little about me :-) ... well,

  My query cache is in usable state and it's efficient for all 
things those motivate me to work on this.

 some basic features:

	- share parsed plans between backends in shared memory
	- store plans to private backend hash table
	- use parameters for stored queries
	- better design for SPI 
			- memory usage for saved plans
			- save plans "by key"

 
 The current query cache code depend on 7.1 memory management. After
official 7.1 release I prepare patch with query cache+SPI (if not
hit me over head, please ..)

 All what will doing next time not depend on me, *it's on code developers*.

 For example Jan has interesting idea about caching all plans which
processing backend. But it's far future and IMHO we must go by small
steps to Oracle's funeral :-) 

 If I need the query cache in the my work (typical for some web+pgsql) or 
will some public interest I will continue on this, if not I freeze it. 
(Exists more interesting work like http://mape.jcu.cz ... sorry of 
advertising :-)

					Karel






From pgsql-hackers-owner+M312@postgresql.org Mon Nov  6 03:27:32 2000
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA28404
	for <pgman@candle.pha.pa.us>; Mon, 6 Nov 2000 03:27:32 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eA68Pos51966;
	Mon, 6 Nov 2000 03:25:50 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M312@postgresql.org)
Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA68Fes50414
	for <pgsql-hackers@postgresql.org>; Mon, 6 Nov 2000 03:15:40 -0500 (EST)
	(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
	by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id JAA20862;
	Mon, 6 Nov 2000 09:15:04 +0100
Date: Mon, 6 Nov 2000 09:15:04 +0100 (CET)
From: Karel Zak <zakkr@zf.jcu.cz>
To: Christof Petig <christof.petig@wtal.de>
cc: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
        The Hermit Hacker <scrappy@hub.org>, pgsql-hackers@postgresql.org
Subject: Re: AW: [HACKERS] Re: [GENERAL] Query caching
In-Reply-To: <3A02DDFF.E8CBFCF3@wtal.de>
Message-ID: <Pine.LNX.3.96.1001106090801.20612C-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


On Fri, 3 Nov 2000, Christof Petig wrote:

> Karel Zak wrote:
> 
> > On Thu, 2 Nov 2000, Zeugswetter Andreas SB wrote:
> >
> > >
> > > > Well I can re-write and resubmit this patch. Add it as a
> > > > compile time option
> > > > is not bad idea. Second possibility is distribute it as patch
> > > > in the contrib
> > > > tree. And if it until not good tested not dirty with this main tree...
> > > >
> > > >  Ok, I next week prepare it...
> > >
> > > One thing that worries me though is, that it extends the sql language,
> > > and there has been no discussion about the chosen syntax.
> > >
> > > Imho the standard embedded SQL syntax (prepare ...) could be a
> > > starting point.
> >
> >  Yes, you are right... my PREPARE/EXECUTE is not too much ready to SQL92,
> > I some old letter I speculate about "SAVE/EXECUTE PLAN" instead
> > PREPARE/EXECUTE. But don't forget, it will *experimental* patch... we can
> > change it in future ..etc.
> >
> >                                         Karel
> 
> [Sorry, I didn't look into your patch, yet.]

 Please, read my old query cache and PREPARE/EXECUTE description...

> What about parameters? Normally you can prepare a statement and execute it

 We have in PG parameters, see SPI, but now it's used inside backend only
and not exist statement that allows to use this feature in be<->fe.

> using different parameters. AFAIK postgres' frontend-backend protocol is not
> designed to take parameters for statements (e.g. like result presents
> results). A very long road to go.
> By the way, I'm somewhat interested in getting this feature in. Perhaps it
> should be part of a protocol redesign (e.g. binary parameters/results).
> Handling endianness is one aspect, floats are harder (but float->ascii->float
> sometimes fails as well).

        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 ]


An example:


PREPARE chris_query AS SELECT * FROM pg_class WHERE relname = $1 USING text;

EXECUTE chris_query USING 'pg_shadow';


	Or mean you something other?
					Karel






From pgsql-hackers-owner+M444@postgresql.org Thu Nov  9 03:32:10 2000
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA09953
	for <pgman@candle.pha.pa.us>; Thu, 9 Nov 2000 03:32:09 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eA98RSs11426;
	Thu, 9 Nov 2000 03:27:28 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M444@postgresql.org)
Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA98OPs11045;
	Thu, 9 Nov 2000 03:24:25 -0500 (EST)
	(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
	by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id JAA08951;
	Thu, 9 Nov 2000 09:23:41 +0100
Date: Thu, 9 Nov 2000 09:23:41 +0100 (CET)
From: Karel Zak <zakkr@zf.jcu.cz>
To: Christof Petig <christof.petig@wtal.de>
cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
        Michael Meskes <meskes@postgresql.org>,
        Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
        The Hermit Hacker <scrappy@hub.org>
Subject: Re: AW: [HACKERS] Re: [GENERAL] Query caching
In-Reply-To: <3A096BCE.F9887955@wtal.de>
Message-ID: <Pine.LNX.3.96.1001109090739.8052B-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


On Wed, 8 Nov 2000, Christof Petig wrote:

> Karel Zak wrote:
> 
> > > What about parameters? Normally you can prepare a statement and execute it
> >
> >  We have in PG parameters, see SPI, but now it's used inside backend only
> > and not exist statement that allows to use this feature in be<->fe.
> 
> Sad. Since ecpg would certainly benefit from this.
> 
> > > using different parameters. AFAIK postgres' frontend-backend protocol is not
> > > designed to take parameters for statements (e.g. like result presents
> > > results). A very long road to go.
> > > By the way, I'm somewhat interested in getting this feature in. Perhaps it
> > > should be part of a protocol redesign (e.g. binary parameters/results).
> > > Handling endianness is one aspect, floats are harder (but float->ascii->float
> > > sometimes fails as well).
> >
> >         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 ]
> >
> > An example:
> >
> > PREPARE chris_query AS SELECT * FROM pg_class WHERE relname = $1 USING text;
> 
> I would prefer '?' as a parameter name, since this is in the embedded sql standard
> (do you have a copy of the 94 draft? I can mail mine to you?)

 This not depend on query cache. The '$n' is PostgreSQL query parametr
keyword and is defined in standard parser. The PREPARE statement not parsing
query it's job for standard parser.

> Also the standard says a whole lot about guessing the parameter's type.
> 
> Also I vote for  ?::type or type(?) or sql's cast(...) (don't know it's syntax)
> instead of abusing the using keyword.

The postgresql executor expect types of parametrs in separate input (array).
I not sure how much expensive/executable is survey it from query.

> > EXECUTE chris_query USING 'pg_shadow';
> 
> Great idea of yours to implement this! Since I was thinking about implementing a
> more decent schema for ecpg but had no mind to touch the backend and be-fe
> protocol (yet).
> It would be desirable to do an 'execute immediate using', since using input
> parameters would take a lot of code away from ecpg.

By the way, PREPARE/EXECUTE is face only. More interesting in this period is
query-cache-kernel. SQL92 is really a little unlike my PREPARE/EXECUTE.

						Karel


From pgsql-hackers-owner+M9563@postgresql.org Thu May 31 16:31:59 2001
Return-path: <pgsql-hackers-owner+M9563@postgresql.org>
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.10.1/8.10.1) with ESMTP id f4VKVxc26942
	for <pgman@candle.pha.pa.us>; Thu, 31 May 2001 16:31:59 -0400 (EDT)
Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28])
	by postgresql.org (8.11.3/8.11.1) with SMTP id f4VKVIE38645;
	Thu, 31 May 2001 16:31:18 -0400 (EDT)
	(envelope-from pgsql-hackers-owner+M9563@postgresql.org)
Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f4VKNVE35356
	for <pgsql-hackers@postgresql.org>; Thu, 31 May 2001 16:23:31 -0400 (EDT)
	(envelope-from zakkr@zf.jcu.cz)
Received: (from zakkr@localhost)
	by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) id WAA19957;
	Thu, 31 May 2001 22:23:26 +0200
Date: Thu, 31 May 2001 22:23:26 +0200
From: Karel Zak <zakkr@zf.jcu.cz>
To: Roberto Abalde <roberto.abalde@galego21.org>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Cache for query plans
Message-ID: <20010531222326.B16862@ara.zf.jcu.cz>
References: <000701c0e932$d17646c0$c6023dc8@ultra>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0.1i
In-Reply-To: <000701c0e932$d17646c0$c6023dc8@ultra>; from roberto.abalde@galego21.org on Wed, May 30, 2001 at 03:00:53PM -0300
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

On Wed, May 30, 2001 at 03:00:53PM -0300, Roberto Abalde wrote:
> Hi,
> 
> I need to implement a cache for query plans as part of my BSc thesis. Does
> anybody know what happened to Karel Zak's patch?
> 


 Hi,


 my patch is on my ftp and nobody works on it, but I mean it's good
begin for some next work. I not sure with implement this experimental
patch (but usable) to official sources. For example Jan has more complex
idea about query plan cache ... but first time we must solve some
sub-problems like memory management in shared memory that is transparently
for starndard routines like copy query plan ... and Tom isn't sure with
query cache in shared memory...etc. Too much queries, but less answers :-)


				Karel
> 
> PS: Sorry for my english :(


 Do you anytime read any my mail :-)


			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 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