v_monitor.error_messages Error Codes

robns

Hi
I've been trying to get a breakdown of the description of error_code from v_monitor.error_messages, alas to no avail. Does anyone know where I can get a description of all error codes?
Thanks

Jim_Knicely

Does this info help?

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ErrorCodes/SQLStates.htm

robns

Hi Jim
Unfortunately not as the errors I see are somewhat different. e.g.

dbadmin=> select distinct error_level, error_code, message from v_monitor.error_messages;
error_level | error_code | message

-------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------

ERROR | 16801924 | Syntax error at or near "."
ERROR | 16801924 | Syntax error at or near "ORDER"
ERROR | 16801924 | Syntax error at or near "epool_name"
ERROR | 16801924 | Syntax error at or near "like"
ERROR | 16801924 | Syntax error at or near "q"
ERROR | 16932996 | Relation "resorce_acquisitions" does not exist
ERROR | 16932996 | Relation "resource_pool_ststus" does not exist
ERROR | 50360452 | Column "45035996444232585" does not exist
ERROR | 50360452 | Column "GENERAL_MEMORY_BORROWED_KB" does not exist
ERROR | 50360452 | Column "NOTICE" does not exist
ERROR | 50360452 | Column "timestamp" does not exist
ERROR | 50856066 | Timestamp units "iso_week" not recognized
ERROR | 52461700 | Function catalog.timestampadd(unknown, numeric, timestamp) does not exist, or permission is denied for catalog.timestampadd(unknown, numeric, timest
ERROR | 52461700 | Operator does not exist: int >= timestamptz
ERROR | 52461700 | Operator does not exist: int ~~ unknown

Bryan_H

Hi, could you be more specific what you need? The "error_code" is the internal Vertica code number. The message is what is returned to the client. Do you need:
The SQLSTATE value, which is SQLSTATE 22023 with Description: ERRCODE_INVALID_PARAMETER_VALUE;
Or the Vertica error code shown in the docs, which is
ERROR 2624: Column "string" does not exist

Jim_Knicely

Also, note errors listed in the docs are specific to each Vertica release! Don't compare the errors listed for 9.2 to an 8.1 DB

Bryan_H

From development: that error_code is the SQL state, encoded with 6 bits for each of the 5 alphanumeric characters in the code. So:
16801924 = 000100, 000010, 000110, 000000, 000001 = SQLState 42601 That's the SQL state for Syntax Error. If you take that and go to the back of manual, you'll see the Vertica-specific errors that go with this SQLState. The vertica-specific code tells which one.

Bryan_H

You can convert the error_code to SQLSTATE using the following:

SELECT 
case 
when error_code % 64 < 10 then (error_code % 64)::varchar
else CHR((error_code % 64) + 48)
end 
||
case 
when floor(error_code / 64) % 64 < 10 then (floor(error_code / 64) % 64)::varchar
else CHR((floor(error_code / 64)::integer % 64) + 48)
end
||
case 
when floor(error_code / 4096) % 64 < 10 then (floor(error_code / 4096) % 64)::varchar
else CHR((floor(error_code / 4096)::integer % 64) + 48)
end
||
case 
when floor(error_code / 262144) % 64 < 10 then (floor(error_code / 262144) % 64)::varchar
else CHR((floor(error_code / 262144)::integer % 64) + 48)
end
||
case 
when floor(error_code / 16777216) % 64 < 10 then (floor(error_code / 16777216) % 64)::varchar
else CHR((floor(error_code / 16777216)::integer % 64) + 48)
end
AS SQLSTATE
,*
from v_monitor.error_messages limit 10;

robns

Great, thank you Bryan & Jim for your help for clarifying.