Re: INSERT ... ON CONFLICT syntax issues

On Wed, Apr 29, 2015 at 3:13 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> My general feeling is "keep it short" but I'm not particular beyond
> that. I do like the idea that we'll be able to support more options in
> the future.

Yeah. To me "ON CONFLICT" sounds like "if there's a war, then...".
So I like ON DUPLICATE, which I think is clear as crystal. But if we
settle on something else I won't cry myself to sleep.

>> > * Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
>> > TARGET.*). Those seem fine to me as well.
>>
>> There seem to be a few votes for NEW and OLD. That's what I proposed
>> originally, and (surprise, surprise) I still like that better too.
>
> I was promoting NEW/OLD, until I realized that we'd end up having a
> problem in trigger functions because NEW/OLD are already defined there,
> unless you have a suggestion for how to improve on that?

I don't. That's a good point.

>> > * Finally, add "ON CONFLICT ON CONSTRAINT my_constraint" support, so
>> > you can name (exactly one) constraint by name. Particularly useful for
>> > unique constraints. I really don't want to make this accept multiple
>> > constraints, even though we may infer multiple constraints, because
>> > messy, and is probably too complex to every be put to good use
>> > (bearing in mind that exclusion constraints, that really need this,
>> > will still only be supported by the IGNORE/DO NOTHING variant).
>>
>> I still think that constraints should never be named in the syntax.
>
> I guess I don't see a particular problem with that..? Perhaps I'm
> missing something, but if there's multiple ways for something to
> conflict, it might be nice to be able to differentiate between them?
> Then again, I'm not sure if that's what the intent here is.

So, with unique indexes, people can create an index concurrently, then
drop the old index concurrently, and nothing breaks. I don't think we
have a similar capacity for constraints at the moment, but we should.
When somebody does that dance, the object names change, but all of the
DML keeps working. That's a property I'd like to preserve.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company