Errors and diagnostics

ASSERT

ASSERT is a debugging feature that checks whether a condition is true. If the condition is false, ASSERT raises an ASSERT_FAILURE exception with an optional error message.

To escape a ' (single quote) character, use ''. Similarly, to escape a " (double quote) character, use "".

ASSERT condition [ , message ];

For example, this procedure checks the number of rows in the products table and uses ASSERT to check that the table is populated. If the table is empty, OpenText™ Analytics Database raises an error:

=> CREATE TABLE products(id UUID, name VARCHARE, price MONEY);
CREATE TABLE

=> SELECT * FROM products;
 id | name | price
----+------+-------
(0 rows)

DO $$
DECLARE
    prod_count INT;
BEGIN
    prod_count := SELECT count(*) FROM products;
    ASSERT prod_count > 0, 'products table is empty';
END;
$$;

ERROR 2005:  products table is empty

To stop the database from checking ASSERT statements, you can set the boolean session-level parameter PLpgSQLCheckAsserts.

RAISE

RAISE can display errors or print a user-specified error message, one of the following:

RAISE [ level ] 'format' [, arg_expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sql-state' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];

This procedure demonstrates various RAISE levels:

=> DO $$
DECLARE
    logfile varchar := 'vertica.log';
BEGIN
    RAISE LOG 'this message was sent to %', logfile;
    RAISE INFO 'info';
    RAISE NOTICE 'notice';
    RAISE WARNING 'warning';
    RAISE EXCEPTION 'exception';

    RAISE NOTICE 'exception changes control flow; this is not printed';
END;
$$;

INFO 2005:  info
NOTICE 2005:  notice
WARNING 2005:  warning
ERROR 2005:  exception

$ grep 'this message was sent to vertica.log' v_vmart_node0001_catalog/vertica.log
<LOG> @v_vmart_node0001: V0002/2005: this message is sent to vertica.log

Exceptions

EXCEPTION blocks let you catch and handle exceptions that might get thrown from statements:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN exception_condition [ OR exception_condition ... ] THEN
        handler_statements
    [ WHEN exception_condition [ OR exception_condition ... ] THEN
        handler_statements
      ... ]
END [ label ];

exception_condition has one of the following forms:

WHEN errcode_division_by_zero THEN ...
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
WHEN OTHERS THEN ...

OTHERS is a special condition that catches all exceptions except QUERY_CANCELLED, ASSERT_FAILURE, and FEATURE_NOT_SUPPORTED.

When an exception is thrown, the database checks the list of exceptions for a matching exception_condition from top to bottom. If it finds a match, it executes the handler_statements and then leaves the exception block's scope.

If the database can't find a match, it propagates the exception up to the next enclosing block. You can do this manually within an exception handler with RAISE:

RAISE;

For example, the following procedure divides 3 by 0 in the inner_block, which is an illegal operation that throws the exception division_by_zero with SQL state 22012. The database checks the inner EXCEPTION block for a matching condition:

1. The first condition checks for SQL state 42501, so the database moves to the next condition.

2. WHEN OTHERS THEN catches all exceptions, it executes that block.

3. The bare RAISE then propagates the exception to the outer_block.

4. The outer EXCEPTION block successfully catches the exception and prints a message.

=> DO $$
<<outer_block>>
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 3 / 0; -- throws exception division_by_zero, SQLSTATE 22012
    EXCEPTION -- this block is checked first for matching exceptions
        WHEN SQLSTATE '42501' THEN
            RAISE NOTICE 'caught insufficient_privilege exception';
        WHEN OTHERS THEN -- catches all exceptions
            RAISE; -- manually propagate the exception to the next enclosing block
    END inner_block;
EXCEPTION -- exception is propagated to this block
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero exception';
END outer_block;
$$;

NOTICE 2005:  caught division_by_zero exception

SQLSTATE and SQLERRM variables

When handling an exception, you can use the following variables to retrieve error information:

• SQLSTATE contains the SQL state

• SQLERRM contains the error message

For details, see SQL state list.

This procedure catches the exception thrown by attempting to assign NULL to a NOT NULL variable and prints the SQL state and error message:

DO $$
DECLARE
    i int NOT NULL := 1;
BEGIN
    i := NULL; -- illegal, i was declared with NOT NULL
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'SQL State: %', SQLSTATE;
        RAISE WARNING 'Error message: %', SQLERRM;
END;
$$;

WARNING 2005:  SQLSTATE: 42809
WARNING 2005:  SQLERRM: Cannot assign null into NOT NULL variable

Retrieving exception information

You can retrieve information about exceptions inside exception handlers with GET STACKED DIAGNOSTICS:

GET STACKED DIAGNOSTICS variable_name { = | := } item [, ... ];

Where item can be any of the following:

For example, this procedure has an EXCEPTION block that catches the division_by_zero error and prints SQL state, error message, and the exception context:

=> DO $$
DECLARE
    message_1 varchar;
    message_2 varchar;
    message_3 varchar;
    x int;
BEGIN
    x := 5 / 0;
EXCEPTION
    WHEN OTHERS THEN -- OTHERS catches all exceptions
    GET STACKED DIAGNOSTICS message_1 = RETURNED_SQLSTATE,
                            message_2 = MESSAGE_TEXT,
                            message_3 = EXCEPTION_CONTEXT;

    RAISE INFO 'SQLSTATE: %', message_1;
    RAISE INFO 'MESSAGE: %', message_2;
    RAISE INFO 'EXCEPTION_CONTEXT: %', message_3;
END;
$$;

INFO 2005:  SQLSTATE: 22012
INFO 2005:  MESSAGE: Division by zero
INFO 2005:  EXCEPTION_CONTEXT: PL/vSQL procedure inline_code_block line 8 at static SQL