Обсуждение: Domain Constraint Violation Error Messages

Hello,

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
unfortunatlythe associated error messages only reference the domain and not the underlying column, e.g. "ERROR: domain
public.t_txtdoes not allow null values". 

That is a huge problem. When domain types are reused in a single table or across multiple tables/schemata it becomes
unclearand hard to debug because one doesn't know what specific data item caused the violation as there is no reference
tothe originating column. In the example above, even a single INSERT INTO "myusers" statement with a constraint
violationon the "t_txt" domain wouldn't be clear on whether it originated because of a bad value in "name" or "email".
Theuse of multi-statement queries and writable CTEs make this problem even worse. 

On stackoverflow there are some comments suggesting that one should simply stick to single-use domains, but then what's
thebenefit of using custom domains in the first place? IMHO the biggest value of domain types is the reusability
accrossthe entire database. In our case whe have one base/public schema with a set of domain types and multiple uniform
schemata(think multi-tenancy) with thousands of tables that reference those few same domains. This allows us to make
universalschema changes easier and more efficient, and it also saves a lot of redundant catalog data, such as
repetitivecolumn level constraints and default expressions). 

Is there any way you could display the underlying column and schema-qualified table names at least as DETAIL on
cosntraintviolation? 
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? 

Thanks in advance.

Benjamin Coutu

ZeyOS, Inc.

ben.coutu@zeyos.com
http://www.zeyos.com

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
unfortunatlythe associated error messages only reference the domain and not the underlying column, e.g. "ERROR: domain
public.t_txtdoes 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

> 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.
>

Well, the problem is that the underlying column is not referenced, and neither is the table. That's the issue right
there.

Also, I'm aware that regular table constraints can reference multiple columns. But it is my understanding that check
constrainton domains may only always refer to one column (through VALUE), right? In any case it would be useful to at
leastdisplay the underlying table name. 

In general, I understand that it is not trivial given the generic/unified use of constraints whether it's column
constraints,table constraints or domain constraints. I'm not giving up hope though, that the column info can be
propagatedat least for single column constraints and more importantly for domain constraints. 

Hi,

On 2018-07-25 17:51:21 +0200, Benjamin Coutu wrote:
> > 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.
> > 
> 
> Well, the problem is that the underlying column is not referenced, and neither is the table. That's the issue right
there.

True.  I didn't yet have my first coffee, sorry for that :).  I think
it'd possibly not be too hard to add support for naming the TABLE. Would
that already be helpful enough for you?

Greetings,

Andres Freund

> True.  I didn't yet have my first coffee, sorry for that :).  I think
> it'd possibly not be too hard to add support for naming the TABLE. Would
> that already be helpful enough for you?

Yeah, that would be of tremendous help, thanks Andres!
Any chance that could still make it into PG11?

On 2018-07-25 18:06:21 +0200, Benjamin Coutu wrote:
> > True.  I didn't yet have my first coffee, sorry for that :).  I think
> > it'd possibly not be too hard to add support for naming the TABLE. Would
> > that already be helpful enough for you?
> 
> Yeah, that would be of tremendous help, thanks Andres!
> Any chance that could still make it into PG11?

No way. PG11 has been feature frozen for quite a while.

Greetings,

Andres Freund

> No way. PG11 has been feature frozen for quite a while.

I understand, thanks. I thought, maybe it would qualify as a trivial "bug" fix, sorry for that.
Would it be hard to also include column name(s) for PG 12 then?

Hi,

On 2018-07-25 18:19:21 +0200, Benjamin Coutu wrote:
> > No way. PG11 has been feature frozen for quite a while.
> 
> I understand, thanks. I thought, maybe it would qualify as a trivial
> "bug" fix, sorry for that.

It's not a bug. People match against error messags. So it'd even have
the potential to break things.


> Would it be hard to also include column name(s) for PG 12 then?

I haven't looked enough to judge that. Unless somebody invests time
doing so, and implementing the feature (be it on a table or column
basis), not much is going to happen...  You can try to nerd snipe
somebody on the issue, work on it yourself, or pay somebody...

Greetings,

Andres Freund

On 2018-07-25 09:31:30 -0700, David G. Johnston wrote:
> On Wed, Jul 25, 2018 at 9:19 AM, Benjamin Coutu <ben.coutu@zeyos.com> wrote:
> 
> > > No way. PG11 has been feature frozen for quite a while.
> >
> > I understand, thanks. I thought, maybe it would qualify as a trivial "bug"
> > fix, sorry for that.
> > Would it be hard to also include column name(s) for PG 12 then?
> >
> 
> IIUC this general problem (it also applies to, e.g., varchar(20)) ​is well
> known and has been discussed many times, as recently as the last 6 months
> if memory serves.  The lack of concrete progress, as well as general
> sentiment, leads me to think that the cost-benefit calculation for
> improving things in this area is extremely poor.  It is not an easy (and,
> likely inexpensive run-time effort) thing to add context to what is a
> simple type input function error.

I think the INSERT ... VALUES() case is actually comparatively
simple. Both code and runtime complexity wise.  And that'd probably
solve a large fraction of the need.  Might even be realistic to tackle
the source->table implicit casts, without adding too much overhead.

If you're instead talking about doing something for every possible use
of a domain, then the problem obviously gets way more complicated.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2018-07-25 17:51:21 +0200, Benjamin Coutu wrote:
>> Well, the problem is that the underlying column is not referenced, and neither is the table. That's the issue right
there.

> True.  I didn't yet have my first coffee, sorry for that :).  I think
> it'd possibly not be too hard to add support for naming the TABLE. Would
> that already be helpful enough for you?

I think we'd be better off casting this as a more generic "report the
location of execution-time errors" issue.  See previous discussions
such as

https://www.postgresql.org/message-id/flat/CAD3a31WR8X1TpjR_MoZxuz4S0BO3ZkPAeLoQ9rPxKHG%3D728eoQ%40mail.gmail.com

            regards, tom lane

> I think we'd be better off casting this as a more generic "report the
> location of execution-time errors" issue.  See previous discussions
> such as
>
> https://www.postgresql.org/message-id/flat/CAD3a31WR8X1TpjR_MoZxuz4S0BO3ZkPAeLoQ9rPxKHG%3D728eoQ%40mail.gmail.com
>

I havn't followed the referenced thread, sorry. At least it's clear that this is an issue that others have as well.
I totally agree with you that it would be nice to have a general solution for reporting such execution errors.