Обсуждение: How to get error message details from libpq based psqlODBC driver (regression)

Hi!

I'm looking for a way to fix psqlODBC driver regression.

Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all
operations (earlier versions used libpg only for authentication) ODBC client
does not show error message details.

For example, users got only generic error message like

    Connectivity error: ERROR: insert or update on table "mytable" violates
    foreign key constraint  "mytable_myfield_fkey

Error message details like

    Key (myfield)=(mykeyvalue) is not present in table "mymastertable".;

are no more returned.

How to detailed error message or at least only bad key value "mykeyvalue"
which is returned in error message details ?

Some ideas:

1. First versions of libpq based ODBC drivers returned same error message 
"no resource found" for all errors. Hiroshi has fixed it it later versions. 
Maybe psqlODBC code can fixed to restore pre-libpq behaviour.

2. Maybe analyzing odbc logs from pre and after 09.05.0100  drivers may 
provide solution. I can provide ODBC log files.

3. psqlODBC allows to pass parameters to libpq from connection string. Maybe 
some libpq parameter can fix this.

4.  Maybe some Postgres query, postgres extension  or direct odbc or libpq 
call can used to return last error message details like Windows API 
GetLastError() or Unix global errno.

5. Maybe it is possible to to create method which returns error message 
detals from postgres log file.

Postgres 12.2 and latest psqlODBC driver 12.01.0000 are used.
psqlODBC is called from Visual FoxPro

Andrus. 

On 2/22/20 2:37 PM, Andrus wrote:
> Hi!
> 
> I'm looking for a way to fix psqlODBC driver regression.
> 
> Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all
> operations (earlier versions used libpg only for authentication) ODBC 
> client
> does not show error message details.
> 
> For example, users got only generic error message like
> 
>     Connectivity error: ERROR: insert or update on table "mytable" violates
>     foreign key constraint  "mytable_myfield_fkey
> 
> Error message details like
> 
>     Key (myfield)=(mykeyvalue) is not present in table "mymastertable".;
> 
> are no more returned.
> 
> How to detailed error message or at least only bad key value "mykeyvalue"
> which is returned in error message details ?
> 
> Some ideas:
> 
> 1. First versions of libpq based ODBC drivers returned same error 
> message "no resource found" for all errors. Hiroshi has fixed it it 
> later versions. Maybe psqlODBC code can fixed to restore pre-libpq 
> behaviour.
> 
> 2. Maybe analyzing odbc logs from pre and after 09.05.0100  drivers may 
> provide solution. I can provide ODBC log files.
> 
> 3. psqlODBC allows to pass parameters to libpq from connection string. 
> Maybe some libpq parameter can fix this.
> 
> 4.  Maybe some Postgres query, postgres extension  or direct odbc or 
> libpq call can used to return last error message details like Windows 
> API GetLastError() or Unix global errno.
> 
> 5. Maybe it is possible to to create method which returns error message 
> detals from postgres log file.
> 
> Postgres 12.2 and latest psqlODBC driver 12.01.0000 are used.
> psqlODBC is called from Visual FoxPro


What does the log_error_verbosity setting in postgresql.conf show?

> 
> Andrus.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/22/20 5:19 PM, Adrian Klaver wrote:
> On 2/22/20 2:37 PM, Andrus wrote:
>> Hi!
>>
>> I'm looking for a way to fix psqlODBC driver regression.
>>
>> Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all
>> operations (earlier versions used libpg only for authentication) ODBC client
>> does not show error message details.
>>
>> For example, users got only generic error message like
>>
>>     Connectivity error: ERROR: insert or update on table "mytable" violates
>>     foreign key constraint  "mytable_myfield_fkey
>>
>> Error message details like
>>
>>     Key (myfield)=(mykeyvalue) is not present in table "mymastertable".;
>>
>> are no more returned.
>>
>> How to detailed error message or at least only bad key value "mykeyvalue"
>> which is returned in error message details ?
>>
[snip]
>
> What does the log_error_verbosity setting in postgresql.conf show?

Does that affect what the ODBC driver sends back to the user?

-- 
Angular momentum makes the world go 'round.

Hi!

>What does the log_error_verbosity setting in postgresql.conf show?

It is not set. postgresql.conf contains it default value from installation:

#log_error_verbosity = default        # terse, default, or verbose messages

I changed it to

log_error_verbosity = verbose

but problem persists.

postgres log file contains

2020-02-23 09:02:27.646 GMT [11252] ERROR:  23503: insert or update on table 
"rid" violates foreign key constraint "rid_yhik_fkey"
2020-02-23 09:02:27.646 GMT [11252] DETAIL:  Key (yhik)=(xx    ) is not 
present in table "mootyhik".
2020-02-23 09:02:27.646 GMT [11252] LOCATION:  ri_ReportViolation, 
d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\adt\ri_triggers.c:2474
2020-02-23 09:02:27.646 GMT [11252] STATEMENT:  insert into rid (dokumnr, 
yhik) values (2065, 'xx')


but application shows only

ERROR: insert or update on table "rid" violates foreign key constraint 
"rid_yhik_fkey"

Andrus. 

On 2/23/20 1:06 AM, Andrus wrote:
> Hi!
> 
>> What does the log_error_verbosity setting in postgresql.conf show?
> 
> It is not set. postgresql.conf contains it default value from installation:
> 
> #log_error_verbosity = default        # terse, default, or verbose messages
> 
> I changed it to
> 
> log_error_verbosity = verbose
> 
> but problem persists.
> 
> postgres log file contains
> 
> 2020-02-23 09:02:27.646 GMT [11252] ERROR:  23503: insert or update on 
> table "rid" violates foreign key constraint "rid_yhik_fkey"
> 2020-02-23 09:02:27.646 GMT [11252] DETAIL:  Key (yhik)=(xx    ) is not 
> present in table "mootyhik".
> 2020-02-23 09:02:27.646 GMT [11252] LOCATION:  ri_ReportViolation, 
> d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\adt\ri_triggers.c:2474 
> 
> 2020-02-23 09:02:27.646 GMT [11252] STATEMENT:  insert into rid 
> (dokumnr, yhik) values (2065, 'xx')
> 
> 
> but application shows only
> 
> ERROR: insert or update on table "rid" violates foreign key constraint 
> "rid_yhik_fkey"

I don't have an answer for you. There maybe someone else on this list 
that could help, though I think your best bet would be to ask the 
question again on the pgsql-odbc list.

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi!

>I don't have an answer for you. There maybe someone else on this list that
>could help, though I think your best bet would be to ask the question again
>on the pgsql-odbc list.

I posted it in pgsql-odbc list.

In pgsql odbc source code file connection.c: line 866

contains:

errprimary = PQresultErrorField(pgres, PG_DIAG_MESSAGE_PRIMARY);

which probably gets only primary error message.
To get error message detail,  PG_DIAG_MESSAGE_DETAIL should used according 
to https://www.postgresql.org/docs/current/libpq-exec.html.
Unfortunately PG_DIAG_MESSAGE_DETAIL  is not used in pgsql-odbc source code.

Andrus.