On 21 May 2015 at 14:25, Peter Geoghegan <p...@heroku.com> wrote: > > > If I have two constraints and I think about it, I would want to be able > to > > specify this... > > > > INSERT > > ON CONFLICT (col1) DO UPDATE... (handle it one way) > > ON CONFLICT (col2) DO UPDATE... (handle it 2nd way) > > > > but I cannot with the current syntax. > > > > It seems strange to force the user to think about constraint handling and > > then not offer them any choices once they have done the thinking. > > What if both constraints are violated? Won't the update end up in trouble? >
Great question. We don't handle that at the moment. So how do we handle that? > > If the update is the same no matter which constraint is violated, why > would > > I need to specify the constraint? We're forcing the developer to make an > > arbitrary choice between two constraints. > > Why would the update be the same, though? *If* is the keyword there. > How could that make sense? > It wouldn't, that is the point. So why does the current syntax force that? > You're still going to have to update both unique-indexed columns with > something, and that could fail. ISTM clear that you might want to handle each kind of violation differently, but we cannot. > We will see many people ask why they have to specify constraints > explicitly. > > I'm not sure that we will, actually, but as I said, go ahead and > propose removing the restriction if you think it's important (maybe > start a thread on it). > I am. I have. Many times. What is wrong with this thread or all of the other times I said it? Please look at the $SUBJECT of this thread. We're here now. > As I've pointed out, if the underlying model changes then you now have to > > explicitly recode all the SQL as well AND time that exactly so you roll > out > > the new code at the same time you add/change constraints. That makes it > much > > harder to use this feature than I would like. > > If the underlying model changes, then it's good that your queries > break, because they're predicated on the original model. I don't think that happens very often at all. If it seldom happens, then why do we need to specify the conflict-target? If I know there is only one unique constraint, why can I not rely upon that knowledge? > What is much more routine - adding > redundant indexes to reindex using CREATE INDEX CONCURRENTLY, or > changing the predicate on whatever partial unique indexes happen to be > defined on the table - is handled gracefully. > What has CREATE INDEX CONCURRENTLY got to do with this? If you don't specify the conflict-target at all, it wouldn't matter what the indexes are. If you have two indexes the same then it clearly wouldn't matter which one was checked first. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
