Expose SQLSTATE (and other diagnostic fields) on error conditions

[schochastics]

Summary

When a query triggers a server-side error, RPostgres surfaces only the libpq
message string on the R condition. The SQLSTATE code and the other diagnostic
fields libpq exposes through PQresultErrorField() are discarded before the
error reaches R. As a result there is no reliable, locale-independent way to
classify an error programmatically.

It would be very useful if RPostgres carried at least the SQLSTATE onto the
condition.

Why this matters

We define server-side validation in PL/pgSQL triggers and raise errors with
stable, explicit error codes:

RAISE EXCEPTION 'konto % ist nicht bebuchbar.', NEW.konto
  USING ERRCODE = 'FS002',
        HINT    = 'Setzen Sie bebuchbar = TRUE ...';

On the R side we want to branch on the code to drive the UI, precisely because
the message is user-facing, localized (German here), and may be reworded. The
code is the stable contract; the message is not.

Because only the message string reaches R, we are forced to recover the
classification by regex-matching the (German) message text:

sqlstate <- if (grepl("ist nicht bebuchbar", msg, fixed = TRUE)) {
  "FS002"
} else if (grepl("nicht gültig", msg, fixed = TRUE)) {
  "FS001"
} else {
  NA_character_
}

This is brittle: it breaks if a message is reworded, it is locale-dependent, and
it cannot distinguish our raised codes from coincidentally similar text. The
ERRCODE we set on the server is the correct key to branch on — we just can't
read it back.

Reproducible example

library(DBI)
con <- dbConnect(RPostgres::Postgres())

err <- tryCatch(
  dbExecute(con, "DO $$ BEGIN RAISE EXCEPTION 'boom' USING ERRCODE = 'FS002'; END $$;"),
  error = function(e) e
)

conditionMessage(err)
#> [1] "Failed to fetch row : ERROR:  boom\nCONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE\n"

str(unclass(err))
#> List of 2
#>  $ message: chr "Failed to fetch row : ERROR:  boom\nCONTEXT:  PL/pgSQL ..."
#>  $ call   : NULL

class(err)
#> [1] "simpleError" "error"       "condition"

It is a plain simpleError: just message and call, no SQLSTATE field and no
SQLSTATE-derived class. The 'FS002' code is gone. There is also no
dbGetException() fallback:

existsMethod("dbGetException", "PqConnection")
#> [1] FALSE

Where it's dropped

Every server-side error funnels through one helper, which keeps only
PQerrorMessage():

// src/DbConnection.cpp
void DbConnection::conn_stop(PGconn* conn, const char* msg) {
  cpp11::stop(std::string(msg) + " : " + PQerrorMessage(conn));
}

Result-level errors take the same path (PqResultImpl::conn_stop →
DbConnection::conn_stop), and cpp11::stop() raises a plain simpleError with
no extra fields. PQresultErrorField / PG_DIAG_SQLSTATE do not appear anywhere
in the source, so the SQLSTATE that libpq has on hand is never read.

Desired behavior

Any of the following would solve it, in rough order of preference:

1. Attach the SQLSTATE as a field on a classed condition (e.g. err$sqlstate),
   and optionally give the condition an extra class such as
   RPostgres_error_<sqlstate> so handlers can dispatch directly:
   tryCatch(..., RPostgres_error_FS002 = ...).
2. Additionally attach the other PQresultErrorField() diagnostics (detail,
   hint, column, constraint, table, schema, context) as fields, so callers don't
   have to parse them out of the message string.
3. At minimum, provide a dbGetException()-style accessor returning the SQLSTATE
   of the last error on the connection.

libpq already exposes everything needed via PQresultErrorField(res, field) —
PG_DIAG_SQLSTATE, PG_DIAG_MESSAGE_DETAIL, PG_DIAG_MESSAGE_HINT,
PG_DIAG_COLUMN_NAME, PG_DIAG_TABLE_NAME, PG_DIAG_CONSTRAINT_NAME, etc.
These would need to be read where the error result is turned into an R error,
and carried onto the condition rather than collapsed into a single string.

[krlmlr]

Thanks. We already have rlang through transitive dependencies and could make the error messages look really nice. The mechanics are interesting -- duckdb is a good reference implementation, see https://github.com/duckdb/duckdb-r/blob/main/R/rethrow-gen.R for a starting point.

Would you like to PR?

[schochastics]

I can give it a try!