It seems there is strange behaviour coming from trying to apply complex Rules to the MERGE statement.
My proposal from here is to allow MERGE to work with Rules, but only when the Rules are equivalent to simply updatable views. This would restrict MERGE somewhat, yet be entirely compatible with SQL Standard behaviour of MERGE, Views etc.. Let's look at some of the strangeness: Rules allow you to define something like this CREATE RULE foo_rule AS ON INSERT TO foo DO ALSO INSERT foo2 ...; so that any insert into foo becomes 2 inserts, 1 into foo, 1 into foo2. Now if we do an INSERT INTO FOO SELECT .... this gets rewritten into 1. INSERT INTO foo SELECT ... 2. INSERT INTO foo2 SELECT ... This means that we fire statement-level insert triggers on foo and foo2. By analogy, we might expect MERGE to behave similarly. That could be true with trivial examples such as MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT though with a statement like this MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE what do we expect to happen exactly? We run the MERGE statement twice, with the insert statements permuted? But what happens with the UPDATE? Especially if there are also rules that apply to UPDATE. Would we run it four times? No, the MERGE query must run once and we must handle the rules within that single execution. Which means if we do that then MERGE acts differently with Rules than does an INSERT SELECT. Which feels like a warning... So we must replace the INSERT with INSERT foo; INSERT foo2 and run the MERGE. If we do this do we treat the second INSERT as a separate statement each time it is executed? If so a statement-level trigger on INSERT foo2 would be executed once for each row we insert. To make that happen correctly we would need to apply the rule as if the INSERT were a top-level statement, then execute it within MERGE as if it were not a top-level statement (i.e. do not execute statement-level triggers for that statement). If we have AFTER ROW triggers, should they be executed after each execution of the sub-statement? or should they be executed in a group at the end of the MERGE statement, as would happen if an INSERT SELECT. What would happen if one of the rules contained a MERGE statement? We'd have to recursively apply rules down into the sub-statements of the MERGE, yet unravel the trigger behaviour correctly. All of this makes the hair on the back of my neck stand up. It's taken a while to realise these issues exist. This feels to me like even stranger behaviour might lurk somewhere there. This is a long way from clear behaviour and makes me think it will be a long way from a clean and useful implementation. Clear, obvious behaviour for MERGE only seems possible when we have some restrictions on rules. My proposal is to throw an "ERROR Cannot resolve rules for MERGE statement", except when the rules meet the following restrictions: * allow MERGE with rules which say DO NOTHING e.g. CREATE RULE foo_rule AS ON INSERT TO foo DO NOTHING; That's fairly trivial * allow MERGE when rules which are merely "redirections" e.g. CREATE RULE foo_rule AS ON INSERT TO foo DO INSTEAD INSERT foo2 So only rules that have a single replacement statement. This is roughly equivalent to a rule we might create to emulate a simply updatable view. * allow rules only when all of the actions are similarly redirected, so we only ever need to make changes to one table i.e. SELECT, UPDATEs, INSERTs and DELETEs are all redirected So a MERGE statement like this MERGE INTO foo USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE with these rules CREATE RULE foo_i AS ON INSERT TO foo DO INSTEAD INSERT foo2 CREATE RULE foo_u AS ON UPDATE TO foo DO INSTEAD UPDATE foo2 CREATE RULE foo_d AS ON DELETE TO foo DO INSTEAD DELETE foo2 would simply be equivalent to MERGE INTO foo2 USING (SELECT ...) ON (...) WHEN NOT MATCHED INSERT WHEN MATCHED UPDATE In fact, that might even be a better clue as to how to complete the implementation of updatable views. So: do we all agree with the restriction on MERGE to only work with rules equivalent to simple updatability? If we do, should the implementation of MERGE go on hold until after updatable views are added (with the required hooks)? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers