Constants for PostgresException.SqlState

[walkindude]

Hello, I was wondering if a static class that grouped all the possible error codes as documented here would be a worthwhile addition to Npgsql.
I recently needed to inspect the SqlState property to figure out what went wrong and act accordingly and, from what I could see, there is no such class that would hide the 5-characters error code behind a more friendly name.

One catch is that a few of the error codes in the linked page have different codes but the same condition identifier, so just transforming them from snake_case to PascalCase to name the constants properly is going to cause a compilation error (see for instance null_value_not_allowed, which is both a Data Exception with code 22004 and an External Routine Invocation Exception with code 39004).

Assuming such a SqlStates class has any value, the obvious workaround would be to nest static classes to group constants into poor man's namespaces. Nested classes would have names that identify the error class, but this might lead to overly long names (especially for classes like Syntax Error or Access Rule Violation).

Client code would look like:

if (ex.SqlState == SqlStates.DataException.DivisionByZero)
{
    // ...
}

All of this might have already been considered and discussed before, and a SqlStates class is just not there exactly because there is no elegant and concise way to solve duplicated error identifiers. If so I apologize.

I'm attaching the class with all the error condition identifiers already converted to PascalCase should it ever come in handy. It's based on the current documentation. As I said it doesn't currently compile.

public static class SqlStates
{
    public const string SuccessfulCompletion = "00000";
    public const string Warning = "01000";
    public const string DynamicResultSetsReturned = "0100C";
    public const string ImplicitZeroBitPadding = "01008";
    public const string NullValueEliminatedInSetFunction = "01003";
    public const string PrivilegeNotGranted = "01007";
    public const string PrivilegeNotRevoked = "01006";
    public const string StringDataRightTruncation = "01004";
    public const string DeprecatedFeature = "01P01";
    public const string NoData = "02000";
    public const string NoAdditionalDynamicResultSetsReturned = "02001";
    public const string SqlStatementNotYetComplete = "03000";
    public const string ConnectionException = "08000";
    public const string ConnectionDoesNotExist = "08003";
    public const string ConnectionFailure = "08006";
    public const string SqlclientUnableToEstablishSqlconnection = "08001";
    public const string SqlserverRejectedEstablishmentOfSqlconnection = "08004";
    public const string TransactionResolutionUnknown = "08007";
    public const string ProtocolViolation = "08P01";
    public const string TriggeredActionException = "09000";
    public const string FeatureNotSupported = "0A000";
    public const string InvalidTransactionInitiation = "0B000";
    public const string LocatorException = "0F000";
    public const string InvalidLocatorSpecification = "0F001";
    public const string InvalidGrantor = "0L000";
    public const string InvalidGrantOperation = "0LP01";
    public const string InvalidRoleSpecification = "0P000";
    public const string DiagnosticsException = "0Z000";
    public const string StackedDiagnosticsAccessedWithoutActiveHandler = "0Z002";
    public const string CaseNotFound = "20000";
    public const string CardinalityViolation = "21000";
    public const string DataException = "22000";
    public const string ArraySubscriptError = "2202E";
    public const string CharacterNotInRepertoire = "22021";
    public const string DatetimeFieldOverflow = "22008";
    public const string DivisionByZero = "22012";
    public const string ErrorInAssignment = "22005";
    public const string EscapeCharacterConflict = "2200B";
    public const string IndicatorOverflow = "22022";
    public const string IntervalFieldOverflow = "22015";
    public const string InvalidArgumentForLogarithm = "2201E";
    public const string InvalidArgumentForNtileFunction = "22014";
    public const string InvalidArgumentForNthValueFunction = "22016";
    public const string InvalidArgumentForPowerFunction = "2201F";
    public const string InvalidArgumentForWidthBucketFunction = "2201G";
    public const string InvalidCharacterValueForCast = "22018";
    public const string InvalidDatetimeFormat = "22007";
    public const string InvalidEscapeCharacter = "22019";
    public const string InvalidEscapeOctet = "2200D";
    public const string InvalidEscapeSequence = "22025";
    public const string NonstandardUseOfEscapeCharacter = "22P06";
    public const string InvalidIndicatorParameterValue = "22010";
    public const string InvalidParameterValue = "22023";
    public const string InvalidRegularExpression = "2201B";
    public const string InvalidRowCountInLimitClause = "2201W";
    public const string InvalidRowCountInResultOffsetClause = "2201X";
    public const string InvalidTablesampleArgument = "2202H";
    public const string InvalidTablesampleRepeat = "2202G";
    public const string InvalidTimeZoneDisplacementValue = "22009";
    public const string InvalidUseOfEscapeCharacter = "2200C";
    public const string MostSpecificTypeMismatch = "2200G";
    public const string NullValueNotAllowed = "22004";
    public const string NullValueNoIndicatorParameter = "22002";
    public const string NumericValueOutOfRange = "22003";
    public const string StringDataLengthMismatch = "22026";
    public const string StringDataRightTruncation = "22001";
    public const string SubstringError = "22011";
    public const string TrimError = "22027";
    public const string UnterminatedCString = "22024";
    public const string ZeroLengthCharacterString = "2200F";
    public const string FloatingPointException = "22P01";
    public const string InvalidTextRepresentation = "22P02";
    public const string InvalidBinaryRepresentation = "22P03";
    public const string BadCopyFileFormat = "22P04";
    public const string UntranslatableCharacter = "22P05";
    public const string NotAnXmlDocument = "2200L";
    public const string InvalidXmlDocument = "2200M";
    public const string InvalidXmlContent = "2200N";
    public const string InvalidXmlComment = "2200S";
    public const string InvalidXmlProcessingInstruction = "2200T";
    public const string IntegrityConstraintViolation = "23000";
    public const string RestrictViolation = "23001";
    public const string NotNullViolation = "23502";
    public const string ForeignKeyViolation = "23503";
    public const string UniqueViolation = "23505";
    public const string CheckViolation = "23514";
    public const string ExclusionViolation = "23P01";
    public const string InvalidCursorState = "24000";
    public const string InvalidTransactionState = "25000";
    public const string ActiveSqlTransaction = "25001";
    public const string BranchTransactionAlreadyActive = "25002";
    public const string HeldCursorRequiresSameIsolationLevel = "25008";
    public const string InappropriateAccessModeForBranchTransaction = "25003";
    public const string InappropriateIsolationLevelForBranchTransaction = "25004";
    public const string NoActiveSqlTransactionForBranchTransaction = "25005";
    public const string ReadOnlySqlTransaction = "25006";
    public const string SchemaAndDataStatementMixingNotSupported = "25007";
    public const string NoActiveSqlTransaction = "25P01";
    public const string InFailedSqlTransaction = "25P02";
    public const string InvalidSqlStatementName = "26000";
    public const string TriggeredDataChangeViolation = "27000";
    public const string InvalidAuthorizationSpecification = "28000";
    public const string InvalidPassword = "28P01";
    public const string DependentPrivilegeDescriptorsStillExist = "2B000";
    public const string DependentObjectsStillExist = "2BP01";
    public const string InvalidTransactionTermination = "2D000";
    public const string SqlRoutineException = "2F000";
    public const string FunctionExecutedNoReturnStatement = "2F005";
    public const string ModifyingSqlDataNotPermitted = "2F002";
    public const string ProhibitedSqlStatementAttempted = "2F003";
    public const string ReadingSqlDataNotPermitted = "2F004";
    public const string InvalidCursorName = "34000";
    public const string ExternalRoutineException = "38000";
    public const string ContainingSqlNotPermitted = "38001";
    public const string ModifyingSqlDataNotPermitted = "38002";
    public const string ProhibitedSqlStatementAttempted = "38003";
    public const string ReadingSqlDataNotPermitted = "38004";
    public const string ExternalRoutineInvocationException = "39000";
    public const string InvalidSqlstateReturned = "39001";
    public const string NullValueNotAllowed = "39004";
    public const string TriggerProtocolViolated = "39P01";
    public const string SrfProtocolViolated = "39P02";
    public const string EventTriggerProtocolViolated = "39P03";
    public const string SavepointException = "3B000";
    public const string InvalidSavepointSpecification = "3B001";
    public const string InvalidCatalogName = "3D000";
    public const string InvalidSchemaName = "3F000";
    public const string TransactionRollback = "40000";
    public const string TransactionIntegrityConstraintViolation = "40002";
    public const string SerializationFailure = "40001";
    public const string StatementCompletionUnknown = "40003";
    public const string DeadlockDetected = "40P01";
    public const string SyntaxErrorOrAccessRuleViolation = "42000";
    public const string SyntaxError = "42601";
    public const string InsufficientPrivilege = "42501";
    public const string CannotCoerce = "42846";
    public const string GroupingError = "42803";
    public const string WindowingError = "42P20";
    public const string InvalidRecursion = "42P19";
    public const string InvalidForeignKey = "42830";
    public const string InvalidName = "42602";
    public const string NameTooLong = "42622";
    public const string ReservedName = "42939";
    public const string DatatypeMismatch = "42804";
    public const string IndeterminateDatatype = "42P18";
    public const string CollationMismatch = "42P21";
    public const string IndeterminateCollation = "42P22";
    public const string WrongObjectType = "42809";
    public const string UndefinedColumn = "42703";
    public const string UndefinedFunction = "42883";
    public const string UndefinedTable = "42P01";
    public const string UndefinedParameter = "42P02";
    public const string UndefinedObject = "42704";
    public const string DuplicateColumn = "42701";
    public const string DuplicateCursor = "42P03";
    public const string DuplicateDatabase = "42P04";
    public const string DuplicateFunction = "42723";
    public const string DuplicatePreparedStatement = "42P05";
    public const string DuplicateSchema = "42P06";
    public const string DuplicateTable = "42P07";
    public const string DuplicateAlias = "42712";
    public const string DuplicateObject = "42710";
    public const string AmbiguousColumn = "42702";
    public const string AmbiguousFunction = "42725";
    public const string AmbiguousParameter = "42P08";
    public const string AmbiguousAlias = "42P09";
    public const string InvalidColumnReference = "42P10";
    public const string InvalidColumnDefinition = "42611";
    public const string InvalidCursorDefinition = "42P11";
    public const string InvalidDatabaseDefinition = "42P12";
    public const string InvalidFunctionDefinition = "42P13";
    public const string InvalidPreparedStatementDefinition = "42P14";
    public const string InvalidSchemaDefinition = "42P15";
    public const string InvalidTableDefinition = "42P16";
    public const string InvalidObjectDefinition = "42P17";
    public const string WithCheckOptionViolation = "44000";
    public const string InsufficientResources = "53000";
    public const string DiskFull = "53100";
    public const string OutOfMemory = "53200";
    public const string TooManyConnections = "53300";
    public const string ConfigurationLimitExceeded = "53400";
    public const string ProgramLimitExceeded = "54000";
    public const string StatementTooComplex = "54001";
    public const string TooManyColumns = "54011";
    public const string TooManyArguments = "54023";
    public const string ObjectNotInPrerequisiteState = "55000";
    public const string ObjectInUse = "55006";
    public const string CantChangeRuntimeParam = "55P02";
    public const string LockNotAvailable = "55P03";
    public const string OperatorIntervention = "57000";
    public const string QueryCanceled = "57014";
    public const string AdminShutdown = "57P01";
    public const string CrashShutdown = "57P02";
    public const string CannotConnectNow = "57P03";
    public const string DatabaseDropped = "57P04";
    public const string SystemError = "58000";
    public const string IoError = "58030";
    public const string UndefinedFile = "58P01";
    public const string DuplicateFile = "58P02";
    public const string ConfigFileError = "F0000";
    public const string LockFileExists = "F0001";
    public const string FdwError = "HV000";
    public const string FdwColumnNameNotFound = "HV005";
    public const string FdwDynamicParameterValueNeeded = "HV002";
    public const string FdwFunctionSequenceError = "HV010";
    public const string FdwInconsistentDescriptorInformation = "HV021";
    public const string FdwInvalidAttributeValue = "HV024";
    public const string FdwInvalidColumnName = "HV007";
    public const string FdwInvalidColumnNumber = "HV008";
    public const string FdwInvalidDataType = "HV004";
    public const string FdwInvalidDataTypeDescriptors = "HV006";
    public const string FdwInvalidDescriptorFieldIdentifier = "HV091";
    public const string FdwInvalidHandle = "HV00B";
    public const string FdwInvalidOptionIndex = "HV00C";
    public const string FdwInvalidOptionName = "HV00D";
    public const string FdwInvalidStringLengthOrBufferLength = "HV090";
    public const string FdwInvalidStringFormat = "HV00A";
    public const string FdwInvalidUseOfNullPointer = "HV009";
    public const string FdwTooManyHandles = "HV014";
    public const string FdwOutOfMemory = "HV001";
    public const string FdwNoSchemas = "HV00P";
    public const string FdwOptionNameNotFound = "HV00J";
    public const string FdwReplyHandle = "HV00K";
    public const string FdwSchemaNotFound = "HV00Q";
    public const string FdwTableNotFound = "HV00R";
    public const string FdwUnableToCreateExecution = "HV00L";
    public const string FdwUnableToCreateReply = "HV00M";
    public const string FdwUnableToEstablishConnection = "HV00N";
    public const string PlpgsqlError = "P0000";
    public const string RaiseException = "P0001";
    public const string NoDataFound = "P0002";
    public const string TooManyRows = "P0003";
    public const string AssertFailure = "P0004";
    public const string InternalError = "XX000";
    public const string DataCorrupted = "XX001";
    public const string IndexCorrupted = "XX002";
}

[roji]

I'm not sure I see great value coming from this, but it could make sense to include it in Npgsql. The list would have to be kept up to date (although I'm not sure how frequently error codes are added).

Regarding the duplicate error names, rather than putting all error codes under a "namespace', I'd simply prefix the problematic values (e.g. ExternalRoutineNullValueNotAllowed), this would at least keep all the other codes short...

If you submit a PR on this I'll merge it.

[walkindude]

Prefixing sounds fine, I had intentionally left it out because I'm usually against introducing "exceptions" in favor of uniformity, but I would have no objections to renaming those few offending identifiers.

If however you think this class would impose maintenance burden, I'm perfectly ok with you deciding not to include it. I'm not sure either how frequently error codes are added, and one could also think of adding them "on demand" if a user opens an issue pointing out a certain error code is missing.
I guess for most purposes basic errors would be enough, so I fully understand why you don't see much value in it as people could just write their own, though.

I can make a PR in the coming days, no problem, but if you feel it brings too little to the table I have, like I said, no objections at all. It is just something I did for me, so if it doesn't suit anybody else it's not a big deal.

[roji]

I don't think there's lots of movement in the error code space, and as you say we can always add more. Feel free to submit a PR (it may take a bit of time for me to look at it though, am about to go on vacation).

[walkindude]

No problems at all, enjoy your vacation then!

[roji]

Am going to close this issue, a PR on this would be accepted though.

[PMExtra]

How is it going?

[daniel-white]

@roji has this been added in some fashion, i'd be happy to help with a PR to add something like this

[roji]

@daniel-white this hasn't been added. I think we'd accept a PR doing this, although let's see what @YohDeadfall and @austindrenski think.

[daniel-white]

@roji cool. i would not make these as const but static readonly so upgrades wouldn't be a full recompile.

[roji]

@daniel-white do you mean in case of code changes on the PostgreSQL side?

[daniel-white]

@roji no in this library, if theres another error message added

[roji]

I may be misunderstanding, but why would adding a new const require a full recompile (as opposed to a static readonly)?

[daniel-white]

@roji https://stackoverflow.com/a/755693/23528

[daniel-white]

well i guess i'm wrong but its your call.

[roji]

I don't have very strong feelings either way: I think PostgreSQL error codes are a rare case where public const actually does make sense, since they're pretty much guaranteed to never, ever change (as this would break all PostgreSQL clients everywhere). On the other hand there's no perf consideration here that would make us prefer const over static readonly. I'd lean towards const simply because it better expresses the fact that these are totally external, never-changing values.

[YohDeadfall]

I'm voting for const and waiting for your PR.