From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | Amit Langote <amitlangote09(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Peter Eisentraut <peter(at)eisentraut(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: pgsql: Add more SQL/JSON constructor functions |
Date: | 2024-09-03 09:05:02 |
Message-ID: | CACJufxHBp=nzJ59uHPDyc6Vx0-vmgiRAdjB7BJG4qrYQxYXDhw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Sep 2, 2024 at 4:18 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> > See 0001.
> >
> >
> > See 0002.
> >
> > I'm also attaching 0003 to fix a minor annoyance that JSON_TABLE()
> > columns' default ON ERROR, ON EMPTY behaviors are unnecessarily
> > emitted in the deparsed output when the top-level ON ERROR behavior is
> > ERROR.
> >
> > Will push these on Monday.
v2-0001 looks good to me.
+-- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY
+-- behavior
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$'));
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);
Are these tests duplicated? appears both in v2-0002 and v2-0003.
0002 output is:
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
+ Output: a
+ Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS
json_table_path_0 COLUMNS (a text PATH '$' NULL ON EMPTY NULL ON
ERROR) ERROR ON ERROR)
+(3 rows)
0003 output is:
EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
Output: a
Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS
json_table_path_0 COLUMNS (a text PATH '$') ERROR ON ERROR)
(3 rows)
two patches with different output,
overall we should merge 0002 and 0003?
if (jsexpr->on_error &&
jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
(jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))
we can be simplified as
if ( jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
(jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))
since if jsexpr->on_error is NULL, then segfault will appear at the beginning of
ExecInitJsonExpr
+ *
+ * Only add the extra steps for a NULL-valued expression when RETURNING a
+ * domain type to check the constraints, if any.
*/
+ jsestate->jump_error = state->steps_len;
if (jsexpr->on_error &&
- jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+ jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
+ (jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))
+ *
+ * Only add the extra steps for a NULL-valued expression when RETURNING a
+ * domain type to check the constraints, if any.
*/
+ jsestate->jump_empty = state->steps_len;
if (jsexpr->on_empty != NULL &&
- jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+ jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR &&
+ (jsexpr->on_empty->btype != JSON_BEHAVIOR_NULL || returning_domain))
I am a little bit confused with the comments.
not sure the "NULL-valued expression" refers to.
i think it is:
implicitly default for ON EMPTY | ERROR clause is NULL (JSON_BEHAVIOR_NULL)
for that situation, we can skip the json coercion process,
but this only applies when the returning type of JsonExpr is not domain,
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume de Rorthais | 2024-09-03 09:26:37 | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Previous Message | Anton A. Melnikov | 2024-09-03 09:02:26 | Re: Use XLOG_CONTROL_FILE macro everywhere? |