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 6 Aug 2010 04:23 On Fri, Aug 6, 2010 at 3:41 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > 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? My current process for BEFOR / AFTER STATEMENT trigger on MERGE is to fire the triggers for all action types that appears in the command, unless it is replaced by a INSTEAD rule. But the triggers for one action type will be fired only once. That means you will get both UPDATE and INSERT triggers be activated for only once if you are executing a MERGE command with 5 UPDATEs and 10 INSERTs. > What happens if none of the UPDATEs are ever executed? > > The triggers (I mean triggers for statement) will be fired anyway even the UPDATE action matches no tuple. This is not for MERGE only. If you update a table with the command UPDATE foo SET ... WHERE false; It will also fire the STATEMENT triggers of UPDATE type on foo (I think so). And, even not been asked, I want to say that, in current implementation of MERGE, the row level triggers are fired by the actions that take the tuples. If one tuple is caught by an UPDATE action, then the UPDATE row trigger will be fired on this tuple. If it is handled by INSERT action, then the INSRET row triggers are on. Hope you agree with my designs. > Best explain exactly what you mean. > > -- > Simon Riggs www.2ndQuadrant.com <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Training and Services > >
From: Boxuan Zhai on 6 Aug 2010 04:26 On Fri, Aug 6, 2010 at 3:53 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > 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. > > So, we need to add both DO NOTHING and RAISE ERROR actions in the MERGE command now !? What will RAISE ERROR do? To stop the whole MERGE command OR, just throw an error notice for the row and move on. > -- > Simon Riggs www.2ndQuadrant.com <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Training and Services > >
From: Simon Riggs on 6 Aug 2010 04:51 On Fri, 2010-08-06 at 16:26 +0800, Boxuan Zhai wrote: > So, we need to add both DO NOTHING and RAISE ERROR actions in the > MERGE command now !? What will RAISE ERROR do? Let's get the rest of it working first. This would be a later extension, though I think an important one for our developers. > To stop the whole MERGE command OR, just throw an error notice for the > row and move on. As you say, it would be even better to be able to report errors in some way and move onto next row. NOTICE is not the way though. Maybe one of the actions would be to EXECUTE a procedure, so we can call an error logging function. -- 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:03
On 06/08/10 04:39, Boxuan Zhai wrote: > 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? It would be useful to specify it in WHEN MATCHED sometimes, and not necessarily the last. For example: MERGE INTO Stock S USING DailySales DS ON S.Item = DS.Item WHEN MATCHED AND (QtyOnHand ‐ QtySold = 0) THEN DELETE WHEN MATCHED THEN UPDATE SET QtyOnHand = QtyOnHand ‐ QtySold -- Don't add new inactive items to stock if not there already WHEN MATCHED AND (itemtype = 'inactive') THEN DO NOTHING WHEN NOT MATCHED THEN INSERT VALUES (Item, QtySold); It shouldn't be difficult to support DO NOTHING in all cases, right? > 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? We'll need to figure out what the SQL standard says about this. I tried reading the spec but couldn't readily understand what the default action should be. Does someone else know that? What do other DBMSs do? -- 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 |