Re: INSERT ... ON CONFLICT syntax issues
От | Andres Freund |
---|---|
Тема | Re: INSERT ... ON CONFLICT syntax issues |
Дата | |
Msg-id | 20150425182405.GD12723@awork2.anarazel.de обсуждение исходный текст |
Ответ на | Re: INSERT ... ON CONFLICT syntax issues (Peter Geoghegan <pg@heroku.com>) |
Ответы |
Re: INSERT ... ON CONFLICT syntax issues
(Peter Geoghegan <pg@heroku.com>)
|
Список | pgsql-hackers |
On 2015-04-25 11:05:49 -0700, Peter Geoghegan wrote: > On Sat, Apr 25, 2015 at 2:01 AM, Andres Freund <andres@anarazel.de> wrote: > > My problem with the WHERE being inside the parens in the above is that > > it's > > a) different from CREATE INDEX > > I don't think that that's an important goal. Given that it's used to 'match' to indexes, I can't agree. > > b) unclear whether the WHERE belongs to colb or the whole index > > expression. The equivalent for aggregates, which I bet is going to be > > used less often, caused a fair amount of confusing. > > I don't see those two situations as being comparable. The inference > specification does not accept aggregates. Huh? It's pretty much entirely besides the point that inference doesn't accept aggregates. The point is that ORDER BY for aggregates has confused users because it's inside the parens. > > a) Why is is 'CONFLICT"? We're talking about a uniquness violation. What > > if we, at some later point, also want to handle other kind of > > violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ... > > I think that naming unique violations alone would be wrong (not to > mention ludicrously verbose). Why? > The syntax has been like this for some time, and > hasn't been a point of contention for a long time, so I thought this > was settled. I really don't care if it's been that for a long while. This is a not yet commited feature. > > b) For me there's a WITH before the index inference clause missing, to > > have it read in 'SQL' style. > > I'm not seeing it. BTW, Robert was the one who initially proposed that > the unique index inference clause follow this exact style (albeit > before it accepted a WHERE clause to infer partial indexes, which was > only added a couple of months ago). So? I guess I can live with that uglyness; but I'd like somebody else to chime in. > > c) Right now the UPDATE can refer to pseudo relations 'TARGET' and > > 'EXCLUDED'. I think especially the latter doesn't fit anymore at > > all. How about 'CONFLICTING' and 'EXISTING'? Or even NEW and OLD? > > NEW and OLD are terribly misleading, since surely the NEW tuple is the > one actually appended to the relation by the UPDATE, and the OLD one > is the existing one (not the excluded one). Plus they have all that > intellectual baggage from rules. What 'intellectual baggage' would that be? That they're already known to have been used in another place? I don't see the problem. How about EXISTING and NEW? > """ > verb (used with object), excluded, excluding. > 1. > to shut or keep out; prevent the entrance of. > 2. > to shut out from consideration, privilege, etc.: > Employees and their relatives were excluded from participation in the contest. > 3. > to expel and keep out; thrust out; eject: > He was excluded from the club for infractions of the rules. > """ > > Seems pretty descriptive of the situation to me - I actually put a lot > of thought into this. Additionally, the word is widely understood by > non-native speakers. TARGET is also very descriptive, because it > situationally describes either the existing tuple actually present in > the table, or (from a RETURNING clause) the final tuple present in the > table post-UPDATE. We use the term "target" for that pervasively (in > the docs and in the code). Sorry, I don't buy either argument. EXISTING and NEW would surely at least as widely understood than EXCLUDE and TARGET. The latter does just about no sense to me; especially from a user POV. I don't think the existing usage of the term has much to do what it's used for here. That it has 'morphing' characteristics imo just makes it worse, rather than better. Besides being confusing that it has different meanings, it's far from inconceivable that somebody wants to return values from the preexisting, new, and merged rows. Greetings, Andres Freund
В списке pgsql-hackers по дате отправления: