Horton

Thread

  1. AW: Re: [GENERAL] Query caching

    Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2000-11-02T08:35:29Z

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

Andreas

  2. Re: AW: Re: [GENERAL] Query caching

    Karel Zak <zakkr@zf.jcu.cz> — 2000-11-02T14:38:14Z

    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

  3. Re: AW: Re: [GENERAL] Query caching

    Christof Petig <christof.petig@wtal.de> — 2000-11-03T15:47:11Z

    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.]
What about parameters? Normally you can prepare a statement and execute it
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).

Christof

  4. Re: AW: Re: [GENERAL] Query caching

    Karel Zak <zakkr@zf.jcu.cz> — 2000-11-06T08:15:04Z

    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

  5. Re: AW: Re: [GENERAL] Query caching

    Christof Petig <christof.petig@wtal.de> — 2000-11-08T15:05:50Z

    Karel Zak wrote:

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

Sorry I can't find it in my (current) mailbox, do you have a copy around? Or can
you give me a keyword?

> > 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?)
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.

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

Yours
    Christof

PS: I vote for rethinking the always ascii over the wire strategy. CORBA was
proposed as a potential replacement which takes care of endianness and float
conversions. But I would not go that far (???), perhaps taking encodings (aka
marshalling?) from CORBA.

  6. Re: AW: Re: [GENERAL] Query caching

    Ross Reedstrom <reedstrm@rice.edu> — 2000-11-08T16:07:44Z

    On Wed, Nov 08, 2000 at 04:05:50PM +0100, Christof Petig wrote:
> Karel Zak wrote:
> >
> >  Please, read my old query cache and PREPARE/EXECUTE description...
> 
> Sorry I can't find it in my (current) mailbox, do you have a copy around? Or can
> you give me a keyword?
> 

In my archives, there's this one:

Date: Wed, 19 Jul 2000 10:16:13 +0200 (CEST)
From: Karel Zak <zakkr@zf.jcu.cz>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: [HACKERS] The query cache - first snapshot (long)

Here's the URL to the archives:

http://www.postgresql.org/mhonarc/pgsql-hackers/2000-07/msg01098.html

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

  7. Re: AW: Re: [GENERAL] Query caching

    Karel Zak <zakkr@zf.jcu.cz> — 2000-11-09T08:23:41Z

    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

  8. Re: Query caching

    Christof Petig <christof.petig@wtal.de> — 2000-11-10T07:05:38Z

    Karel Zak wrote:

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

Postponed for future improvements ...

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

I see.

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

That would involve changing the parser. Future project.

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

I'm looking forward to get first experiences with the query cache kernel. I think it's
the right way to go.

Christof

  9. Re: Query caching

    Martin Devera <devik@cdi.cz> — 2000-11-10T10:40:09Z

    Did someone think about query costs ? Is you prepare
query like SELECT id FROM t1 WHERE type=$1 and
execute it with $1=1 and 2. For 1 there is one record
in t1 a all other have type=2.
Without caching, first query will use index, second
not.
Should cached plan use index or not ?
devik

Christof Petig wrote:
> 
> Karel Zak wrote:
> 
> > 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.
> 
> Postponed for future improvements ...
> 
> > > > 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.
> 
> I see.
> 
> > > 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.
> 
> That would involve changing the parser. Future project.
> 
> > > > 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.
> 
> I'm looking forward to get first experiences with the query cache kernel. I think it's
> the right way to go.
> 
> Christof

  10. Re: Query caching

    Karel Zak <zakkr@zf.jcu.cz> — 2000-11-12T11:34:51Z

    On Fri, 10 Nov 2000 devik@cdi.cz wrote:

> Did someone think about query costs ? Is you prepare
> query like SELECT id FROM t1 WHERE type=$1 and
> execute it with $1=1 and 2. For 1 there is one record
> in t1 a all other have type=2.
> Without caching, first query will use index, second
> not.
> Should cached plan use index or not ?
> devik

 The postgresql already have planns caching. See SPI (saveplan), but
it's usable for internal stuff (for example triggers..) only. The
PREPARE/EXECUTE pull up it to be<->fe and make new memory type that
allows save it in shared memory. But else it's *nothing* new. 

 A validity of cached planns is user problem now. Not some internal
method how check changes that out of date some query (or exist some idea?). 
It can be more changes like changes in DB schema.

 If resolve this anyone clever person it will great for VIEW, SPI too.

 Rebuid a query plan in the planner is not a problem, in the cache is 
stored original query tree, but you must known when... or must know
it a DB user.

					Karel