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

On 2014-09-26 15:58:17 +0300, Heikki Linnakangas wrote:
> On 09/26/2014 03:40 PM, Andres Freund 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 think it's actually quite common to want to have uniqueness constraint
overlapping partitions. Consider e.g. partitioning on the username. You
might still want to ensure emails are unique.

> >But primarily I mean that bulk of the uniqueness checking logic has to
> >live outside the individual AMs. It doesn't sound enticing to reach from
> >inside one AM into another partitions index to do stuff.
>
> Yeah, that's a non-starter. Even with the index locking stuff, though, it
> wouldn't be the AM's responsibility to reach out to other partitions.

I'm thinking of the way btree currently does uniqueness checks. Unless
you move a large chunk of that out of the AM you'll have a hard time
building anything crossing partitions based on it. At least I can't see
how.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services