Re: AW: Re: [GENERAL] Query caching

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.