Re: UNIQUE predicate

Neil Conway wrote:
> On Sat, Jul 06, 2002 at 05:32:53PM -0400, Tom Lane wrote:
> > nconway(at)klamath(dot)dyndns(dot)org (Neil Conway) writes:
> > > The attached patch implements the SQL92 UNIQUE predicate.
> >
> > The implementation seems to be well short of usefulness in a production
> > setting, for two reasons: (1) you're accumulating all the tuples into
> > memory --- what if they don't fit? (2) the comparison step is O(N^2),
> > which renders the first point rather moot ... a test case large enough
> > to risk memory exhaustion will not complete in your lifetime.
>
> That's true -- I probably should have noted in the original email that
> my implementation was pretty much "the simplest thing that works".
>
> > I think a useful implementation will require work in the planner to
> > convert the UNIQUE predicate into a SORT/UNIQUE plan structure (somewhat
> > like the way DISTINCT is implemented, but we just want a boolean
> > result).
>
> Hmmm... that's certainly possible, but I'm not sure the feature is
> important enough to justify that much effort.

I am going to agree with Tom on this one. We do foreign key triggers in
memory, but having a entire query result in memory to perform UNIQUE
seems really stretching the resources of the machine.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026