Thread: Access last_sqlstate from libpq

Hi,

I am the author of a PostgreSQL C++ client library, taoPQ (https://github.com/taocpp/taopq), wrapping the C-API of
libpq.

In case of an error when I received a PGresult*, I can access the SQLSTATE by calling

   PGresult* pgresult = ...;
   const char* sqlstate = PQresultErrorField( pgresult, PG_DIAG_SQLSTATE );

However, this is not possible in a couple of other cases where I don't have a PGresult*, only the PGconn* is available:

* PQconnectdb (and variants)

* PQputCopyData
* PQputCopyEnd
* PQgetCopyData

* lo_* (large object functions)

Obviously, I can take the error message from PQerrorMessage and throw a generic runtime error - but it would be so much
nicerif I could use the SQLSTATE to throw the correct exception class and give users more information just like I do
forPGresult*. 

After some research, it appears that PGconn* does have a field called last_sqlstate - it just can't be accessed.
Are there any problems adding a simple accessor to libpq? Or is there some way to access it that I'm missing?

Regards,
Daniel

However, this is not possible in a couple of other cases where I don't have a PGresult*, only the PGconn* is available:

* PQconnectdb (and variants)

* PQputCopyData
* PQputCopyEnd
* PQgetCopyData

* lo_* (large object functions)

After some research, it appears that PGconn* does have a field called last_sqlstate - it just can't be accessed.
Are there any problems adding a simple accessor to libpq? Or is there some way to access it that I'm missing?

> On 18. Sep 2021, at 01:45, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
>
> On Friday, September 17, 2021, Daniel Frey <d.frey@gmx.de> wrote:
>
> However, this is not possible in a couple of other cases where I don't have a PGresult*, only the PGconn* is
available:
>
> * PQconnectdb (and variants)
>
> * PQputCopyData
> * PQputCopyEnd
> * PQgetCopyData
>
> * lo_* (large object functions)
>
> After some research, it appears that PGconn* does have a field called last_sqlstate - it just can't be accessed.
> Are there any problems adding a simple accessor to libpq? Or is there some way to access it that I'm missing?
>
> I suspect the reason for the omission is that there isn’t any usable data to be gotten.  Those interfaces are not SQL
interfacesand thus do not have a relevant last_sqlstate to report. 
>
> David J.

Are you sure or are you guessing? It appears that for PQconnectdb there are a couple of SQLSTATES defined which could
helpusers. The 08 Class "Connection Exception" contains at least 08001, 08004, 08P01 which could be helpful for users.
ForPGputCopyData, etc. Class 22 contains a lot of states that could explain what went wrong (in case it's the data),
otherstates potentially also apply (like when the connection is lost, etc.). Even for large data it might me helpful to
seestates that indicate if the server ran out of disk space, etc. 

Maybe not all of this is currently implemented (i.e. a reasonable SQLSTATE is stored in last_sqlstate), but I would
hopethat it is in some cases. 

Daniel

Are you sure or are you guessing?

Daniel Frey <d.frey@gmx.de> writes:
> In case of an error when I received a PGresult*, I can access the SQLSTATE by calling

>    PGresult* pgresult = ...;
>    const char* sqlstate = PQresultErrorField( pgresult, PG_DIAG_SQLSTATE );

Right ...

> However, this is not possible in a couple of other cases where I don't have a PGresult*, only the PGconn* is
available:
> * PQconnectdb (and variants)
> * PQputCopyData
> * PQputCopyEnd
> * PQgetCopyData

In these cases, any error you might get is probably from libpq itself,
not from the server.  libpq does not generate SQLSTATEs for its errors,
so it's likely that last_sqlstate is not relevant at all.

(Getting libpq to assign SQLSTATEs to its errors has been on the to-do
list for a couple of decades.  I'm not holding my breath for somebody
to undertake that.)

> Are there any problems adding a simple accessor to libpq?

I would be strongly against that unless somebody first did the
legwork to ensure it was meaningful.

            regards, tom lane