Introduce standardized exception for integrity constraint violation

[mkouba]

Describe the feature

It might be useful to introduce a standardized exception for integrity constraint violations, e.g. something like java.sql.SQLIntegrityConstraintViolationException, so that higher layers can detect such a violation in a portable way.

Use cases

An application developer would be able to detect a violation and provide a useful message to the user. For example, the exception thrown by pg-client is something like io.vertx.pgclient.PgException: ERROR: duplicate key value violates unique constraint "foo". An app developer can check the error message but that's a vendor-specific value and it's not very elegant ;-).

[DavideD]

so that higher layers can detect such a violation in a portable way

Hibernate Reactive for example

[vietj]

I believe that's not something the client support can easily because it really depends on each database and implementation.

Looking at the code of JDBC client for PostgreSQL, it seems that SQLIntegrityConstraintViolationException is not supported.

@DavideD is SQLIntegrityConstraintViolationException used in practice in blocking Hibernate ? because if it does, it means it would not reliably work with PostgreSQL

[mrgrew]

This sounds a lot like Spring's DuplicateKeyException. Maybe that can serve as a model.

[DavideD]

@DavideD is SQLIntegrityConstraintViolationException used in practice in blocking Hibernate ? because if it does, it means it would not reliably work with PostgreSQL

I think Hibernate ORM tries different things to recongnize the exception. After a very quick look I can see it checks the error code from a SQLException or if the type of the exception is an SQLIntegrityConstraintViolationException. And it also uses dialect to have different approaches for different databases.

[DavideD]

Now that I'm having a better look at this, I'm noticing that PgException actually has several fields with info about the error. Maybe that's enough for us to figure out that's a constraint violation (or something else).

[vietj]

it would be more efficient to use pg database info, if we do this in the client, then we might wrongly interpret the code and you would need to wait for fixes

On Fri, Jan 21, 2022 at 6:48 PM DavideD @.***> wrote:

Now that I'm having a better look at this, I'm noticing that PgException actually has several fields with info about the error. Maybe that's enough for us to figure out that's a constraint violation (or something else).

— Reply to this email directly, view it on GitHub https://github.com/eclipse-vertx/vertx-sql-client/issues/1121#issuecomment-1018729510, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABXDCWOF3L34T5VZ5NC563UXGL6FANCNFSM5MPYHY3A . You are receiving this because you commented.Message ID: @.***>

[mkouba]

Now that I'm having a better look at this, I'm noticing that PgException actually has several fields with info about the error. Maybe that's enough for us to figure out that's a constraint violation (or something else).

Yes, there seems to be a bunch of error codes for constraint violations for PostgreSQL; and the PgException.getCode() could be used to access the code.

I'm going to close this issue.