Re: WIP: Deferrable unique constraints

2009/7/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> 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
>
>> 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?
>
> What's mainly bothering me is the fear that real use cases are so
> heavily skewed towards (b) that we shouldn't bother implementing (a)
> at all.  In that case we'd end up throwing away a lot of this patch,
> or else carrying a lot of not-very-useful code.
>

I have a definite use for (a), which is why I started this. I typically
write web apps on top of PG using an ORM, and I've had to write
some fairly intricate code to save things in the right order to avoid
violating my own unique constraints.

I agree that (b) is important too, but typically when I do this kind of
bulk update, I drop and re-add the indexes.

More importantly, if you implement (b) first, how is it ever going
to help with (a)? I thought it best to do (a) first and then think about
how to switchover to (b).

- Dean

> I don't have a lot of concrete evidence about this, but as I said
> most of the complaints we've heard are suggesting bulk updates, eg
> http://archives.postgresql.org/pgsql-bugs/2003-05/msg00052.php
> http://archives.postgresql.org/pgsql-bugs/2004-09/msg00248.php
> http://archives.postgresql.org/pgsql-bugs/2007-07/msg00070.php
>
> There are some suggesting the other, eg,
> http://archives.postgresql.org/pgsql-bugs/2008-01/msg00172.php
> but they're clearly in the minority.
>
> It's possible that the first group are just using simple updates
> to illustrate the bug, rather than as examples of what they
> really want to do, but I'm unconvinced of that.
>
> Anyway, I'd feel a lot better about it if there were a near-term
> plan to do something about (b).
>
>                        regards, tom lane
>