Re: enhanced error fields

2012/12/29 Peter Geoghegan <peter(at)2ndquadrant(dot)com>:
> On 29 December 2012 05:04, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> again - it is reason why I propose ROUTINE_NAME and TRIGGER_NAME - it
>> can be useful for some use cases, where developer should to handle
>> exception when they coming from known functions/triggers and he would
>> to raise exception, when it was raised elsewhere. Is possible working
>> with CONTEXT, but it needs little bit more work and situation is very
>> similar to fields COLUMN_NAME, where I can parse message and I can get
>> this information. But then it depends on message format.
>
> That seems very thin. Again, I have to point out that a precedent for
> this doesn't really appear to exist in any major programming language,
> even though ISTM it would be just as useful in a wide variety of
> programming environments as it would be within Postgres. As I've said,
> the DB2 GET DIAGNOSTIC stuff isn't anything like what you've proposed.
>
>>> So I'm with Peter G on this: the existing CONTEXT mechanism is good
>>> enough, we do not need to split out selected sub-parts of that as
>>> separate error fields. Moreover, doing so would provide only an utterly
>>> arbitrary subset of the context stack: who's to say whether it would be
>>> more important to report the most closely nested function, or the
>>> outermost one?
>>
>> I don't agree with this argument - you can say too "COLUMN_NAME,
>> TABLE_NAME" is not necessary, because MESSAGE is good enough. I don't
>> see any difference - and I would to use these variables for error
>> handling (not for logging) without dependency on current format of
>> MESSAGE or CONTEXT.
>
> In my judgement, COLUMN_NAME and TABLE_NAME can be used without having
> an unreasonable degree of coupling between client and server-side
> code. They are at least easily understood, and not at all astonishing,
> unlike ROUTINE_NAME. That said, CONSTRAINT_NAME clearly is by far the
> most compelling new field, and I actually wouldn't mind losing the
> other ones too much. I might have suggested losing COLUMN_NAME and
> TABLE_NAME myself if we could reliably get something like a constraint
> name for NOT NULL violations.
>

Maybe this is main difference of our views. In my mind I never
thinking about using ROUTINE_NAME on client side part. My motivation
and reason why I push this feature is a using ROUTINE_NAME inside
PL/pgSQL - or other PL, where it can helps with more precious
exception processing - one real example - handling exception in
business process routines

BEGIN
...
... business logic
EXCEPTION WHEN OTHERS THEN
.. all is returned back
GET STACKED DIAGNOSTICS _message = MESSAGE, _routine_name =
ROUTINE_NAME, ...
IF verbosity <> 'verbose' THEN
INSERT INTO log(...) VALUES(_message, _routine_name)
ELSE
-- log CONTEXT too
INSERT INTO log(...) VALUES(_message, _routine_name, _context);
END IF;
END;

ROUTINE_NAME is information, that has different character from MESSAGE
--> and it is simply (not too detailed) information, where exception
coming from. Usually I don't know what check or builtin function
failed, because it is described by MESSAGE. And I don't would to use
CONTEXT everywhere, because it is too detailed.

>> Second question - what should be context is important. I am thinking
>> so standard and implementation in other databases is relative clean -
>> it is name of custom routine, that doesn't handle exception. Moving to
>> pg - it is name of routine on the top on error callback stack, because
>> builtin functions doesn't set it - I don't need on top of CONTEX -
>> div_int or other name of builtin function - but I need there function
>> foo(b): a := 10/b.
>
> I don't think the fact that built-in functions don't set ROUTINE_NAME
> supports your position. In fact, it seems pretty broken to me that one
> pl handler sets the value, while others may not. Furthermore, the
> distinction between built-in and not built-in is fairly small within
> Postgres - who is to say that even if a person thinks the proposed
> semantics are useful, they'll continue to when they find out that
> ROUTINE_NAME isn't set to the name of their C function?

If C function sets error callback then it can fill ROUTINE_NAME.

yes, now errors callback is used only for PL languages - probably it
is more intuitive design than explicit design, but it is working. It
is different design than in generic languages, where you can track
exception to really last point. But I don't think so it is necessary
in PL - typical customer is not interested in PostgreSQL internals.

>
>> other point - theoretically I can get ROUTINE_NAME from CONTEXT, but I
>> cannot get TRIGGER_NAME - this information is lost.
>
> I don't think there should be a TRIGGER_NAME either - I think that we
> should make interfaces easy to use correctly, and hard to use
> incorrectly, and a mechanised response to a TRIGGER_NAME seems
> incorrect. If you think that there should be a trigger name within
> CONTEXT, there might be a case to be made for that, but I would prefer
> to have that reviewed separately.

We can do it safely for any PL language - because PL handlers
correctly use error callbacks.

>
> --
> Peter Geoghegan http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training and Services