Re: Domain Constraint Violation Error Messages

From: Andres Freund <andres(at)anarazel(dot)de>
To: Benjamin Coutu <ben(dot)coutu(at)zeyos(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: Domain Constraint Violation Error Messages
Date: 2018-07-25 15:34:21
Message-ID: 20180725153421.3oqs4cmaokbw5vky@alap3.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On 2018-07-25 17:23:21 +0200, Benjamin Coutu wrote:
> Please consider domains with domain constraints such as the following:
>
> CREATE DOMAIN "t_txt" text NOT NULL
> CONSTRAINT "dc_txt" CHECK (VALUE <> '');
>
> CREATE DOMAIN "t_sha1" varchar(40) COLLATE "C" NOT NULL DEFAULT ''
> CONSTRAINT "dc_sha1" CHECK (VALUE ~ '^([a-f0-9]{40})?$');
>
> ... and some table such as this contrived example:
>
> CREATE TABLE "myusers" (
> "name" "t_txt",
> "email" "t_txt",
> "token" "t_sha1"
> );
>
> Now, on inserting or updating the table with invalid data, appropriate domain constraint violations occur. But unfortunatly the associated error messages only reference the domain and not the underlying column, e.g. "ERROR: domain public.t_txt does not allow null values".

If you have more verbose error messages turned on (the errors are always
transported to the client), you do get additional information:

terse:

postgres[15271][1]=# \set VERBOSITY terse
postgres[15271][1]=# INSERT INTO myusers (name, email, token) VALUES('', 'b', 'x');
ERROR: value for domain t_txt violates check constraint "dc_txt"
Time: 0.803 ms

verbose:

postgres[15271][1]=# \set VERBOSITY verbose
postgres[15271][1]=# INSERT INTO myusers (name, email, token) VALUES('', 'b', 'x');
ERROR: 23514: value for domain t_txt violates check constraint "dc_txt"
SCHEMA NAME: public
DATATYPE NAME: t_txt
CONSTRAINT NAME: dc_txt
LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3521
Time: 0.503 ms

That seems to address most of your complaint? Unfortunately the column
name is not available, as check constraints can involve more than one
column.

> I'm aware that this is not per se a bug, but without the column info,
> domains are somewhat useless for all practical purposes. Wouldn't you
> agree?

That still doesn't make this a bug. Please ask such questions on the
normal "user question" lists, not on bugs.

Greetings,

Andres Freund

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Ze Victor Harry 2018-07-25 15:39:02 mvn package not installing/error
Previous Message Benjamin Coutu 2018-07-25 15:23:21 Domain Constraint Violation Error Messages