Exception handling within a stored procedure

JamesSmith

I need to manage exception handling within a stored procedure. I use EXCEPTION WHEN OTHERS, but it only catches some errors. When an error occurs in a INSERT INTO ... SELECT ... FROM query, it cannot catch the correct error and whole procedure fall into [57014] [4278] [Vertica]VJDBC ERROR: Operation canceled.

When I removed the EXCEPTION WHEN OTHERS part during testing, the error message on the constraint was correct, but I need to manage EXCEPTIONS. Do you have any recommendations on how to work with that within a stored procedure? How to handle all exceptions?

Here is simple script to test this issue:

CREATE TABLE test_schema.SRC_TABLE (
COLUMN_1 INT,
COLUMN_2 TIMESTAMP,
COLUMN_3 VARCHAR(32)
)
;

INSERT INTO test_schema.SRC_TABLE VALUES(1,getdate(),NULL);
INSERT INTO test_schema.SRC_TABLE VALUES(2,NULL,'string2');
INSERT INTO test_schema.SRC_TABLE VALUES(NULL,getdate(),'string3');

CREATE TABLE test_schema.TGT_TABLE (
COLUMN_1 INT NOT NULL,
COLUMN_2 TIMESTAMP NOT NULL,
COLUMN_3 VARCHAR(32) NOT NULL
)
;

CREATE OR REPLACE PROCEDURE test_schema.SP_TGT_TABLE()
AS
$$
BEGIN

EXECUTE
        'INSERT INTO test_schema.TGT_TABLE (COLUMN_1, COLUMN_2, COLUMN_3)
            SELECT
                 COLUMN_1
                ,COLUMN_2
                ,COLUMN_3
            FROM test_schema.SRC_TABLE';

/*** This query correctly handles exception ***/
/* 
        'INSERT INTO test_schema.TGT_TABLE (COLUMN_1, COLUMN_2, COLUMN_3)
            SELECT
                 NULL AS COLUMN_1
                ,NULL AS COLUMN_2
                ,NULL AS COLUMN_3
            FROM test_schema.SRC_TABLE';
*/

EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Error message: %', SQLERRM;

END;
$$;

CALL test_schema.SP_TGT_TABLE();

I work with Vertica version 24.4.

Thank you for feedback and ideas.

SruthiA

please open support case and share vertica logs for review.

JamesSmith

The case has already been opened with support, so I hope a solution will be found.

SruthiA

could you please share me case number?