Re: [HACKERS] Cache query implemented

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