On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
On 2019-10-20 13:20:23 -0700, Steven Pousty wrote:
> I would think though that raising an exception is better than a default
> behavior which deletes data.
> As an app dev I am quite used to all sorts of "APIs" throwing exceptions and
> have learned to deal with them.
>
> This is my way of saying that raising an exception is an improvement over the
> current situation. May not be the "best" solution but definitely an
> improvement.
I somewhat disagree. SQL isn't in general a language which uses
exceptions a lot. It does have the value NULL to mean "unknown", and
generally unknown combined with something else results in an unknown
value again:
[...]
Throwing an exception for a pure function seems "un-SQLy" to me. In
particular, jsonb_set does something similar for json values as replace
does for strings, so it should behave similarly.
Now if only the vast majority of users could have and keep this level of understanding in mind while writing complex queries so that they remember to always add protections to compensate for the unique design decision that SQL has taken here...
In this case I would favor a break from the historical to a more safe design, regardless of its novelty in the codebase, since the usage patterns and risks involved with typical JSON using code are considerably different/larger than those for "replace".
Just because its always been done one way, and we won't change existing code, doesn't mean we shouldn't apply lessons learned to newer code. In the case of JSON maybe its too late to worry about changing (though moving to exception is safe) but a policy choice now could at least pave the way to avoid this situation when the next new datatype is implemented. In many functions we do provoke exceptions when known invalid input is provided - supplying a function with a primary/important argument being undefined should fall into the same "malformed" category of problematic input.
David J.
