Re: [HACKERS] Cache query implemented

From: Karel Zak - Zakkr <zakkr(at)zf(dot)jcu(dot)cz>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Jan Wieck <wieck(at)debis(dot)com>, Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] Cache query implemented
Date: 2000-02-28 11:30:45
Message-ID: Pine.LNX.3.96.1000228110508.2717A-100000@ara.zf.jcu.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Hollomon 2000-02-28 12:50:35 Re: [HACKERS] Is anyone working on pg_dump?
Previous Message Bruce Momjian 2000-02-28 10:58:27 7.0 Changes list