Re: patch for 9.2: enhanced errors

2011/6/19 Florian Pflug <fgp(at)phlo(dot)org>:
> On Jun19, 2011, at 14:03 , Pavel Stehule wrote:
>> 2011/6/19 Florian Pflug <fgp(at)phlo(dot)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
>

I don't have a idea. These data should be available via GET
DIAGNOSTICS statement, so you can't use a repeated properties. I would
to use a simple access to column names because it is in ANSI SQL.

> 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)
>

there should be some similar, but probably we need to have some
dictionary type in core before. If we are too hurry, then we can have
a problem with backing compatibility :(. Theoretically we have a know
columns in COLUMNS property, so we can serialize values in same order
in serialized array format.

COLUMNS: a, b, c
VALUES: some, else, "some with \" or , "

Regards

Pavel

> 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
>
>