Compound procedures are a special type of procedure that let you extend the capabilities of the database server. Compound procedures are not transactional. However, they do let you chain multiple transactional stored procedures together, define branching and error handling, along with any additional programming logic your application needs. In other words, they let you automate complex business processes into a single callable procedure.
The primary use for compound procedures is processing inbound topics, where the topic invokes a procedure. At times the business logic for adding intelligence to the stream requires more than just a single transaction. (For example, the topic record's contents need to be written to two separately partitioned tables.) Compound procedures provide the ability to add logic similar to that used by a client application, except executed automatically on the cluster.
The following sections explain what compound procedures are, how they work, as well as how to create, declare, and invoke them. A final section covers special considerations around error handling and performance.
Regular VoltDB stored procedures are transactional. That is, the procedure, whether single- or multi-partitioned, succeeds or rolls back as a whole. Combined with single-threaded, non-locking execution engines, partitioned procedures allow VoltDB to provide best-in-class throughput while guaranteeing reliability and consistency.
However, not all business logic fits within a single stored procedure. For example, an application might need to accept messages from IoT devices, look up the device ID to find out which account it is assigned to, associate the message with the account ID, record the usage, and pass the enhanced message along to other applications. In this case, the device records are likely to be partitioned on the device ID while account records are partitioned on the account ID — different partitions.
Now, it is possible to complete the entire interaction in one multi-partitioned procedure. However, this is not recommended, since it will block all other transactions until it finishes. The alternative is for the application to call two or more partitioned procedures to perform the device and account lookups and then record the final usage. But when automating streams with VoltDB topics, there is no application to link the transactions together. Instead, a single procedure is associated with the inbound topic as part of the topic declaration in the configuration. With compound procedures, it is now possible to include the necessary business logic in a single compound procedure, which can be invoked by the inbound topic.
The process for creating a compound procedure is:
Define the procedure as a Java class that extends
VoltCompoundProcedure.
Compile and Load the procedure by compiling the Java source file, packaging it in a JAR file (just as you would transactional procedures), and loading it into your database using the sqlcmd LOAD CLASSES directive.
Declare the procedure in your schema using the CREATE COMPOUND PROCEDURE FROM CLASS statement.
Invoke the procedure by associating it with an inbound topic in the configuration, or the procedure can be called from a client application.
The following sections describe each of these step separately.
Documentation