On Mon, Oct 13, 2014 at 2:02 PM, Peter Geoghegan <p...@heroku.com> wrote: >> If the user issues INSERT .. ON DUPLICATE (a) UPDATE, we'll fire >> before-insert triggers and then inspect the tuple to be inserted. If >> b is neither 1 nor 2, then we'll fail with an error saying that we >> can't support ON DUPLICATE without a relevant index to enforce >> uniqueness. (This can presumably re-use the same error message that >> would have popped out if the user done ON DUPLICATE (b), which is >> altogether un-indexed.) But if b is 1 or 2, then we'll search the >> matching index for a conflicting tuple; finding none, we'll insert; >> finding one, we'll do the update as per the user's instructions. > > Before row insert triggers might invalidate that conclusion at the > last possible moment. So you'd have to recheck, which is just messy.
I can't imagine that you'd decide which index to use and then change your mind when you turn out to be wrong. I think rather you'd compute a list of possibly-applicable indexes based on the ON DUPLICATE column list, and then, after firing before-insert triggers, check whether there's one that will definitely work. If there's a non-partial unique index on the relevant columns, then you can put any single such index into the list of possibly-usable indexes and leave the rest out; otherwise, you include all the candidates and pick between them at runtime. If that seems too complicated, leave it out for v1: just insist that there must be at least one unique non-partial index on the relevant set of columns. >> I'm considering your points in this area as well as I can, but as far >> as I can tell you haven't actually set what's bad about it, just that >> it might be hazardous in some way that you don't appear to have >> specified, and that MySQL doesn't allow it. I am untroubled by the >> latter point; it is not our goal to confine our implementation to a >> subset of MySQL. > > I did - several times. I linked to the discussion [1]. I said "There > is a trade-off here. I am willing to go another way in that trade-off, > but let's have a realistic conversation about it". And Kevin > eventually said of not supporting partial unique indexes: "That seems > like the only sensible course, to me". At which point I agreed to do > it that way [2]. So you've already won this argument. All it took was > looking at my reasons for doing things that way from my perspective. > If there has been digging of heals, you should consider that it might > be for a reason, even if on balance you still disagree with my > conclusion (which was clearly not really a firm conclusion in this > instance anyway). That's all I mean here. There seems to be some confusion here. This part was about this syntax: >>>> INSERT INTO overwrite_with_abandon (key, value) >>>> VALUES (42, 'meaning of life') >>>> ON DUPLICATE (key) UPDATE; That's a different issue from naming indexes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
