Horton

Thread

  1. Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-22T15:48:35Z

      Hi,

 as I said, I tring implement PREPARE / EXECUTE command for user a
controllable query cache (in TODO: Cache most recent query plan(s)).

I have implement first usable version now (I know that it is not
interesting for current feature-freeze state, but I believe that
it is interesting for next release and for major developers). See:


test=# prepare sel as select * from tab where id = $1 and data 
       like $2 using int, text;
PREPARE
test=# execute sel using 1, '%a';
 id | data
----+------
  1 | aaaa
(1 row)

test=# prepare ins as insert into tab (data) values($1) using text;
PREPARE
test=# execute ins_tab using 'cccc';
INSERT 18974 1
 

The queryTree and planTree are save in hash table and in the 
TopMemoryContext (Is it good space for this cache?). All is
without change-schema detection (IMHO is user problem if he
changes DB schema and use old cached plan). In future I try
add any 'change-schema' detection (to alter/drop table,rule..etc).


I'am not sure with syntax, now is:

        PREPARE name AS optimizable-statement [ USING type, ... ]
        EXECUTE name [ USING value, ... ] 	

Comments? Suggestions? (SQL92?)

(Note: I try test speed and speed for cached query plan (select) executed 
       via EXECUTE rise very very up (70% !).)  


						Karel						

					
----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/

Docs:        http://docs.linux.cz                    (big docs archive)	
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
-----------------------------------------------------------------------

  2. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Marc G. Fournier <scrappy@hub.org> — 2000-02-22T16:27:40Z

    On Tue, 22 Feb 2000, Karel Zak - Zakkr wrote:

> The queryTree and planTree are save in hash table and in the 
> TopMemoryContext (Is it good space for this cache?). All is
> without change-schema detection (IMHO is user problem if he
> changes DB schema and use old cached plan). In future I try

Just curious, but a new 'PREPARE name AS...' with the same name just
overrides the previously saved plan?

Actually, can someone who may know the internals of DBI comment on
this?  If I have a CGI that runs the same SELECT call each and every time,
this would come in handy ... but how does DBI do its prepare?  Would it
set a new name for each invocation, so you would have several 'cached
plans' for the exact same SELECT call?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

  3. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-22T17:12:22Z

    On Tue, 22 Feb 2000, The Hermit Hacker wrote:

> On Tue, 22 Feb 2000, Karel Zak - Zakkr wrote:
> 
> > The queryTree and planTree are save in hash table and in the 
> > TopMemoryContext (Is it good space for this cache?). All is
> > without change-schema detection (IMHO is user problem if he
> > changes DB schema and use old cached plan). In future I try
> 
> Just curious, but a new 'PREPARE name AS...' with the same name just
> overrides the previously saved plan?

 Current code return you:

test=# prepare one as select * from aaa;
PREPARE
test=# prepare one as select * from aaa;
ERROR:  Query plan with name 'one' already exist.
test=#

 I prefer any DROP command instead overriding. But I open for any other
suggestions...

> Actually, can someone who may know the internals of DBI comment on
> this?  If I have a CGI that runs the same SELECT call each and every time,
> this would come in handy ... but how does DBI do its prepare?  Would it
> set a new name for each invocation, so you would have several 'cached
> plans' for the exact same SELECT call?

 I not sure if I good understand you. But..

 1/ this cache is in memory only (it is not across re-connection persistent), 
    not save in any table..etc. 
 2/ you can have (equil or differnet) several plans in this cache, number of
    plans is not limited.
 3/ you can't have two same query's name in cache (name is hash key)
 4/ after EXECUTE is plan still in cache, you can run it again...  

 potential usage:

 example - you start connection to PG and you know that you need use 
20x same question (example INSERT). You can PREPARE plan for this query,
and run fast EXECUTE only (instead 20x full insert);
 
						Karel

  4. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-22T17:16:15Z

    Karel Zak - Zakkr <zakkr@zf.jcu.cz> writes:
>  as I said, I tring implement PREPARE / EXECUTE command for user a
> controllable query cache (in TODO: Cache most recent query plan(s)).

Looks cool.

> The queryTree and planTree are save in hash table and in the 
> TopMemoryContext (Is it good space for this cache?).

Probably not.  I'd suggest making a separate memory context for
this purpose --- they're cheap, and that gives you more control.
Look at the creation and use of CacheMemoryContext for an example.

> I'am not sure with syntax, now is:

>         PREPARE name AS optimizable-statement [ USING type, ... ]
>         EXECUTE name [ USING value, ... ] 	

> Comments? Suggestions? (SQL92?)

This seems to be quite at variance with SQL92, unfortunately, so it
might not be a good idea to use the same keywords they do...

			regards, tom lane

  5. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-22T17:30:48Z

    On Tue, 22 Feb 2000, Tom Lane wrote:

> Karel Zak - Zakkr <zakkr@zf.jcu.cz> writes:
> >  as I said, I tring implement PREPARE / EXECUTE command for user a
> > controllable query cache (in TODO: Cache most recent query plan(s)).
> 
> Looks cool.

Thanks.

> 
> > The queryTree and planTree are save in hash table and in the 
> > TopMemoryContext (Is it good space for this cache?).
> 
> Probably not.  I'd suggest making a separate memory context for
> this purpose --- they're cheap, and that gives you more control.
> Look at the creation and use of CacheMemoryContext for an example.

 Yes, I agree (TopMemoryContext was simpl for first hacking). 
But I not sure how create new (across transaction persistent?) 
MemoryContext. It needs new portal? (Sorry I not thoroughly explore
PG's memory management.)  

> 
> > I'am not sure with syntax, now is:
> 
> >         PREPARE name AS optimizable-statement [ USING type, ... ]
> >         EXECUTE name [ USING value, ... ] 	
> 
> > Comments? Suggestions? (SQL92?)
> 
> This seems to be quite at variance with SQL92, unfortunately, so it
> might not be a good idea to use the same keywords they do...

 Hmm, I inspire with Jan's TODO item. What use:

	CREATE PLAN 
	DROP PLAN
	EXECUTE PLAN

 IMHO these kaywords are better.
						Karel

  6. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Don Baccus <dhogaza@pacifier.com> — 2000-02-22T18:56:07Z

    At 06:30 PM 2/22/00 +0100, Karel Zak - Zakkr wrote:

> Yes, I agree (TopMemoryContext was simpl for first hacking). 
>But I not sure how create new (across transaction persistent?) 
>MemoryContext. It needs new portal? (Sorry I not thoroughly explore
>PG's memory management.)  

Jan is caching the plans needed for referential integrity checking
and referential actions - look at ri_triggers.c in src/backend/utils/adt.
ri_InitHashTables initializes the RI cache.

(I *assume* Jan, with his great experience, is doing it right, I'm
in no position to judge!)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  7. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-22T20:18:47Z

    On Tue, 22 Feb 2000, Don Baccus wrote:

> At 06:30 PM 2/22/00 +0100, Karel Zak - Zakkr wrote:
> 
> > Yes, I agree (TopMemoryContext was simpl for first hacking). 
> >But I not sure how create new (across transaction persistent?) 
> >MemoryContext. It needs new portal? (Sorry I not thoroughly explore
> >PG's memory management.)  
> 
> Jan is caching the plans needed for referential integrity checking
> and referential actions - look at ri_triggers.c in src/backend/utils/adt.
> ri_InitHashTables initializes the RI cache.

 My cache table routines for PREPARE = Jan's RI routines :-) 
(I copy and a little modify Jan's code (*Thanks* Jan for good inspiration..).

But if I good look at Jan use SPI context for this, not any specific
context. 

						Karel

  8. RE: [HACKERS] Cache query (PREPARE/EXECUTE)

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-02-23T08:50:48Z

    > -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> 
> Karel Zak - Zakkr <zakkr@zf.jcu.cz> writes:
> >  as I said, I tring implement PREPARE / EXECUTE command for user a
> > controllable query cache (in TODO: Cache most recent query plan(s)).
> 
> Looks cool.
> 
> > The queryTree and planTree are save in hash table and in the 
> > TopMemoryContext (Is it good space for this cache?).
> 
> Probably not.  I'd suggest making a separate memory context for
> this purpose --- they're cheap, and that gives you more control.
> Look at the creation and use of CacheMemoryContext for an example.
>

Hmm,shoudn't per plan memory context be created ?

Though current SPI stuff saves prepared plans to TopMemory
Context,we couldn't remove them forever.  It seems that SPI 
should also be changed in its implementation about saving
plans.

Note that freeObject() is unavailable at all.
We would be able to free PREPAREd resources by destroying 
corrsponding memory context.

If I recognize Jan's original idea correctly,he also suggested
the same way.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

  9. RE: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-23T10:26:27Z

    > Though current SPI stuff saves prepared plans to TopMemory
> Context,we couldn't remove them forever.  It seems that SPI 
> should also be changed in its implementation about saving
> plans.

 Yes, I know about SPI plan saving... from here is my inspiration
with TopMemoryContext. But we have in current PG code very often
any cached queryPlan/Tree (PREPARE, SPI and Jan's RI saves plans
to TopM. too), I agree with Tom that is not bad idea saving all
plans to _one_ specific MemoryContext. 

 My idea is make any basic routines for query cache (hash table,
ExecuteCachedQuery() ...etc) and use these routines for more
operation (SPI, FKeys, PREPARE..). Comments?

> Note that freeObject() is unavailable at all.
> We would be able to free PREPAREd resources by destroying 
> corrsponding memory context.

 If I good understand, we can't destroy any plan? We must 
destroy _full_ memory context? If yes (please no), we can't
make a DROP PLAN command or we must create for each plan specific
memory context (and drop this specific Context (Jan's original idea)).

 If I call SPI_saveplan(), is the plan forever save in 
TopMemoryContext? (hmm, the SPI is memory feeder).

						Karel

  10. RE: [HACKERS] Cache query (PREPARE/EXECUTE)

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-02-23T16:19:57Z

    > -----Original Message-----
> From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> 
> > Though current SPI stuff saves prepared plans to TopMemory
> > Context,we couldn't remove them forever.  It seems that SPI 
> > should also be changed in its implementation about saving
> > plans.
> 
>  Yes, I know about SPI plan saving... from here is my inspiration
> with TopMemoryContext. But we have in current PG code very often
> any cached queryPlan/Tree (PREPARE, SPI and Jan's RI saves plans
> to TopM. too), I agree with Tom that is not bad idea saving all
> plans to _one_ specific MemoryContext. 
> 
>  My idea is make any basic routines for query cache (hash table,
> ExecuteCachedQuery() ...etc) and use these routines for more
> operation (SPI, FKeys, PREPARE..). Comments?
> 
> > Note that freeObject() is unavailable at all.
> > We would be able to free PREPAREd resources by destroying 
> > corrsponding memory context.
> 
>  If I good understand, we can't destroy any plan? We must

I think so.  The problem is that Node struct couldn't be freed safely
due to the lack of reference count in its definition.  As far as I see
plans could be destroyed only when the memory context in which
they are placed are destroyed.

> destroy _full_ memory context? If yes (please no), we can't
> make a DROP PLAN command or we must create for each plan specific
> memory context (and drop this specific Context (Jan's original idea)).
>

You can DROP a PLAN by removing its hash entry but of cource
there remains memory leak. 

>  If I call SPI_saveplan(), is the plan forever save in 
> TopMemoryContext? (hmm, the SPI is memory feeder).
>

Probably.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

  11. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-23T16:53:11Z

    "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> I think so.  The problem is that Node struct couldn't be freed safely
> due to the lack of reference count in its definition.  As far as I see
> plans could be destroyed only when the memory context in which
> they are placed are destroyed.

This is overly conservative.  It should be safe to destroy a plan tree
via freeObject() if it was created via copyObject() --- and that is
certainly how the plan would get into a permanent memory context.

Currently, rule definitions are leaked in CacheContext at relcache
flushes.  I plan to start freeing them via freeObject at the beginning
of the 7.1 development cycle --- I didn't want to risk it during the
runup to 7.0, but I believe it will work fine.

			regards, tom lane

  12. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-23T17:11:22Z

    On Wed, 23 Feb 2000, Tom Lane wrote:

> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > I think so.  The problem is that Node struct couldn't be freed safely
> > due to the lack of reference count in its definition.  As far as I see
> > plans could be destroyed only when the memory context in which
> > they are placed are destroyed.
> 
> This is overly conservative.  It should be safe to destroy a plan tree
> via freeObject() if it was created via copyObject() --- and that is
> certainly how the plan would get into a permanent memory context.

Yes, SPI and my PREPARE use copyObject() for saving to TopMemoryContext.

Well, I believe you Tom that freeObject() is correct and I start 
implement PlanCacheMemoryContext's routines for PREPARE (and
SPI's saveplan ?). 

						Karel Z.

  13. RE: [HACKERS] Cache query (PREPARE/EXECUTE)

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-02-23T17:34:04Z

    > -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > I think so.  The problem is that Node struct couldn't be freed safely
> > due to the lack of reference count in its definition.  As far as I see
> > plans could be destroyed only when the memory context in which
> > they are placed are destroyed.
> 
> This is overly conservative.  It should be safe to destroy a plan tree
> via freeObject() if it was created via copyObject() --- and that is
> certainly how the plan would get into a permanent memory context.
>

I proposed the implementation of copyObject() which keeps the
references among objects once before. It seems unnatural to me
that such kind of implementation would never be allowed by this
restriction. 
Why is memory context per plan bad ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

  14. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Jan Wieck <wieck@debis.com> — 2000-02-23T18:22:16Z

    > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > I think so.  The problem is that Node struct couldn't be freed safely
> > due to the lack of reference count in its definition.  As far as I see
> > plans could be destroyed only when the memory context in which
> > they are placed are destroyed.
>
> This is overly conservative.  It should be safe to destroy a plan tree
> via freeObject() if it was created via copyObject() --- and that is
> certainly how the plan would get into a permanent memory context.
>
> Currently, rule definitions are leaked in CacheContext at relcache
> flushes.  I plan to start freeing them via freeObject at the beginning
> of the 7.1 development cycle --- I didn't want to risk it during the
> runup to 7.0, but I believe it will work fine.

    I  don't  see  any  reason,  why  each  saved  plan  or  rule
    definition  shouldn't  go  into  it's  own,  private   memory
    context.  Then,  a  simple  destruction of the entire context
    will surely free all it's memory, and I think it will also be
    faster  since  the  en-block  allocation, done for many small
    objects, doesn't need to free all them separately - it throws
    away  the  entire  blocks. No need to traverse the node tree,
    nor any problems with multiple object references  inside  the
    tree.

    Since  plans  are (ought to be) saved via SPI_saveplan(plan),
    there is already a central point where it  can  be  done  for
    plans.  And a corresponding SPI_freeplan(savedplan) should be
    easy to create, since the context can be held in the SPI plan
    structure itself.

    Needs  only  some  general naming convention for these memory
    contexts.  But something like a

        MemoryContext CreateObjectMemoryContext();

    that  guarantees  uniqueness  in  the  context  name  and  no
    conflicts  by using some appropriate prefix in them should do
    it.

    The overhead, payed for separate contexts is IMHO negligible.


Jan

--

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

  15. RE: [HACKERS] Cache query (PREPARE/EXECUTE)

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-23T18:48:25Z

    On Thu, 24 Feb 2000, Hiroshi Inoue wrote:

> > This is overly conservative.  It should be safe to destroy a plan tree
> > via freeObject() if it was created via copyObject() --- and that is
> > certainly how the plan would get into a permanent memory context.
> >
> 
> I proposed the implementation of copyObject() which keeps the
> references among objects once before. It seems unnatural to me
> that such kind of implementation would never be allowed by this
> restriction. 
>
> Why is memory context per plan bad ?

 One context is more simple. 

 We talking about a *cache*. If exist interface for this cache and
 all operations are with copy/freeObject it not has restriction. 
 
 For how action it will restriction? 

 The PlanCacheMemoryContext will store space only, it isn't space for 
 any action.


						Karel Z.

  16. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Jan Wieck <wieck@debis.com> — 2000-02-23T20:11:08Z

    Karel wrote:

> > Why is memory context per plan bad ?
>
>  One context is more simple.

    I  don't  see  much complexity difference between one context
    per plan vs. one context for  all.  At  least  if  we  do  it
    transparently inside of SPI_saveplan() and SPI_freeplan().

>  We talking about a *cache*. If exist interface for this cache and
>  all operations are with copy/freeObject it not has restriction.
>
>  For how action it will restriction?

    No restrictions I can see.

    But I think one context per plan is still better, since first
    there is no leakage/multiref  problem.  Second,  there  is  a
    performance   difference   between   explicitly   pfree()'ing
    hundreds of small allocations (in freeObject() traverse), and
    just  destroying  a  context.  The  changes  I  made  to  the
    MemoryContextAlloc stuff for v6.5 (IIRC), using bigger blocks
    incl.  padding/reuse  for small allocations, caused a speedup
    of 5+% for the entire regression test. This was only  because
    it  uses lesser real calls to malloc()/free() and the context
    destruction does not need to process  a  huge  list  of  all,
    however small allocations anymore.  It simply throws away all
    blocks now.

    This  time,  we  talk  about  a   more   complex,   recursive
    freeObject(), switch()'ing for every node type into separate,
    per object  type  specific  functions,  pfree()'ing  all  the
    little  chunks.   So  there  is  at  least  a  difference  in
    first/second-level RAM cache rows required. And if  that  can
    simply  be  avoided by using one context per plan, I vote for
    1by1.

    Then  again,  copyObject/freeObject   must   be   fixed   WRT
    leakage/multiref anyway.


Jan

--

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

  17. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-23T22:40:47Z

    wieck@debis.com (Jan Wieck) writes:
>     But I think one context per plan is still better, since first
>     there is no leakage/multiref  problem.  Second,  there  is  a
>     performance   difference   between   explicitly   pfree()'ing
>     hundreds of small allocations (in freeObject() traverse), and
>     just  destroying  a  context.

Agreed, though one would hope that performance of cache flushes
is not a major consideration ;-).

What I find attractive about going in this direction is the idea
that we could get rid of freeObject() entirely, and eliminate that
part of the work involved in changing node definitions.

>     Then  again,  copyObject/freeObject   must   be   fixed   WRT
>     leakage/multiref anyway.

Not if we decide to get rid of freeObject, instead.

I think that a little work would have to be done to support efficient
use of large numbers of contexts, but it's certainly doable.  This
path seems more attractive than trying to make the world safe for
freeObject of arbitrary node trees.

			regards, tom lane

  18. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Jan Wieck <wieck@debis.com> — 2000-02-23T23:21:24Z

    Tom Lane wrote:

> wieck@debis.com (Jan Wieck) writes:
> >     Then  again,  copyObject/freeObject   must   be   fixed   WRT
> >     leakage/multiref anyway.
>
> Not if we decide to get rid of freeObject, instead.
>
> I think that a little work would have to be done to support efficient
> use of large numbers of contexts, but it's certainly doable.  This
> path seems more attractive than trying to make the world safe for
> freeObject of arbitrary node trees.

    Yes,    little    work    to   build   the   framework.   All
    alloc/realloc/free functions for  a  particular  context  are
    just  function-pointers  inside the context structure itself.
    So ther'll be no additional call overhead when  dealing  with
    large numbers of contexts.

    OTOH,  this new per-object-context stuff could hand down some
    lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
    and  MCXT_UNTIL_INFINITY  to  start  from. The memory context
    creation/destruction routines could manage some global  lists
    of    contexts,   that   automatically   get   destroyed   on
    AtXactCommitMemory and so on, making  such  a  kind  of  per-
    object  memory  context  a fire'n'forget missile (Uh - played
    F15 too  excessively  :-).   It  should  still  be  destroyed
    explicitly  if  not needed anymore, but if allocated with the
    correct lifetime, wouldn't hurt that much if forgotten.

    More work to get all  the  existing  places  in  the  backend
    making use of this functionality where applicable.


Jan

--

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

  19. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-23T23:38:29Z

    wieck@debis.com (Jan Wieck) writes:
>     OTOH,  this new per-object-context stuff could hand down some
>     lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
>     and  MCXT_UNTIL_INFINITY  to  start  from.

A good thing to keep in mind, but for the short term I'm not sure
we need it; the proposed new contexts are all for indefinite-lifetime
caches, so there's no chance to make them go away automatically.
Eventually we might have more uses for limited-lifetime contexts,
though.

Something else that needs to be looked at is how memory contexts
are tied to "portals" presently.  That mechanism probably needs
to be redesigned.  I have to admit I don't understand what it's
for...

			regards, tom lane

  20. Re: [HACKERS] Cache query (PREPARE/EXECUTE)

    Jan Wieck <wieck@debis.com> — 2000-02-24T00:16:31Z

    Tom Lane wrote:

> wieck@debis.com (Jan Wieck) writes:
> >     OTOH,  this new per-object-context stuff could hand down some
> >     lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
> >     and  MCXT_UNTIL_INFINITY  to  start  from.
>
> A good thing to keep in mind, but for the short term I'm not sure
> we need it; the proposed new contexts are all for indefinite-lifetime
> caches, so there's no chance to make them go away automatically.
> Eventually we might have more uses for limited-lifetime contexts,
> though.

    Sure,  was only what I thought might be useful in some cases.
    If not used, would it hurt to have  support  for  it  either?
    Some unused List*'ers somewhere - nothing important.

> Something else that needs to be looked at is how memory contexts
> are tied to "portals" presently.  That mechanism probably needs
> to be redesigned.  I have to admit I don't understand what it's
> for...

    U2? Makes 2 of us.


Jan

--

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

  21. [HACKERS] Cache query implemented

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-24T17:35:14Z

    On Wed, 23 Feb 2000, Jan Wieck wrote:
> 
>     I  don't  see  much complexity difference between one context
>     per plan vs. one context for  all.  At  least  if  we  do  it
>     transparently inside of SPI_saveplan() and SPI_freeplan().
> 

 Well, I explore PG's memory context routines and is probably more
simple destroy mem context (than use feeeObject()) and create new context
for plan is simple too. (Jan, Hiroshi and PG's source convince me :-)

 Today afternoon I rewrite query cache and now is implemented as 
'context-per-plan'. It allows me write a 'DROP PLAN' command. We can use 
this cache in SPI too, and create new command SPI_freeplan() (and stop 
TopMemoryContext feeding).

 Now, PREPARE/EXECUTE are ready to usage. See:

test=# prepare my_plan as select * from tab where id = $1 using int;
PREPARE
test=# execute my_plan using 2;
 id | data
----+------
  2 | aaaa
(1 row)

test=# drop plan my_plan;
DROP
test=# execute my_plan using 2;
ERROR:  Plan with name 'my_plan' not exist
 
 
 I still not sure with PREPARE/EXECUTE keywords, I vote for:

	CREATE PLAN name AS query [ USING type, ... ]
	EXECUTE PLAN name [ USING values, ... ]
	DROP PLAN name

 Comments? (Please. I really not SQL's standard guru...)

						Karel

  22. Re: [HACKERS] Cache query implemented

    Peter Eisentraut <peter_e@gmx.net> — 2000-02-26T01:36:10Z

    Karel Zak - Zakkr writes:

>  I still not sure with PREPARE/EXECUTE keywords, I vote for:
> 
> 	CREATE PLAN name AS query [ USING type, ... ]
> 	EXECUTE PLAN name [ USING values, ... ]
> 	DROP PLAN name
> 
>  Comments? (Please. I really not SQL's standard guru...)

SQL seems to have something like the following. (Note: The section on
dynamic SQL is mostly incomprehensible to me.)

PREPARE name AS query
DESCRIBE INPUT name [ USING x, ... ]
DESCRIBE [OUTPUT] name [ USING x, ... ]
EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
DEALLOCATE PREPARE name

I'm not sure if these match exactly what you're doing, but if it is at all
possible to match what you're doing to these, I'd say it would be a shame
not to do it. You've got time.

Meanwhile I'm wondering whether it would not be possible to provide the
plan caching functionality even if all you do is send the same SELECT
twice in a row. Might be tricky, of course.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  23. Re: [HACKERS] Cache query implemented

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-28T11:30:45Z

    On Sat, 26 Feb 2000, Peter Eisentraut wrote:

> Karel Zak - Zakkr writes:
> 
> >  I still not sure with PREPARE/EXECUTE keywords, I vote for:
> > 
> > 	CREATE PLAN name AS query [ USING type, ... ]
> > 	EXECUTE PLAN name [ USING values, ... ]
> > 	DROP PLAN name
> > 
> >  Comments? (Please. I really not SQL's standard guru...)
> 
> SQL seems to have something like the following. (Note: The section on
> dynamic SQL is mostly incomprehensible to me.)

 I'am studing SQL92 just now. And I not sure if my idea is same as SQL92's
PREPARE. My implementation is very simular with SPI's plan operations,
and is designed as simple way to very fast query execution.

> PREPARE name AS query

 In my PREPARE go query to parser and if in PG query is '$n', parser needs
(Oid) argstypes array, hence it needs 
	
  PREPARE name AS <query with parameters - $n> USING valuetype, ...

 But in SQL92 is PREPARE without "USING valuetype, ...".

> DESCRIBE INPUT name [ USING x, ... ]
> DESCRIBE [OUTPUT] name [ USING x, ... ]

It is probably used instead 'USING' in PREPARE. It specific columns
for select (OUTPUT) and INPUT specific values for parser ($n paremetrs
in PG). 

People which define SQL92 must be crazy. This PREPARE concept split one
query plan to three commands. Who join it to one plan?....  


> EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]

 This command "Associate input parametrs and output targets with a prepared
statement and execute the statement" (SQL92).

 'INTO' - I really not sure if is possible in PG join more plans into
one plan. If I good understand, INTO is targetlist for cached 
query, but in cached query is targetlist too. Is any way how join/replace 
targetlist in cached query with targetlist from EXECUTE's INTO? 
(QueryRewrite?). But, INTO for EXECUTE is nod bad idea.
 
> DEALLOCATE PREPARE name

It is better than 'DROP'.


> Meanwhile I'm wondering whether it would not be possible to provide the
> plan caching functionality even if all you do is send the same SELECT
> twice in a row. Might be tricky, of course.

 Here, I'am not understand you.

 Exist any other SQL which has implemented a PREPARE/EXECUTE? 
(Oracle8 has not it, and other..?)

 I still vote for simple PREPARE/EXECUTE (or non-standard CREATE PLAN),
because SQL92's PREPARE is not implementable :-)
  
						Karel

  24. Re: [HACKERS] Cache query implemented

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-28T14:03:17Z

    > > EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
> 
>  This command "Associate input parametrs and output targets with a prepared
> statement and execute the statement" (SQL92).
> 
>  'INTO' - I really not sure if is possible in PG join more plans into
> one plan. If I good understand, INTO is targetlist for cached 
> query, but in cached query is targetlist too. Is any way how join/replace 
> targetlist in cached query with targetlist from EXECUTE's INTO? 
> (QueryRewrite?). But, INTO for EXECUTE is nod bad idea.

 Sorry, previous paragraph is stupid. The 'into' is simple item in 
the query struct and not any targetlist. I spend more time with previous
stupidity than with implementation: 

 EXECUTE <name> 
	[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] 
	[ USING val, ... ]


test=# prepare sel as select * from tab;
PREPARE
test=# execute sel into x;
SELECT
test=# select * from x;
 id | data
----+------
  1 | aaaa
  2 | bbbb
  3 | cccc
  4 | dddd
  5 | eeee
(5 rows)


 The PostgreSQL source code is really very modular :-)

						Karel

  25. RE: [HACKERS] Cache query implemented

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-02-29T05:05:02Z

    > -----Original Message-----
> From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> 
> > > EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
> > 
> >  This command "Associate input parametrs and output targets 
> with a prepared
> > statement and execute the statement" (SQL92).
> > 

I don't know well about PREPARE statement.
But is above syntax for interative SQL command ?
Isn't it for embedded SQL or SQL module ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

  26. RE: [HACKERS] Cache query implemented

    Karel Zak <zakkr@zf.jcu.cz> — 2000-02-29T12:51:35Z

    On Tue, 29 Feb 2000, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> > 
> > > > EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
> > > 
> > >  This command "Associate input parametrs and output targets 
> > with a prepared
> > > statement and execute the statement" (SQL92).
> > > 
> 
> I don't know well about PREPARE statement.
> But is above syntax for interative SQL command ?
> Isn't it for embedded SQL or SQL module ?

 - PREPARE save to cache any standard sql command (OptimizableStmt).
 - EXECUTE run this cached plan (query) and send data to frontend or
   INTO any relation.

 Or what you mean?

							Karel

  27. RE: [HACKERS] Cache query implemented

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-03-01T01:35:04Z

    > -----Original Message-----
> From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> 
> On Tue, 29 Feb 2000, Hiroshi Inoue wrote:
> 
> > > -----Original Message-----
> > > From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> > > 
> > > > > EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
> > > > 
> > > >  This command "Associate input parametrs and output targets 
> > > with a prepared
> > > > statement and execute the statement" (SQL92).
> > > > 
> > 
> > I don't know well about PREPARE statement.
> > But is above syntax for interative SQL command ?
> > Isn't it for embedded SQL or SQL module ?
> 
>  - PREPARE save to cache any standard sql command (OptimizableStmt).
>  - EXECUTE run this cached plan (query) and send data to frontend or
>    INTO any relation.
> 
>  Or what you mean?
>

In old Oracle(I don't know recent Oracle,sorry),PREPARE couldn't be called
as an interactive SQL command. It was used only in embedded SQL.

Seems x, y  after INTO are output variables. In embedded SQL they are
host variables. But I don't know what they are in interactive SQL.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

  28. RE: [HACKERS] Cache query implemented

    Karel Zak <zakkr@zf.jcu.cz> — 2000-03-01T09:33:18Z

    On Wed, 1 Mar 2000, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> > 
> > On Tue, 29 Feb 2000, Hiroshi Inoue wrote:
> > 
> > > > -----Original Message-----
> > > > From: Karel Zak - Zakkr [mailto:zakkr@zf.jcu.cz]
> > > > 
> > > > > > EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
> > > > > 
> > > > >  This command "Associate input parametrs and output targets 
> > > > with a prepared
> > > > > statement and execute the statement" (SQL92).
> > > > > 
> > > 
> > > I don't know well about PREPARE statement.
> > > But is above syntax for interative SQL command ?
> > > Isn't it for embedded SQL or SQL module ?
> > 
> >  - PREPARE save to cache any standard sql command (OptimizableStmt).
> >  - EXECUTE run this cached plan (query) and send data to frontend or
> >    INTO any relation.
> > 
> >  Or what you mean?
> >
> 
> In old Oracle(I don't know recent Oracle,sorry),PREPARE couldn't be called
> as an interactive SQL command. It was used only in embedded SQL.

 Oh, yes I understand you now. No, prepare is a standard command 
(interactive) (IMO).
 
> Seems x, y  after INTO are output variables. In embedded SQL they are
> host variables. But I don't know what they are in interactive SQL.

 A INTO is same as (example) SELECT ..INTO, see:

 PREPARE myplan AS SELECT * FROM tab;
 EXECUTE myplan INTO newtab;

 A INTO only remove query destination for cached plan.

 ...it is in my implementation. I don't no how it is in any others SQLs.
In my Oracle8's tutorial it isn't. 

						Karel