public inbox for [email protected]help / color / mirror / Atom feed
How to get column, table or parameter name reporting when violating DOMAIN type constraint 6+ messages / 3 participants [nested] [flat]
* How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-26 10:54 Valerio Battaglia <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Valerio Battaglia @ 2023-03-26 10:54 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-26 15:13 David G. Johnston <[email protected]> parent: Valerio Battaglia <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2023-03-26 15:13 UTC (permalink / raw) To: Valerio Battaglia <[email protected]>; +Cc: [email protected] <[email protected]> On Sunday, March 26, 2023, Valerio Battaglia <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-26 15:26 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2023-03-26 15:26 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Valerio Battaglia <[email protected]>; [email protected] <[email protected]> "David G. Johnston" <[email protected]> writes: > On Sunday, March 26, 2023, Valerio Battaglia <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-26 15:36 David G. Johnston <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2023-03-26 15:36 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Valerio Battaglia <[email protected]>; [email protected] <[email protected]> On Sunday, March 26, 2023, Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected]> writes: > > On Sunday, March 26, 2023, Valerio Battaglia <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-26 16:00 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2023-03-26 16:00 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Valerio Battaglia <[email protected]>; [email protected] <[email protected]> "David G. Johnston" <[email protected]> writes: > On Sunday, March 26, 2023, Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint @ 2023-03-27 00:37 Valerio Battaglia <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Valerio Battaglia @ 2023-03-27 00:37 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> 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 <[email protected]> wrote: > "David G. Johnston" <[email protected]> writes: > > On Sunday, March 26, 2023, Tom Lane <[email protected]> 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 > ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-03-27 00:37 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-03-26 10:54 How to get column, table or parameter name reporting when violating DOMAIN type constraint Valerio Battaglia <[email protected]> 2023-03-26 15:13 ` David G. Johnston <[email protected]> 2023-03-26 15:26 ` Tom Lane <[email protected]> 2023-03-26 15:36 ` David G. Johnston <[email protected]> 2023-03-26 16:00 ` Tom Lane <[email protected]> 2023-03-27 00:37 ` Valerio Battaglia <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox