2ndQuadrant | PostgreSQL
PostgreSQL Solutions for the Enterprise
+39 0574 159 3000
  • Contact Us
  • EN
    • FR
    • IT
    • ES
    • DE
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
      • IBM Z Production Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
      • Kubernetes for Postgres and BDR
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • PostgreSQL with High Availability
    • BDR
    • 2ndQPostgres
    • pglogical
      • Installation instruction for pglogical
      • Documentation
    • repmgr
    • Barman
    • Postgres Cloud Manager
    • SQL Firewall
    • Postgres-XL
    • OmniDB
    • Postgres Installer
    • 2UDA
  • Downloads
    • Postgres Installer
    • 2UDA – Unified Data Analytics
  • Postgres Learning Center
    • Webinars
      • You forgot to put the WHERE in DELETE?
      • BDR Overview
    • Whitepapers
      • Highly Available Postgres Clusters
      • AlwaysOn Postgres
      • BDR
      • PostgreSQL Security Best Practices
    • 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
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Training
      • Training Catalog and Scheduled Courses
        • Advanced Development & Performance
        • Linux for PostgreSQL DBAs
        • BDR
        • PostgreSQL Database Administration
        • PostgreSQL Data Warehousing & Partitioning
        • PostgreSQL for Developers
        • PostgreSQL Immersion
        • PostgreSQL Immersion for Cloud Databases
        • PostgreSQL Security
        • Postgres-XL-10
        • Practical SQL
        • Replication, Backup & Disaster Recovery
        • Introduction to PostgreSQL and Kubernetes
    • 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
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • Business Case for PostgreSQL
      • Security Information
    • Events
    • Blog
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • Ask Simon
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu
You are here: Home / Blog / Greenplum / 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

Recent Posts

  • Setting SSL/TLS protocol versions with PostgreSQL 12 November 27, 2019
  • Webinar: Using SSL with PostgreSQL and pgbouncer [Follow Up] November 14, 2019
  • PostgreSQL 12: Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes November 5, 2019
  • Webinar: PostgreSQL Partitioning [Follow up] October 28, 2019
  • Postgres-BDR: It is also about fast safe upgrades October 15, 2019

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 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB kanban logical decoding logical replication monitoring open source performance PG12 pgbarman pgday pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL 11 PostgreSQL11 PostgreSQL 11 New Features postgresql repmgr Recovery release replication sql standby wal webinar
UK +44 (0)870 766 7756

US +1 650 378 1218

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

©2001-2019 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
×