Confused about strange import behavior within plpython stored procedures

I'm running Postgresql 9.4, and I'm confused about how it handles plpython imports, specifically regarding Decimal.

First example:

CREATE OR REPLACE FUNCTION devel.foo()
  RETURNS void AS
$BODY$
def myfoo():
    eval("Decimal('40.0')")

myfoo()
plpy.notice("done")
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;
ALTER FUNCTION devel.foo()
  OWNER TO postgres;

SELECT devel.foo();

Result:

ERROR: NameError: name 'Decimal' is not defined

A little surprising since one of the changes in Postgresql 9.4 is that it's supposed to use Decimal implicitly instead of float within plpython. But OK fine, let's add the import:

CREATE OR REPLACE FUNCTION devel.foo()
  RETURNS void AS
$BODY$
from decimal import Decimal

def myfoo():
    eval("Decimal('40.0')");

myfoo()
plpy.notice("done")
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;
ALTER FUNCTION devel.foo()
  OWNER TO postgres;

select devel.foo()

Again:

ERROR: NameError: name 'Decimal' is not defined

Now let's try placing the import inside the function:

CREATE OR REPLACE FUNCTION devel.foo()
  RETURNS void AS
$BODY$
def myfoo():
    from decimal import Decimal
    eval("Decimal('40.0')")

myfoo()
plpy.notice("done")
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;
ALTER FUNCTION devel.foo()
  OWNER TO postgres;


select devel.foo();

Result: devel.foo() now executes without problems, printing 'done'. Another curious thing: the following also works:

CREATE OR REPLACE FUNCTION devel.foo()
  RETURNS void AS
$BODY$
from decimal import Decimal
def myfoo():
    var = Decimal('40.0')
    eval("Decimal('40.0')")

myfoo()
plpy.notice("done")
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;
ALTER FUNCTION devel.foo()
  OWNER TO postgres;

Questions: why isn't the top level import seen inside myfoo()? Why does it suddenly works if I use Decimal within the function?

Solution

This is python behavior as documented here. The plpythonu execution environment is not global.

Note, eval() does not have access to the nested scopes (non-locals) in the enclosing environment.

Try this code in python (outside of PostgreSQL):

def bar():
    from decimal import Decimal
    def foo():
        return eval("Decimal('40.0')")

    return foo()

print(bar())

It fails with NameError: name 'Decimal' is not defined.

Putting Decimal into your globals with global works:

def bar():
    from decimal import Decimal
    global Decimal
    def foo():
        return eval("Decimal('40.0')")

    return foo()

print(bar())