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