Re: 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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Hi,

I've attached the changes I've made to pg_attribute.h - I can't see what's
wrong but whenever I do an initdb it fails:

initdb -D /home/chriskl/local/data
The files belonging to this database system will be owned by user "chriskl".
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /home/chriskl/local/data... ok
creating directory /home/chriskl/local/data/base... ok
creating directory /home/chriskl/local/data/global... ok
creating directory /home/chriskl/local/data/pg_xlog... ok
creating directory /home/chriskl/local/data/pg_clog... ok
creating template1 database in /home/chriskl/local/data/base/1...
initdb failed.
Removing /home/chriskl/local/data.

Chris

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> I've attached the changes I've made to pg_attribute.h - I can't see what's
> wrong but whenever I do an initdb it fails:

Did you change the relnatts entry in pg_class.h for pg_attribute?

More generally, run initdb with -d or -v or whatever its debug-output
switch is, and look at the last few lines to see the actual error.
(Caution: this may produce megabytes of output.)

regards, tom lane

Seems we may not need isdropped, so I will hold on evaluating this.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Hi,
>
> I've attached the changes I've made to pg_attribute.h - I can't see what's
> wrong but whenever I do an initdb it fails:
>
> initdb -D /home/chriskl/local/data
> The files belonging to this database system will be owned by user "chriskl".
> This user must also own the server process.
>
> The database cluster will be initialized with locale C.
>
> creating directory /home/chriskl/local/data... ok
> creating directory /home/chriskl/local/data/base... ok
> creating directory /home/chriskl/local/data/global... ok
> creating directory /home/chriskl/local/data/pg_xlog... ok
> creating directory /home/chriskl/local/data/pg_clog... ok
> creating template1 database in /home/chriskl/local/data/base/1...
> initdb failed.
> Removing /home/chriskl/local/data.
>
> Chris

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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

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.

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

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

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