On Jun19, 2011, at 14:03 , Pavel Stehule wrote:
> 2011/6/19 Florian Pflug <fgp@phlo.org>:
>> Speaking as someone who's wished for the feature that Pavel's patch provides
>> many times in the past - shouldn't there also be a field containing the
>> offending value? If we had that, it'd finally be possible to translate
>> constraint-related error messages to informative messages for the user.
>
> The value is available in almost cases. There is only one issue - this
> should not be only one value - it could be list of values - so basic
> question is about format and property name. PostgreSQL doesn't hold
> relation between column and column constraint - all column constraints
> are transformed to table constrains. All column informations are
> derived from constraint - so when constraint is a > b and this
> constraint is false, we have two values.
Hm, you could rename COLUMN to VALUE, make it include the value,
and repeat it for every column in the constraint or index that caused
the error. For example, you'd get
VALUE: "a":5
VALUE: "b":3
if you violated a CHECK constraint asserting that "a < b".
You could also use that in custom constraint enforcement triggers -
i.e. I'm maintaining an application that enforces foreign key
constraints for arrays. With VALUE fields available, I could emit
one value field for every offending array member.
If repeating the same field multiple times is undesirable, the
information could of course be packed into one field, giving
VALUES: ("a":5, "b":3)
for the example from above. My array FK constraint trigger would
the presumably report
VALUES: ("array_field":42, "array_field":23)
if array members 42 and 23 lacked a corresponding row in the
referenced table.
That'd also work work for foreign keys and unique constraints. Exclusion
constraints are harder, because there the conflicting value might also
be of interest. (Hm, actually it might even be for unique indices if
some columns are NULL - not sure right now if there's a mode where we
treat NULL as a kind of wildcard...).
best regards,
Florian Pflug