Re: Constraint violations don't report the value that violates

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL - General ML <pgsql-general(at)postgresql(dot)org>
Subject: Re: Constraint violations don't report the value that violates
Date: 2008-02-07 06:34:47
Message-ID: 47AAA687.70008@pws.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Tom Lane wrote:
>> Foreign keys give a value that is failing for the foreign key, is there
>> a reason that other constraints don't do the same thing?
>>
>
> It's not very well-defined, for instance what would you report for
>
> CHECK some_black_box_function(a, b, c+d)
>
> FKs are constrained by the spec syntax to only involve simple column
> values, but this is not the case for either CHECK or unique indexes.
>
> Unique partial indexes would make things even more interesting.
>
> regards, tom lane
>
I would have thought that the inputs would be well defined. In the
example, a,b,c and d. This would be the same for any partial index.

So instead of this report where test3_z_check is the black box function
above;
ERROR: new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT: insert into test3 select g from generate_series(-1,1) as g;

You would get an error like this;
ERROR: new row for relation "test3" violates check constraint
"test3_z_check"
DETAIL: Input (a) = 'fred', (b) = 2, (c) = 6, (d) = -1
STATEMENT: insert into test3 select g from generate_series(-1,1) as g;

The data must have been attempted to be inserted with values to violate
the check. I can't see how it's possible to have an input set of data
that is not a specific set of values for a check/unique constraint.

some_black_box_function must be immutable and can only take column data elements from the parent table. Or am I missing something about the complexity that I can make indexes?

Regards

Russell Smith

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2008-02-07 08:01:38 Subquery Factoring ?
Previous Message Andrej Ricnik-Bay 2008-02-07 02:41:40 Re: Reload only specific databases from pg_dumpall