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: Boxuan Zhai on 5 Aug 2010 21:39 Dear All, I have seen a lively discussion about the DO NOTING action in MERGE command. And, I think most people want it. So it will be added to my next patch. Before the implementation, I still have some questions to confirm: 1. If we have a DO NOTHING action specified, it should be the last WHEN clause. It must be of the NOT MATCHED cases, and it CANNOT have any additional action qualifications. Am I correct? 2. If no DO NOTHING specified, we will imply a INSERT DEFAULT VALUES action as the end of MERGE. My question is, is this action taken only for the NOT MATCHED tuples? If this is the case, then what about the MATCHED tuples that match not previous actions? Ignore them? That means we are in fact going to add two implicit WHEN clause: a) WHEN NOT MATCHED INSERT default values; b) WHEN MATCHED THEN DO NOTHING. OR, is the INSERT DEFAULT VALUES applied to ALL tuples not matter they are MATCHED or not? Besides, (I mean no offense, but) can this method really avoid losing row? So far as I know, the DEFAULT values for table attributes are defined when the table is created and no variables are allowed in the default value expressions. That means, they are usually constants or simple serial numbers. Image that we have a MERGE command that has thousands of NOT MATCHED tuples going to the implicit action. Then, the target table will inserted with thousands of rows with DEAULT VALUES. These row will have similar (if not exactly the same) simple content, which contains NO information from the source table of MERGE. Is this really what we want? If it is not, then what is the use of the INSERT DEFAULT VALUES action? Regards
From: Simon Riggs on 6 Aug 2010 03:12 On Fri, 2010-08-06 at 09:39 +0800, Boxuan Zhai wrote: > Besides, (I mean no offense, but) can this method really avoid losing > row? Not as you just specified, no. You need *both* actions of RAISE ERROR and DO NOTHING, or you may as well have neither. (1) Natural style allows missing rows if you are not careful - and also allows missing rows in future when COL is allowed to take value 'C', which may not have been originally considered when SQL first written WHEN NOT MATCHED AND COL = 'A' INSERT... WHEN NOT MATCHED AND COL = 'B' INSERT... (2) Shows code style required to explicitly avoid missing rows WHEN NOT MATCHED AND COL = 'A' INSERT... WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED RAISE ERROR (3) More complex example, with explicit DO NOTHING, showing how it can provide well structured code WHEN NOT MATCHED AND COL = 'A' DO NOTHING WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED RAISE ERROR So DO NOTHING is the default and implies silently ignoring rows. RAISE ERROR is the opposite. Coding for those seems very easy, its just a question of "should we do it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2 introduction of AND clauses, and SQL:2011 has so far followed the DB2 introduction of DELETE action also. Given that Peter is now attending SQL Standards meetings, I would suggest we leave out my suggestion above, for now. We have time to raise this at standards meetings and influence the outcome and then follow later. There is a workaround: WHEN NOT MATCHED AND COL = 'A' DO NOTHING WHEN NOT MATCHED AND COL = 'B' INSERT... WHEN NOT MATCHED AND TRUE INSERT INTO ERROR_TABLE (errortext); where ERROR_TABLE has an INSERT trigger which throws an ERROR with given text. SQL:2011 makes no mention of how MERGE should react to statement level triggers. MERGE is not a trigger action even. Given considerable confusion in this area, IMHO we should just say the MERGE does not call statement triggers at all, of any kind. -- 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
From: Heikki Linnakangas on 6 Aug 2010 03:28 On 06/08/10 10:12, Simon Riggs wrote: > So DO NOTHING is the default and implies silently ignoring rows. RAISE > ERROR is the opposite. > > Coding for those seems very easy, its just a question of "should we do > it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2 > introduction of AND clauses, and SQL:2011 has so far followed the DB2 > introduction of DELETE action also. I see neither DO NOTHING or RAISE ERROR in the documentation of DB2, Oracle, or MSSQL server. > Given that Peter is now attending SQL Standards meetings, I would > suggest we leave out my suggestion above, for now. We have time to raise > this at standards meetings and influence the outcome and then follow > later. Ok, fair enough. > SQL:2011 makes no mention of how MERGE should react to statement level > triggers. MERGE is not a trigger action even. Given considerable > confusion in this area, IMHO we should just say the MERGE does not call > statement triggers at all, of any kind. IMO the UPDATE/DELETE/INSERT actions should fire the respective statement level triggers, but the MERGE itself should not. -- 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 6 Aug 2010 03:41 On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote: > > SQL:2011 makes no mention of how MERGE should react to statement level > > triggers. MERGE is not a trigger action even. Given considerable > > confusion in this area, IMHO we should just say the MERGE does not call > > statement triggers at all, of any kind. > > IMO the UPDATE/DELETE/INSERT actions should fire the respective > statement level triggers, but the MERGE itself should not. When, and how? If an UPDATE is mentioned 5 times, do we call the trigger 5 times? What happens if none of the UPDATEs are ever executed? Best explain exactly what you mean. -- 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
From: Simon Riggs on 6 Aug 2010 03:53
On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote: > On 06/08/10 10:12, Simon Riggs wrote: > > So DO NOTHING is the default and implies silently ignoring rows. RAISE > > ERROR is the opposite. > > > > Coding for those seems very easy, its just a question of "should we do > > it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2 > > introduction of AND clauses, and SQL:2011 has so far followed the DB2 > > introduction of DELETE action also. > > I see neither DO NOTHING or RAISE ERROR in the documentation of DB2, > Oracle, or MSSQL server. Agreed, Oracle and MSSQL server does not have these. However, DB2 very clearly does have these features * SIGNAL which raises an error and can be used in place of any action, at any point in sequence of WHEN clauses. DB2 already supports SIGNAL as part of SQL/PSM, which we do not, so RAISE ERROR was the nearest equivalent command for PostgreSQL. * ELSE IGNORE which does same thing as DO NOTHING, except it must always be last statement in a sequence of WHEN clauses. DO NOTHING is already a phrase with exactly this meaning in PostgreSQL, so I suggest that. -- 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 |