Handling Errors

If your UDx encounters some sort of error, it can report it back to Vertica using the vt_report_error macro. When called, this macro halts the execution of the UDx and causes the statement that called the function to fail. The macro takes two parameters: an error number and an error message string. Both the error number and message appear in the error that Vertica reports to the user. The error number is not defined by Vertica. You can use whatever value that you wish.

For example, the following ScalarFunction class divides two integers. To prevent division by zero, it tests the second parameter. If it is zero, the function reports the error back to Vertica.

/*
 * Demonstrate reporting an error
 */
class Div2ints : public ScalarFunction
{
public:
  virtual void processBlock(ServerInterface &srvInterface,
                            BlockReader &arg_reader,
                            BlockWriter &res_writer)
  {
    // While we have inputs to process
    do
      {
        const vint a = arg_reader.getIntRef(0);
        const vint b = arg_reader.getIntRef(1);
        if (b == 0)
          {
            vt_report_error(1,"Attempted divide by zero");
          }
        res_writer.setInt(a/b);
        res_writer.next();
      }
    while (arg_reader.next());
  }
};

Loading and invoking the function demonstrates how the error appears to the user.

=> CREATE LIBRARY Div2IntsLib AS '/home/dbadmin/Div2ints.so';
CREATE LIBRARY
=> CREATE FUNCTION div2ints AS LANGUAGE 'C++' NAME 'Div2intsInfo' LIBRARY Div2IntsLib;
CREATE FUNCTION
=> SELECT div2ints(25, 5);
 div2ints
----------
        5
(1 row)
=> SELECT * FROM MyTable;
 a  | b
----+---
 12 | 6
  7 | 0
 12 | 2
 18 | 9
(4 rows)
=> SELECT * FROM MyTable WHERE div2ints(a, b) > 2;
ERROR:  Error in calling processBlock() for User Defined Scalar Function 
div2ints at Div2ints.cpp:21, error code: 1, message: Attempted divide by zero

Your function must not allow an exception to be passed back to Vertica. You should use a top-level try-catch block to catch any stray exceptions that might be thrown by your code or any functions or libraries your code calls. This is especially important when running a UDx in unfenced mode. Any errors in an unfenced UDx can result in database instability or even data loss.