Thread: AW: Re: [GENERAL] Query caching
> 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
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
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
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
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.
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.
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
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
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
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