On Wed, Oct 8, 2014 at 2:50 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> What I said was in response to your assertion that your technique
> would *never* generate a duplicate key error.
That is strictly true: the INSERT cannot raise a unique violation
error, because to do so would cause it to take the UPDATE path
(assuming there is no WITHIN clause). The UPDATE may get one, though.
It doesn't have to get one for your statement to have effects that are
surprising, though.
> As others have again
> been pointing out, when there is more than one unique index you can
> have rows to apply which cannot be applied accurately without
> causing such an error.
It's simpler than that. You want to make sure that the right condition
- the right unique index having a would-be duplicate violation - is
the one taken *in practice*, the condition on which the UPDATE path is
taken. What happens after that is not that interesting (e.g. whether
or not there is an UPDATE-path duplicate violation), because either
way it's broken.
> What I requested was that the behavior in
> those cases be clear and documented. I didn't take a position on
> whether you pick an index or ignore the input row (with a
> warning?), but we need to decide how it is handled. I have made
> the same point Heikki is making, though -- we have no business
> referencing an index name in the statement.
I think that's fair enough. That's all fine - but actually doing that
is quite tricky. Look at what I've said in relation to doing that.
Consider the corner-cases I name. They're certainly only a small
minority of cases in practice, but as an implementer I still need to
worry about them (maybe even just as much). Yes, I would like to just
name columns, but it's hard to make that fully generally.
If you want me to do what you say, fine. But in order to do that, I
need support for having the corner cases make parse analysis throw up
its hands and error. Is that a price you're willing to pay? If so,
then I'll implement it. Or, alternatively, we could do WITHIN PRIMARY
key/NOT WITHIN PRIMARY KEY.
--
Peter Geoghegan