MIME-Version: 1.0
References: 
 <CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com>
 <CAKFQuwYJtF-5Uvqo6ahKkeEAxOKta_yVb3n3at5Y=mvSgC0FrA@mail.gmail.com>
 <2911010.1679844383@sss.pgh.pa.us>
 <CAKFQuwa00CR2HPxmngsGqx0mGOc4j215gZ4j_v32OYO_gfpQYg@mail.gmail.com>
 <2914598.1679846405@sss.pgh.pa.us>
In-Reply-To: <2914598.1679846405@sss.pgh.pa.us>
From: Valerio Battaglia <vabatta@gmail.com>
Date: Mon, 27 Mar 2023 02:37:59 +0200
Message-ID: 
 <CACxJNS=sXe14q3o1_VXpU+fkuKcc5r09xqVLva68kY30hvf9qw@mail.gmail.com>
Subject: Re: How to get column, table or parameter name reporting when
 violating DOMAIN type constraint
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	"pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001f5fc105f7d6f579"
Archived-At: 
 <https://www.postgresql.org/message-id/CACxJNS%3DsXe14q3o1_VXpU%2BfkuKcc5r09xqVLva68kY30hvf9qw%40mail.gmail.com>
Precedence: bulk

--0000000000001f5fc105f7d6f579
Content-Type: text/plain; charset="UTF-8"

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
>

--0000000000001f5fc105f7d6f579
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div>Thanks for the answer and explanation. My reason=
ing behind the
 use of domains, particularly in function calls, is to have a useful=20
approach for validating=20
input before it enters a function. By doing so, it eliminates the need=20
to manually verify input for public / exposed functions, and would effectiv=
ely make domains provide an=20
efficient way to encapsulate and enforce business rules.<br><br></div><div>=
Considering
 the aforementioned example, having a message reporting with the=20
position name would make the consumer life much easier, program or human
 alike</div><div><pre><code>SELECT my_function(100, -100);<br>-- ERROR: val=
ue &quot;second_parameter&quot; <span>for</span> domain my_domain violates =
<span>check</span> <span>constraint</span> &quot;value_min&quot;<br><br>SEL=
ECT (-1)::my_domain;<br></code><code>-- ERROR: value &quot;unnamed&quot; <s=
pan>for</span> domain my_domain violates <span>check</span> <span>constrain=
t</span> &quot;value_min&quot;</code></pre></div><br></div><div>Valerio<br>=
</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_=
attr">On Sun, 26 Mar 2023 at 18:00, Tom Lane &lt;<a href=3D"mailto:tgl@sss.=
pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">&quot;David G. Johnston&quot; &lt;<a href=3D"ma=
ilto:david.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.c=
om</a>&gt; writes:<br>
&gt; On Sunday, March 26, 2023, Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.=
pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>
&gt;&gt; More to the point, you have the wrong mental model: a domain const=
raint<br>
&gt;&gt; violation might not be associated with a table column at all.<br>
<br>
&gt; I disagree that the mental model is wrong.<br>
<br>
The OP is asking for action-at-a-distance.=C2=A0 There are probably cases<b=
r>
where the distance is short enough that we could associate the runtime<br>
error with a particular insertion target column, but I don&#39;t think it<b=
r>
could possibly be made to work for every sort of insert/select query.<br>
In any case, the possibility of a hypothetical future feature (which<br>
nobody is actively working on AFAIK) doesn&#39;t seem like a very useful<br=
>
answer here.<br>
<br>
&gt; There are existing threads that I may hunt later.=C2=A0 IIRC, you (Tom=
) even<br>
&gt; suggested a possible way forward on this last time it came up.<br>
<br>
I recall proposing that we try to produce syntax-error-like error<br>
cursors for runtime errors, but it&#39;s not apparent to me that that&#39;d=
<br>
be tremendously applicable to the OP&#39;s problem.=C2=A0 The output would<=
br>
look something like<br>
<br>
ERROR: value for domain my_domain violates check constraint &quot;value_min=
&quot;<br>
LINE 1: SELECT my_function(100, -100);<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^<br>
<br>
which might be helpful for a human, but probably not very much so<br>
for a program.=C2=A0 (BTW, this illustrates another issue: I&#39;m pretty<b=
r>
sure that in the given case, the error is thrown while evaluating<br>
my_function&#39;s arguments, long before we get within hailing distance<br>
of any INSERT at all.)<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--0000000000001f5fc105f7d6f579--