Обсуждение: [JDBC] Using \errverbose through JDBC

Hello,

Postgres 9.6 introduced a new feature in libpq to get more detailed information about an error message.

This can currently only be used in psql using the \errverbose command or by changing the VERBOSITY variable.

I wonder if this is also available through JDBC somehow?

Regards
Thomas

=?UTF-8?Q?Jorge_Sol=C3=B3rzano?= <jorsol@gmail.com> writes:
> AFAICT this is a libpq functionality, and since the JDBC driver is not
> libpq based then it can't use that functionality.

> And it doesn't looks to be that useful for JDBC users:

It's not really useful for programs, I think.  ISTM a program would prefer
to have access to the individual fields of the error message.  There
definitely are fields in there that are of interest, for some messages:

regression=# create table t1 (f1 int primary key);
CREATE TABLE
regression=# create table t2 (f2 int references t1);
CREATE TABLE
regression=# insert into t2 values(11);
ERROR:  insert or update on table "t2" violates foreign key constraint "t2_f2_fkey"
DETAIL:  Key (f2)=(11) is not present in table "t1".
regression=# \errverbose
ERROR:  23503: insert or update on table "t2" violates foreign key constraint "t2_f2_fkey"
DETAIL:  Key (f2)=(11) is not present in table "t1".
SCHEMA NAME:  public
TABLE NAME:  t2
CONSTRAINT NAME:  t2_f2_fkey
LOCATION:  ri_ReportViolation, ri_triggers.c:3324

So really what would be appropriate for JDBC to provide, IMO, is
some equivalent of libpq's PQresultErrorField().  Perhaps that's
already there.

            regards, tom lane

=?UTF-8?Q?Jorge_Sol=C3=B3rzano?= <jorsol@gmail.com> writes:
> AFAICT this is a libpq functionality, and since the JDBC driver is not
> libpq based then it can't use that functionality.

> And it doesn't looks to be that useful for JDBC users:

It's not really useful for programs, I think.  ISTM a program would prefer
to have access to the individual fields of the error message.  There
definitely are fields in there that are of interest, for some messages:

regression=# create table t1 (f1 int primary key);
CREATE TABLE
regression=# create table t2 (f2 int references t1);
CREATE TABLE
regression=# insert into t2 values(11);
ERROR:  insert or update on table "t2" violates foreign key constraint "t2_f2_fkey"
DETAIL:  Key (f2)=(11) is not present in table "t1".
regression=# \errverbose
ERROR:  23503: insert or update on table "t2" violates foreign key constraint "t2_f2_fkey"
DETAIL:  Key (f2)=(11) is not present in table "t1".
SCHEMA NAME:  public
TABLE NAME:  t2
CONSTRAINT NAME:  t2_f2_fkey
LOCATION:  ri_ReportViolation, ri_triggers.c:3324

So really what would be appropriate for JDBC to provide, IMO, is
some equivalent of libpq's PQresultErrorField().  Perhaps that's
already there.

            regards, tom lane

Vladimir Sitnikov schrieb am 01.02.2017 um 14:28:
> Thomas>This can currently only be used in psql using the \errverbose command or by changing the VERBOSITY variable.
>
> As far as I know, PSQLException (that is a base class of pgjdbc's SQLExceptions) always has relevant data avaliable
viagetServerErrorMessage. 
>
> Is it something that you want?
>
>
https://github.com/pgjdbc/pgjdbc/blob/8be516d47ece60b7aeba5a9474b5cac1d538a04a/pgjdbc/src/main/java/org/postgresql/util/PSQLException.java#L28

>
> Vladimir

Cool, this is what I was looking for.

Thanks

Vladimir Sitnikov schrieb am 01.02.2017 um 14:28:
> Thomas>This can currently only be used in psql using the \errverbose command or by changing the VERBOSITY variable.
>
> As far as I know, PSQLException (that is a base class of pgjdbc's SQLExceptions) always has relevant data avaliable
viagetServerErrorMessage. 
>
> Is it something that you want?
>
>
https://github.com/pgjdbc/pgjdbc/blob/8be516d47ece60b7aeba5a9474b5cac1d538a04a/pgjdbc/src/main/java/org/postgresql/util/PSQLException.java#L28

>
> Vladimir

Cool, this is what I was looking for.

Thanks

Jorge Solórzano schrieb am 01.02.2017 um 16:11:
> The use case for this is?
>
> AFAICT this is a libpq functionality, and since the JDBC driver is not libpq based then it can't use that
functionality.
>
> And it doesn't looks to be that useful for JDBC users:

I think it would:

postgres> \set VERBOSITY verbose
postgres> insert into foobar (data) values ('x');
ERROR:  23502: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, x).
SCHEMA NAME:  public
TABLE NAME:  foobar
COLUMN NAME:  id
LOCATION:  ExecConstraints, execMain.c:1732
postgres>

When you run such an insert via JDBC, you'd only have "null value in column "id" violates not-null constraint" in the
SQLException's message.  
But for logging purposes (on the client) it would be interesting to get details about the table into which the insert
wasdone.  

This would enable to show those details (e.g. the table that was affected) in tools like SQL Workbench

Vladimir pointed me into the right direction. Those details are already available through the Postgres JDBC driver.

Jorge Solórzano schrieb am 01.02.2017 um 16:11:
> The use case for this is?
>
> AFAICT this is a libpq functionality, and since the JDBC driver is not libpq based then it can't use that
functionality.
>
> And it doesn't looks to be that useful for JDBC users:

I think it would:

postgres> \set VERBOSITY verbose
postgres> insert into foobar (data) values ('x');
ERROR:  23502: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, x).
SCHEMA NAME:  public
TABLE NAME:  foobar
COLUMN NAME:  id
LOCATION:  ExecConstraints, execMain.c:1732
postgres>

When you run such an insert via JDBC, you'd only have "null value in column "id" violates not-null constraint" in the
SQLException's message.  
But for logging purposes (on the client) it would be interesting to get details about the table into which the insert
wasdone.  

This would enable to show those details (e.g. the table that was affected) in tools like SQL Workbench

Vladimir pointed me into the right direction. Those details are already available through the Postgres JDBC driver.

Tom Lane schrieb am 01.02.2017 um 16:24:
> So really what would be appropriate for JDBC to provide, IMO, is
> some equivalent of libpq's PQresultErrorField().  Perhaps that's
> already there.

The JDBC driver already does that.

Vladimir pointed me to the correct location:

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/ServerErrorMessage.html

Tom Lane schrieb am 01.02.2017 um 16:24:
> So really what would be appropriate for JDBC to provide, IMO, is
> some equivalent of libpq's PQresultErrorField().  Perhaps that's
> already there.

The JDBC driver already does that.

Vladimir pointed me to the correct location:

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/ServerErrorMessage.html