Hello,
On Fri, Oct 18, 2019 at 5:57 PM Christoph Moench-Tegeder
<c...@burggraben.net> wrote:
>
> ## Ariadne Conill (aria...@dereferenced.org):
>
> > Why don't we fix the database engine to not eat data when the
> > jsonb_set() operation fails?
>
> It didn't fail, it worked like SQL (you've been doing SQL for too
> long when you get used to the NULL propagation, but that's still
> what SQL does - check "+" for example).
> And changing a function will cause fun for everyone who relies on
> the current behaviour - so at least it shouldn't be done on a whim
> (some might argue that a whim was what got us into this situation
> in the first place).
NULL propagation makes sense in the context of traditional SQL. What
users expect from the JSONB support is for it to behave as JSON
manipulation behaves everywhere else. It makes sense that 2 + NULL
returns NULL -- it's easily understood as a type mismatch. It does
not make sense that jsonb_set('{}'::jsonb, '{foo}', NULL) returns NULL
because a *value* was SQL NULL. In this case, it should, at the
least, automatically coalesce to 'null'::jsonb.
> Continuing along that thought, I'd even argue that your are
> writing code which relies on properties of the data which you never
> guaranteed. There is a use case for data types and constraints.
There is a use case, but this frequently comes up as a question people
ask. At some point, you have to start pondering whether the behaviour
does not make logical sense in the context that people frame the JSONB
type and it's associated manipulation functions. It is not *obvious*
that jsonb_set() will trash your data, but that is what it is capable
of doing. In a database that is advertised as being durable and not
trashing data, even.
> Not that I'm arguing for maximum surprise in programming, but
> I'm a little puzzled when people eschew thew built-in tools and
> start implmenting them again side-to-side with what's already
> there.
If you read the safe_jsonb_set() function, all we do is coalesce any
SQL NULL to 'null'::jsonb, which is what it should be doing anyway,
and then additionally handling any *unanticipated* failure case on top
of that. While you are arguing that we should use tools to work
around unanticipated effects (that are not even documented -- in no
place in the jsonb_set() documentation does it say "if you pass SQL
NULL to this function as a value, it will return SQL NULL"), I am
arguing that jsonb_set() shouldn't set people up for their data to be
trashed in the first place.
Even MySQL gets this right. MySQL! The database that everyone knows
takes your data out for a night it will never forget. This argument
is miserable. It does not matter to me how jsonb_set() works as long
as it does not return NULL when passed NULL as a value to set. JSONB
columns should be treated as the complex types that they are: you
don't null out an entire hash table because someone set a key to SQL
NULL. So, please, let us fix this.
Ariadne
