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