Thread: prepareable statements

The attached patch implements per-backend prepareable statements.

The syntax is:

    PREPARE name_of_stmt(param_types) FROM <some query>;

    EXECUTE name_of_stmt [INTO relation] [USING args];

    DEALLOCATE [PREPARE] name_of_stmt;

I don't really like the 'FROM' keyword in PREPARE (I was planning to
use 'AS'), but that's what SQL92 specifies.

The PREPARE keyword in DEALLOCATE is ignored, for SQL92 compliance.

You can specify EXECUTE ... INTO, using the same syntax as SELECT
INTO, to store the result set from the EXECUTE in a relation.

The syntax is largely SQL92 compliant, but not totally. I'm not sure how
the SQL spec expects parameters to be set up in PREPARE, but I doubt
it's the same way I used. And the SQL92 spec for EXECUTE is functionally
similar, but uses a different syntax (EXECUTE ... USING INTO <rel>, I
think). If someone can decipher the spec on these two points and
can suggest what the proper syntax should be, let me know.

Parameters are fully supported -- for example:

    PREPARE q1(text) FROM SELECT * FROM pg_class WHERE relname = $1;

    EXECUTE q1 USING 'abc';

For simple queries such as the preceding one, using PREPARE followed
by EXECUTE is about 10% faster than continuosly using SELECT (when
executing 100,000 statements). When executing more complex statements
(such as the monstrous 12 table join used by the JDBC driver for
getting some meta-data), the performance improvement is more drastic
(IIRC it was about 100x in that case, when executing 75 statements).

I've included some regression tests for the work -- when/if the
patch is applied I'll write the documentation.

The patch stores queries in a hash table in TopMemoryContext. I
considered replacing the hash table with a linked list and
searching through that linearly, but I decided it wasn't worth
the bother (since the # of prepared statements is likely to be
very small, I would expect a linked list to outperform a hash
table in the common case). If you feel strongly one way or another,
let me know.

Also, I'm not entirely sure my approach to memory management is
correct. Each entry in the hash table stores its data in its
own MemoryContext, which is deleted when the statement is
DEALLOCATE'd. When actually running the prepared statement
through the executor, CurrentMemoryContext is used. Let me know
if there's a better way to do this.

This patch is based on Karel Zak's qCache patch for 7.0, but it's
completely new code (it's also a lot simpler, and doesn't bother
with caching plans in shared memory, as discussed on -hackers).

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

> The syntax is largely SQL92 compliant, but not totally. I'm not sure how
> the SQL spec expects parameters to be set up in PREPARE, but I doubt
> it's the same way I used. And the SQL92 spec for EXECUTE is functionally
> similar, but uses a different syntax (EXECUTE ... USING INTO <rel>, I
> think). If someone can decipher the spec on these two points and
> can suggest what the proper syntax should be, let me know.

I'll have a read of the spec for you to see if I can decode something out of
it!  I think it's pretty essential we have full standard compliance on this
one!

Chris

On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
> The attached patch implements per-backend prepareable statements.

Can someone comment on when this will be reviewed and/or applied?

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

nconway@klamath.dyndns.org (Neil Conway) writes:
> On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
>> The attached patch implements per-backend prepareable statements.

> Can someone comment on when this will be reviewed and/or applied?

It's on my to-look-at list, but I'm deathly behind on reviewing patches.

I guess the good news is that lots of great stuff is coming in from a
lot of fairly new contributors.  The bad news is that we're getting way
behind on reviewing it.  I think I've spent all my reviewing time this
month just on stuff from Rod Taylor...

            regards, tom lane

Tom Lane wrote:
> nconway@klamath.dyndns.org (Neil Conway) writes:
> > On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
> >> The attached patch implements per-backend prepareable statements.
>
> > Can someone comment on when this will be reviewed and/or applied?
>
> It's on my to-look-at list, but I'm deathly behind on reviewing patches.
>
> I guess the good news is that lots of great stuff is coming in from a
> lot of fairly new contributors.  The bad news is that we're getting way
> behind on reviewing it.  I think I've spent all my reviewing time this
> month just on stuff from Rod Taylor...

Yes, we are backed up.  I am applying stuff that Tom doesn't claim after
a few days, but even then Tom will go back and review them.  Not sure
what we can do except to say everything will be in before 7.3 beta, and
we regret that a few items can't get in sooner.

The good news is that it is only a few patches that are held up.  The
others are getting applied in a timely manner.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026