Analyse postgresql error

[David Harel]

Hi there,

Newbie on golang. Using sqlc: https://github.com/kyleconroy/sqlc in the environment created by Karl Keefer: https://github.com/karlkeefer/pngr/actions/workflows/build.yml, thanks Karl.

My queries use querier which is auto generated by sqlc.

Sometime I get an error result in my query such as key violation.

I want to check the error code of such an error.

as one of the guys suggested I can look into the object using Marshal

and I noticed that the error contains a very elaborated, like:

{"Severity":"ERROR","Code":"23505","Message":"duplicate key value violates unique constraint \"treatment_pk\"","Detail":"Key (customer_id, patient_ttz, therapist_id, appointment_timestamp)=(1, 060525649, 160, 2022-10-20 10:30:00) already exists.","Hint":"","Position":"","InternalPosition":"","InternalQuery":"","Where":"","Schema":"public","Table":"treatment","Column":"","DataTypeName":"","Constraint":"treatment_pk","File":"nbtinsert.c","Line":"434","Routine":"_bt_check_unique"}

Trying to get the same result using straight forward approach like:

fmt.Printf("%+v\n", err) gives me a much simpler object display.

How can I get the "smart" content of the error object like the "Code" member?

Sorry to ask such a trivial question. Unfortunately for my, I was unable to find an answer yet.

Thanks.

[Konstantin Khomoutov]

On Fri, Oct 21, 2022 at 08:17:16AM -0700, David Harel wrote:

> Newbie on golang. Using sqlc: https://github.com/kyleconroy/sqlc in the
> environment created by Karl Keefer:
> https://github.com/karlkeefer/pngr/actions/workflows/build.yml, thanks Karl.
> My queries use querier which is auto generated by sqlc.
>
> Sometime I get an error result in my query such as key violation.
> I want to check the error code of such an error.
>
> as one of the guys suggested I can look into the object using Marshal
> and I noticed that the error contains a very elaborated, like:
> {"Severity":"ERROR","Code":"23505","Message":"duplicate key value violates
> unique constraint \"treatment_pk\"","Detail":"Key (customer_id, patient_ttz,
> therapist_id, appointment_timestamp)=(1, 060525649, 160, 2022-10-20
> 10:30:00) already
> exists.","Hint":"","Position":"","InternalPosition":"",
> "InternalQuery":"","Where":"","Schema":"public",
> "Table":"treatment","Column":"","DataTypeName":"",
> "Constraint":"treatment_pk","File":"nbtinsert.c","Line":"434",
> "Routine":"_bt_check_unique"}

What is "Marshal" you're referring to here?
Is this encoding/json.Marshal called on some value returned by that
"querier generated by sqlc"?

> Trying to get the same result using straight forward approach like:
> fmt.Printf("%+v\n", err) gives me a much simpler object display.
>
> How can I get the "smart" content of the error object like the "Code"
> member?

If my guesseneering is correct, you can just access the Code field of that
value you passed through "Marshal" and analyze it using any convenient method
such as an if or switch statement.

From [1], I gather that "23505" is indeed a standardized error code for
violation of unique key constraints in PostgreSQL, so basically you could do

const uniqueKeyViolation = "23505"

switch result.Code {
case uniqueKeyViolation:
// Do something sensible
default:
log.Println("unexpected failure: ", result.Code)
}

Still, please notice that all of the above is what's called "psychic
debugging" and might miss the point partially or completely because your
problem statement is not an MCVE [2]. I'm not sure you could sensibly create
one (as it could have required posting swaths of generated code or something
like this) so please don't take it as a blame.

1. https://www.postgresql.org/docs/current/errcodes-appendix.html
2. https://stackoverflow.com/help/minimal-reproducible-example

[David Harel]

Thanks for your reply.

After I submitted my question I made some progress and found that:

The infrastructure of my solution is using the pq package - import "github.com/lib/pq"

The marshaling method is not relevant anymore.

I can analyse the error by casting to pq.Error.

Code example:

func errorCheckResult(err error) string {
    if err == nil {
        return ""
    }
    pqerr := err.(*pq.Error)
    switch pqerr.Get('C') {
    case "23505":
        return "Key violation"
    }
    return "Error unknown"
}

Now I want to be able to test my code thereby generating this type of error in the testing function.

So I need to create an error variable of the type pq.Error and use it as a parameter to db.Mock. Something like:

mockDB := wrapper.NewMockQuerier(ctrl)

// The part that breaks

err := fmt.Errorf("pq: %s", `duplicate key value violates unique constraint "treatment_pk" {"Severity":"ERROR","Code":"23505","Message":".....MoreStuff....."}`)

mockDB.EXPECT().AppointmentUpdate(gomock.Any(), gomock.Any()).Return(err)
    

Any idea?

--

דוד הראל

עמוקה

ד.נ. מרום הגליל
1380200

טל: 054-2263892

[Konstantin Khomoutov]

On Wed, Oct 26, 2022 at 01:38:51PM +0300, David Harel wrote:

[...]

> func errorCheckResult(err error) string {
> if err == nil {
> return ""
> }
> pqerr := err.(*pq.Error)
> switch pqerr.Get('C') {
> case "23505":
> return "Key violation"
> }
> return "Error unknown"
> }
>
> Now I want to be able to test my code thereby generating this type of error
> in the testing function.
> So I need to create an error variable of the type pq.Error and use it as a
> parameter to db.Mock. Something like:
>
> mockDB := wrapper.NewMockQuerier(ctrl)
> // The part that breaks
> err := fmt.Errorf("pq: %s", `duplicate key value violates unique constraint
> "treatment_pk"
> {"Severity":"ERROR","Code":"23505","Message":".....MoreStuff....."}`)
>
> mockDB.EXPECT().AppointmentUpdate(gomock.Any(), gomock.Any()).Return(err)
>
> Any idea?

Well, look at what you do:

fmt.Errorf("pq: %s", `some long text`)

creates a value of some (irrelevant) type implementing the standard interface
error and containing a string which is obtained by calling fmt.Sprintf on the
aguments. The "%s" verb in the format string means "get the string
representation of the matching argument and insert in into the format string
in place of the verb". Your argument is itself a string, and it is completely
opaque to fmt.Errorf - it's taken "as is" and is not interpreted in any way.

Please stop and think of this for a moment: even if the contents of this
string would be somehow interpreted, how would the code in package fmt guess
it has to produce a value of type pg.Error from that string, and how exactly?

So, if you need to create a *pq.Error, go on and do just that in your
mock. Based on [1], you could do something like

return &pq.Error{
Code: "23505",
Severity: "ERROR',

Message: `duplicate key value violates unique constraint "treatment_pk"`,
}

1. https://github.com/lib/pq/blob/d65e6ae4bdd1c86b16cd6d2bcff4fe970dc697b4/error.go#L25

[David Harel]

Thanks. Exactly what I needed.