Обсуждение: brain-teaser with CONSTRAINT - any SQL experts?

I'm stuck on a brain-teaser with CONSTRAINT:

Imagine a table like "lineitems" in a bookstore - where you don't need
an ISBN to be unique because a book will be in buying history more
than once.

But you DO need to make sure that the ISBN number is ONLY matched to
one book name - NOT to more than one book name.

This is OK:
isbn    name
1234    Red Roses
1234    Red Roses

This is OK:  (two books can have the same name)
isbn    name
1234    Red Roses
5555    Red Roses

This is NOT OK:  (an isbn must be tied to one book only!)
isbn    name
1234    Red Roses
1234    Green Glasses


I know it's tempting to say, "just link a separate table for the book
and don't store the book name" but let's just pretend that's not an
option - because I'm not actually dealing with books : I just made up
this simplified version of something at work, where we can't change
the table : both isbn and name MUST be in the table, and what I'm
trying to do is put a CONSTRAINT on the table definition to protect
against user error, by making sure that any entered isbn is only tied
to one book-name in that table.

Thoughts?

how about using 2 tables with according unique/primary key constraints and a
view to actually access the data (mixing the 2 tables into one) ?


On Saturday 08 October 2005 22:36, Miles Keaton wrote:
> I'm stuck on a brain-teaser with CONSTRAINT:
>
> Imagine a table like "lineitems" in a bookstore - where you don't need
> an ISBN to be unique because a book will be in buying history more
> than once.
>
> But you DO need to make sure that the ISBN number is ONLY matched to
> one book name - NOT to more than one book name.
>
> This is OK:
> isbn    name
> 1234    Red Roses
> 1234    Red Roses
>
> This is OK:  (two books can have the same name)
> isbn    name
> 1234    Red Roses
> 5555    Red Roses
>
> This is NOT OK:  (an isbn must be tied to one book only!)
> isbn    name
> 1234    Red Roses
> 1234    Green Glasses
>
>
> I know it's tempting to say, "just link a separate table for the book
> and don't store the book name" but let's just pretend that's not an
> option - because I'm not actually dealing with books : I just made up
> this simplified version of something at work, where we can't change
> the table : both isbn and name MUST be in the table, and what I'm
> trying to do is put a CONSTRAINT on the table definition to protect
> against user error, by making sure that any entered isbn is only tied
> to one book-name in that table.
>
> Thoughts?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
    UC

--
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417

On Sat, 2005-10-08 at 22:36 -0700, Miles Keaton wrote:
> ... both isbn and name MUST be in the table, and what I'm
> trying to do is put a CONSTRAINT on the table definition to protect
> against user error, by making sure that any entered isbn is only tied
> to one book-name in that table.

Create a separate table with the two columns name and isbn which are
that table's primary key; on the main table, create a foreign key to the
new table.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

I've been using SuSE and PostgreSQL for a fairly long time. Recently
(last 12 months), I've noticed that the 9.x (9.2 and 9.3 specifically)
versions of SuSE do not include PostgreSQL on the CD install -- only on
the DVD. At first (9.2), I thought it was just a glitch that didn't get
fixed in 9.3. Now, it seems to have been deliberate.

With SuSE 10.0, it appears PostgreSQL is not even included on the DVD.
At least not the eval DVD currently available for download. If SuSE 10.0
users want to have a PostgreSQL server, they'll have to build it from
source themselves (or find the RPM online). The Novell web site says
PostgreSQL 8.0.3 is included in the SuSE 10 distribution, and includes
the packages for the full install. Reality appears to be just the libs.

I'm hoping this is a packaging glitch at Novell, and not something more
serious. Does anyone have any information why SuSE appears to be making
it more difficult to install PostgreSQL on their distro? I am still
searching the opensuse forums for more information as well, but really
wanted to hear from this side of the fence.

Thanks,
Greg

Gregory Youngblood <pgcluster@netio.org> writes:
> I've been using SuSE and PostgreSQL for a fairly long time. Recently
> (last 12 months), I've noticed that the 9.x (9.2 and 9.3 specifically)
> versions of SuSE do not include PostgreSQL on the CD install -- only on
> the DVD. At first (9.2), I thought it was just a glitch that didn't get
> fixed in 9.3. Now, it seems to have been deliberate.

The right thing to do is complain.

Linux packagers usually try to limit the size of the "core" distro to
three or four CDs, which these days calls for some pretty hard
compromises.  SuSE probably decided that Postgres wasn't sufficiently
widely used to justify a place in their core distro.  If they don't
get push-back from users, then that decision is self-justifying.

For comparison, Red Hat (which has slightly more sense than SuSE, in
my highly biased opinion) tried to push mysql-server out of the core
distro in RHEL3.  We got enough push-back on that to reverse the
decision.

> With SuSE 10.0, it appears PostgreSQL is not even included on the DVD.

Hm, does SuSE have a core/extras structure like Red Hat uses?  It'd be
plausible for them to put postgres clients/libs in core and the server
in extras (exactly what Red Hat did to mysql).  I would have thought
that a DVD release would include everything, though.

> The Novell web site says
> PostgreSQL 8.0.3 is included in the SuSE 10 distribution, and includes
> the packages for the full install. Reality appears to be just the libs.

In that case it may just be a packaging mistake ... but in any case,
if you don't complain they'll never change it.

            regards, tom lane

Hi,

On Sun, 9 Oct 2005, Tom Lane wrote:

> Gregory Youngblood <pgcluster@netio.org> writes:
>> I've been using SuSE and PostgreSQL for a fairly long time. Recently
>> (last 12 months), I've noticed that the 9.x (9.2 and 9.3 specifically)
>> versions of SuSE do not include PostgreSQL on the CD install -- only on
>> the DVD. At first (9.2), I thought it was just a glitch that didn't get
>> fixed in 9.3. Now, it seems to have been deliberate.
>
> The right thing to do is complain.

It might also be good to ask Reinhard about this. (CC'ed to him)

Reinhard?
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                       http://www.gunduz.org

on 10/8/05 11:36 PM, mileskeaton@gmail.com purportedly said:

> I know it's tempting to say, "just link a separate table for the book
> and don't store the book name" but let's just pretend that's not an
> option - because I'm not actually dealing with books : I just made up
> this simplified version of something at work, where we can't change
> the table : both isbn and name MUST be in the table, and what I'm
> trying to do is put a CONSTRAINT on the table definition to protect
> against user error, by making sure that any entered isbn is only tied
> to one book-name in that table.

I would create a multi-column unique index on the table. This should solve
the problem mentioned although you may still have an integrity issue if a
"book" name is mistyped.

Best regards,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

Uwe said:
> how about using 2 tables with according unique/primary key constraints and a
> view to actually access the data (mixing the 2 tables into one) ?

Oliver said:
> Create a separate table with the two columns name and isbn which are
> that table's primary key; on the main table, create a foreign key to the
> new table

But my original email said:
> > I know it's tempting to say, "just link a separate table for the book
> > and don't store the book name" but let's just pretend that's not an
> > option


For reasons not worth going into, creating a separate table for the
code and name is not an option.  It has to be a self-contained
restriction inside this table.

Perhaps there's another way, using triggers or something?

Any other ideas?

> I would create a multi-column unique index on the table. This should solve
> the problem mentioned although you may still have an integrity issue if a
> "book" name is mistyped.

Hm?

This sounds promising, except it's the exact opposite of what I need.

Is this what you meant?

CREATE TABLE lineitems (code int, name varchar(12), UNIQUE (code, name));

Because that breaks the whole idea where I should be able to have many
lines with the same item:

insert into lineitems(code, name) VALUES (123, 'bob');
INSERT 35489 1
insert into lineitems(code, name) VALUES (123, 'bob');
ERROR:  duplicate key violates unique constraint "lineitems_code_key"

What I want is for that situation, above, to NOT make an error.
But this, below, should:

insert into lineitems(code, name) VALUES (123, 'bob');
insert into lineitems(code, name) VALUES (123, 'xxx');

Hey

Could you write specific functions "insert"/"update" that people use when
they update the data in the db, that checks for the constraints you are
talking about. So the functions would take in the input data and then would
scan the table to make sure there is no "book" with the same "isbn" that
also has different name, if it did your function could raise an error
message.

I realise you said you actually wanted to put the constraint on the table,
but I just thought I would suggest this anyway.

Cheers
Adam

Adam Lawrence
Mediasculpt

Direct Line: +64 6 3546038
Email: adam@mediasculpt.com
----- Original Message -----
From: "Miles Keaton" <mileskeaton@gmail.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, October 10, 2005 4:25 PM
Subject: Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?


> > I would create a multi-column unique index on the table. This should
solve
> > the problem mentioned although you may still have an integrity issue if
a
> > "book" name is mistyped.
>
> Hm?
>
> This sounds promising, except it's the exact opposite of what I need.
>
> Is this what you meant?
>
> CREATE TABLE lineitems (code int, name varchar(12), UNIQUE (code, name));
>
> Because that breaks the whole idea where I should be able to have many
> lines with the same item:
>
> insert into lineitems(code, name) VALUES (123, 'bob');
> INSERT 35489 1
> insert into lineitems(code, name) VALUES (123, 'bob');
> ERROR:  duplicate key violates unique constraint "lineitems_code_key"
>
> What I want is for that situation, above, to NOT make an error.
> But this, below, should:
>
> insert into lineitems(code, name) VALUES (123, 'bob');
> insert into lineitems(code, name) VALUES (123, 'xxx');
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Solved!

CREATE OR REPLACE FUNCTION non_duplicated_isbn() RETURNS trigger AS $function$
DECLARE
  rez RECORD;
BEGIN
  SELECT INTO rez * FROM books WHERE isbn=NEW.isbn AND name != NEW.name;
  IF FOUND THEN
    RAISE EXCEPTION 'isbn % already used for different book name: %',
NEW.isbn, rez.name;
  END IF;
  RETURN NEW;
END;
$function$ LANGUAGE plpgsql;

CREATE TRIGGER ndi BEFORE INSERT OR UPDATE ON books FOR EACH ROW
EXECUTE PROCEDURE non_duplicated_isbn();




On 10/8/05, Miles Keaton <mileskeaton@gmail.com> wrote:
> I'm stuck on a brain-teaser with CONSTRAINT:
>
> Imagine a table like "lineitems" in a bookstore - where you don't need
> an ISBN to be unique because a book will be in buying history more
> than once.
>
> But you DO need to make sure that the ISBN number is ONLY matched to
> one book name - NOT to more than one book name.
>
> This is OK:
> isbn    name
> 1234    Red Roses
> 1234    Red Roses
>
> This is OK:  (two books can have the same name)
> isbn    name
> 1234    Red Roses
> 5555    Red Roses
>
> This is NOT OK:  (an isbn must be tied to one book only!)
> isbn    name
> 1234    Red Roses
> 1234    Green Glasses
>
>
> I know it's tempting to say, "just link a separate table for the book
> and don't store the book name" but let's just pretend that's not an
> option - because I'm not actually dealing with books : I just made up
> this simplified version of something at work, where we can't change
> the table : both isbn and name MUST be in the table, and what I'm
> trying to do is put a CONSTRAINT on the table definition to protect
> against user error, by making sure that any entered isbn is only tied
> to one book-name in that table.
>
> Thoughts?
>

On Sun, Oct 09, 2005 at 09:32:55PM -0700, Miles Keaton wrote:
> Solved!
>
> CREATE OR REPLACE FUNCTION non_duplicated_isbn() RETURNS trigger AS $function$
> DECLARE
>   rez RECORD;
> BEGIN
>   SELECT INTO rez * FROM books WHERE isbn=NEW.isbn AND name != NEW.name;
>   IF FOUND THEN
>     RAISE EXCEPTION 'isbn % already used for different book name: %',
> NEW.isbn, rez.name;
>   END IF;
>   RETURN NEW;
> END;
> $function$ LANGUAGE plpgsql;
>
> CREATE TRIGGER ndi BEFORE INSERT OR UPDATE ON books FOR EACH ROW
> EXECUTE PROCEDURE non_duplicated_isbn();

This solution doesn't allow for concurrency.  Suppose no records
for ISBN 1234 exist in the table, then two concurrent transactions
try to insert (1234,Red Roses) and (1234,Green Glasses), respectively.
Both will find no conflicting records, so both inserts will succeed.
In other words, you have a race condition because transaction B
will fail only if transaction A happens to commit its change before
transaction B makes its check.  To prevent this problem you'll need
a locking mechanism, which can hurt performance if it locks the
entire table.

Using a foreign key reference seems like a better solution.  You
could probably implement this without changing the structure of the
existing table aside from adding the foreign key constraint and
perhaps a trigger to automatically add records to the other table,
so you shouldn't need any application changes either.  Would a
change like that still be out of the question?

--
Michael Fuhr

> > Gregory Youngblood <pgcluster@netio.org> writes:
> >> I've been using SuSE and PostgreSQL for a fairly long time.
> >> Recently (last 12 months), I've noticed that the 9.x (9.2 and
> >> 9.3 specifically) versions of SuSE do not include PostgreSQL on
> >> the CD install -- only on the DVD. At first (9.2), I thought it
> >> was just a glitch that didn't get fixed in 9.3. Now, it seems to
> >> have been deliberate.

The real problem is that SuSE is not upfront about the difference
between the CD and the DVD versions of their product. Someone (me)
picking up the box would assume that the versions are the same but
packaged on two different media but they (um, me) would be wrong.

Even items listed on the box itself are not included on the CDs
(Thunderbird, for example). Tech support was not helpful ("Sorry,
can't help. Go buy a DVD reader.")

Amusingly, even though the box contains CDs and DVDs, someone viewing
the systems requirements on the web
(http://www.novell.com/products/linuxprofessional/sysreqs.html) would
not see that a CD or DVD is required at all. I suggested to the techs
with whom I spoke that the web and box should have a prominent note
that the CDs only contain a starter subset of the product but I
haven't seen any evidence that SuSE/Novell wants to implement that
form of truth-in-advertising.

Better still, they could quit being so frigging cheap and spring for
the couple pennies/CD to make the CD version match the DVD version.

Cheers,
Steve

On Sun, 9 Oct 2005 at 18:59, Devrim GUNDUZ wrote:

> On Sun, 9 Oct 2005, Tom Lane wrote:
>
> > Gregory Youngblood <pgcluster@netio.org> writes:
> > > I've been using SuSE and PostgreSQL for a fairly long time.
> > > Recently (last 12 months), I've noticed that the 9.x (9.2 and
> > > 9.3 specifically) versions of SuSE do not include PostgreSQL on
> > > the CD install -- only on the DVD. At first (9.2), I thought it
> > > was just a glitch that didn't get fixed in 9.3. Now, it seems to
> > > have been deliberate.
> >
> > The right thing to do is complain.
>
> It might also be good to ask Reinhard about this. (CC'ed to him)
>
> Reinhard?

It's simply a space problem. We have much more packages than there is
space on five CDs, and so some packages are only contained on the DVD.
As the main focus of the CD set is on desktop/client side/end user
applications, stuff like the PostgreSQL server package is among the
first to be left out if we run out of CD space.

Possible workarounds are:
 - copy the package from a machine that has a DVD drive
 - install the package from the FTP version of SUSE Linux.
 - get a DVD drive (they are cheap these days) ;)

cu
    Reinhard

On Mon, 2005-10-10 at 11:04 -0700, Steve Crawford wrote:
> > > Gregory Youngblood <pgcluster@netio.org> writes:
> > >> I've been using SuSE and PostgreSQL for a fairly long time.
> > >> Recently (last 12 months), I've noticed that the 9.x (9.2 and
> > >> 9.3 specifically) versions of SuSE do not include PostgreSQL on
> > >> the CD install -- only on the DVD. At first (9.2), I thought it
> > >> was just a glitch that didn't get fixed in 9.3. Now, it seems to
> > >> have been deliberate.
>
> The real problem is that SuSE is not upfront about the difference
> between the CD and the DVD versions of their product. Someone (me)
> picking up the box would assume that the versions are the same but
> packaged on two different media but they (um, me) would be wrong.

You are absolutely correct! It used to be that the DVD and the CD
versions were identical, or pretty close to it. I never had to use the
DVD, and in fact, SuSE DVDs have been notoriously wishy-washy, unable to
complete the installation process on about 20% of the DVD readers I've
used (no other problems for those drives with other disks), requiring
the CDs to be used for an install.

And to move it back on topic -- now, it appears PostgreSQL is not on the
DVD either. For the record, this is the Eval SuSE 10.0 DVD.

On Tuesday 11 October 2005 09:37, Gregory Youngblood wrote:

> And to move it back on topic -- now, it appears PostgreSQL is not
> on the DVD either. For the record, this is the Eval SuSE 10.0 DVD.

Probably just because it's the eval version. Check out
http://www.novell.com/products/suselinux/applicationdevelopment.html
where they say:

"...And, with powerful databases like MySQL, PostgreSQL and Derby as
application-development resources, there's no telling how far you can
go!..."

and later on:

" ...PostgreSQL is a highly-scalable, SQL-compliant, open source
database management system. Its newest features include savepoints
(which allow specific parts of a transaction to be aborted without
affecting the transaction's remainder), point-in-time recovery,
tablespaces, column-type changes, new Perl server-side language and
much more. For the first time, SUSE Linux 10.0 now includes a YaST
module to easily set up PostgreSQL...."

So it doesn't look like SuSE is dumping PostgreSQL.

Cheers,
Steve

SuSE 10 is the "commercial" release from Novell. It contains additional
packages not in the opensuse.org open source version. There is an eval
cd and dvd set that can be downloaded of the "commercial" release -
though I do not believe it is licensed for anything beyond evaluation
purposes, but I could be mistaken. The open source opensuse edition is
freely available and not limited to evaluation.

The packages not in the opensuse version are listed here:
http://www.novell.com/coolsolutions/tip/16015.html

There has been some confusion (and annoyance) because I believe packages
that relied on one of the removed packages were also removed. Due to
Java not being included, I believe the Eclipse environment was also not
included. Don't quote me on this -- I'm going from memory.

Here is a link to instructions on tweaking the opensuse version:
http://www.thejemreport.com/mambo/content/view/178/42/

Hope this helps. If anyone has any more general SuSE questions, please
ask off-list. I'll try to reply.

To move this back on-topic, I have been looking at files available via
FTP install for SuSE 10 (at least for the opensuse version), and the
postgresql packages are there. So, it's looking more like a packaging
and DVD creation error on the main DVD (at least for it not including
postgresql). Hopefully.

Greg


On Tue, 2005-10-11 at 13:57 -0400, Alex Turner wrote:
> I know this is a bit off-topic, but this is part of the problem I'm
> encountering.  I can't find a definitive document on SuSe licensing
> and what is 'eval' and what I have to pay for... can I still download
> the full version for free? or is that just the eval?  It's kind of
> confusing.  Does anyone have information/a link to documentation that
> clarifies that stuff?
>
> Alex Turner
> NetEconomist
>
> On 10/11/05, Gregory Youngblood <pgcluster@netio.org> wrote:
>         On Mon, 2005-10-10 at 11:04 -0700, Steve Crawford wrote:
>         > > > Gregory Youngblood <pgcluster@netio.org> writes:
>         > > >> I've been using SuSE and PostgreSQL for a fairly long
>         time.
>         > > >> Recently (last 12 months), I've noticed that the 9.x
>         (9.2 and
>         > > >> 9.3 specifically) versions of SuSE do not include
>         PostgreSQL on
>         > > >> the CD install -- only on the DVD. At first ( 9.2), I
>         thought it
>         > > >> was just a glitch that didn't get fixed in 9.3. Now, it
>         seems to
>         > > >> have been deliberate.
>         >
>         > The real problem is that SuSE is not upfront about the
>         difference
>         > between the CD and the DVD versions of their product.
>         Someone (me)
>         > picking up the box would assume that the versions are the
>         same but
>         > packaged on two different media but they (um, me) would be
>         wrong.
>
>         You are absolutely correct! It used to be that the DVD and the
>         CD
>         versions were identical, or pretty close to it. I never had to
>         use the
>         DVD, and in fact, SuSE DVDs have been notoriously wishy-washy,
>         unable to
>         complete the installation process on about 20% of the DVD
>         readers I've
>         used (no other problems for those drives with other disks),
>         requiring
>         the CDs to be used for an install.
>
>         And to move it back on topic -- now, it appears PostgreSQL is
>         not on the
>         DVD either. For the record, this is the Eval SuSE 10.0 DVD.
>
>
>
>
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 3: Have you checked our extensive FAQ?
>
>                        http://www.postgresql.org/docs/faq
>

On Tue, 11 Oct 2005 at 21:57, Gregory Youngblood wrote:

> SuSE 10 is the "commercial" release from Novell. It contains
> additional packages not in the opensuse.org open source version.
> There is an eval cd and dvd set that can be downloaded of the
> "commercial" release - though I do not believe it is licensed for
> anything beyond evaluation purposes, but I could be mistaken.

You are ;)

It would be a violation of the GPL and probably other licenses if SUSE
tried to limit the use of the eval CDs or DVDs to evaluation purposes.
These editions are called "Eval" because they contain a reduced set of
packages and don't have the installation support included that comes
with the "commercial" boxed version.

> To move this back on-topic, I have been looking at files available
> via FTP install for SuSE 10 (at least for the opensuse version), and
> the postgresql packages are there. So, it's looking more like a
> packaging and DVD creation error on the main DVD (at least for it
> not including postgresql). Hopefully.

You must have been looking at the (3.3GB) Eval-DVD, which has the same
reduced set of packages as the 5 CDs, and doesn't include all of the
postgresql packages. But they are definitely included in the (8GB) DVD
that comes with the boxed version of SUSE Linux 10.

cu
    Reinhard