去pq和Postgres适当的错误处理约束

Question

I'm currently using the pq lib for Go to communicate with my PostgreSQL database. Error checking is proving to be a little more difficult than anticipated. The easiest way to describe my question is through an example scenario.

Imagine a web form:

Username  ________
Email     ________
Voucher   ________
Password  ________

A rough schema:

username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
voucher VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL

Ignore the presumed plain text password for now. If a person submits the form, I can do all of my validation to verify constraints such as length/allowed characters/etc.

Now it comes to putting it in the database, so we write a prepared statement and execute it. If the validation was done correctly, the only thing that can really go wrong is the UNIQUE constraints. In the event that someone attempts to enter an existing username, database/sql is going to fire back an error.

My problem is that I have no idea what to do with that error and recover from (what should be) a recoverable error. pq provides some support for this, but there still appears to be come ambiguity to what's returned.

I can see two solutions, neither of which sound particularly appealing to me: A SERIALIZABLE transaction which checks every single form value prior to insertion. Alternatively, some form of parsing on the pq error struct.

Is there a common pattern for implementing such a system? I'd like to be able to say to a user Sorry that username exists rather than Sorry something bad happened

As a sidenote, the PostgreSQL documentation states:

The fields for schema name, table name, column name, data type name, and constraint name are supplied only for a limited number of error types; see Appendix A.

but the linked page isn't very helpful with respect to values returned in the database object.

Answer 1

If the validation was done correctly, the only thing that can really go wrong is the UNIQUE constraints.

No, the client could lack sufficient privileges, the client might have entered a valid password that's not the right password, the client might have entered a valid voucher that belongs to a different client, etc.

Using "A SERIALIZABLE transaction which checks every single form value prior to insertion" doesn't make sense. Just insert data, and trap errors.

At the very least, your code needs to examine and respond to the C (Code) field, which is always present in the error struct. You don't need to parse the error struct, but you do need to read it.

If you violate a unique constraint, PostgreSQL will return SQL state 23505 in the Code field. It will also return the name of the first constraint that's violated. It doesn't return the column name, probably because a unique constraint can include more than one column.

You can select the column(s) the constraint refers to by querying the information_schema views.

Here's a simple version of your table.

create table test (
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  voucher VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

insert into test values ('msherrill', '[e-mail]', 'a', 'wibble');

This quick and dirty go program inserts the same row again. It violates every unique constraint.

package main

import (
    "github.com/lib/pq"
    "database/sql"
    "fmt"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost port=5435 user=postgres password=xxxxxxxx dbname=scratch sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Exec("insert into public.test values ('msherrill', '[e-mail]', 'a', 'wibble');")
    if err, ok := err.(*pq.Error); ok {
        fmt.Println("Severity:", err.Severity)
        fmt.Println("Code:", err.Code)
        fmt.Println("Message:", err.Message)
        fmt.Println("Detail:", err.Detail)
        fmt.Println("Hint:", err.Hint)
        fmt.Println("Position:", err.Position)
        fmt.Println("InternalPosition:", err.InternalPosition)
        fmt.Println("Where:", err.Where)
        fmt.Println("Schema:", err.Schema)
        fmt.Println("Table:", err.Table)
        fmt.Println("Column:", err.Column)
        fmt.Println("DataTypeName:", err.DataTypeName)
        fmt.Println("Constraint:", err.Constraint)
        fmt.Println("File:", err.File)
        fmt.Println("Line:", err.Line)
        fmt.Println("Routine:", err.Routine)
    }
   fmt.Println(rows)
}

Here's the output.

Severity: ERROR
Code: 23505
Message: duplicate key value violates unique constraint "test_username_key"
Detail: Key (username)=(msherrill) already exists.
Hint: 
Position: 
InternalPosition: 
Where: 
Schema: public
Table: test
Column: 
DataTypeName: 
Constraint: test_username_key
File: nbtinsert.c
Line: 406
Routine: _bt_check_unique

You have the schema, table, and constraint names. You presumably know the database (catalog) name, too. Use these values to select the schema, table, and column names from information_schema views. You're lucky; in this case you need only one view.

select table_catalog, table_schema, table_name, column_name 
from information_schema.key_column_usage
where 
    table_catalog = 'scratch' and          -- Database name
    table_schema = 'public' and            -- value returned by err.Schema
    table_name = 'test' and                -- value returned by err.Table
    constraint_name = 'test_username_key'  -- value returned by err.Constraint
order by constraint_catalog, constraint_schema, constraint_name, ordinal_position;