On constraint violation, return any constraint comment in the response

[a-mckinley]

If an insert or update causes a constraint violation, including the comment in the response would be very helpful as a first option for user feedback.

E.g.

create table test ( 
    sometext text not null, 
    constraint length_check check (char_length(sometext) > 5));
);
comment on constraint length_check on test is 'sometext must be longer than 5 characters';

If the comment was included in the json response when the failing insert was attempted, it would be very informative and could be potentially directly displayed to the user instead of custom code in the front end to parse and make sense of the error.

[begriffs]

Alastair McKinley wrote:

If an insert or update causes a constraint violation, including the comment in the response would be very helpful

I experimented a while ago with customizing postgresql error messages based on comments in the database:

https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#improved-error-messages

It may be irrelevant, but posting it here in case it's useful for thinking about the implementation.

[steve-chavez]

@a-mckinley One simple way could be naming the constraint as descriptively as possible. I've seen this used on sql server https://stackoverflow.com/a/12920655/4692662. So like:

create table test (
    sometext text not null,
    constraint "sometext must be longer than 5 characters" 
    check (char_length(sometext) > 5)
);

curl -H "Content-Type: application/json" -d '{"sometext": "ee"}' l:3000/test

{"hint":null,"details":"Failing row contains (ee).",
"code":"23514","message":"new row for relation \"test\" violates check constraint \"sometext must be longer than 5 characters\""}

(you'd have to work a bit to extract the text from the message, but should be doable since the error message is regular for every table)

pg constraints names are scoped by table, so it would be fine if you have the same description for another table constraint.

Would that work for you?

---

Related: https://github.com/PostgREST/postgrest/issues/2706

[wolfgangwalther]

I experimented a while ago with customizing postgresql error messages based on comments in the database: https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#improved-error-messages It may be irrelevant, but posting it here in case it's useful for thinking about the implementation.

It is useful. Since catching the exception in plpgsql is not going to work for us, we need to implement the same idea on the "client-side" (as in sql client, so in postgrest).

I found this:

For some types of errors, the server reports the name of a database object (a table, table column, data type, or constraint) associated with the error; for example, the name of the unique constraint that caused a unique_violation error. Such names are supplied in separate fields of the error report message so that applications need not try to extract them from the possibly-localized human-readable text of the message. As of PostgreSQL 9.3, complete coverage for this feature exists only for errors in SQLSTATE class 23 (integrity constraint violation), but this is likely to be expanded in future.

So it seems like the same fields (table, column, constraint names) should be available through libpq as well.

The docs for libpq mention those fields here: https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-EXEC-MAIN

PQresultErrorField [...] PG_DIAG_SCHEMA_NAME [...] PG_DIAG_TABLE_NAME [...] PG_DIAG_COLUMN_NAME [...] PG_DIAG_DATATYPE_NAME [...] PG_DIAG_CONSTRAINT_NAME [...]

In postgresql-libpq the error fields are mapped here: https://hackage.haskell.org/package/postgresql-libpq-0.9.4.3/docs/Database-PostgreSQL-LibPQ.html#t:FieldCode

The name fields are however missing from that list.

Looking at the source code for that, it seems like it should be easy to add those, though:

https://github.com/haskellari/postgresql-libpq/blob/421502b664efb4c4893f16171fd91f6f9f648322/src/Database/PostgreSQL/LibPQ.hsc#L1140-L1166

Once this is done, they would have to be made available through hasql as well: https://hackage.haskell.org/package/hasql-1.4.4.2/docs/Hasql-Session.html#t:ResultError

Once all of this is done, it should be trivial to load those comments from our schema cache when an exception is handled.