Re: [HACKERS] message for constraint

Hi,

From time to time people ask me if there is a way to "customize"
messages for constraints so they could be more informative to the
user...

Imagine something like:

create table foo (fld int4 check (fld > 0));
message for constraint foo_fld_check on foo is 'fld field must contain
possitive numbers only.';

so i can let this message go directly to my users, and they can
respond without knowing waht a check constraint is...

we can use the pg_description catalog with a column added to indicate
if it is a comment or a message for constraint...

what do you think, it's worth the effort?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Jaime Casanova wrote:
> From time to time people ask me if there is a way to "customize"
> messages for constraints so they could be more informative to the
> user...

What about this?

=> create table foo (fld int4 constraint "fld must contain positive numbers" check (fld > 0));
CREATE TABLE
=> insert into foo values (-5);
ERROR: new row for relation "foo" violates check constraint "fld must contain positive numbers"

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 1/14/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Jaime Casanova wrote:
> > From time to time people ask me if there is a way to "customize"
> > messages for constraints so they could be more informative to the
> > user...
>
> What about this?
>
> => create table foo (fld int4 constraint "fld must contain positive numbers" check (fld > 0));
> CREATE TABLE
> => insert into foo values (-5);
> ERROR: new row for relation "foo" violates check constraint "fld must contain positive numbers"
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>

ok, i didn't know you can use such names...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

I have added the following documentation addition to suggest this usage
for constraint names.

---------------------------------------------------------------------------

Jaime Casanova wrote:
> On 1/14/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > Jaime Casanova wrote:
> > > From time to time people ask me if there is a way to "customize"
> > > messages for constraints so they could be more informative to the
> > > user...
> >
> > What about this?
> >
> > => create table foo (fld int4 constraint "fld must contain positive numbers" check (fld > 0));
> > CREATE TABLE
> > => insert into foo values (-5);
> > ERROR: new row for relation "foo" violates check constraint "fld must contain positive numbers"
> >
> > --
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> >
>
> ok, i didn't know you can use such names...
>
> --
> regards,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 1/16/06, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> I have added the following documentation addition to suggest this usage
> for constraint names.
>
> ---------------------------------------------------------------------------
>
> --- 293,304 ----
> <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
> <listitem>
> <para>
> ! An optional name for a column or table constraint. If the
> ! constraint is violated, the constraint name is present in error messages,
> ! so constraint names like <literal>"col must be positive"</> can be used
> ! to communicate helpful constraint information to client applications.
> ! (Double-quotes are required for constraint names that contain spaces.)
> ! If not specified, the system generates a name.
> </para>
> </listitem>
> </varlistentry>
>
>

a lot better... thanx...

what about lower the context part of the messages for plpgsql
functions? that seems debug info for me... just an idea ;)

pruebas=# select prueba();
ERROR: new row for relation "foo" violates check constraint "foo_fld_check"
CONTEXT: SQL statement "insert into foo values (-1)"
PL/pgSQL function "prueba" line 2 at SQL statement

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Jaime Casanova wrote:
> On 1/16/06, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:

> what about lower the context part of the messages for plpgsql
> functions? that seems debug info for me... just an idea ;)
>
> pruebas=# select prueba();
> ERROR: new row for relation "foo" violates check constraint "foo_fld_check"
> CONTEXT: SQL statement "insert into foo values (-1)"
> PL/pgSQL function "prueba" line 2 at SQL statement

It's not debug info in the sense of the DEBUG error levels. These are
meant as debug info of the server itself, like, say, a transaction
started. The CONTEXT line is exactly that, context information.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"World domination is proceeding according to plan" (Andrew Morton)

On 1/16/06, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Jaime Casanova wrote:
> > On 1/16/06, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> > what about lower the context part of the messages for plpgsql
> > functions? that seems debug info for me... just an idea ;)
> >
> > pruebas=# select prueba();
> > ERROR: new row for relation "foo" violates check constraint "foo_fld_check"
> > CONTEXT: SQL statement "insert into foo values (-1)"
> > PL/pgSQL function "prueba" line 2 at SQL statement
>
> It's not debug info in the sense of the DEBUG error levels. These are
> meant as debug info of the server itself, like, say, a transaction
> started. The CONTEXT line is exactly that, context information.
>

ok... maybe notice? log? i suppose that the ERROR line is error level
so the idea is lowering the context so i can put
client_min_messages='error' and see just what the user can
understand...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> ok... maybe notice? log? i suppose that the ERROR line is error level
> so the idea is lowering the context so i can put
> client_min_messages='error' and see just what the user can
> understand...

If you don't want to show the context field, build your own error
message from the other fields. libpq provides adequate support for
that. I'm not sure what the state of play is in JDBC or other APIs,
but if you need this you should be lobbying the client-side library
authors to change, not the backend.

regards, tom lane