Thread: Cannot create perlplu function

Hi, all.

Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.

From the shell, I have executed:

    createlang plperl db_name;
    createlang plperlu db_name;

As the superuser running `psql`, I have executed:

    GRANT ALL ON LANGUAGE plperl TO account_name;
    GRANT ALL ON LANGUAGE plperlu TO account_name;

The `pg_language` table reveals:

    select lanname,lanpltrusted from pg_language where lanname like
'plperl%';

    "plperl";t
    "plperlu";t

When I create the following function:

    CREATE OR REPLACE FUNCTION get_hostname()
      RETURNS text AS
    $BODY$
      use Sys::Hostname;
      return hostname;
    $BODY$
      LANGUAGE plperlu IMMUTABLE
      COST 1;
    ALTER FUNCTION get_hostname()
      OWNER TO account_name;

I receive the following error:

    ERROR:  Unable to load Sys/Hostname.pm into plperl at line 2.
    BEGIN failed--compilation aborted at line 2.
    CONTEXT:  compilation of PL/Perl function "get_hostname"

Yet the following works:

    CREATE OR REPLACE FUNCTION get_hostname()
      RETURNS text AS
    $BODY$
      return '127.0.0.1';
    $BODY$
      LANGUAGE plperlu IMMUTABLE
      COST 1;
    ALTER FUNCTION get_hostname()
      OWNER TO account_name;

The following Perl script works as expected from the shell:

    use Sys::Hostname;
    print hostname;

I tried to run the function as an anonymous block:

    DO $$
      use Sys::Hostname;
      print hostname;
    $$ LANGUAGE plperlu;

This returned the same error as before, with this additional information:

    ERROR: Unable to load Sys/Hostname.pm into plperl at line 3.
    BEGIN failed--compilation aborted at line 3.
    SQL state: 42601
    Context: PL/Perl anonymous code block

Thangalin <thangalin@gmail.com> writes:
> When I create the following function:

>     CREATE OR REPLACE FUNCTION get_hostname()
>       RETURNS text AS
>     $BODY$
>       use Sys::Hostname;
>       return hostname;
>     $BODY$
>       LANGUAGE plperlu IMMUTABLE
>       COST 1;
>     ALTER FUNCTION get_hostname()
>       OWNER TO account_name;

> I receive the following error:

>     ERROR:  Unable to load Sys/Hostname.pm into plperl at line 2.

Ok, so it's not finding the module you're trying to "use".

> The following Perl script works as expected from the shell:

>     use Sys::Hostname;
>     print hostname;

This probably means that your shell environment defines a Perl @INC
search path that finds the Sys::Hostname module, but the postmaster's
environment doesn't have that.  I'm not much of a Perl hacker, but
I'd try looking to see if your private ~/.profile or similar file is
setting up a custom Perl search path.  If so, you could propagate a
similar setting into the environment of the user ID you're running
the postmaster under.

One trick that might be helpful is to look at the postmaster's stderr
output (which should be getting logged somewhere if you've got a well
configured setup).  The underlying Perl message about "Can't locate
Sys/hostname.pm in @INC" might show up there, and if so it'd tell you
just what search path the postmaster is actually seeing.

            regards, tom lane