Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

From the documentation, error 42601 is a syntax error.

I see no syntax error.

Furthermore, the function loaded fine with the superuser account when I imported the database:

psql -d db_name -U account_name -W -f db-dump.sql > import.log 2> error.log

I tried to simplify the code to no avail:

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS
$$
  use Sys::Hostname;
  return hostname;
$$
LANGUAGE plperlu;

Same error as before.

What I don't understand is why, according to the error message, PostgreSQL is trying to load the code into plperl instead of plperlu.

Any ideas?

Update #1

Note that the postgres user is a Superuser:

                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Due to plperlu being untrusted, the account requires superuser authorization:

DROP LANGUAGE plperl;
DROP LANGUAGE plperlu;
CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
UPDATE pg_language set lanpltrusted = true where lanname='plperlu';
GRANT ALL ON LANGUAGE plperlu TO account_name WITH GRANT OPTION;
ALTER USER account_name WITH SUPERUSER;

I do not know if plperl and plperlu are allowed to be installed at the same time, so I dropped the former. The function can now be created.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.