Re: ON ERROR in json_query and the like

On Thursday, June 20, 2024, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>
>
> pá 21. 6. 2024 v 6:01 odesílatel Amit Langote <amitlangote09(at)gmail(dot)com>
> napsal:
>
>> On Fri, Jun 21, 2024 at 10:01 AM David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> > > By the standard, it is implementation-defined whether JSON parsing
>> errors
>> > > should be caught by ON ERROR clause.
>> >
>> > Absent someone contradicting that claim I retract my position here and
>> am fine with failing if these "functions" are supplied with something that
>> cannot be cast to json. I'd document them like functions that accept json
>> with the implications that any casting to json happens before the function
>> is called and thus its arguments do not apply to that step.
>>
>> Thanks for that clarification.
>>
>> So, there are the following options:
>>
>> 1. Disallow anything but jsonb for context_item (the patch I posted
>> yesterday)
>>
>> 2. Continue allowing context_item to be non-json character or utf-8
>> encoded bytea strings, but document that any parsing errors do not
>> respect the ON ERROR clause.
>>
>> 3. Go ahead and fix implicit casts to jsonb so that any parsing errors
>> respect ON ERROR (no patch written yet).
>>
>> David's vote seems to be 2, which is my inclination too. Markus' vote
>> seems to be either 1 or 3. Anyone else?
>>
>
> @3 can be possibly messy (although be near Oracle or standard). I don't
> think it is safe - one example '{a:10}' is valid for Oracle, but not for
> Postgres, and using @3 impacts different results (better to raise an
> exception).
>
> The effect of @1 and @2 is similar - @1 is better so the user needs to
> explicitly cast, so maybe it is cleaner, so the cast should not be handled,
> @2 is more user friendly, because it accepts unknown string literal. From a
> developer perspective I prefer @1, from a user perspective I prefer @2.
> Maybe @2 is a good compromise.
>

2 also has the benefit of being standard conforming while 1 does not.

3 is also conforming and I wouldn’t object to it had we already done it
that way.

But since 2 is conforming too and implemented, and we are in beta, I'm
thinking we need to go with this option.

David J.