PL/Python prepare example's use of setdefault

[Jonathan Rogers]

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()?

[Adrian Klaver]

Post by Jonathan Rogers
I was just reading the PL/Python docs section "42.7.1 Database Access
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;

[Tom Lane]

Post by Jonathan Rogers
I was just reading the PL/Python docs section "42.7.1 Database Access
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()?

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

regards, tom lane

[Jonathan Rogers]

Post by Adrian Klaver

Post by Jonathan Rogers
I was just reading the PL/Python docs section "42.7.1 Database Access
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()?

http://www.postgresql.org/docs/9.1/static/plpython-database.html
CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
plan = SD["plan"]
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;