From: | Jonathan Rogers <jrogers(at)socialserve(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: PL/Python prepare example's use of setdefault |
Date: | 2014-10-15 21:58:37 |
Message-ID: | 543EEE0D.2060002@socialserve.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yogesh. Sharma | 2014-10-16 03:10:30 | Re: Any postgres API available to get errorcode for PQerrorMessage |
Previous Message | Tom Lane | 2014-10-15 21:56:59 | Re: PL/Python prepare example's use of setdefault |