SQL MERGE patches for PostgreSQL Versions

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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]: https://wiki.postgresql.org/wiki/UPSERT#MERGE_disadvantages -- Peter Geoghegan

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers