Thread: UNIQUE predicate

Hi all,

The attached patch implements the SQL92 UNIQUE predicate. I've written
some regression tests (as well as adding a few for subselects in FROM
clauses). I'll update the documentation if/when this patch is accepted.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

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.

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).

            regards, tom lane

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.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

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