On 06/03/2015 10:02 PM, Peter Geoghegan wrote:
I've noticed some more issues with the jsonb documentation, and the
new jsonb stuff generally. I didn't set out to give Andrew feedback on
the semantics weeks after feature freeze, but unfortunately this feels
like another discussion that we need to have now rather than later.
Yes, I wish you had raised these issues months ago when this was
published. That's the way the process is supposed to work.
"operator jsonb - integer"
===================
Summary: I think that this operator has a problem, but a problem that
can easily be fixed.
I think it was a bad idea to allow array-style removal of object
key/value pairs. ISTM that it implies a level of stability in the
ordering that doesn't make sense. Besides, is it really all that
useful?
The origin of this is nested hstore. Looking at my last version of that
patch, I see:
SELECT 'a=>1, b=>2, c=>3'::hstore - 3;
?column?
------------------------
"a"=>1, "b"=>2, "c"=>3
(1 row)
But I agree that it's not a great contribution to science, especially
since the index will be applied to the list of elements in the somewhat
counter-intuitive storage order we use, and we could just raise an error
if we try to apply integer delete to an object instead of an array.
"operator jsonb - text[]" (and *nested* deletion more generally)
===============================================
Summary: I think that this operator has many problems, and should be
scraped (although only as an operator). IMV nested deletion should
only be handled by functions, and the way that nested deletion works
in general should be slightly adjusted.
The new "operator jsonb - text[]" operator is confusingly inconsistent with:
A) "operator jsonb text"
What exactly is this? I have no idea what you're talking about.
and:
B) the established "operator hstore - text[]" operator, since that
operator deletes all key/value pairs that have keys that match any of
the right operand text array values. In contrast, this new operator is
passed as its right operand an array of text elements that constitute
a "path" (so the order in the rhs text[] operand matters). If the text
element in the rhs text[] operand happens to be what would pass for a
Postgres integer literal, it can be used to traverse lhs array values
through subscripting at that nesting level.
The fact that hstore uses it that way doesn't really concern me. Since
hstore isn't nested it doesn't make a whole lot of sense for it to mean
anything else there. But json(b) is nested, and jsonb - path seems quite
a reasonable treatment, something you're much more likely to want to do
than removeing top level elements in bulk.
Regarding nested deletion behavior more generally, consider this
example of how this can work out badly:
postgres=# select jsonb_delete(jsonb_set('["a"]', '{5}', '"b"'), '{5}') ;
jsonb_delete
--------------
["a", "b"]
(1 row)
Here, we're adding and then deleting an array element at offset 5 (the
string "b"). But the element is never deleted by the outer
jsonb_delete(), because we can't rely on the element actually being
stored at offset 5. Seems a bit fragile.
The behaviour of jsonb_set is pretty explicitly documented. If we wanted
to do something else then we'd have to disable the special meaning given
to negative indices, but that would mean in turn we wouldn't be able to
prepend to an array.
More importantly, consider the inconsistency with "operator jsonb
text" ("point A" above):
postgres=# select '["a"]'::jsonb ?| '{a}'::text[]; -- historic/9.4 behavior
?column?
----------
t
(1 row)
postgres=# select '["a"]'::jsonb - '{a}'::text[]; -- new to 9.5
operator, does not delete!
?column?
----------
["a"]
(1 row)
You are conflating two different things here, quite pointlessly. The RH
operand of ?| is not a path, whereas the RH operand of this - variant
is. The fact that they are both text arrays doesn't mean that they
should mean the same thing. And this is really the whole problem with
the rest of your analysis.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
