MERGE Specification
in [PgSql]
|
Prev: [HACKERS] shared_preload_libraries is ignored in single user mode
Next: Earn more for a more satisfactory life. agonizing adelocodonic affrighted
From: Simon Riggs on 5 Aug 2010 03:46 On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: > The following two files specify the behaviour of the MERGE statement and > how it will work in the world of PostgreSQL. > The HTML file was generated from SGML source, though the latter is not > included here for clarity. Enclose merge.sgml docs for forthcoming MERGE command, as originally written. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services
From: Heikki Linnakangas on 5 Aug 2010 05:29 On 05/08/10 10:46, Simon Riggs wrote: > On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: >> The following two files specify the behaviour of the MERGE statement and >> how it will work in the world of PostgreSQL. > >> The HTML file was generated from SGML source, though the latter is not >> included here for clarity. > > Enclose merge.sgml docs for forthcoming MERGE command, as originally > written. Oh, cool, I wasn't aware you had written that already. Boxuan, please include this in your patch, after reviewing and removing/editing anything that doesn't apply to your patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 5 Aug 2010 07:25 On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: > On 05/08/10 10:46, Simon Riggs wrote: > > On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: > >> The following two files specify the behaviour of the MERGE statement and > >> how it will work in the world of PostgreSQL. > > > >> The HTML file was generated from SGML source, though the latter is not > >> included here for clarity. > > > > Enclose merge.sgml docs for forthcoming MERGE command, as originally > > written. > > Oh, cool, I wasn't aware you had written that already. Boxuan, please > include this in your patch, after reviewing and removing/editing > anything that doesn't apply to your patch. Also had these fragments as well, if they're still useful. Probably just useful as pointers as to what else to change to include the docs. The tests and docs were written from SQL standard, so any deviations would need to be flagged. The idea of writing the tests first was that they provide an objective test of whether the implementation works according to spec. I'd quite like a commentary on anything that needs changing. Not saying I will necessarily object to differences, but knowing the differences sounds important for us. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services
From: Boxuan Zhai on 5 Aug 2010 09:55 On Thu, Aug 5, 2010 at 7:25 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: > > On 05/08/10 10:46, Simon Riggs wrote: > > > On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: > > >> The following two files specify the behaviour of the MERGE statement > and > > >> how it will work in the world of PostgreSQL. > > > > > >> The HTML file was generated from SGML source, though the latter is not > > >> included here for clarity. > > > > > > Enclose merge.sgml docs for forthcoming MERGE command, as originally > > > written. > > > > Oh, cool, I wasn't aware you had written that already. Boxuan, please > > include this in your patch, after reviewing and removing/editing > > anything that doesn't apply to your patch. > > Thanks a lot for the instruction file of MERGE command. I have read through it carefully. It is really a great work. I have to admit that I am not familiar with the sgml language, and I cannot write the instruction by myself. All features of MERGE demonstrated in this file are consistent with my implementation, EXCEPT the DO NOTHING option. In current edition, we don't have the DO NOTHING action type. That is, during the execution of MERGE commands, if one tuple is not caught by any of the merge actions, it will be ignored. In another word, DO NOTING (although cannot be specified explicitly by user) is the DEFAULT action for tuples. In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. Now, I have changed the RULE strategy of MERGE to the better logic. And I am working on triggers for MERGE, which is also mentioned in the instruction file. I will build a new patch with no long comment and blank line around functions, and possibly contain the regress test file and this sgml instructions in it. I wish we can reach a agreement on the DO NOTHING thing before my next submission, so I can make necessary modification on my code for it. (the new patch may be finished in one or two days, I think) Thanks! PS: I have an embarrassing question: how to view the sgml instructions of postgres in web page form, rather than read the source code of them? > Also had these fragments as well, if they're still useful. Probably just > useful as pointers as to what else to change to include the docs. > > > The tests and docs were written from SQL standard, so any deviations > would need to be flagged. The idea of writing the tests first was that > they provide an objective test of whether the implementation works > according to spec. > > I'd quite like a commentary on anything that needs changing. Not saying > I will necessarily object to differences, but knowing the differences > sounds important for us. > > -- > Simon Riggs www.2ndQuadrant.com <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Training and Services >
From: Simon Riggs on 5 Aug 2010 10:22
On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: > In the contrary, Simon's instruction says that the DEFAULT action for > the tuple caught by no actions is > WHEN NOT MATCHED THEN INSERT DEFAULT VALUES > > From the user's point of view, these two kinds of MERGE command may > have not much differences. But, as the coder, I prefer current > setting, because we can save the implementation for a new type > of MERGE actions (DO NOTHING is a special merge action type). And, > thus, no checks and special process for it. (For example, we need to > make sure that DO NOTHING is the last WHEN clause, and it has no > additional qual. And we have to generate a INSERT DEFAULT VALUES > action for the MERGE command if we don't find the DO NOTHING action) > > Well, if people want the DO NOTHING action, I will add it in the > system. This is only important when using AND <search condition>, so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default => ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. > Now, I have changed the RULE strategy of MERGE to the better logic. > And I am working on triggers for MERGE, which is also mentioned in the > instruction file. I will build a new patch with no long comment and > blank line around functions, and possibly contain the regress test > file and this sgml instructions in it. > > I wish we can reach a agreement on the DO NOTHING thing before my next > submission, so I can make necessary modification on my code for > it. (the new patch may be finished in one or two days, I think) > > Thanks! > > PS: I have an embarrassing question: how to view the sgml instructions > of postgres in web page form, rather than read the source code of > them? If you edit the files, as shown in the patches here, then you just need to drop into the doc/sgml/src directory and type "make". The SGML will then be compiled into HTML and you can view the resulting file directly in your web browser. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |