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

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

From

Valerio Battaglia

Date:

26 March 2023, 13:54:18

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

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

From

"David G. Johnston"

Date:

26 March 2023, 18:13:50

On Sunday, March 26, 2023, Valerio Battaglia <vabatta@gmail.com> wrote:

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

What you see is what you get.

David J.

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

From

Tom Lane

Date:

26 March 2023, 18:26:23

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, March 26, 2023, Valerio Battaglia <vabatta@gmail.com> wrote:
>> 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

> 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

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

From

"David G. Johnston"

Date:

26 March 2023, 18:36:58

On Sunday, March 26, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, March 26, 2023, Valerio Battaglia <vabatta@gmail.com> wrote:
>> 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

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

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.

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

From

Tom Lane

Date:

26 March 2023, 19:00:05

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, March 26, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> More to the point, you have the wrong mental model: a domain constraint
>> violation might not be associated with a table column at all.

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

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

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

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

From

Valerio Battaglia

Date:

27 March 2023, 03:37:59

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

On Sun, 26 Mar 2023 at 18:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, March 26, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> More to the point, you have the wrong mental model: a domain constraint
>> violation might not be associated with a table column at all.

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

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

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