Re: SQL/JSON revisited

Amit Langote <amitlangote09@gmail.com>

From: Amit Langote <amitlangote09@gmail.com>

To: Andres Freund <andres@anarazel.de>

Cc: vignesh C <vignesh21@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Andrew Dunstan <andrew@dunslane.net>, e.indrupskaya@postgrespro.ru

Date: 2023-03-06T03:18:39Z

Lists: pgsql-hackers

Attachments

v8-0009-Claim-SQL-standard-compliance-for-SQL-JSON-featur.patch (application/x-patch) patch v8-0009
v8-0007-PLAN-clauses-for-JSON_TABLE.patch (application/x-patch) patch v8-0007
v8-0005-RETURNING-clause-for-JSON-and-JSON_SCALAR.patch (application/x-patch) patch v8-0005
v8-0008-Documentation-for-SQL-JSON-features.patch (application/x-patch) patch v8-0008
v8-0006-JSON_TABLE.patch (application/x-patch) patch v8-0006
v8-0004-SQL-JSON-functions.patch (application/x-patch) patch v8-0004
v8-0003-SQL-JSON-query-functions.patch (application/x-patch) patch v8-0003
v8-0002-IS-JSON-predicate.patch (application/x-patch) patch v8-0002
v8-0001-SQL-JSON-constructors.patch (application/x-patch) patch v8-0001

On Tue, Feb 28, 2023 at 8:36 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Mon, Feb 27, 2023 at 4:45 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > On Tue, Feb 21, 2023 at 2:25 AM Andres Freund <andres@anarazel.de> wrote:
> > > Evaluating N expressions for a json table isn't a good approach, both memory
> > > and CPU efficiency wise.
> >
> > Are you referring to JsonTableInitOpaque() initializing all these
> > sub-expressions of JsonTableParent, especially colvalexprs, using N
> > *independent* ExprStates?  That could perhaps be made to work by
> > making JsonTableParent be an expression recognized by execExpr.c, so
> > that a single ExprState can store the steps for all its
> > sub-expressions, much like JsonExpr is.  I'll give that a try, though
> > I wonder if the semantics of making this work in a single
> > ExecEvalExpr() call will mismatch that of the current way, because
> > different sub-expressions are currently evaluated under different APIs
> > of TableFuncRoutine.
>
> Hmm, the idea to turn JSON_TABLE into a single expression turned out
> to be a non-starter after all, because, unlike JsonExpr, it can
> produce multiple values.  So there must be an ExprState for computing
> each column of its output rows.  As I mentioned in my other reply,
> TableFuncScanState has a list of ExprStates anyway for
> TableFunc.colexprs.  What we could do is move the ExprStates of
> TableFunc.colvalexprs into TableFuncScanState instead of making that
> part of the JSON_TABLE opaque state, as I've done in the attached
> updated patch.

Here's another version in which I've also moved the ExprStates of
PASSING args into TableFuncScanState instead of keeping them in
JSON_TABLE opaque state.  That means all the subsidiary ExprStates of
TableFuncScanState are now initialized only once during
ExecInitTableFuncScan().  Previously, JSON_TABLE related ones would be
initialized on every call of JsonTableInitOpaque().

I've also done some cosmetic changes such as renaming the
JsonTableContext to JsonTableParseContext in parse_jsontable.c and to
JsonTableExecContext in jsonpath_exec.c.


--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com

Commits

Add SQL/JSON query functions
- 6185c9737cf4 17.0 landed
Add soft error handling to some expression nodes
- aaaf9449ec6b 17.0 landed
Adjust populate_record_field() to handle errors softly
- 1edb3b491bee 17.0 landed
Refactor code used by jsonpath executor to fetch variables
- faa2b953ba3b 17.0 landed
Code review for recent SQL/JSON commits
- 71bfd1543f8b 16.0 landed
Fix inconsistencies and style issues in new SQL/JSON code
- 60966f56c3e4 16.0 landed
Don't install postmaster symlink anymore
- 37e267335068 16.0 cited
Revert SQL/JSON features
- 2f2b18bd3f55 16.0 cited
Doc: standardize markup a bit more.
- 47046763c3ed 13.0 cited