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

On Fri, Sep 26, 2014 at 5:58 AM, Heikki Linnakangas
 wrote:
>> And the reason that the buffer locking approach in the overlapping case
>> is that you'd need to hold a large number of pages locked at the same
>> time. Right?
>
>
> Yeah, you would. To be honest, I didn't even think about the overlapping
> case, I just assumed that the overlapping case is the typical one and only
> thought about that.

I'm not sure that I follow. Unique constraints don't work across
partitions today. Why should this work across partitions in the most
general case? Simply because there'd have to be one page lock held per
unique index/partition, where promise tuples are somewhat like row
locks, so presumably only one lock table entry is required?

In other database systems with better partitioning support, there is
such a thing as indexes that apply across all partitions ("global
indexes"). There are also "local indexes", that can only be unique if
that comports with the partitioning key in a way that makes sense. But
we don't have anything like global indexes, and even in those other
systems there are huge caveats around MERGE and its impact on global
indexes (they are automatically *marked unusable* by an SQL MERGE
command). So I think making what you have in mind here work for
current Postgres partitioning is totally unrealistic, unless (at the
very least) someone also goes and writes a global index feature, which
is obviously an enormous project.

-- 
Peter Geoghegan