Re: WIP: Deferrable unique constraints

2009/7/27 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Mon, 2009-07-27 at 13:14 -0400, Tom Lane wrote:
>> The main thing that is bothering me is something Dean pointed out at
>> the very beginning: the patch will not scale well for large numbers of
>> conflicts.
>

I'd pefer to take option #3, and I want to try to tackle the scaling
issue next. I didn't do it as part of this patch because it has
already grown very big, and I think the scaling problem is largely an
orthogonal issue which affects all AR triggers.

> The way I see it, there are two strategies:
>  (a) build up a list as you go, and check it later
>  (b) do a check of the full table at once
>
> Is there another reasonable option?
>

IMO (a) is the only way to go, since you don't know until the end of
an update command what proportion of rows were potential conflicts,
and so whether or not to do a row-by-row or a wholesale check.

I think that this will be almost entirely a patch to trigger.c, for
which there is already a separate TODO item.

Actually I see 2 parts to this:
(1). make trigger queue scalable with easy mechanism to switchover to
wholesale check
(2). implement wholesale check for UNIQUE

but (1) seems like to lion's share of the work.

(and then maybe (3). wholesale check for RI constraints)

- Dean

> The patch seems like a reasonable implementation of (a), so what it's
> missing is the ability to fall back to (b) when the list gets too large
> (compared with available memory or relative to the table size).
>
> Are you suggesting that we wait until (b) is implemented, or do you
> envision something else entirely?
>
> Regards,
>        Jeff Davis
>
>