On 08/30/2013 03:09 PM, Peter Geoghegan wrote: > The attached WIP patch implements this for Postgres, with a few > notable differences:
Thank you for addressing this. If nobody is going to hack out MERGE, we could really use at least this feature. > 3) RETURNING is expanded - "RETURNING REJECTS *" is now possible where > that makes sense. Oh, nifty! OK, now I can *really* use this feature. > This patch is a spin-off from a broader effort to implement > INSERT...ON DUPLICATE KEY UPDATE (upsert). During the 2012 developer Yeah, I was wondering when we'd get to that. Obviously there will be users clamoring for it ... > Unlike some other systems like DB2, we have always allowed BEFORE ROW > triggers to execute arbitrary SQL. I've frequently thought this was a > bit of a wart (e.g. [10]), and certainly not supportive of sensible, > idiomatic trigger use, but there isn't much we can do about it at this > stage. Right now, BEFORE ROW triggers will still fire when the new > code decides to not do the insert. It certainly wouldn't be acceptable > to allow before triggers to run *after* the first phase of speculative > insertion, because they might try and access an index with an > exclusive locked buffer, resulting in backend self-deadlock. Besides, > we cannot really know *what* to lock until after the before triggers > fire. That leaves us with two options, as far as I can tell: > > 1. Just have users live with those semantics. This is what the patch > does presently, and anyone who is using triggers in what I consider to > be a sensible way doesn't have to care. For everyone else, it's a > gotcha that they have to deal with, to be noted prominently. +1. We already allow BEFORE triggers to violate referential integrity. I don't think that allowing them to behave oddly around INSERT ... IGNORE is a problem compared to that. We just need to document it, so that users know that their BEFORE code will fire even if the INSERT is being ignored, and that a BEFORE trigger can cause an INSERT ... IGNORE to error out. > I have done no performance testing to date. Reviewers will want to pay > attention to the performance implications, particularly in the regular > insert case; it's conceivable that I've regressed things, though I > don't specifically suspect that I have. Yeah, we'll also want to document the performance overhead for the bulk loading case. I know I'll want to use this syntax as a primitive form of MERGE, and I'll want to know what it costs me. Does this work with multiple VALUES rows? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
