2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Greenplum3 / Using PL/Java in Greenplum
Carlo Ascani

Using PL/Java in Greenplum

September 16, 2011/1 Comment/in Greenplum /by Carlo Ascani

In this article we are going to show you how to write PL/Java functions in Greenplum.
I assume that you have a working Greenplum (or Greenplum Community Edition) at your disposal. In this example we will use version **4.0.4**, installed in /usr/local/greenplum-db-4.0.4.0 (which is the default location).


# Install PL/Java
In order to use PL/Java in a Greenplum Database, you must install **JDK 1.6** on every host (master and segments) in your Greenplum Database cluster. In case of Community Edition, you only have one host.
In this example we will use **OpenJDK 1.6** on a **CentOS 5** server running [Greenplum Community Edition](http://www.greenplum.com/community/), which can be used for free on a single host (refer to the Greenplum website for licensing information).


# yum install java-1.6.0-openjdk.x86_64

Make sure that the JAVA_HOME environment variable is set up for the gpadmin user on all hosts.
In our example, I have used:


$ export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64

in gpadmin’s .bashrc file.
Then put the Java libraries in the library path.
On Linux you can create a file containing the right path under
/etc/ld.so.conf.d/ directory and run ldconfig.
So execute, as root:


# echo "$JAVA_HOME/jre/lib/amd64/server" > /etc/ld.so.conf.d/libjdk.conf
# ldconfig

# Enable PL/Java in Greenplum
Run the install.sql file shipped with Greenplum to enable PL/Java in the given database.
In a default installation, it is placed in /usr/local/greenplum-db-4.0.4.0/share/postgresql/pljava directory.
**Important**: you have to run this script as a database superuser!


$ psql -U gpadmin -f /usr/local/greenplum-db/share/postgresql/pljava/install.sql pljavatestdb
CREATE FUNCTION
CREATE FUNCTION
CREATE LANGUAGE
CREATE LANGUAGE

In order to uninstall PL/Java, you can use an SQL script file called uninstall.sql:


$ psql -U gpadmin -f /usr/local/greenplum-db/share/postgresql/pljava/uninstall.sql pljavatestdb
DROP LANGUAGE
DROP LANGUAGE

# The real thing: hello world!
We are ready to make a real – but simple – PL/Java function.
As far as this article is concerned, we will work in a temporary directory (for production environments we recommend that you use GIT or any other source control technology).
*Step 1*: Make a temporary directory and enter inside


$ cd `mktemp -d`

*Step 2*: Write your Java function in a file called HelloWorld.java, containing the following lines of code:


package mypkg;
public class HelloWorld
{
static java.lang.String say_hello()
{
return "Hello World";
}
}

As you can read, the function we have written simply returns “Hello World” as a java.lang.String, that is mapped in Greenplum (and PostgreSQL) as a TEXT.
*Step 3*: compile HelloWorld.java and create a *jar* of it, named HelloWorld.jar:


$ javac HelloWorld.java
$ jar cvf HelloWorld.jar HelloWorld.class

*Step 4*: copy HelloWorld.jar in the defult Greenplum classpath, and setup the pljava_classpath configuration option (in this example, we will perform it only for our session using set_config, for permanent changes set it in the master’s postgresql.conf file):


$ cp HelloWorld.jar /usr/local/greenplum-db-4.0.4.0/lib/postgresql/java/
$ psql -U gpadmin postgres
psql (8.2.14)
Type "help" for help.
postgres=# SELECT set_config('pljava_classpath', 'HelloWorld.jar', false);
set_config
----------------
HelloWorld.jar
(1 row)

*Step 5*: Now it is time to add the actual PL/Java function inside the database:


pljavatestdb=# CREATE FUNCTION say_hello()
pljavatestdb-# RETURNS TEXT
pljavatestdb-# AS 'mypkg.HelloWorld.say_hello'
pljavatestdb-# LANGUAGE java;
CREATE FUNCTION

Great! You can execute it by simply calling:


pljavatestdb=# SELECT say_hello();
say_hello
-------------
Hello World
(1 row)

**WARNING**
If you encounter any kind of heap memory error, I advice to add the pljava_vmoptions='-Xmx256m' option in postgres configuration file and set that to a resonable value for your goals.
That option sets the heap memory size for the Java Virtual Machine.
Obviously, you can organize your classes in packages, simply remember to specify the correct path in the AS 'path.to.function' row.
We encourage you to evaluate Greenplum Community Edition and [participate to the forum](http://www.greenplum.com/community/forums/), where Greenplum experts can help you getting started with it.

Tags: greenplum, pl/java, postgres, PostgreSQL
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. newbie_user
    newbie_user says:
    June 16, 2012 at 1:06 am

    Hi,

    Great tutorial!

    I tried your example, but I’m getting a ERROR: java.lang.ClassNotFoundException: mypkg.HelloWorld (JNICalls.c:70)

    Do you have any tips on how I can solve this?

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
ETL with Talend and Greenplum – Part one: connections ETL with Talend and Greenplum – Part two: data import
Scroll to top
×