Re: [HACKERS] BUG #3244: problem with PREPARE

In the modified version of ECPG that we used for our benchmark, we
PREPARE'd all statements.

-----Original Message-----
From: Michael Meskes [mailto:meskes(at)postgresql(dot)org]
Sent: Tuesday, April 24, 2007 12:45 AM
To: William Lawrance
Cc: Tom Lane; pgsql-bugs(at)postgresql(dot)org; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] [BUGS] BUG #3244: problem with PREPARE

On Mon, Apr 23, 2007 at 02:02:04PM -0700, William Lawrance wrote:
> Our first attempt to use the ECPG prepare interface revealed that ECPG
> doesn't use the PQlib prepare function. The ECPG prepare replaces any
> parameters with their values and presents a new SQL statement to the

This is true and should also be documented. The reason for this
behaviour is simply that ECPG prepare feature was added before the
backend had its own prepare feature. And no one changed it so far.

> There are several difficulties to be encountered when attempting to use
> this within a program using the ECPG interface. For example, the
> connection structure for PQlib isn't readily available, and the
> transaction semantics must be synchronized with ECPG's state. This did
> work, but it was fairly clumsy.

Right, that's what makes it non trivial.

> Since we wanted to do this in a cleaner manner, and also wished to avoid
> changing the applications if possible, we used the following approach:
>
> Within the "execute.c" module, we added routines to manage a cache
> of prepared statements. These routines are able to search, insert,
> and delete entries in the cache. The key for these cache entries is
> the text of the SQL statement as passed by ECPG from the application
> program.
>
> Within the same module, we replaced the "ECPGexecute" function.
> This is the function that is called to execute a statement after
> some preliminary housekeeping is done. The original "ECPGexecute"
> function constructs an ASCII string by replacing each host variable
> with its current value and then calling "PQexec". The new
> "ECPGexecute" function does the following:
>
> - build an array of the current values of the host variables.
>
> - search the cache for an entry indicating that this statement
> has already been prepare'd, via "PQprepare"
>
> - If no entry was found in the previous step, call "PQprepare"
> for the statement and then insert an entry for it into the
> cache. If this requires an entry to be re-used, execute a
> "DEALLOCATE PREPARE.." for the previous contents.
>
> - At this point, the SQL statement has been prepare'd by PQlib,
> either when the statement was executed in the past, or in
> the previous step.
>
> - call "PQexecPrepared", using the array of parameters built
> in the first step above.

Does this mean you prepare ALL statements? Or where you only talking
about statements that are prepared in the application?

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!