Re: PL/Python prepare example's use of setdefault

On 10/15/2014 02:39 PM, Jonathan Rogers wrote:
> I was just reading the PL/Python docs section "42.7.1 Database Access
> Functions" and saw this example:
>
> CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
>      plan = SD.setdefault("plan", plpy.prepare("SELECT 1"))
>      # rest of function
> $$ LANGUAGE plpythonu;
>
> The above example uses the plpy.prepare() function, reusing the result
> across function calls uses setdefault(). Unfortunately, since
> setdefault() is a method on dict objects, the values passed to it must
> be evaluated before it can be called. Therefore, plpy.prepare() will be
> called every time usesavedplan() executes whether a result already
> exists in the SD dict or not.
>
> I'm not sure if it's a problem that plpy.prepare() is called every time
> since the result is discarded if a prepared statement had been cached by
> a previous execution of usesavedplan(). It seems that some wasted
> processing will occur, but maybe not enough to matter. The documentation
> for SPI_prepare() does not clearly state what tasks that function
> performs other than constructing a prepared statement object. It seems
> to imply that parsing does occur within SPI_prepare(). It does state
> that query planning occurs within SPI_execute_plan().
>
> Can anyone clarify what occurs when plpy.prepare() is called? Is it
> worth using a Python conditional to determine whether to call it rather
> than using SD.setdefault()?

Like in the older documentation?:

http://www.postgresql.org/docs/9.1/static/plpython-database.html

CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
     if SD.has_key("plan"):
         plan = SD["plan"]
     else:
         plan = plpy.prepare("SELECT 1")
         SD["plan"] = plan
     # rest of function
$$ LANGUAGE plpythonu;

>


--
Adrian Klaver
adrian.klaver@aklaver.com