Opened 3 months ago
Last modified 6 weeks ago
#35778 closed Cleanup/optimization
Use native JSONObject on Postgres 16+ with server side bindings — at Version 1
Reported by: | john-parton | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette, Sarah Boyce, Mariusz Felisiak, Sage Abdullah | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
JSONObject on Postgres 16 with server side bindings recently resulted in a crash. The most recent fix is to fallback to the use of jsonb_build_object on postgres 16 when using server side bindings.
See https://code.djangoproject.com/ticket/35734
And https://github.com/django/django/pull/18549
It is possible to use the native JSONObject with server side bindings, but it requires a little bit of use of cast
.
See https://github.com/django/django/commit/0f53d48115ba0295cefea33512dc146caad39443
There are two minor issues:
- Should Postgres 16 *without* server-side bindings use "cast" even though it's not strictly necessary? It it desirable or preferable to keep the generated SQL the same when toggling the server-side binding feature? I mentioned digging through logs as one example where it might matter.
- Use of both cast and native json will require at least a minor change to escaping. This is because we use the double-colon operator to cast and the native json syntax uses a single colon to separate key-value pairs. This creates a parsing ambiguity which results in a syntax error (on at least one version of postgres). For solutions, they're all pretty similar
Options for minor issue 2:
- Update the
as_native
function to wrap the keys in parenthesis, effectively resolving the ambiguity. (This does raise yet another question, a question within a question: should we go ahead and wrap the keys in parenthesis on ALL backends? I think Oracle doesn't necessary require that for example.) - Update the Cast function to always wrap values in parenthesis in all contexts. This seems like overkill.
- Change postgres from using the double-colon operator to the CAST(x AS type) syntax. This also seems like overkill, and results in sql being generated that is less postgres-y, if that makes sense.