Subject: How to get column, table or parameter name reporting when violating DOMAIN type constraint

From: Valerio Battaglia <[email-masked]>
Date: Sun, 26 Mar 2023 12:54:18 +0200

Hi,

I am reaching out to request assistance with obtaining column, table or
parameter name when a DOMAIN type constraint is violated in PostgreSQL.
Specifically, I am looking for a way to easily identify the column or
parameter that is causing the constraint violation, so that I can have
better error / validation reporting in the client.

Imagine the following SQL code:

CREATE DOMAIN my_domain AS integer
 CONSTRAINT value_min CHECK (VALUE > 0)
 CONSTRAINT value_max CHECK (VALUE <= 200);
CREATE FUNCTION my_function(first_parameter my_domain,
second_parameter my_domain) RETURNS void AS $$BEGIN
  INSERT INTO my_table (first_column, second_column) VALUES
(first_parameter, second_parameter);END;
$$ LANGUAGE plpgsql;

When I call this function with a value that violates the DOMAIN type
constraint, I receive the following error message:

SELECT my_function(100, -100);
-- ERROR:  value for domain my_domain violates check constraint "value_min"

However, this error message does not provide any information about which
column or parameter is causing the constraint violation. The same scenario
applies when calling the INSERT INTO statement directly as well.

Is there a way to obtain more detailed information about the column, table
or parameter that is causing the constraint violation in this scenario? I
would greatly appreciate any guidance or advice you could provide on this
matter.

Thanks

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 26 Mar 2023 08:13:50 -0700

On Sunday, March 26, 2023, Valerio Battaglia <[email-masked]> wrote:
> matter

What you see is what you get.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sun, 26 Mar 2023 11:26:23 -0400

> What you see is what you get.

More to the point, you have the wrong mental model: a domain constraint
violation might not be associated with a table column at all.
For example,

postgres=# select (-1)::my_domain;
ERROR:  value for domain my_domain violates check constraint "value_min"

There is some useful data split out into fields of the error report:

postgres=# \errverbose 
ERROR:  23514: value for domain my_domain violates check constraint "value_min"
SCHEMA NAME:  public
DATATYPE NAME:  my_domain
CONSTRAINT NAME:  value_min
LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3651

... but the only context that's available is the domain name.

If you made the constraints be table check constraints, then
you'd have localization of the sort you want:

postgres=# create table t1 (col1 int check (col1 > 0));
CREATE TABLE
postgres=# insert into t1 values (-1);
ERROR:  new row for relation "t1" violates check constraint "t1_col1_check"
DETAIL:  Failing row contains (-1).
postgres=# \errverbose 
ERROR:  23514: new row for relation "t1" violates check constraint "t1_col1_check"
DETAIL:  Failing row contains (-1).
SCHEMA NAME:  public
TABLE NAME:  t1
CONSTRAINT NAME:  t1_col1_check
LOCATION:  ExecConstraints, execMain.c:2023

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 26 Mar 2023 08:36:58 -0700

> violation might not be associated with a table column at all.
I disagree that the mental model is wrong.  Asking for better context when
constructing rows is a common request not only for domains but even simple
data types like dates or numbers.  Sure, that added context is not always
applicable but when it is the lack of error detail does make life hard.
But it isn’t an easy problem to solve either.

There are existing threads that I may hunt later.  IIRC, you (Tom) even
suggested a possible way forward on this last time it came up.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sun, 26 Mar 2023 12:00:05 -0400

> I disagree that the mental model is wrong.

The OP is asking for action-at-a-distance.  There are probably cases
where the distance is short enough that we could associate the runtime
error with a particular insertion target column, but I don't think it
could possibly be made to work for every sort of insert/select query.
In any case, the possibility of a hypothetical future feature (which
nobody is actively working on AFAIK) doesn't seem like a very useful
answer here.

> suggested a possible way forward on this last time it came up.

I recall proposing that we try to produce syntax-error-like error
cursors for runtime errors, but it's not apparent to me that that'd
be tremendously applicable to the OP's problem.  The output would
look something like

ERROR: value for domain my_domain violates check constraint "value_min"
LINE 1: SELECT my_function(100, -100);
                                ^

which might be helpful for a human, but probably not very much so
for a program.  (BTW, this illustrates another issue: I'm pretty
sure that in the given case, the error is thrown while evaluating
my_function's arguments, long before we get within hailing distance
of any INSERT at all.)

			regards, tom lane

---

From: Valerio Battaglia <[email-masked]>
Date: Mon, 27 Mar 2023 02:37:59 +0200

Thanks for the answer and explanation. My reasoning behind the use of
domains, particularly in function calls, is to have a useful approach for
validating input before it enters a function. By doing so, it eliminates
the need to manually verify input for public / exposed functions, and would
effectively make domains provide an efficient way to encapsulate and
enforce business rules.

Considering the aforementioned example, having a message reporting with the
position name would make the consumer life much easier, program or human
alike

SELECT my_function(100, -100);
-- ERROR: value "second_parameter" for domain my_domain violates check
constraint "value_min"

SELECT (-1)::my_domain;
-- ERROR: value "unnamed" for domain my_domain violates check
constraint "value_min"

Valerio