Thread: [HACKERS] SQL MERGE patches for PostgreSQL Versions
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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.
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