Thread: 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