How many plpython execution environments are opened in a query?

Question

Are user defined functions in postgres (and greenplum) run in isolation? How many execution environments are open when I have a query with a plpythonu user-defined functions? Is there any shared python interpreter state when running a query?

Say I have a plpython user defined function:

CREATE OR REPLACE FUNCTION file2text(path string) RETURNS text AS $$
    f = open(path, 'r')
    return f.read()
    f.close()
$$ LANGUAGE plpythonu;

For a table create table files (name varchar, path varchar) and a query over that table as follows:

SELECT f.name
FROM files f
WHERE character_length( file2text(f.path) ) > 4096
ORDER BY f.name

Is a new python environment spawned for each execution of my plpython function? In a MPP databases, can I assume that the behavior of postgres be duplicated across each segment with no shared state across segments?

score 4 · Answer 1 · answered Dec 03 '12 at 23:56

PL/Python state is isolated between sessions, and shared within a session.

You can test that pretty easily by placing a variable in the sys module (for testing only!) or better, in a custom module you import. You should see that the variable is visible to other functions that run within the same session (connection), but not to functions in other sessions.

How many plpython execution environments are opened in a query?

1 Answers1