pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
MERGE lacks ruleutils.c decompiling support!?
10+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2023-05-07 41e2c52fd6 f200b9695f  Add ruleutils support for decompiling MERGE commands.
* MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-05 13:36  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 10+ messages in thread

From: Tom Lane @ 2023-05-05 13:36 UTC (permalink / raw)
  To: pgsql-hackers@lists.postgresql.org

I made this function:

CREATE OR REPLACE FUNCTION test_fun()
RETURNS void
LANGUAGE SQL
BEGIN ATOMIC
MERGE INTO target
USING source s on s.id = target.id
WHEN MATCHED THEN
  UPDATE SET data = s.data
WHEN NOT MATCHED THEN
  INSERT VALUES (s.id, s.data);
end;

It appears to work fine, but:

regression=# \sf+ test_fun()
ERROR:  unrecognized query command type: 5

and it also breaks pg_dump.  Somebody screwed up pretty badly
here.  Is there any hope of fixing it for Monday's releases?

(I'd guess that decompiling the WHEN clause would take a nontrivial
amount of new code, so maybe fixing it on such short notice is
impractical.  But ugh.)

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-05 16:21  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 10+ messages in thread

From: Alvaro Herrera @ 2023-05-05 16:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org

On 2023-May-05, Tom Lane wrote:

> I made this function:
> 
> CREATE OR REPLACE FUNCTION test_fun()
> RETURNS void
> LANGUAGE SQL
> BEGIN ATOMIC
> MERGE INTO target
> USING source s on s.id = target.id
> WHEN MATCHED THEN
>   UPDATE SET data = s.data
> WHEN NOT MATCHED THEN
>   INSERT VALUES (s.id, s.data);
> end;
> 
> It appears to work fine, but:
> 
> regression=# \sf+ test_fun()
> ERROR:  unrecognized query command type: 5
> 
> and it also breaks pg_dump.  Somebody screwed up pretty badly
> here.  Is there any hope of fixing it for Monday's releases?
> 
> (I'd guess that decompiling the WHEN clause would take a nontrivial
> amount of new code, so maybe fixing it on such short notice is
> impractical.  But ugh.)

Hmm, there is *some* code in ruleutils for MERGE, but clearly something
is missing.  Let me have a look ...

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-05 18:43  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 10+ messages in thread

From: Alvaro Herrera @ 2023-05-05 18:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org

On 2023-May-05, Tom Lane wrote:

> (I'd guess that decompiling the WHEN clause would take a nontrivial
> amount of new code, so maybe fixing it on such short notice is
> impractical.  But ugh.)

Here's a first attempt.  I mostly just copied code from the insert and
update support routines.  There's a couple of things missing still, but
I'm not sure I'll get to it tonight.  I only tested to the extent of
what's in the new regression test.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
<Schwern> It does it in a really, really complicated way
<crab> why does it need to be complicated?
<Schwern> Because it's MakeMaker.

Attachments:

0001-add-ruleutils.c-support-for-MERGE.patch (text/x-diff, 8.6K) download From a89d46592ba4d113f57a890b63ef123d8470db45 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera <alvherre@alvh.no-ip.org> Date: Fri, 5 May 2023 20:40:38 +0200 Subject: [PATCH] add ruleutils.c support for MERGE --- src/backend/utils/adt/ruleutils.c | 119 ++++++++++++++++++++++++++++ src/test/regress/expected/merge.out | 68 ++++++++++++++++ src/test/regress/sql/merge.sql | 38 +++++++++ 3 files changed, 225 insertions(+) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 461735e84f0..851488dd6d3 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -411,6 +411,8 @@ static void get_update_query_targetlist_def(Query *query, List *targetList, RangeTblEntry *rte); static void get_delete_query_def(Query *query, deparse_context *context, bool colNamesVisible); +static void get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible); static void get_utility_query_def(Query *query, deparse_context *context); static void get_basic_select_query(Query *query, deparse_context *context, TupleDesc resultDesc, bool colNamesVisible); @@ -5448,6 +5450,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, get_delete_query_def(query, &context, colNamesVisible); break; + case CMD_MERGE: + get_merge_query_def(query, &context, colNamesVisible); + break; + case CMD_NOTHING: appendStringInfoString(buf, "NOTHING"); break; @@ -7043,6 +7049,119 @@ get_delete_query_def(Query *query, deparse_context *context, } } +/* ---------- + * get_merge_query_def - Parse back a MERGE parsetree + * ---------- + */ +static void +get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible) +{ + + StringInfo buf = context->buf; + RangeTblEntry *rte; + ListCell *lc; + + /* Insert the WITH clause if given */ + get_with_clause(query, context); + + /* + * Start the query with MERGE INTO relname + */ + rte = rt_fetch(query->resultRelation, query->rtable); + Assert(rte->rtekind == RTE_RELATION); + if (PRETTY_INDENT(context)) + { + appendStringInfoChar(buf, ' '); + context->indentLevel += PRETTYINDENT_STD; + } + appendStringInfo(buf, "MERGE INTO %s%s", + only_marker(rte), + generate_relation_name(rte->relid, NIL)); + + /* Print the relation alias, if needed */ + get_rte_alias(rte, query->resultRelation, false, context); + + get_from_clause(query, " USING ", context); + appendContextKeyword(context, " ON ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + get_rule_expr(query->jointree->quals, context, false); + + appendStringInfoChar(buf, '\n'); /* FIXME proper way to do this? */ + + /* FIXME missing: OVERRIDING clause */ + foreach(lc, query->mergeActionList) + { + MergeAction *action = lfirst_node(MergeAction, lc); + + appendStringInfo(buf, "WHEN %sMATCHED", action->matched ? "" : "NOT "); + + if (action->qual) + { + appendContextKeyword(context, " AND ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + get_rule_expr(action->qual, context, false); + } + appendContextKeyword(context, " THEN ", -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + + if (action->commandType == CMD_INSERT) + { + ListCell *lc2; + char *sep = ""; + List *strippedexprs = NIL; + + appendStringInfoString(buf, "INSERT "); + + /* + * This matches what get_insert_query_def does for the VALUES + * clause + */ + if (action->targetList) + appendStringInfoChar(buf, '('); + foreach(lc2, action->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc2); + + Assert(!tle->resjunk); + + appendStringInfoString(buf, sep); + sep = ", "; + + appendStringInfoString(buf, quote_identifier(get_attname(rte->relid, + tle->resno, + false))); + strippedexprs = lappend(strippedexprs, + processIndirection((Node *) tle->expr, + context)); + } + if (action->targetList) + appendStringInfoString(buf, ") "); + if (strippedexprs) + { + appendContextKeyword(context, "VALUES (", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); + get_rule_list_toplevel(strippedexprs, context, false); + appendStringInfoChar(buf, ')'); + } + else + appendStringInfoString(buf, "DEFAULT VALUES"); + } + else if (action->commandType == CMD_UPDATE) + { + appendStringInfoString(buf, "UPDATE SET "); + get_update_query_targetlist_def(query, action->targetList, context, rte); + } + else if (action->commandType == CMD_DELETE) + appendStringInfoString(buf, "DELETE "); + else if (action->commandType == CMD_NOTHING) + appendStringInfoString(buf, "DO NOTHING "); + + appendStringInfoChar(buf, '\n'); + } + + /* No RETURNING support yet */ + Assert(query->returningList == NIL); +} /* ---------- * get_utility_query_def - Parse back a UTILITY parsetree diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 133d42117c0..2e71dda4228 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1319,6 +1319,74 @@ ERROR: syntax error at or near "RETURNING" LINE 10: RETURNING *; ^ ROLLBACK; +-- Verify that ruleutils can properly deparse +CREATE TABLE sf_target(id int, data text, filling int[]); +CREATE TABLE sf_source(id int, data text); +CREATE OR REPLACE FUNCTION public.merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target + USING sf_source s + ON (s.id = sf_target.id) +WHEN MATCHED + AND ((s.id + sf_target.id) = 42) + THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data) +WHEN NOT MATCHED + AND (s.data IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.data, s.id) +WHEN MATCHED + AND (length((s.data || sf_target.data)) > 10) + THEN UPDATE SET data = s.data +WHEN MATCHED + THEN UPDATE SET filling[s.id] = sf_target.id +WHEN NOT MATCHED + AND (s.id > 200) + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND (s.id > 100) + THEN INSERT (id, data) + VALUES (s.id, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.id, s.id) +; +END; +\sf+ merge_sf_test + CREATE OR REPLACE FUNCTION public.merge_sf_test() + RETURNS void + LANGUAGE sql +1 BEGIN ATOMIC +2 MERGE INTO sf_target +3 USING sf_source s +4 ON (s.id = sf_target.id) +5 WHEN MATCHED +6 AND ((s.id + sf_target.id) = 42) +7 THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data) +8 WHEN NOT MATCHED +9 AND (s.data IS NOT NULL) +10 THEN INSERT (data, id) +11 VALUES (s.data, s.id) +12 WHEN MATCHED +13 AND (length((s.data || sf_target.data)) > 10) +14 THEN UPDATE SET data = s.data +15 WHEN MATCHED +16 THEN UPDATE SET filling[s.id] = sf_target.id +17 WHEN NOT MATCHED +18 AND (s.id > 200) +19 THEN INSERT DEFAULT VALUES +20 WHEN NOT MATCHED +21 AND (s.id > 100) +22 THEN INSERT (id, data) +23 VALUES (s.id, DEFAULT) +24 WHEN NOT MATCHED +25 THEN INSERT (filling[1], id) +26 VALUES (s.id, s.id) +27 ; +28 END +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target, sf_source; -- EXPLAIN CREATE TABLE ex_mtarget (a int, b int) WITH (autovacuum_enabled=off); diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 4cf6db908b5..41662089905 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -870,6 +870,44 @@ WHEN MATCHED AND tid < 2 THEN RETURNING *; ROLLBACK; +-- Verify that ruleutils can properly deparse +CREATE TABLE sf_target(id int, data text, filling int[]); +CREATE TABLE sf_source(id int, data text); +CREATE OR REPLACE FUNCTION public.merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target + USING sf_source s + ON (s.id = sf_target.id) +WHEN MATCHED + AND ((s.id + sf_target.id) = 42) + THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data) +WHEN NOT MATCHED + AND (s.data IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.data, s.id) +WHEN MATCHED + AND (length((s.data || sf_target.data)) > 10) + THEN UPDATE SET data = s.data +WHEN MATCHED + THEN UPDATE SET filling[s.id] = sf_target.id +WHEN NOT MATCHED + AND (s.id > 200) + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND (s.id > 100) + THEN INSERT (id, data) + VALUES (s.id, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.id, s.id) +; +END; +\sf+ merge_sf_test +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target, sf_source; + -- EXPLAIN CREATE TABLE ex_mtarget (a int, b int) WITH (autovacuum_enabled=off); -- 2.39.2
^ permalink raw reply [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-05 21:06  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 2 replies; 10+ messages in thread

From: Tom Lane @ 2023-05-05 21:06 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-hackers@lists.postgresql.org

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Here's a first attempt.  I mostly just copied code from the insert and
> update support routines.  There's a couple of things missing still, but
> I'm not sure I'll get to it tonight.  I only tested to the extent of
> what's in the new regression test.

I did a bit of review and more work on this:

* Added the missing OVERRIDING support

* Played around with the pretty-printing indentation

* Improved test coverage, and moved the test to rules.sql where
I thought it fit more naturally.

I think we could probably commit this, though I've not tried it
in v15 yet.

			regards, tom lane

Attachments:

0001-add-ruleutils.c-support-for-MERGE-v2.patch (text/x-diff, 8.5K) download diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 461735e84f..60f9d08d5d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -411,6 +411,8 @@ static void get_update_query_targetlist_def(Query *query, List *targetList, RangeTblEntry *rte); static void get_delete_query_def(Query *query, deparse_context *context, bool colNamesVisible); +static void get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible); static void get_utility_query_def(Query *query, deparse_context *context); static void get_basic_select_query(Query *query, deparse_context *context, TupleDesc resultDesc, bool colNamesVisible); @@ -5448,6 +5450,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, get_delete_query_def(query, &context, colNamesVisible); break; + case CMD_MERGE: + get_merge_query_def(query, &context, colNamesVisible); + break; + case CMD_NOTHING: appendStringInfoString(buf, "NOTHING"); break; @@ -7044,6 +7050,128 @@ get_delete_query_def(Query *query, deparse_context *context, } +/* ---------- + * get_merge_query_def - Parse back a MERGE parsetree + * ---------- + */ +static void +get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible) +{ + StringInfo buf = context->buf; + RangeTblEntry *rte; + ListCell *lc; + + /* Insert the WITH clause if given */ + get_with_clause(query, context); + + /* + * Start the query with MERGE INTO relname + */ + rte = rt_fetch(query->resultRelation, query->rtable); + Assert(rte->rtekind == RTE_RELATION); + if (PRETTY_INDENT(context)) + { + appendStringInfoChar(buf, ' '); + context->indentLevel += PRETTYINDENT_STD; + } + appendStringInfo(buf, "MERGE INTO %s%s", + only_marker(rte), + generate_relation_name(rte->relid, NIL)); + + /* Print the relation alias, if needed */ + get_rte_alias(rte, query->resultRelation, false, context); + + /* Print the source relation and join clause */ + get_from_clause(query, " USING ", context); + appendContextKeyword(context, " ON ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); + get_rule_expr(query->jointree->quals, context, false); + + /* Print each merge action */ + foreach(lc, query->mergeActionList) + { + MergeAction *action = lfirst_node(MergeAction, lc); + + appendContextKeyword(context, " WHEN ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); + appendStringInfo(buf, "%sMATCHED", action->matched ? "" : "NOT "); + + if (action->qual) + { + appendContextKeyword(context, " AND ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 3); + get_rule_expr(action->qual, context, false); + } + appendContextKeyword(context, " THEN ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 3); + + if (action->commandType == CMD_INSERT) + { + /* This generally matches get_insert_query_def() */ + List *strippedexprs = NIL; + const char *sep = ""; + ListCell *lc2; + + appendStringInfoString(buf, "INSERT"); + + if (action->targetList) + appendStringInfoString(buf, " ("); + foreach(lc2, action->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc2); + + Assert(!tle->resjunk); + + appendStringInfoString(buf, sep); + sep = ", "; + + appendStringInfoString(buf, + quote_identifier(get_attname(rte->relid, + tle->resno, + false))); + strippedexprs = lappend(strippedexprs, + processIndirection((Node *) tle->expr, + context)); + } + if (action->targetList) + appendStringInfoChar(buf, ')'); + + if (action->override) + { + if (action->override == OVERRIDING_SYSTEM_VALUE) + appendStringInfoString(buf, " OVERRIDING SYSTEM VALUE"); + else if (action->override == OVERRIDING_USER_VALUE) + appendStringInfoString(buf, " OVERRIDING USER VALUE"); + } + + if (strippedexprs) + { + appendContextKeyword(context, " VALUES (", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 4); + get_rule_list_toplevel(strippedexprs, context, false); + appendStringInfoChar(buf, ')'); + } + else + appendStringInfoString(buf, " DEFAULT VALUES"); + } + else if (action->commandType == CMD_UPDATE) + { + appendStringInfoString(buf, "UPDATE SET "); + get_update_query_targetlist_def(query, action->targetList, + context, rte); + } + else if (action->commandType == CMD_DELETE) + appendStringInfoString(buf, "DELETE"); + else if (action->commandType == CMD_NOTHING) + appendStringInfoString(buf, "DO NOTHING"); + } + + /* No RETURNING support in MERGE yet */ + Assert(query->returningList == NIL); +} + + /* ---------- * get_utility_query_def - Parse back a UTILITY parsetree * ---------- diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 69957687fe..684ecc2ed7 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3585,6 +3585,91 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s DELETE WHEN NOT MATCHED THEN INSERT VALUES (s.a, ''); +-- test deparsing +CREATE TABLE sf_target(id int, data text, filling int[]); +CREATE FUNCTION merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN MATCHED + AND (s.a + t.id) = 42 + THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b) +WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) +WHEN MATCHED + AND length(s.b || t.data) > 10 + THEN UPDATE SET data = s.b +WHEN MATCHED + AND s.a > 200 + THEN UPDATE SET filling[s.a] = t.id +WHEN MATCHED + AND s.a > 100 + THEN DELETE +WHEN MATCHED + THEN DO NOTHING +WHEN NOT MATCHED + AND s.a > 200 + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND s.a > 100 + THEN INSERT (id, data) OVERRIDING SYSTEM VALUE + VALUES (s.a, DEFAULT) +WHEN NOT MATCHED + AND s.a > 0 + THEN INSERT + VALUES (s.a, s.b, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END; +\sf merge_sf_test +CREATE OR REPLACE FUNCTION public.merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) + WHEN MATCHED + AND ((s.a + t.id) = 42) + THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b) + WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) + WHEN MATCHED + AND (length((s.b || t.data)) > 10) + THEN UPDATE SET data = s.b + WHEN MATCHED + AND (s.a > 200) + THEN UPDATE SET filling[s.a] = t.id + WHEN MATCHED + AND (s.a > 100) + THEN DELETE + WHEN MATCHED + THEN DO NOTHING + WHEN NOT MATCHED + AND (s.a > 200) + THEN INSERT DEFAULT VALUES + WHEN NOT MATCHED + AND (s.a > 100) + THEN INSERT (id, data) OVERRIDING SYSTEM VALUE + VALUES (s.a, DEFAULT) + WHEN NOT MATCHED + AND (s.a > 0) + THEN INSERT (id, data, filling) + VALUES (s.a, s.b, DEFAULT) + WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target; -- -- Test enabling/disabling -- diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 4caab3434b..9111618005 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1277,6 +1277,55 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s WHEN NOT MATCHED THEN INSERT VALUES (s.a, ''); +-- test deparsing +CREATE TABLE sf_target(id int, data text, filling int[]); + +CREATE FUNCTION merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN MATCHED + AND (s.a + t.id) = 42 + THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b) +WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) +WHEN MATCHED + AND length(s.b || t.data) > 10 + THEN UPDATE SET data = s.b +WHEN MATCHED + AND s.a > 200 + THEN UPDATE SET filling[s.a] = t.id +WHEN MATCHED + AND s.a > 100 + THEN DELETE +WHEN MATCHED + THEN DO NOTHING +WHEN NOT MATCHED + AND s.a > 200 + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND s.a > 100 + THEN INSERT (id, data) OVERRIDING SYSTEM VALUE + VALUES (s.a, DEFAULT) +WHEN NOT MATCHED + AND s.a > 0 + THEN INSERT + VALUES (s.a, s.b, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END; + +\sf merge_sf_test + +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target; + -- -- Test enabling/disabling --
^ permalink raw reply [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-06 02:46  Michael Paquier <michael@paquier.xyz>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Michael Paquier @ 2023-05-06 02:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-hackers@lists.postgresql.org

On Fri, May 05, 2023 at 05:06:34PM -0400, Tom Lane wrote:
> I did a bit of review and more work on this:
> 
> * Added the missing OVERRIDING support
> 
> * Played around with the pretty-printing indentation
> 
> * Improved test coverage, and moved the test to rules.sql where
> I thought it fit more naturally.
> 
> I think we could probably commit this, though I've not tried it
> in v15 yet.

Seems rather OK..

+WHEN NOT MATCHED
+   AND s.a > 100
+   THEN INSERT (id, data) OVERRIDING SYSTEM VALUE
+   VALUES (s.a, DEFAULT)

About OVERRIDING, I can see that this is still missing coverage for
OVERRIDING USER VALUE.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-06 02:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2023-05-06 02:49 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-hackers@lists.postgresql.org

Michael Paquier <michael@paquier.xyz> writes:
> +WHEN NOT MATCHED
> +   AND s.a > 100
> +   THEN INSERT (id, data) OVERRIDING SYSTEM VALUE
> +   VALUES (s.a, DEFAULT)

> About OVERRIDING, I can see that this is still missing coverage for
> OVERRIDING USER VALUE.

Yeah, I couldn't see that covering that too was worth any cycles.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-07 04:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 2023-05-07 04:49 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-hackers@lists.postgresql.org

I wrote:
> I think we could probably commit this, though I've not tried it
> in v15 yet.

Ping?  The hours grow short, if we're to get this into 15.3.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-07 15:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2023-05-07 15:02 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-hackers@lists.postgresql.org

I wrote:
> Ping?  The hours grow short, if we're to get this into 15.3.

I went ahead and pushed v2, since we can't wait any longer if
we're to get reasonable buildfarm coverage before 15.3 wraps.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-07 17:06  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Alvaro Herrera @ 2023-05-07 17:06 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org

On 2023-May-07, Tom Lane wrote:

> I wrote:
> > Ping?  The hours grow short, if we're to get this into 15.3.
> 
> I went ahead and pushed v2, since we can't wait any longer if
> we're to get reasonable buildfarm coverage before 15.3 wraps.

Much appreciated.  I wanted to get back to this yesterday but was unable
to.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: MERGE lacks ruleutils.c decompiling support!?
@ 2023-05-10 09:40  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 10+ messages in thread

From: Alvaro Herrera @ 2023-05-10 09:40 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org

BTW, I spent some time adding a cross-check to see if the code was at
least approximately correct for all the queries in the MERGE regression
tests, and couldn't find any failures.  I then extended the test to the
other optimizable commands, and couldn't find any problems there either.

My approach was perhaps a bit simple-minded: I just patched
pg_analyze_and_rewrite_fixedparams() to call pg_get_querydef() after
parse_analyze_fixedparams(), then ran the main regression tests.  No
crashes.  Also had it output as a WARNING together with the
query_string, so that I could eyeball for any discrepancies; I couldn't
find any queries that produce wrong contents, though this was just
manual examination of the resulting noise logs.

I suppose a better approach might be to run the query produced by
pg_get_querydef() again through parse analysis and see if it produces
the same; or better yet, discard the original parsed query and parse the
pg_get_querydef().  I didn't try to do this.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2023-05-10 09:40 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-05 13:36 MERGE lacks ruleutils.c decompiling support!? Tom Lane <tgl@sss.pgh.pa.us>
2023-05-05 16:21 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-05 18:43 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-05 21:06   ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-06 02:46     ` Michael Paquier <michael@paquier.xyz>
2023-05-06 02:49       ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-07 04:49     ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-07 15:02       ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-07 17:06         ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-10 09:40 ` Alvaro Herrera <alvherre@alvh.no-ip.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox