Re: INSERT...ON DUPLICATE KEY IGNORE

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