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 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".
That is a huge problem. When domain types are reused in a single table or across multiple tables/schemata it becomes unclear and hard to debug because one doesn't know what specific data item caused the violation as there is no reference to the originating column. In the example above, even a single INSERT INTO "myusers" statement with a constraint violation on the "t_txt" domain wouldn't be clear on whether it originated because of a bad value in "name" or "email". The use 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 the benefit of using custom domains in the first place? IMHO the biggest value of domain types is the reusability accross the 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 universal schema changes easier and more efficient, and it also saves a lot of redundant catalog data, such as repetitive column 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 cosntraint violation?
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 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
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 msThat 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 constraint on domains may only always refer to one column (through VALUE), right? In any case it would be useful to at least display 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 propagated at least for single column constraints and more importantly for domain constraints.
Import Notes
Resolved by subject fallback
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 msThat 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?
Import Notes
Resolved by subject fallback
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
Import Notes
Reply to msg id not found: 20180725160839.460AF5FB0A@mx.zeyos.comReference msg id not found: 20180725160839.460AF5FB0A@mx.zeyos.com | Resolved by subject fallback
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?
Import Notes
Resolved by subject fallback
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 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.
David J.
P.S. I'm not sure of the specifics off the top of my head but the inclusion
of "NOT NULL" on the domain has limits. I believe the recommended setup is
to also include NOT NULL on the relevant table definitions.
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
/messages/by-id/CAD3a31WR8X1TpjR_MoZxuz4S0BO3ZkPAeLoQ9rPxKHG=728eoQ@mail.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/messages/by-id/CAD3a31WR8X1TpjR_MoZxuz4S0BO3ZkPAeLoQ9rPxKHG=728eoQ@mail.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.
Import Notes
Resolved by subject fallback