Re: Pl/Python runtime overhead

Thanks Sergey,
This is going to help for sure. I'll also look at the url. What I've been
trying to understand is when python runtime is invoked during the function
execution (lifecycle?) . Maybe looking at plpython's source may help get an
understanding of that.

Regards
Seref

On Thu, Aug 8, 2013 at 2:54 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Wed, Aug 7, 2013 at 7:43 AM, Seref Arikan
> <serefarikan(at)kurumsalteknoloji(dot)com> wrote:
> > When a pl/python based function is invoked, does it keep a python runtime
> > running across calls to same function? That is, if I use connection
> pooling,
> > can I save on the python runtime initialization and loading costs?
>
> You can use the following wrapping technique to cache function's body,
> that will save you some resources and time. It stores the main() in SD
> (session data) built-in object and retrieves it when stored, so
> plpython does not need to process it every time stored function is
> called.
>
> CREATE OR REPLACE FUNCTION some_plpython_function()
> RETURNS integer
> LANGUAGE plpythonu
> AS $function$
> """ An example of a function's body caching and error handling """
>
> sdNamespace = 'some_plpython_function'
>
> if sdNamespace not in SD:
>
> def main():
> """ The function is assumed to be cached in SD and reused """
>
> result = None
>
> # Do whatever you need here
>
> return result
>
> # Cache body in SD
> SD[sdNamespace] = main
>
> try:
> return SD[sdNamespace]()
> except Exception, e:
> import traceback
> plpy.info(traceback.format_exc())
>
> $function$;
>
> I can also recommend you to cache query plans, as plpython does not do
> it itself. The code below also works with SD to store prepared plans
> and retrieve them. This allows you to avoid preparing every time you
> are executing the same query. Just like plpgsql does, but manually.
>
> if SD.has_key('%s_somePlan' % sdNamespace):
> somePlan = SD['%s_planName' % sdNamespace]
> else:
> somePlan = plpy.prepare(...)
>
>
> > Are there any documents/books etc you'd recommend to get a good
> > understanding of extending postgres with languages like python? I'd
> really
> > like to get a good grip of the architecture of this type of extension,
> and
> > possibly attempt to introduce a language of my own choosing. The docs
> I've
> > seen so far are mostly too specific, making it a bit for hard for me to
> see
> > the forest from the trees.
>
> AFAIK, this one is the best one
> http://www.postgresql.org/docs/9.2/interactive/plpython.html.
>
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray(dot)ru(at)gmail(dot)com
>