Обсуждение: PL/Python prepare example's use of setdefault

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()?
--
Jonathan Ross Rogers

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> 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.
>>
>> 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?:

Hm ... this was changed in commit 6f6b46c9c0ca3d96.  Peter, did
you consider efficiency here?

            regards, tom lane

On 10/15/2014 05:51 PM, Adrian Klaver wrote:
> 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;
>

Exactly. It seems to me that the approach taken by the newer
documentation will be less efficient. If so, why was the example
changed? BTW, I would rewrite the 9.1 example to be shorter while
behaving the same:


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


--
Jonathan Ross Rogers

On 10/15/14 5:56 PM, Tom Lane wrote:
> Hm ... this was changed in commit 6f6b46c9c0ca3d96.  Peter, did
> you consider efficiency here?

Fixed.

On 10/15/14 5:58 PM, Jonathan Rogers wrote:
> BTW, I would rewrite the 9.1 example to be shorter while
> behaving the same:
>
>
> CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
>     plan = SD.get("plan")
>     if plan is None:

If we're going for shortness, how about

    if not plan:

?

>         SD["plan"] = plan = plpy.prepare("SELECT 1")

and here maybe

    plan = SD["plan"] = plpy.prepare("SELECT 1")

to emphasize the assignment to "plan"?

>     # rest of function
> $$ LANGUAGE plpythonu;

On 11/01/2014 12:13 PM, Peter Eisentraut wrote:
> On 10/15/14 5:58 PM, Jonathan Rogers wrote:
>> BTW, I would rewrite the 9.1 example to be shorter while
>> behaving the same:
>>
>>
>> CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
>>     plan = SD.get("plan")
>>     if plan is None:
>
> If we're going for shortness, how about
>
>     if not plan:

Sure, that's fine as long as a plan object never looks Falsey.

>
> ?
>
>>         SD["plan"] = plan = plpy.prepare("SELECT 1")
>
> and here maybe
>
>     plan = SD["plan"] = plpy.prepare("SELECT 1")
>
> to emphasize the assignment to "plan"?

Yeah, order of assignment shouldn't matter.

>
>>     # rest of function
>> $$ LANGUAGE plpythonu;
>


--
Jonathan Ross Rogers