Native UDFs for PostgreSQL and MariaDB¶

Native UDFs for PostgreSQL and MariaDB
- Introduction
- Setup

Introduction¶

Starting with FWD v4, PL/Java User Defined Functions (UDFs) are no longer supported. Instead of Java as a procedure language, database-native UDFs are used. These "Native UDFs" are written in SQL and/or a database-specific procedure language such as PL/pgSQL. The primary reasons for this are ease of deployment and customer or cloud infrastructure restrictions on running Java within the database. For example, cloud database services such as Amazon RDS, Amazon Aurora or SkySQL do not provide an ability to run Java as a procedure language.

Setup¶

The following changes are required to use the native UDFs:

FWD Build¶

The SQL scripts for the native UDFs are located in the udf/<dialect> subfolder of the FWD project (e.g., udf/postgresql, udf/mariadb). This subfolder contains:

udfs.sql - the SQL script with the definition of most UDFs (hand-written).
guarded.sql - the guarded versions of the UDFs from udfs.sql (for handling errors when UDFs are used in CAN-FIND). Generated from udfs.sql by guard.awk AWK script during the FWD build.
error_handler.sql - UDFs to support handling errors when UDFs are used in CAN-FIND
getFWDVersion.sql - the template for the getFWDVersion() UDF.

These scripts cannot be used unmodified. During the FWD build, the FWD version is injected into the getFWDVersion.sql and a single SQL script udf/<dialect>/udfs.sql is created and added to the p2j.jar.

General Information¶

For PostgreSQL the native UDFs are located in a separate udf schema. This allows having both native and Java UDFs in the database and switching between them by changing a single setting in the directory.xml. However, the continued use of Java UDFs is only meant for developer testing and is no longer supported in user deployments, starting with FWD v4 (discussed in the For Developer Testing Only section below).

For MariaDB the UDFs are located in the same database all other objects (tables, sequences, etc...).

The udfs.sql script is the same for all applications and all that is needed is to apply it to the target database with any SQL client, such as psql; however, the preferred method is to use the ScriptRunner utility (see the Database Creation section).

Normally udfs.sql is deployed during the database creation. However, this script is idempotent and can be applied multiple times if required. This can happen e.g. if some UDFs were occasionally dropped or corrupted or as a part of the maintenance procedure to apply UDFs changes w/o re-creating the database. Formally it can done even without stopping/restarting the application(s) using the database. However, it is not recommended to do it on-the-fly when the application is in use. This can be done using any database client connected as fwd_user just like any SQL script.

If the FWD server does not find getFWDVersion UDF during startup it automatically applies udfs.sql to the target database.

The udf.sql script can be manually extracted from the p2j.jar using

jar xvf p2j.jar  udf/<dialect>/udfs.sql

where dialect is either postgresql or mariadb. Please note that jar extracts the udf.sql script to the udf/<dialect> directory.

Please note that for PostgreSQL the UDFs required for the CONTAINS operator support (word tables) are located in a separate file which comes in 3 versions -

words-udfs-sql.sql - to work with native UDFs only
words-udfs-java.sql - to work with Java UDFs only
words-udfs-dual.sql - to work both with Java and native UDFs. Which version will be used can be configured in the directory.xml.

At the moment of writing word tables support is not yet implemented for MariaDB.

Database Creation¶

As mentioned in the previous section, the udfs.sql script can be applied to the target database using any database client which can run SQL scripts. The script can be extracted from the p2j.jar as described in the previus section. However, the preferred way to apply the script is to use the helper class com.goldencode.p2j.persist.deploy.ScriptRunner (which is also contained in the p2j.jar). Its main method accepts 4 arguments: [<db url>, <user>, <password>, <command>].

At the time of writing, the following commands are accepted:

udf.install - installs the UDFs. The ScriptRunner extracts /udf/<dialect>/udfs.sql script from its classpath, splits it to separate DDL statements and executes them one by one.
udf.install.search_path - installs the UDFs the same as udf.install. Additionally, this command sets the search path to allow unqualified references to UDFs in SQL statements. (for PostgreSQL only)

For PostgreSQL the normal deployments should always use udf.install.search_path. udf.install is only meant for developer testing use. For MariaDB udf.install.search_path is a synonym for udf.install

For example, one can add the following target to the Ant script if such script is used for the database creation and call this target from the appropriate place:

   <target name="sql.udfs" if="${db.sql.udfs}">
      <record name="sql_udfs_pg_${db.name}_${LOG_STAMP}.log" action="start"/>
      <java classname="com.goldencode.p2j.persist.deploy.ScriptRunner" 
            fork="true" 
            failonerror="true" 
            dir="${basedir}" >
         <classpath refid="app.classpath"/>
         <jvmarg value="-Xmx1g"/>
         <jvmarg value="-Dfile.encoding=UTF-8"/>
         <jvmarg value="-Djava.util.logging.config.file=${p2j.home}/cfg/logging.properties"/>
         <arg value ="${sql.url.pg}"/>
         <arg value ="${sql.admin}"/>
         <arg value ="${sql.admin.pass}"/>
         <arg value ="udf.install.search_path"/>
      </java>
      <record name="sql_udfs_pg_${db.name}_${LOG_STAMP}.log" action="stop"/>
   </target>

Please note, the the ScriptRunner does not extract udfs.sql to the file system, it reads and execute it directly from the p2j.jar.

How to Continue Using PL/Java¶

For PostgreSQL ScriptRunner deploys words-udfs-sql.sql by default. To deploy e.g. words-udfs-dual.sql instead use udf.install.search_path udfs.sql words-udfs-dual.sql

For FWD development usage usage only. The configuration described in this section is neither intended to apply to nor to be supported for normal deployments. Native UDFs are the only supported form of UDFs, starting with FWD v4. See Java UDF Support for the description of the Java UDFs setup.

Word table index support presents a complication for the dual-mode feature. The word tables' triggers use words UDFs (with different signatures). To generate "dual-mode" triggers which will use Java or native UDFs depending on runtime configuration, the conversion should be aware of UDF type(s) to be supported. For this reason, support was added for optional configuration within the dialect-specific element for postgresql in p2j.cfg:

        <dialect-specific name="postgresql">
            <parameter name="udf" value="sql,java" />
         </dialect-specific>

The parameter udf can contain a list of UDF types for the database. If both java and sql are specified words UDFs used in the word tables' triggers will switch between Java and SQL low-level UDFs based on the runtime configuration. If only one value is specified, these UDFs will use the only specified types of UDFs regardless of the runtime configuration.

The udf parameter should not be configured at all for normal deployments, since sql is the default.

By default at runtime, native UDFs will be used. To switch to Java UDFs, add

              <node class="boolean" name="use_java_udfs">
                <node-attribute name="value" value="TRUE"/>
              </node>

to the p2j subnode of the database configuration in the directory.xml (alongside the schema subnode).

Project

General

Profile

FWD

Wiki