Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Sat, Dec 27, 2014 at 11:48 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> Could you elaborate, please? What are the details of the torture test
>> you're performing?

> The gist of it is that I increment a count column of a random row (via pk)
> in multiple connections simultaneously.

This is great. In general, I strongly believe that we should be doing
this kind of thing more formally and more frequently. Thanks!

> That is for my original code. For this purpose, I made the count go either
> up or down randomly, and when a row's count passes through zero it gets
> deleted. Then when it is chosen for increment/decrement again, it has to be
> inserted. I've made this happen either through a update-or-insert-or-retry
> loop (two variants) or by using your new syntax.

Did you continue to limit your investigation to value locking approach
#1? I think that #2 is the more likely candidate for commit, that we
should focus on. However, #1 is more "conceptually pure", and is
therefore an interesting basis of comparison with #2 when doing this
kind of testing.

> There is a patch which adds a simulation for a torn-page-write followed by a
> crash, and also adds some elogs that I've sometimes found useful for
> tracking down problems, with new GUCs to control them.

Cool.

> I don't think you made changes to the WAL/recovery routines, so I don't
> expect crashing recovery to be a big hazard for your patch, but I wanted to
> run a test where I was generally familiar with the framework, and thought an
> independently derived test might exercise some new aspects.

Value locking approach #2 does touch crash recovery. Value locking
approach #1 does not.

I certainly see the logic in starting with independently derived
tests. We all have our blind spots.

> The one thing I noticed is that using your syntax starts out slightly slower
> than the retry loop, but then gets much slower (down by 2 or 3 times) after
> a while. It might be a vacuuming issue.

Interesting. I'd like to compare both approaches to value locking here.

> I'll try to look at your own stress tests on github as well.

Would you be opposed to merging your custom stress-test suite into my
git repo? I'll give you the ability to push to it.

I can help you out if you think you'd benefit from access to my
Quad-core server (Intel Core i7-4770) for stress-testing. I'll
coordinate with you about it privately.
--
Peter Geoghegan