Provide detailed error info for POST to records API when in violation of a unique constraint

[seancolsen]

Current behavior

1. Begin with the following data:

   DROP TABLE IF EXISTS "musician_roles";
   CREATE TABLE "musician_roles" (
       "id" serial NOT NULL,
       "band_name" character varying(255),
       "person_name" character varying(255),
       "instrument" character varying(255),
       "start_year" integer,
       "end_year" integer,
       CONSTRAINT "person_pkey" PRIMARY KEY ("id"),
       CONSTRAINT "musician_roles_unique_key"
           UNIQUE ("band_name", "person_name", "instrument")
   );
   
   INSERT INTO "musician_roles" 
       ( "band_name"   , "person_name"     , "instrument", "start_year", "end_year" )
   VALUES
       ( 'The Beatles' , 'John Lennon'     , 'guitar'    , 1960        , 1970       ),
       ( 'The Beatles' , 'John Lennon'     , 'vocals'    , 1960        , 1970       ),
       ( 'The Beatles' , 'Paul McCartney'  , 'bass'      , 1960        , 1970       ),
       ( 'The Beatles' , 'Paul McCartney'  , 'vocals'    , 1960        , 1970       ),
       ( 'The Beatles' , 'George Harrison' , 'guitar'    , 1960        , 1970       ),
       ( 'The Beatles' , 'George Harrison' , 'vocals'    , 1960        , 1970       ),
       ( 'The Beatles' , 'Ringo Starr'     , 'drums'     , 1960        , 1970       ),
       ( 'The Beatles' , 'Ringo Starr'     , 'vocals'    , 1960        , 1970       );
   

2. View the table in Mathesar.

3. On row 8, edit the value for instrument, changing "vocals" to "drums".

4. Observe a PATCH request to the records API.

5. Observe the response to have HTTP status code 500. Expect a 4xx code instead.

6. Observe the response body as:

   [
     {
       "code": 4208,
       "message": "This column has non-unique values so a unique constraint cannot be set",
       "field": null,
       "detail": null
     }
   ]
   

7. Expect the response to give the client a way to determine which columns were in violation of the unique constraint.

Desired response body

We could take a variety of different approach here to satisfy the goal of giving the client more info about the columns.

I've marked this ticket as "draft" so that we may discuss various response body schemas and come to an agreement.

Some ideas thus far:

• In a separate discussion, @pavish said:

  if the server could respond with the name of the constraint that failed, we can use that to highlight all associated columns.

• We could also respond with an array of column ids

[seancolsen]

@kgodey I stuck this in [06] Working with Tables because it fit well with other work there. I know we're trying to close out that milestone though, so feel free to re-prioritize this.

[mathemancer]

I'm going to return the violated constraint rather than fields. The reason is, the violation may have to do with a union of fields (this is different from each field being violated individually).

Unfortunately, PostgreSQL only gives one violation per error. That is, if there are two uniqueness constraints on the table, and we violate both, the error can only tell us about one.