Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint - Mailing list pgsql-hackers
| From | Simon Riggs |
|---|---|
| Subject | Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint |
| Date | |
| Msg-id | CANP8+jLWfLrRahWcEyuTxftB-JWOP4wJrY-jiMnr78=8ak8Z1w@mail.gmail.com Whole thread Raw |
| In response to | Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint (Peter Geoghegan <pg@heroku.com>) |
| Responses |
Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
(Stephen Frost <sfrost@snowman.net>)
Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint (Peter Geoghegan <pg@heroku.com>) |
| List | pgsql-hackers |
On 21 May 2015 at 14:25, Peter Geoghegan <pg@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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: