Thread: [HACKERS] SQL MERGE patches for PostgreSQL Versions

[HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
Dear PG hackers,

First my apology if I appear to be a jerk or not following the policy.

I emailed Boxuan Zhai who was  in charge of the SQL Merge keyword in
2010 of GSoC but without reply.

I want to apply merge_v201.patch to specific PG version.

It failed saying 1 or 2 of 5 hunk failed.

My question is:1. Given x old patch of PG, is it possible to know  to which PG
version can be applied?2. If not possible, can someone tell me the general approach to apply
given old patch(merge_v201.patch) to PG.3. Who was the mentor of SQL Merge of GSoC in 2010 and if present in
this PG Community, can he/she tell me which PG version was applied SQL
Merge?

My goal is to apply SQL Merge pacth into PG successfully and to from
learn the code applied.

Regards,
Zeray



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Michael Paquier
Date:
On Thu, Jun 22, 2017 at 5:05 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
> First my apology if I appear to be a jerk or not following the policy.

No problem.

> I emailed Boxuan Zhai who was  in charge of the SQL Merge keyword in
> 2010 of GSoC but without reply.
>
> I want to apply merge_v201.patch to specific PG version.
>
> It failed saying 1 or 2 of 5 hunk failed.
>
> My question is:
>  1. Given x old patch of PG, is it possible to know  to which PG
> version can be applied?
>  2. If not possible, can someone tell me the general approach to apply
> given old patch(merge_v201.patch) to PG.
>  3. Who was the mentor of SQL Merge of GSoC in 2010 and if present in
> this PG Community, can he/she tell me which PG version was applied SQL
> Merge?
>
> My goal is to apply SQL Merge patch into PG successfully and to from
> learn the code applied.

If the goal is to learn from the past code and you are not really
willing to fix code conflicts, you could always try to apply a patch
of 2010 using a version of the master branch located between 8.4 and
9.0 :)
-- 
Michael



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Craig Ringer
Date:
On 22 June 2017 at 16:05, Kang Yuzhe <tiggreen87@gmail.com> wrote:
> Dear PG hackers,
>
> First my apology if I appear to be a jerk or not following the policy.
>
> I emailed Boxuan Zhai who was  in charge of the SQL Merge keyword in
> 2010 of GSoC but without reply.
>
> I want to apply merge_v201.patch to specific PG version.
>
> It failed saying 1 or 2 of 5 hunk failed.
>
> My question is:
>  1. Given x old patch of PG, is it possible to know  to which PG
> version can be applied?

If it's produced by git-format-patch you can look at the git ref
information in the patch. Otherwise you have to rely on what's in the
email thread.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 11:16 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Jun 22, 2017 at 5:05 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
>> First my apology if I appear to be a jerk or not following the policy.
>
> No problem.
>
>> I emailed Boxuan Zhai who was  in charge of the SQL Merge keyword in
>> 2010 of GSoC but without reply.
>>
>> I want to apply merge_v201.patch to specific PG version.
>>
>> It failed saying 1 or 2 of 5 hunk failed.
>>
>> My question is:
>>  1. Given x old patch of PG, is it possible to know  to which PG
>> version can be applied?
>>  2. If not possible, can someone tell me the general approach to apply
>> given old patch(merge_v201.patch) to PG.
>>  3. Who was the mentor of SQL Merge of GSoC in 2010 and if present in
>> this PG Community, can he/she tell me which PG version was applied SQL
>> Merge?
>>
>> My goal is to apply SQL Merge patch into PG successfully and to from
>> learn the code applied.
>
> If the goal is to learn from the past code and you are not really
> willing to fix code conflicts, you could always try to apply a patch
> of 2010 using a version of the master branch located between 8.4 and
> 9.0 :)
I wish I could but it's because I don't believe that I have the right
capability to fix code conflicts. My ultimate goal is to be PG hacker
like YOU.  Anyway, I will consider your perspective.
Regards,
Zeray



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 11:23 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 22 June 2017 at 16:05, Kang Yuzhe <tiggreen87@gmail.com> wrote:
>> Dear PG hackers,
>>
>> First my apology if I appear to be a jerk or not following the policy.
>>
>> I emailed Boxuan Zhai who was  in charge of the SQL Merge keyword in
>> 2010 of GSoC but without reply.
>>
>> I want to apply merge_v201.patch to specific PG version.
>>
>> It failed saying 1 or 2 of 5 hunk failed.
>>
>> My question is:
>>  1. Given x old patch of PG, is it possible to know  to which PG
>> version can be applied?
>
> If it's produced by git-format-patch you can look at the git ref
> information in the patch. Otherwise you have to rely on what's in the
> email thread.

If you were having merge_v201.patch, how would you determine whether
it was produced by git-format-patch ow email thread?

I just downloaded the patch from GSoC site.

A code snippet from the merge_v201.patch is shown below:

diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 8619ce3..e3ac758 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -582,6 +582,113 @@ lreplace:;    return NULL;}

+static TupleTableSlot *
+MergeRaiseErr(void)
+{
+    elog(NOTICE, "one tuple is ERROR");
+    return NULL;
+}
+
+static TupleTableSlot *
+ExecMerge(ItemPointer tupleid,
+           TupleTableSlot *slot,
+           TupleTableSlot *planSlot,
+           MergeActionSet *actset,
+           EState *estate)
+{
+
+    TupleTableSlot *actslot = NULL;
+    ListCell *each;
+
+    /*
+     * Try the merge actions one by one until we have a match.
+     */
+    foreach(each, actset->actions)
+    {
+        ModifyTableState *mt_pstate;
+        MergeActionState *action_pstate;
+        ExprContext *econtext;
+        bool matched;
+
+        mt_pstate = (ModifyTableState *) lfirst(each);
+        Assert(IsA(mt_pstate, ModifyTableState));
+
+        /*
+         * mt_pstate is supposed to have only ONE mt_plans,
+         * which is a MergeActionState
+         */
+        action_pstate = (MergeActionState *) mt_pstate->mt_plans[0];
+        matched = ((MergeAction *)action_pstate->ps.plan)->matched;
+
+        /*
+         * If tupleid == NULL, it is a NOT MATCHED case,
+         * else, it is a MATCHED case,
+         */
+        if ((tupleid == NULL && matched) ||
+            (tupleid != NULL && !matched))
+            continue;
+
+        /* Setup the expression context. */
+        econtext = action_pstate->ps.ps_ExprContext;
+
+        /*
+         * Check that additional quals match, if any.
+         */
+        if (action_pstate->ps.qual)
+        {
+            ResetExprContext(econtext);
+
+            econtext->ecxt_scantuple = slot;
+            econtext->ecxt_outertuple = planSlot;
+
+            if (!ExecQual(action_pstate->ps.qual, econtext, false))
+                continue;
+        }
+
+        /* Ok, we have a match. Perform the action */
+
+        /* First project any RETURNING result tuple slot, if needed */
+        if (action_pstate->operation == CMD_INSERT ||
+            action_pstate->operation == CMD_UPDATE)
+            actslot = ExecProcessReturning(action_pstate->ps.ps_ProjInfo,
+                                           slot, planSlot);
+
+        switch (action_pstate->operation)
+        {
+            case CMD_INSERT:
+                return ExecInsert(actslot, planSlot, estate);
+
+            case CMD_UPDATE:
+                return ExecUpdate(tupleid,
+                                  actslot,
+                                  planSlot,
+                                  &mt_pstate->mt_epqstate,
+                                  estate);
+
+            case CMD_DELETE:
+                return ExecDelete(tupleid,
+                                  planSlot,
+                                  &mt_pstate->mt_epqstate,
+                                  estate);
+
+            case CMD_DONOTHING:
+                return NULL;
+
+            case CMD_RAISEERR:
+                return MergeRaiseErr();
+
+            default:
+                elog(ERROR, "unknown merge action type for excute");
+                break;
+        }
+    }
+
+    /*
+     * No matching action found. Perform the default action, which is
+     * RAISE ERROR.
+     */
+    return MergeRaiseErr();
+}



Now, is it possible to extract info from this code snippet whether it
was by git-format-patch or email thread?

Regards,
Zeray



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Thomas Munro
Date:
On Thu, Jun 22, 2017 at 9:00 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
> I just downloaded the patch from GSoC site.

I just looked at
https://wiki.postgresql.org/wiki/Add_MERGE_command_GSoC_2010 and saw
that the file https://wiki.postgresql.org/wiki/File:Merge_v201.tar was
uploaded on 24 Aug 2010.  So I picked a random commit from that date,
git checkout 005e427a22e3bb7fa01a84a7b476a3d6359a0344, and then I was
able to apply that patch with patch -p1 < merge_v201.patch without any
failures.

-- 
Thomas Munro
http://www.enterprisedb.com



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Craig Ringer
Date:
On 22 June 2017 at 17:00, Kang Yuzhe <tiggreen87@gmail.com> wrote:

> diff --git a/src/backend/executor/nodeModifyTable.c
> b/src/backend/executor/nodeModifyTable.c
> index 8619ce3..e3ac758 100644
> --- a/src/backend/executor/nodeModifyTable.c
> +++ b/src/backend/executor/nodeModifyTable.c

The first entry in the 'index' is the git commit hash of the base commit, IIRC.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Michael Paquier
Date:
On Thu, Jun 22, 2017 at 5:30 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
> I wish I could but it's because I don't believe that I have the right
> capability to fix code conflicts. My ultimate goal is to be PG hacker
> like YOU.  Anyway, I will consider your perspective.

Nice to see such a goal, though as a first patch presented to the
community you may want something less ambitious. That's a complicated
topic you are trying to deal with.
-- 
Michael



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Peter Eisentraut
Date:
On 6/22/17 05:13, Craig Ringer wrote:
> On 22 June 2017 at 17:00, Kang Yuzhe <tiggreen87@gmail.com> wrote:
> 
>> diff --git a/src/backend/executor/nodeModifyTable.c
>> b/src/backend/executor/nodeModifyTable.c
>> index 8619ce3..e3ac758 100644
>> --- a/src/backend/executor/nodeModifyTable.c
>> +++ b/src/backend/executor/nodeModifyTable.c
> 
> The first entry in the 'index' is the git commit hash of the base commit, IIRC.
I don't know what the technical term is, but these values are hashes of
the file before and after, or something like that.  They are not Git
commits or trees.

See git format-patch option --base for how to communicate the base commit.

(The above patch was possibly created from an earlier unofficial Git
repository, because I can't find those hashes.)

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 12:10 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Thu, Jun 22, 2017 at 9:00 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
>> I just downloaded the patch from GSoC site.
>
> I just looked at
> https://wiki.postgresql.org/wiki/Add_MERGE_command_GSoC_2010 and saw
> that the file https://wiki.postgresql.org/wiki/File:Merge_v201.tar was
> uploaded on 24 Aug 2010.  So I picked a random commit from that date,
> git checkout 005e427a22e3bb7fa01a84a7b476a3d6359a0344, and then I was
> able to apply that patch with patch -p1 < merge_v201.patch without any
> failures.

Thanks so much Thomas! I have managed to apply without any failures by
following your approach.
Here is a sample what I did after applying the patch.

testdb=# BEGIN;
BEGIN
testdb=#
testdb=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
testdb-#  WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
testdb-#  WHEN NOT MATCHED THEN DO NOTHING;
MERGE 1
testdb=# SELECT * FROM Stock;item_id | balance
---------+---------     20 |    1900     10 |    3200
(2 rows)

testdb=# ROLLBACK;
ROLLBACK

I think and believe that though it may be tedious to do so, it is the
style Thomas followed to help me that hackers should do to help
newbies so that they can pursue their ambition.

I am humbled to say that I didn't know how to search git commits by a
specific day like what Thomas did.

Regards,
Zeray



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 3:51 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> On 6/22/17 05:13, Craig Ringer wrote:
>> On 22 June 2017 at 17:00, Kang Yuzhe <tiggreen87@gmail.com> wrote:
>>
>>> diff --git a/src/backend/executor/nodeModifyTable.c
>>> b/src/backend/executor/nodeModifyTable.c
>>> index 8619ce3..e3ac758 100644
>>> --- a/src/backend/executor/nodeModifyTable.c
>>> +++ b/src/backend/executor/nodeModifyTable.c
>>
>> The first entry in the 'index' is the git commit hash of the base commit, IIRC.
> I don't know what the technical term is, but these values are hashes of
> the file before and after, or something like that.  They are not Git
> commits or trees.
>
> See git format-patch option --base for how to communicate the base commit.
>
> (The above patch was possibly created from an earlier unofficial Git
> repository, because I can't find those hashes.)

Thanks Peter for trying to help.
According to Thomas Munro advice, I did git log --since="Aug 4 2010"
--until="Aug 29 2010"
Picking random commit which is  005e427a22e3bb7fa01a84a7b476a3d6359a0344, I did
git checkout 005e427a22e3bb7fa01a84a7b476a3d6359a0344.

Next I did git checkout -b pgSQLMerge

And finally I did the following and it worked.

patch -p1 < merge_v201.patch

Regards,
Zeray



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Jan de Visser
Date:
On Thursday, June 22, 2017 12:32:14 PM EDT Kang Yuzhe wrote:
> Here is a sample what I did after applying the patch.
> 
> testdb=# BEGIN;
> BEGIN
> testdb=#
> testdb=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
> testdb-#  WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
> testdb-#  WHEN NOT MATCHED THEN DO NOTHING;
> MERGE 1
> testdb=# SELECT * FROM Stock;
>  item_id | balance
> ---------+---------
>       20 |    1900
>       10 |    3200
> (2 rows)
> 
> testdb=# ROLLBACK;
> ROLLBACK

I am not quite sure what you're trying to achieve, but are you aware that 
pgsql 9.6 introduced the ON CONFLICT clause, which allows you to do the same 
with a different syntax?

https://www.postgresql.org/docs/9.6/static/sql-insert.html

Look for ON CONFLICT.





Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 7:52 PM, Jan de Visser <jan@de-visser.net> wrote:
> On Thursday, June 22, 2017 12:32:14 PM EDT Kang Yuzhe wrote:
>> Here is a sample what I did after applying the patch.
>>
>> testdb=# BEGIN;
>> BEGIN
>> testdb=#
>> testdb=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
>> testdb-#  WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
>> testdb-#  WHEN NOT MATCHED THEN DO NOTHING;
>> MERGE 1
>> testdb=# SELECT * FROM Stock;
>>  item_id | balance
>> ---------+---------
>>       20 |    1900
>>       10 |    3200
>> (2 rows)
>>
>> testdb=# ROLLBACK;
>> ROLLBACK
>
> I am not quite sure what you're trying to achieve, but are you aware that
> pgsql 9.6 introduced the ON CONFLICT clause, which allows you to do the same
> with a different syntax?
>
> https://www.postgresql.org/docs/9.6/static/sql-insert.html
>
> Look for ON CONFLICT.

Yes, I am aware of ON CONFLICT.
DO NOTHING in SQL Merge is one type of scenario which is like ON CONFLICT.
My goal is to understand how SQL MERGE works which is in SQL ANSI/ISO
standard. And I would implement as a patch in the latest PG if I could
do that.

Regards,
Zeray.



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Kang Yuzhe
Date:
On Thu, Jun 22, 2017 at 3:13 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Jun 22, 2017 at 5:30 PM, Kang Yuzhe <tiggreen87@gmail.com> wrote:
>> I wish I could but it's because I don't believe that I have the right
>> capability to fix code conflicts. My ultimate goal is to be PG hacker
>> like YOU.  Anyway, I will consider your perspective.
>
> Nice to see such a goal, though as a first patch presented to the
> community you may want something less ambitious. That's a complicated
> topic you are trying to deal with.

I think so!
Having estimated my skill, would you please point me to something less
ambitious patch(task) that I can work on so that I can find my way in
PG hacking?

Regards,
Zeray.



Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

From
Peter Geoghegan
Date:
On Thu, Jun 22, 2017 at 9:52 AM, Jan de Visser <jan@de-visser.net> wrote:
> I am not quite sure what you're trying to achieve, but are you aware that
> pgsql 9.6 introduced the ON CONFLICT clause, which allows you to do the same
> with a different syntax?
>
> https://www.postgresql.org/docs/9.6/static/sql-insert.html

I don't think it's the same thing. I think we could reasonably have
both SQL MERGE and ON CONFLICT. Or at least, I think that that makes
sense. Teradata already has both (their own custom UPSERT syntax, plus
an implementation of SQL MERGE).

Boxuan Zhai's patch didn't try to do anything special about
concurrency. At the time, this was controversial. However, we now
understand that SQL MERGE really isn't obligated to handle that at all
[1]. Besides, we have ON CONFLICT for those use-cases.

[1] https://wiki.postgresql.org/wiki/UPSERT#MERGE_disadvantages
-- 
Peter Geoghegan