Re: Refactoring speculative insertion with unique indexes a little

On 09/24/2016 02:34 PM, Peter Geoghegan wrote:
> On Tue, Sep 20, 2016 at 8:55 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>> Likewise, in ExecInsertIndexTuples(), this makes the deferred-index
>> case work slightly differently from speculative insertion. It's not a big
>> difference, but it again forces you to keep one more scenario in mind, when
>> reading the code
>
> This actually does have useful practical consequences, although I
> didn't point that out earlier (though I should have). To see what I
> mean, consider the complaint in this recent thread, which is based on
> an actual user application problem:
>
> https://www.postgresql.org/message-id/flat/1472134358(dot)649524557(at)f146(dot)i(dot)mail(dot)ru#1472134358(dot)649524557@f146.i.mail.ru
>
> I go on to explain how this patch represents a partial solution to
> that [1]. That's what I mean by "useful practical consequences". As I
> say in [1], I think we could even get a full solution, if we applied
> this patch and *also* made the ordering in which the relcache returns
> a list of index OIDs more useful (it should still be based on
> something stable, to avoid deadlocks, but more than just OID order.
> Instead, relcache.c can sort indexes such that we insert into primary
> keys first, then unique indexes, then all other indexes. This also
> avoids bloat if there is a unique violation, by getting unique indexes
> out of the way first during ExecInsert()).

Hmm, yeah, that'd be nice to fix. I'd like to see a patch specifically
to fix that. I'm not convinced that we need all the complications of
this patch, to get that fixed. (In particular, indexam's still wouldn't
need to care about the different between CHECK_UNIQUE_PARTIAL and
CHECK_UNIQUE_SPECULATIVE, I think.)

- Heikki