Hi! During my work on bringing jsonpath patchset to commit, I was always keeping in mind that we need to make jsonb_path_*() functions immutable. Having these functions immutable, users can build expression indexes over them. Naturally, in majority of cases one doesn't need to index whole json documents, but only some parts of them. jsonpath provide great facilities to extract indexable parts of document, much more powerful than our current operator set.
However, we've spotted some deviations between standard and our implementation. * like_regex predicate uses our regular expression engine, which deviates from standard. * We always do numeric computations using numeric datatype. Even if user explicitly calls .double() method. Probably, our current implementation still fits standard. But in future we may like to use floating point computation in some cases for performance optimization. These deviations don't look critical by itself. But immutable functions make problematic fixing them in future. Also, I'm not sure this is complete list of deviations we have. We might have, for example, hidden deviations in handling strict/lax modes, which are hard to detect and understand. Therefore, I'm going to mark jsonb_path_*() functions stable, not immutable. Nevertheless users will still have multiple options for indexing: 1) jsonb_path_ops supports jsonpath matching operators in some cases. 2) One can wrap jsonb_path_*() in pl/* function and mark it as immutable on his own risk. This approach is widely used to build indexes over to_date()/to_timestamp(). 3) We're going to provide support of jsonpath operators in jsquery extension before release of PostgreSQL 12. I'd like to note I don't mean we wouldn't ever have immutable functions for jsonpath evaluation. I think once we sure enough that we know immutable subset of jsonpath, we may define immutable functions for its evaluation. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
switch_jsonpath_functions_to_stable.patch
Description: Binary data
