Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Wed, Mar 25, 2015 at 12:42 PM, Peter Geoghegan <pg@heroku.com> wrote:
> My next revision will have a more polished version of this scheme. I'm
> not going to immediately act on Robert's feedback elsewhere (although
> I'd like to), owing to time constraints - no reason to deny you the
> opportunity to review the entirely unrelated low-level speculative
> locking mechanism due to that.

Attached revision, V3.1, implements this slightly different way of
figuring out if an insertion is speculative, as discussed. We reuse
t_ctid for speculatively inserted tuples. That's where the counter
goes. I think that this is a significant improvement, since there is
no longer any need to touch the proc array for any reason, without
there being any significant disadvantage that I'm aware of. I also
fixed some bitrot, and a bug with index costing (the details aren't
terribly interesting - tuple width wasn't being calculated correctly).

I have worked through Heikki's feedback on documentation and code
tweaks, too (they were mostly just documentation changes).

Notably, I have not:

* Added ON CONFLICT PRIMARY KEY (might get to this later)

* Otherwise altered the inference specification. I have not allowed it
to name a constraint, which Heikki and I favor (Robert wanted to name
an operator directly). Again, just haven't got around to it.

* Done anything new with logical decoding. My handling of conflicts
during transaction reassembly remains questionable. I hope this can be
worked out soon, possibly with input from Andres. He is sitting next
to me at pgConf.US, so maybe we can work something out in person.

* Addressed the concerns held by Heikki and Robert about multiple
equivalent unique indexes. I confirmed through testing that this can
cause unique violations that are arguably spurious. It isn't too bad,
though - with a couple of unique indexes, jjanes_upsert requires high
concurrency (i.e. 128 clients) in order to see one or two or three
such unique violations after a minute or so. But even still, let's fix
it.

As I mentioned, I am at a conference, and obviously there are other
time pressures, so I haven't put as much testing into this revision as
I'd like. I thought that under the circumstances it was preferable to
post what I have sooner rather than later, though.

--
Peter Geoghegan