• Greenplum Database does not support PL/Python triggers.
• PL/Python is available only as a Greenplum Database untrusted language.
• Updatable cursors (UPDATE...WHERE CURRENT OF and DELETE...WHERE CURRENT OF) are not supported.

For each database that requires its use, register the PL/Python language with the SQL command CREATE LANGUAGE or the Greenplum Database utility createlang. Because PL/Python is an untrusted language, only superusers can register PL/Python with a database. For example, running this command as the gpadmin system user registers PL/Python with the database named testdb:

$ createlang plpythonu -d testdb

PL/Python is registered as an untrusted language.

For a database that no longer requires the PL/Python language, remove support for PL/Python with the SQL command DROP LANGUAGE or the Greenplum Database droplang utility. Because PL/Python is an untrusted language, only superusers can remove support for the PL/Python language from a database. For example, running this command as the gpadmin system user removes support for PL/Python from the database named testdb:

$ droplang plpythonu -d testdb

When you remove support for PL/Python, the PL/Python user-defined functions that you created in the database will no longer work.

You pass SQL array values into PL/Python functions with a Python list. Similarly, PL/Python functions return SQL array values as a Python list. In the typical PL/Python usage pattern, you will specify an array with [].

The following example creates a PL/Python function that returns an array of integers:

CREATE FUNCTION return_py_int_array()
  RETURNS int[]
AS $$
  return [1, 11, 21, 31]
$$ LANGUAGE plpythonu;

SELECT return_py_int_array();
 return_py_int_array 
---------------------
 {1,11,21,31}
(1 row) 

PL/Python treats multi-dimensional arrays as lists of lists. You pass a multi-dimensional array to a PL/Python function using nested Python lists. When a PL/Python function returns a multi-dimensional array, the inner lists at each level must all be of the same size.

The following example creates a PL/Python function that takes a multi-dimensional array of integers as input. The function displays the type of the provided argument, and returns the multi-dimensional array:

CREATE FUNCTION return_multidim_py_array(x int4[]) 
  RETURNS int4[]
AS $$
  plpy.info(x, type(x))
  return x
$$ LANGUAGE plpythonu;

SELECT * FROM return_multidim_py_array(ARRAY[[1,2,3], [4,5,6]]);
INFO:  ([[1, 2, 3], [4, 5, 6]], <type 'list'>)
CONTEXT:  PL/Python function "return_multidim_py_type"
 return_multidim_py_array 
--------------------------
 {{1,2,3},{4,5,6}}
(1 row) 

PL/Python also accepts other Python sequences, such as tuples, as function arguments for backwards compatibility with Greenplum versions where multi-dimensional arrays were not supported. In such cases, the Python sequences are always treated as one-dimensional arrays because they are ambiguous with composite types.

You pass composite-type arguments to a PL/Python function using Python mappings. The element names of the mapping are the attribute names of the composite types. If an attribute has the null value, its mapping value is None.

You can return a composite type result as a sequence type (tuple or list). You must specify a composite type as a tuple, rather than a list, when it is used in a multi-dimensional array. You cannot return an array of composite types as a list because it would be ambiguous to determine whether the list represents a composite type or another array dimension. In the typical usage pattern, you will specify composite type tuples with ().

In the following example, you create a composite type and a PL/Python function that returns an array of the composite type:

CREATE TYPE type_record AS (
  first text,
  second int4
);

CREATE FUNCTION composite_type_as_list()
  RETURNS type_record[]
AS $$              
  return [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)], [('first', 3), ('second', 3)]];
$$ LANGUAGE plpythonu;

SELECT * FROM composite_type_as_list();
                               composite_type_as_list                           
------------------------------------------------------------------------------------
 {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"},{"(first,3)","(second,3)"}}
(1 row) 

Refer to the PostgreSQL Arrays, Lists documentation for additional information on PL/Python handling of arrays and composite types.

The PL/Python plpy module provides two Python functions to execute an SQL query and prepare an execution plan for a query, plpy.execute and plpy.prepare. Preparing the execution plan for a query is useful if you run the query from multiple Python functions.

PL/Python also supports the plpy.subtransaction() function to help manage plpy.execute calls in an explicit subtransaction. See Explicit Subtransactions in the PostgreSQL documentation for additional information about plpy.subtransaction().

The Python module plpy implements these functions to manage errors and messages:

• plpy.debug
• plpy.log
• plpy.info
• plpy.notice
• plpy.warning
• plpy.error
• plpy.fatal
• plpy.debug

The message functions plpy.error and plpy.fatal raise a Python exception which, if uncaught, propagates out to the calling query, causing the current transaction or subtransaction to be aborted. The functions raise plpy.ERROR(msg) and raise plpy.FATAL(msg) are equivalent to calling plpy.error and plpy.fatal, respectively. The other message functions only generate messages of different priority levels.

Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the Greenplum Database server configuration parameters log_min_messages and client_min_messages. For information about the parameters see the Greenplum Database Reference Guide.

In terms of performance, importing a Python module is an expensive operation and can affect performance. If you are importing the same module frequently, you can use Python global variables to load the module on the first invocation and not require importing the module on subsequent calls. The following PL/Python function uses the GD persistent storage dictionary to avoid importing a module if it has already been imported and is in the GD.

psql=#
   CREATE FUNCTION pytest() returns text as $$ 
      if 'mymodule' not in GD:
        import mymodule
        GD['mymodule'] = mymodule
    return GD['mymodule'].sumd([1,2,3])
$$;

This example manually installs the Python setuptools module from the Python Package Index repository. The module lets you easily download, build, install, upgrade, and uninstall Python packages.

This example first builds the module from a package and installs the module on a single host. Then the module is built and installed on segment hosts.

1. Get the module package from the Python Package Index site. For example, run this wget command on a Greenplum Database host as the gpadmin user to get the tar file.

   wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-18.4.tar.gz

2. Extract the files from the tar file.

   tar -xzvf distribute-0.6.21.tar.gz

3. Go to the directory that contains the package files and run the Python scripts to build and install the Python package.

   cd setuptools-18.4
   python setup.py build && python setup.py install

4. The following Python command returns no errors if the module is available to Python.

   python -c "import setuptools"

5. Copy the package to the Greenplum Database hosts with the gpscp utility. For example, this command copies the tar file from the current host to the host systems listed in the file remote-hosts.

   gpscp -f remote-hosts setuptools-18.4.tar.gz =:/home/gpadmin

6. Run the commands to build, install, and test the package with gpssh utility on the hosts listed in the file remote-hosts. The file remote-hosts lists all the remote Greenplum Database segment hosts:

   gpssh -f remote_hosts
   >>> tar -xzvf distribute-0.6.21.tar.gz
   >>> cd setuptools-18.4
   >>> python setup.py build && python setup.py install
   >>> python -c "import setuptools"
   >>> exit

easy_install pip

You can use the gpssh utility to run the easy-install command on all the Greenplum Database segment hosts.

This example builds and installs the Python module NumPy. NumPy is a module for scientific computing with Python. For information about NumPy, see http://www.numpy.org/.

This example process assumes yum is installed on the Greenplum Database segment hosts and the gpadmin user is a member of sudoers with root privileges on the hosts.

wget --directory-prefix=packages http://github.com/xianyi/OpenBLAS/tarball/v0.2.8
wget --directory-prefix=packages http://sourceforge.net/projects/numpy/files/NumPy/1.8.0/numpy-1.8.0.tar.gz/download

gpssh -f gpdb_remotes mkdir packages 
gpscp -f gpdb_remotes packages/* =:/home/gpadmin/packages

Before build and install the NumPy module, you install the OpenBLAS libraries. This section describes how to build and install the libraries on a single host.

1. Extract the OpenBLAS files from the file. These commands extract the files from the OpenBLAS tar file and simplify the directory name that contains the OpenBLAS files.

   tar -xzf packages/v0.2.8 -C /home/gpadmin/packages
   mv /home/gpadmin/packages/xianyi-OpenBLAS-9c51cdf /home/gpadmin/packages/OpenBLAS

2. Compile OpenBLAS. These commands set the LIBRARY_PATH environment variable and run the make command to build OpenBLAS libraries.

   cd /home/gpadmin/packages/OpenBLAS
   export LIBRARY_PATH=$LD_LIBRARY_PATH
   make FC=gfortran USE_THREAD=0

3. These commands install the OpenBLAS libraries in /usr/local as root and change the owner of the files to gpadmin.

   cd /home/gpadmin/packages/OpenBLAS/
   sudo make PREFIX=/usr/local install
   sudo ldconfig
   sudo chown -R gpadmin /usr/local/lib

libopenblas.a -> libopenblas_sandybridge-r0.2.8.a
libopenblas_sandybridge-r0.2.8.a
libopenblas_sandybridge-r0.2.8.so
libopenblas.so -> libopenblas_sandybridge-r0.2.8.so
libopenblas.so.0 -> libopenblas_sandybridge-r0.2.8.so

You can use the gpssh utility to build and install the OpenBLAS libraries on multiple hosts.

All the Greenplum Database hosts (master and segment hosts) have identical configurations. You can copy the OpenBLAS libraries from the system where they were built instead of building the OpenBlas libraries on all the hosts. For example, these gpssh and gpscp commands copy and install the OpenBLAS libraries on the hosts listed in the gpdb_remotes file.

gpssh -f gpdb_remotes -e 'sudo yum -y install gcc gcc-gfortran gcc-c++'
gpssh -f gpdb_remotes -e 'ln -s /usr/bin/g++ /usr/bin/gxx'
gpssh -f gpdb_remotes -e sudo chown gpadmin /usr/local/lib
gpscp -f gpdb_remotes /usr/local/lib/libopen*sandy* =:/usr/local/lib

gpssh -f gpdb_remotes
>>> cd /usr/local/lib
>>> ln -s libopenblas_sandybridge-r0.2.8.a libopenblas.a
>>> ln -s libopenblas_sandybridge-r0.2.8.so libopenblas.so
>>> ln -s libopenblas_sandybridge-r0.2.8.so libopenblas.so.0
>>> sudo ldconfig

After you have installed the OpenBLAS libraries, you can build and install NumPy module. These steps install the NumPy module on a single host. You can use the gpssh utility to build and install the NumPy module on multiple hosts.

1. Go to the packages subdirectory and get the NumPy module source and extract the files.

   cd /home/gpadmin/packages
   tar -xzf numpy-1.8.0.tar.gz

2. Set up the environment for building and installing NumPy.

   export BLAS=/usr/local/lib/libopenblas.a
   export LAPACK=/usr/local/lib/libopenblas.a
   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib/
   export LIBRARY_PATH=$LD_LIBRARY_PATH

3. Go to the NumPy directory and build and install NumPy. Building the NumPy package might take some time.

   cd numpy-1.8.0
   python setup.py build
   python setup.py install

   Note: If the NumPy module did not successfully build, the NumPy build process might need a site.cfg that specifies the location of the OpenBLAS libraries. Create the file site.cfg in the NumPy package directory:

   cd ~/packages/numpy-1.8.0
   touch site.cfg

   Add the following to the site.cfg file and run the NumPy build command again:

   [default]
   library_dirs = /usr/local/lib
   
   [atlas]
   atlas_libs = openblas
   library_dirs = /usr/local/lib
   
   [lapack]
   lapack_libs = openblas
   library_dirs = /usr/local/lib
   
   # added for scikit-learn 
   [openblas]
   libraries = openblas
   library_dirs = /usr/local/lib
   include_dirs = /usr/local/include

4. The following Python command ensures that the module is available for import by Python on a host system.

   python -c "import numpy"

As in the simple module installation, you can use the gpssh utility to build, install, and test the module on Greenplum Database segment hosts.

echo -e '\n#Needed for NumPy' >> ~/.bashrc
echo -e 'export BLAS=/usr/local/lib/libopenblas.a' >> ~/.bashrc
echo -e 'export LAPACK=/usr/local/lib/libopenblas.a' >> ~/.bashrc
echo -e 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib' >> ~/.bashrc
echo -e 'export LIBRARY_PATH=$LD_LIBRARY_PATH' >> ~/.bashrc

You can create a simple PL/Python user-defined function (UDF) to validate that Python a module is available in the Greenplum Database. This example tests the NumPy module.

This PL/Python UDF imports the NumPy module. The function returns SUCCESS if the module is imported, and FAILURE if an import error occurs.

CREATE OR REPLACE FUNCTION plpy_test(x int)
returns text
as $$
  try:
      from numpy import *
      return 'SUCCESS'
  except ImportError, e:
      return 'FAILURE'
$$ language plpythonu;

Create a table that contains data on each Greenplum Database segment instance. Depending on the size of your Greenplum Database installation, you might need to generate more data to ensure data is distributed to all segment instances.

CREATE TABLE DIST AS (SELECT x FROM generate_series(1,50) x ) DISTRIBUTED RANDOMLY ;

This SELECT command runs the UDF on the segment hosts where data is stored in the primary segment instances.

SELECT gp_segment_id, plpy_test(x) AS status
  FROM dist
  GROUP BY gp_segment_id, status
  ORDER BY gp_segment_id, status;

The SELECT command returns SUCCESS if the UDF imported the Python module on the Greenplum Database segment instance. If the SELECT command returns FAILURE, you can find the segment host of the segment instance host. The Greenplum Database system table gp_segment_configuration contains information about mirroring and segment configuration. This command returns the host name for a segment ID.

SELECT hostname, content AS seg_ID FROM gp_segment_configuration
  WHERE content = seg_id ;

If FAILURE is returned, these are some possible causes:

• A problem accessing required libraries. For the NumPy example, a Greenplum Database might have a problem accessing the OpenBLAS libraries or the Python libraries on a segment host.
  Make sure you get no errors when running command on the segment host as the gpadmin user. This gpssh command tests importing the numpy module on the segment host mdw1.

  gpssh -h mdw1 python -c "import numpy"

• If the Python import command does not return an error, environment variables might not be configured in the Greenplum Database environment. For example, the variables are not in the .bashrc file, or Greenplum Database might not have been restarted after adding the environment variables to the .bashrc file.

  Ensure sure that the environment variables are properly set and then restart the Greenplum Database. For the NumPy example, ensure the environment variables listed at the end of the section Build and Install NumPy are defined in the .bashrc file for the gpadmin user on the master and segment hosts.

  Note: On the Greenplum Database master and segment hosts, the .bashrc file for the gpadmin user must source the file $GPHOME/greenplum_path.sh.

For information about the Python language, see https://www.python.org/.

For information about PL/Python see the PostgreSQL documentation at https://www.postgresql.org/docs/8.3/static/plpython.html.

For information about Python Package Index (PyPI), see https://pypi.python.org/pypi.