Re: UNIQUE predicate

Neil Conway wrote:
> On Sat, Jul 06, 2002 at 05:32:53PM -0400, Tom Lane wrote:
> > nconway@klamath.dyndns.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026