Pivotal Knowledge Base

Follow

HowTo - Install pljava Package

Environment

Product
Pivotal Greenplum(GPDB)

Purpose

This article discusses how to install a pljava package on GPDB.

Procedure

Ensure that you have completed the following prerequisites:

  • Prior to installing the PL/Java extension, ensure that GPDB is running, you have determined a path to the greenplum_path.sh file and that the $MASTER_DATA_DIRECTORY and $GPHOME variables are set.

    [gpadmin@smdw honohb-1]$ gpstate -e
    20150915:19:31:40:006772 gpstate:smdw:gpadmin-[INFO]:-Starting gpstate with args: -e
    20150915:19:31:40:006772 gpstate:smdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.5.3 build 2'
    20150915:19:31:40:006772 gpstate:smdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.5.3 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Aug 14 2015 16:50:26'
    20150915:19:31:40:006772 gpstate:smdw:gpadmin-[INFO]:-Obtaining Segment details from master...
    20150915:19:31:40:006772 gpstate:smdw:gpadmin-[INFO]:-Gathering data from segments...
    .
    20150915:19:31:41:006772 gpstate:smdw:gpadmin-[INFO]:-----------------------------------------------------
    20150915:19:31:41:006772 gpstate:smdw:gpadmin-[INFO]:-Segment Mirroring Status Report
    20150915:19:31:41:006772 gpstate:smdw:gpadmin-[INFO]:-----------------------------------------------------
    20150915:19:31:41:006772 gpstate:smdw:gpadmin-[INFO]:-All segments are running normally
    [gpadmin@smdw honohb-1]$ ls -ltr /usr/local/greenplum-db
    lrwxrwxrwx 1 gpadmin gpadmin 21 Sep  4 22:19 /usr/local/greenplum-db -> /usr/local/GP-4.3.5.3
    [gpadmin@smdw honohb-1]$ ls -ltr /usr/local/greenplum-db/greenplum_path.sh
    -rw-r--r-- 1 gpadmin gpadmin 852 Sep 15 18:22 /usr/local/greenplum-db/greenplum_path.sh
    [gpadmin@smdw honohb-1]$ source /usr/local/greenplum-db/greenplum_path.sh
    [gpadmin@smdw honohb-1]$ env | grep MASTER
    MASTER_DATA_DIRECTORY=/data/master_bhonoh/honohb-1
    [gpadmin@smdw honohb-1]$ env | grep GPHOME
    GPHOME=/usr/local/greenplum-db/.
  • Download the required language package from the Product Portal.

Installing the Package

  • After downloading the required package install the package using  the following command:

    [gpadmin@smdw bhonohan]$ gppkg -i /tmp/pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg
  • 20150915:18:22:22:002662 gppkg:smdw:gpadmin-[INFO]:-Starting gppkg with args: -i /tmp/pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg
    20150915:18:22:22:002662 gppkg:smdw:gpadmin-[INFO]:-Installing package pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg
    20150915:18:22:23:002662 gppkg:smdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/GP-4.3.5.3/.tmp/pljava-1.2-2.x86_64.rpm /usr/local/GP-4.3.5.3/.tmp/jre-1.6.0_32-1.x86_64.rpm --dbpath /usr/local/GP-4.3.5.3/share/packages/database --prefix /usr/local/GP-4.3.5.3'
    20150915:18:22:30:002662 gppkg:smdw:gpadmin-[INFO]:-Installing pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg locally
    20150915:18:22:31:002662 gppkg:smdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/GP-4.3.5.3/.tmp/pljava-1.2-2.x86_64.rpm /usr/local/GP-4.3.5.3/.tmp/jre-1.6.0_32-1.x86_64.rpm --dbpath /usr/local/GP-4.3.5.3/share/packages/database --prefix /usr/local/GP-4.3.5.3'
    20150915:18:22:31:002662 gppkg:smdw:gpadmin-[INFO]:-Installing rpms cmdStr='rpm -i /usr/local/GP-4.3.5.3/.tmp/pljava-1.2-2.x86_64.rpm /usr/local/GP-4.3.5.3/.tmp/jre-1.6.0_32-1.x86_64.rpm --dbpath /usr/local/GP-4.3.5.3/share/packages/database --prefix=/usr/local/GP-4.3.5.3'
    20150915:18:22:33:002662 gppkg:smdw:gpadmin-[INFO]:-Completed local installation of pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg.
    20150915:18:22:33:002662 gppkg:smdw:gpadmin-[INFO]:-Please source your $GPHOME/greenplum_path.sh file and restart the database.
    You can enable pljava by running psql -d mydatabase -f $GPHOME/share/postgresql/pljava/install.sql.
    20150915:18:22:33:002662 gppkg:smdw:gpadmin-[INFO]:-pljava-ossv1.4.0_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg successfully installed.
  • If you now try to run the install.sql script you will be treated with the below error message 

    [gpadmin@smdw bhonohan]$ psql -d <database-name> -f $GPHOME/share/postgresql/pljava/install.sql
  • psql:/usr/local/GP-4.3.5.3/share/postgresql/pljava/install.sql:1: ERROR:  could not load library "/usr/local/GP-4.3.5.3/lib/postgresql/pljava.so": libjvm.so: cannot open shared object file: No such file or directory
    psql:/usr/local/GP-4.3.5.3/share/postgresql/pljava/install.sql:2: ERROR:  could not load library "/usr/local/GP-4.3.5.3/lib/postgresql/pljava.so": libjvm.so: cannot open shared object file: No such file or directory
    psql:/usr/local/GP-4.3.5.3/share/postgresql/pljava/install.sql:3: ERROR:  function pljava_call_handler() does not exist
    psql:/usr/local/GP-4.3.5.3/share/postgresql/pljava/install.sql:4: ERROR:  function pljavau_call_handler() does not exist 
  • Restart the database

    [gpadmin@smdw bhonohan]$ gpstop -afr
    20150915:18:37:39:003164 gpstop:smdw:gpadmin-[INFO]:-Starting gpstop with args: -afr
    20150915:18:37:39:003164 gpstop:smdw:gpadmin-[INFO]:-Gathering information and validating the environment...
    [......]
    [......] 20150915:18:38:02:003164 gpstop:smdw:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20150915:18:38:02:003164 gpstop:smdw:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20150915:18:38:02:003164 gpstop:smdw:gpadmin-[INFO]:-Cleaning up leftover shared memory 20150915:18:38:07:003164 gpstop:smdw:gpadmin-[INFO]:-Restarting System.. 
  • Rerun the install.sql again

    [gpadmin@smdw bhonohan]$ !psql:p
    psql -d <database-name> -f $GPHOME/share/postgresql/pljava/install.sql
    [gpadmin@smdw bhonohan]$ psql -d <database-name>  -f $GPHOME/share/postgresql/pljava/install.sql>
    CREATE FUNCTION
    CREATE FUNCTION
    CREATE LANGUAGE
    CREATE LANGUAGE
    

Post Installation

  • If you have custom jar files, Copy your Java archives (JAR files) to $GPHOME/lib/postgresql/java/ on to all GPDB hosts.

    NOTE: This example uses GPDB gpscp utility to copy the file myclasses.jar:

    $ gpscp -f all_hosts myclasses.jar =:/usr/local/greenplum-db/lib/postgresql/java/

    The file all_hosts contains a list of the GPDB hosts.

  • Set the pljavaclasspath server configuration parameter in the master postgresql.conf file.

    The parameter value is a colon (:) separated list of the JAR files containing the Java classes used in any PL/Java functions.

    For example: 


    $ gpconfig -c pljava_classpath -v \'examples.jar:myclasses.jar\' --masteronly
    
  • Restart the database again

    [gpadmin@smdw honohb-1]$ gpstop -afr
    20150915:18:44:09:003698 gpstop:smdw:gpadmin-[INFO]:-Starting gpstop with args: -afr
    20150915:18:44:09:003698 gpstop:smdw:gpadmin-[INFO]:-Gathering information and validating the environment...
    [......]
    [......] 20150915:18:44:31:003698 gpstop:smdw:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20150915:18:44:31:003698 gpstop:smdw:gpadmin-[INFO]:-Cleaning up leftover shared memory 20150915:18:44:37:003698 gpstop:smdw:gpadmin-[INFO]:-Restarting System... [gpadmin@smdw honohb-1]$

Optional

GPDB provides an examples.sql file containing sample PL/Java functions that you can use for testing.

  • Run the commands in this file to create the test functions (which use the Java classes in examples.jar).

    [gpadmin@smdw honohb-1]$ psql -f $GPHOME/share/postgresql/pljava/examples.sql -d <database-name>
    psql:/usr/local/GP-4.3.5.3/share/postgresql/pljava/examples.sql:1: ERROR:  schema "javatest" does not exist
    CREATE SCHEMA
    SET
    SET
    SET
    SELECT 1
    CREATE FUNCTION
        java_gettimestamp
    -------------------------
     2015-09-15 18:45:03.062
    (1 row)
    
    [......] [......]
    makearray ----------- {1} (1 row) 
  • Running some test example
  • d1=# \df javatest.print
                                 List of functions
      Schema  | Name  |  Result data type  |   Argument data types    |  Type
    ----------+-------+--------------------+--------------------------+--------
     javatest | print | bigint             | bigint                   | normal
     javatest | print | bigint[]           | bigint[]                 | normal
     javatest | print | bytea              | bytea                    | normal
     javatest | print | "char"             | "char"                   | normal
     javatest | print | void               | date                     | normal
     javatest | print | double precision   | double precision         | normal
     javatest | print | double precision[] | double precision[]       | normal
     javatest | print | integer            | integer                  | normal
     javatest | print | integer[]          | integer[]                | normal
     javatest | print | real               | real                     | normal
     javatest | print | real[]             | real[]                   | normal
     javatest | print | smallint           | smallint                 | normal
     javatest | print | smallint[]         | smallint[]               | normal
     javatest | print | void               | timestamp with time zone | normal
     javatest | print | void               | time with time zone      | normal
    (15 rows)
    
    d1=# select javatest.print(123);
     print
    -------
       123
    (1 row)
    
    d1=# \df javatest.java_gettimestamptz
                                        List of functions
      Schema  |        Name         |     Result data type     | Argument data types |  Type
    ----------+---------------------+--------------------------+---------------------+--------
     javatest | java_gettimestamptz | timestamp with time zone |                     | normal
    (1 row)
    
    d1=# select javatest.java_gettimestamptz();
        java_gettimestamptz
    ----------------------------
     2015-09-15 19:28:18.798+00
    (1 row)
    
    d1=#
  • When you enable the PL/Java Extension in the template1 database, the PL/Java Extension is enabled on any new database.

    $ psql template1 -f $GPHOME/share/postgresql/pljava/install.sql

Additional Information

If you need help downloading Pivotal products, refer to the article: Where to download Pivotal Greenplum (GPDB) Software or Pivotal 

Comments

  • Avatar
    Krzysztof Byszewski

    If you don't put a path in postgresql.conf
    $ gpconfig -c pljava_classpath -v \'examples.jar:myclasses.jar\' --masteronly

    you will get error like this:
    prprodjsq=# select javatest.print(123);
    ERROR: java.lang.ClassNotFoundException: org.postgresql.example.Parameters (JNICalls.c:70)
    prprodjsq=# \df javatest.java_gettimestamptz
    List of functions
    Schema | Name | Result data type | Argument data types | Type
    ----------+---------------------+--------------------------+---------------------+--------
    javatest | java_gettimestamptz | timestamp with time zone | | normal
    (1 row)

    prprodjsq=# select javatest.java_gettimestamptz();
    ERROR: java.lang.ClassNotFoundException: org.postgresql.example.Parameters (JNICalls.c:70)

    But this is still ok, it's just an example, the language is installed correctly

Powered by Zendesk