From greg.toombs@bluebottle.com Thu Mar 8 19:06:14 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 1FE929FB41F for ; Thu, 8 Mar 2007 15:06:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 01649-09 for ; Thu, 8 Mar 2007 15:06:02 -0400 (AST) X-Greylist: delayed 01:04:25.158175 by SQLgrey-1.7.4 Received: from mi0.bluebottle.com (mi0.bluebottle.com [206.188.25.15]) by postgresql.org (Postfix) with ESMTP id DBFCD9FBC63 for ; Thu, 8 Mar 2007 15:06:03 -0400 (AST) Received: from fe0.bluebottle.com (internal.bluebottle.com [206.188.24.43]) by mi0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l28I1buh002710 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 8 Mar 2007 10:01:37 -0800 DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns; h=received:message-id:date:from:user-agent:mime-version:to: subject:x-enigmail-version:content-type:content-transfer-encoding; b=VUNY0pEFmQYJLTkBJ83WLcfC/udJo2fTgxrZQg6OqJ3ZZSaWVQTv+PePL+tb58cMY dKZoTDSk0+AWoQdSfs7h94B+KOkWptEGx8CELQpaQbqDMQFzQiyzHmbTM6wze0u Received: from [10.0.0.4] (bas3-kitchener06-1096649828.dsl.bell.ca [65.93.140.100]) (authenticated bits=0) by fe0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l28I1WB4013839 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 8 Mar 2007 10:01:37 -0800 Message-ID: <45F04F8F.8030800@bluebottle.com> Date: Thu, 08 Mar 2007 13:01:51 -0500 From: Greg Toombs User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: A form of inheritance with PostgreSQL X-Enigmail-Version: 0.94.2.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on localhost X-Virus-Status: Clean X-Trusted-Delivery: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.353 tagged_above=0 required=5 tests=BAYES_50, HTML_10_20, HTML_MESSAGE, MIME_HTML_ONLY, SPF_PASS X-Spam-Level: * X-Archive-Number: 200703/82 X-Sequence-Number: 27720 Hello.

I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following:

Tables Fruit, Apple, Orange

I want to design the foreign key scheme such that there are relations between fruit and apple, and fruit and orange, that imply that apple is a fruit, and orange is a fruit.

I don't want to eliminate the existence of Apple and Orange tables, because there will be columns specific to both Apple and Orange; if I include these columns in Fruit, then if Fruit is an Orange, the Apple columns will be needlessly present in Apple rows.

The different ways of implementing this scheme that I've thought of (some uglier than others):

- Have Fruit contain foreign keys to both Apple and Orange, and write a check constraint in Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be non-null. The disadvantage of this method is that it isn't exactly loosely coupled. For every other fruit type table I implemented I'd have to go back and add a foreign key in Fruit.

- Have a foreign key in Apple to Fruit, and in Orange to Fruit; then somehow create a constraint that imposes uniqueness on the union of foreign keys in both Apple and Orange. To figure out what type of fruit a Fruit row is, run a query for foreign keys in Orange and Apple matching the primary key of Fruit. You'd also want to somehow create a constraint that the result of this query should always return exactly one row (perhaps with a trigger?)

Any advice will be appreciated! As I'm relatively new to Postgre, I might need some help with the actual implementation as well.

Thank you.

- Greg
From dev@archonet.com Thu Mar 8 20:03:08 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 514069FBF77 for ; Thu, 8 Mar 2007 16:03:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 56213-03 for ; Thu, 8 Mar 2007 16:02:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 823089FBEDF for ; Thu, 8 Mar 2007 16:02:59 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 3FBB4757C6; Thu, 8 Mar 2007 19:58:55 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id BF26527400D; Thu, 8 Mar 2007 19:58:54 +0000 (GMT) Message-ID: <45F06AFE.3050103@archonet.com> Date: Thu, 08 Mar 2007 19:58:54 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: Greg Toombs Cc: pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL References: <45F04F8F.8030800@bluebottle.com> In-Reply-To: <45F04F8F.8030800@bluebottle.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.205 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/83 X-Sequence-Number: 27721 Greg Toombs wrote: > Hello. > > I'm trying to figure out how to nicely implement a C++ class-like system with > PostgreSQL. Consider the following: Don't do a lot of this myself, but I do know that there are several object-relational mappers that do this sort of stuff for you. Might be worth a bit of googling. -- Richard Huxton Archonet Ltd From rabroersma@yahoo.com Thu Mar 8 20:05:21 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5D5379FBFD2 for ; Thu, 8 Mar 2007 16:05:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 36087-01-5 for ; Thu, 8 Mar 2007 16:05:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web31812.mail.mud.yahoo.com (web31812.mail.mud.yahoo.com [68.142.207.75]) by postgresql.org (Postfix) with SMTP id E63809FBFDC for ; Thu, 8 Mar 2007 16:05:07 -0400 (AST) Received: (qmail 95956 invoked by uid 60001); 8 Mar 2007 20:05:06 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=TrCnh+uXwoUKM6JLD0t7cDjpSj2Qv7CsbE9VSAjhTE6Ov+xfySf8c3d2sMg6LFlqMGmh7PKLNmjIECojfxssiRO/ri3E2ZTfogk1ID891PAa0mtFBTZe5NMlJCdubadiOQxQ4M2CB9EvUF99oqMxol5DaZ0Fa2e8IRyWAtBRYWE=; X-YMail-OSG: vQjAjE4VM1mdb1k5eauN3GfHF8u4SFm_7.4TEoyEzsLXfmzFgbcDGECeRURiBkALRcd6quWh.IJ4gxc5D1tEsANiihOMX5N2PjVR1pcNib84QHdZXQHZozwJnOx4if9E5oKNMwiiEIK0p.A5DZeZGOAxfrRKApcdnzu4Vhx0A.xH Received: from [63.203.180.122] by web31812.mail.mud.yahoo.com via HTTP; Thu, 08 Mar 2007 12:05:05 PST Date: Thu, 8 Mar 2007 12:05:05 -0800 (PST) From: Richard Broersma Jr Subject: Re: A form of inheritance with PostgreSQL To: Greg Toombs , pgsql-sql@postgresql.org In-Reply-To: <45F04F8F.8030800@bluebottle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <956922.95469.qm@web31812.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.308 tagged_above=0 required=5 tests=AWL, BAYES_50, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: ** X-Archive-Number: 200703/84 X-Sequence-Number: 27722 > I want to design the foreign key scheme such that there are relationsbetween > fruit and apple, and fruit and orange, that imply that apple isa fruit, > and orange is a fruit. Sometime in the future, you will be able to achieve this beautifully and easily using postgresql's feature known as table-inheritance. Right now it can already support hierchey between different types of fruit, however it does not allow for foreign key relationships. However, I figured out a work-around using seperate a table for each entity of the heirchey. And then I rolled up these entities using views made update-able with the Postgresql rule system. Here is a link were I present how it is done. http://archives.postgresql.org/pgsql-general/2006-12/msg00913.php Let me know if you have any questions. Regards, Richard Broersma Jr. From Bart.Degryse@indicator.be Fri Mar 9 08:28:28 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B5B469FB274 for ; Fri, 9 Mar 2007 04:28:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 18227-10 for ; Fri, 9 Mar 2007 04:28:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 4B04B9FA501 for ; Fri, 9 Mar 2007 04:28:21 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Fri, 09 Mar 2007 09:15:41 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 09 Mar 2007 09:28:19 +0100 Message-Id: <45F128A3.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 09 Mar 2007 09:28:04 +0100 From: "Bart Degryse" To: Subject: Re: A form of inheritance with PostgreSQL References: <45F04F8F.8030800@bluebottle.com> In-Reply-To: <45F04F8F.8030800@bluebottle.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part4266FB84.1__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.316 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE, UNPARSEABLE_RELAY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200703/96 X-Sequence-Number: 27734 --=__Part4266FB84.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Just a little example of what I've been using on version 8.0.3 with total = satisfaction. =20 CREATE TABLE "public"."tblMovementDetails" ( "ID" INTEGER NOT NULL, "PlanningDetailID" INTEGER NOT NULL, "MovementID" INTEGER NOT NULL, "UserID" VARCHAR(5) NOT NULL, "Number" INTEGER DEFAULT 0 NOT NULL, "ChangeDate" DATE NOT NULL, CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID") ) WITHOUT OIDS; =20 CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE TABLE "public"."tblDeliveryDetails" ( "InvoiceAmount" NUMERIC(8,2) DEFAULT 0 NOT NULL, CONSTRAINT "PK_tblDeliveryDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblDeliveryDetails_InvoiceAmount" CHECK ("InvoiceAmount" = >=3D (0)::numeric), CONSTRAINT "CH_tblDeliveryDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")= REFERENCES "public"."tblDelivery"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblDeliveryDetails_tblPlanningDetails" FOREIGN KEY = ("PlanningDetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON "public"."tblDeli= veryDetails" USING btree ("PlanningDetailID"); =20 CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR UPDATE OR = DELETE ON "public"."tblDeliveryDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_archive_delivery"(); =20 CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR = UPDATE ON "public"."tblDeliveryDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDeliveryDetai= ls" DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS "ID", new."Plann= ingDetailID";); =20 CREATE TABLE "public"."tblOrderDetails" ( "QuoteID" INTEGER NOT NULL, CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY ("MovementID") REFERENCES "public"."tblOrder"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY ("Planning= DetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID") REFERENCES "public"."tblQuote"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR UPDATE ON "public"."tblOrderDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDetails"= DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID", new."Planning= DetailID";); =20 CREATE TABLE "public"."tblSendingDetails" ( "HandlingCode" VARCHAR(10) DEFAULT ''::character varying, CONSTRAINT "PK_tblSendingDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblSendingDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblSendingDetails_tblMovement" FOREIGN KEY ("MovementID") REFERENCES "public"."tblSending"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN KEY = ("PlanningDetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON "public"."tblSendi= ngDetails" USING btree ("PlanningDetailID"); =20 CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR UPDATE OR = DELETE ON "public"."tblSendingDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_archive_sending"(); =20 CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR UPDATE ON "public"."tblSendingDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingDetail= s" DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID", new."Planni= ngDetailID";); >>> Greg Toombs 2007-03-08 19:01 >>> Hello. I'm trying to figure out how to nicely implement a C++ class-like system = with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange I want to design the foreign key scheme such that there are relations = between fruit and apple, and fruit and orange, that imply that apple is a = fruit, and orange is a fruit. I don't want to eliminate the existence of Apple and Orange tables, = because there will be columns specific to both Apple and Orange; if I = include these columns in Fruit, then if Fruit is an Orange, the Apple = columns will be needlessly present in Apple rows. The different ways of implementing this scheme that I've thought of (some = uglier than others): - Have Fruit contain foreign keys to both Apple and Orange, and write a = check constraint in Fruit specifying that exactly one of (Apple FK, Orange = FK) needs to be non-null. The disadvantage of this method is that it isn't = exactly loosely coupled. For every other fruit type table I implemented = I'd have to go back and add a foreign key in Fruit. - Have a foreign key in Apple to Fruit, and in Orange to Fruit; then = somehow create a constraint that imposes uniqueness on the union of = foreign keys in both Apple and Orange. To figure out what type of fruit a = Fruit row is, run a query for foreign keys in Orange and Apple matching = the primary key of Fruit. You'd also want to somehow create a constraint = that the result of this query should always return exactly one row = (perhaps with a trigger?) Any advice will be appreciated! As I'm relatively new to Postgre, I might = need some help with the actual implementation as well. Thank you. - Greg --=__Part4266FB84.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

Just a little example of what I've been using on version 8.0.3 with = total satisfaction.

CREATE TABLE "public"."tblMovementDetails" (
"ID" INTEGER = NOT NULL,
"PlanningDetailID" INTEGER NOT NULL,
= "MovementID" INTEGER NOT NULL,
"UserID" VARCHAR(5) NOT NULL,
&= nbsp; "Number" INTEGER DEFAULT 0 NOT NULL,
"ChangeDate" DATE NOT = NULL,
CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID")
) = WITHOUT OIDS;

CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;

CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;

CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;

CREATE TABLE "public"."tblDeliveryDetails" (
"InvoiceAmount"= NUMERIC(8,2) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_tblDeliveryDetai= ls" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblDeliveryDetails_InvoiceA= mount" CHECK ("InvoiceAmount" >=3D (0)::numeric),
CONSTRAINT = "CH_tblDeliveryDetails_Number" CHECK ("Number" >=3D 0),
= CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")    REFERENCES "public"."tblDelivery"("ID")
  = ; ON DELETE NO ACTION
    ON UPDATE NO ACTION
&n= bsp;   NOT DEFERRABLE,
CONSTRAINT "FK_tblDeliveryDetail= s_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")
   = ; REFERENCES "public"."tblPlanningDetails"("ID")
    ON = DELETE NO ACTION
    ON UPDATE NO ACTION
  &= nbsp; NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHO= UT OIDS;

CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON "public"."tb= lDeliveryDetails"
USING btree ("PlanningDetailID");

CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR = UPDATE OR DELETE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUT= E PROCEDURE "public"."func_archive_delivery"();

CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE = PROCEDURE "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDelivery= Details"
DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS = "ID", new."PlanningDetailID";);

CREATE TABLE "public"."tblOrderDetails" (
"QuoteID" INTEGER = NOT NULL,
CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"),
&= nbsp; CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >=3D = 0),
CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY = ("MovementID")
    REFERENCES "public"."tblOrder"("ID")    ON DELETE NO ACTION
    ON UPDATE = NO ACTION
    NOT DEFERRABLE,
CONSTRAINT = "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")    REFERENCES "public"."tblPlanningDetails"("ID")
= ;   ON DELETE NO ACTION
    ON UPDATE NO = ACTION
    NOT DEFERRABLE,
CONSTRAINT = "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID")
   = REFERENCES "public"."tblQuote"("ID")
    ON DELETE NO = ACTION
    ON UPDATE NO ACTION
    NOT = DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT = OIDS;

CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblOrderDetails" FOR EACH ROW
EXECUTE PROCEDURE = "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDet= ails"
DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID", = new."PlanningDetailID";);

CREATE TABLE "public"."tblSendingDetails" (
"HandlingCode" = VARCHAR(10) DEFAULT ''::character varying,
CONSTRAINT "PK_tblSend= ingDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblSendingDetails_N= umber" CHECK ("Number" >=3D 0),
CONSTRAINT "FK_tblSendingDetai= ls_tblMovement" FOREIGN KEY ("MovementID")
    REFERENCES= "public"."tblSending"("ID")
    ON DELETE NO ACTION
&= nbsp;   ON UPDATE NO ACTION
    NOT DEFERRABLE,=
CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN = KEY ("PlanningDetailID")
    REFERENCES "public"."tblPlan= ningDetails"("ID")
    ON DELETE NO ACTION
  = ; ON UPDATE NO ACTION
    NOT DEFERRABLE
) = INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;

CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON "public"."tbl= SendingDetails"
USING btree ("PlanningDetailID");

CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR = UPDATE OR DELETE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE= PROCEDURE "public"."func_archive_sending"();

CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE = PROCEDURE "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingD= etails"
DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID", = new."PlanningDetailID";);

>>> Greg Toombs <greg.toombs@b= luebottle.com> 2007-03-08 19:01 >>>
Hello.

I'm = trying to figure out how to nicely implement a C++ class-like system with = PostgreSQL. Consider the following:

Tables Fruit, Apple, Orange
<= BR>I want to design the foreign key scheme such that there are relations = between fruit and apple, and fruit and orange, that imply that apple is a = fruit, and orange is a fruit.

I don't want to eliminate the = existence of Apple and Orange tables, because there will be columns = specific to both Apple and Orange; if I include these columns in Fruit, = then if Fruit is an Orange, the Apple columns will be needlessly present = in Apple rows.

The different ways of implementing this scheme that = I've thought of (some uglier than others):

- Have Fruit contain = foreign keys to both Apple and Orange, and write a check constraint in = Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be = non-null. The disadvantage of this method is that it isn't exactly loosely = coupled. For every other fruit type table I implemented I'd have to go = back and add a foreign key in Fruit.

- Have a foreign key in Apple = to Fruit, and in Orange to Fruit; then somehow create a constraint that = imposes uniqueness on the union of foreign keys in both Apple and Orange. = To figure out what type of fruit a Fruit row is, run a query for foreign = keys in Orange and Apple matching the primary key of Fruit. You'd also = want to somehow create a constraint that the result of this query should = always return exactly one row (perhaps with a trigger?)

Any advice = will be appreciated! As I'm relatively new to Postgre, I might need some = help with the actual implementation as well.

Thank you.

- = Greg

--=__Part4266FB84.1__=-- From public@misuse.org Fri Mar 9 16:38:49 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8FD199FBC93 for ; Fri, 9 Mar 2007 12:38:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 77885-04-2 for ; Fri, 9 Mar 2007 12:38:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lightning.he.net (lightning.he.net [64.62.240.2]) by postgresql.org (Postfix) with SMTP id 66FFB9FBC7D for ; Fri, 9 Mar 2007 12:38:40 -0400 (AST) Received: from dino-1.misuse.org ([68.120.198.62]) by lightning.he.net for ; Fri, 9 Mar 2007 08:38:30 -0800 X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Fri, 09 Mar 2007 08:38:26 -0800 To: pgsql-sql@postgresql.org From: Steve Midgley Subject: Re: A form of inheritance with PostgreSQL Cc: greg.toombs@bluebottle.com In-Reply-To: <20070309083514.A5B05605B0F@mx1.hub.org> References: <20070309083514.A5B05605B0F@mx1.hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Message-Id: <20070309163840.66FFB9FBC7D@postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.354 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200703/116 X-Sequence-Number: 27754 Hi Greg, While not in a C++ framework, you might find that it's not too hard to implement something similar in your system - It's called "Single Table Inheritance." References to the Ruby on Rails implementation here: http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance It's based on Martin Fowler's Patterns of Enterprise Architecture book - please find references to his original patterns here: http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html The key, I believe, is simply adding a "type" and a "parent_id" to the "class" table, so you can model all your types and their hierarchical relations. Fowler's diagram is pretty clear. I think then you would store the data in another table (or tables) and link into this inheritance structure to establish ancestry for any piece of data (some people try to store the data in this table too, but I think that's a mistake personally). If I understand what you're trying to do, you can use this design pattern in your application language to implement an inheritance scheme without any special database features (i.e. in a SQL-standard manner). I hope this is helpful, Steve At 12:28 AM 3/9/2007, pgsql-sql-owner@postgresql.org wrote: >Date: Thu, 08 Mar 2007 13:01:51 -0500 >From: Greg Toombs >To: pgsql-sql@postgresql.org >Subject: A form of inheritance with PostgreSQL >Message-ID: <45F04F8F.8030800@bluebottle.com> > > Hello. > >I'm trying to figure out how to nicely implement a C++ class-like >system >with PostgreSQL. Consider the following: > >Tables Fruit, Apple, Orange > >I want to design the foreign key scheme such that there are relations >between fruit and apple, and fruit and orange, that imply that apple >is a >fruit, and orange is a fruit. > >I don't want to eliminate the existence of Apple and Orange tables, >because there will be columns specific to both Apple and Orange; if I >include these columns in Fruit, then if Fruit is an Orange, the Apple >columns will be needlessly present in Apple rows. > >The different ways of implementing this scheme that I've thought of >(some >uglier than others): > >- Have Fruit contain foreign keys to both Apple and Orange, and write >a >check constraint in Fruit specifying that exactly one of (Apple FK, >Orange FK) needs to be non-null. The disadvantage of this method is >that >it isn't exactly loosely coupled. For every other fruit type table I >implemented I'd have to go back and add a foreign key in Fruit. > >- Have a foreign key in Apple to Fruit, and in Orange to Fruit; then >somehow create a constraint that imposes uniqueness on the union of >foreign keys in both Apple and Orange. To figure out what type of >fruit a >Fruit row is, run a query for foreign keys in Orange and Apple >matching >the primary key of Fruit. You'd also want to somehow create a >constraint >that the result of this query should always return exactly one row >(perhaps with a trigger?) > >Any advice will be appreciated! As I'm relatively new to Postgre, I >might >need some help with the actual implementation as well. > >Thank you. > >- Greg From greg.toombs@bluebottle.com Tue Mar 13 00:08:24 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D996B9FB429 for ; Mon, 12 Mar 2007 21:08:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 91688-03 for ; Mon, 12 Mar 2007 21:08:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mi1.bluebottle.com (mi1.bluebottle.com [206.188.25.14]) by postgresql.org (Postfix) with ESMTP id E20949FB46B for ; Mon, 12 Mar 2007 21:08:19 -0300 (ADT) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by mi1.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D08IGK021628 for ; Mon, 12 Mar 2007 17:08:18 -0700 DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns; h=received:date:from:user-agent:mime-version:to:cc:subject: references:in-reply-to:x-enigmail-version:content-type:content-transfer-encoding; b=Mr6PgWaSHysGTDt/732UQ2N2/0XXR7QfGuUip6b+DPwA8t2AJT5AL3yx/3pn/0fbh aXky76UrRfQnbKoujxUtCVZ4lkyrEk3Qp+kg2tiS0pFG2rBQQLKBdNFBqrw1smh Message-Id: <200703130008.l2D08IGK021628@mi1.bluebottle.com> Received: from [10.0.0.4] (bas3-kitchener06-1096623463.dsl.bell.ca [65.93.37.103]) (authenticated bits=0) by fe0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D08GgN014860 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 12 Mar 2007 17:08:17 -0700 Date: Mon, 12 Mar 2007 20:08:12 -0400 From: Greg Toombs User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Steve Midgley CC: pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> In-Reply-To: <20070309163840.66FFB9FBC7D@postgresql.org> X-Enigmail-Version: 0.94.2.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on localhost X-Virus-Status: Clean X-Trusted-Delivery: <03d92e982055d456b65aaa7cb5dd93fd> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.122 tagged_above=0 required=5 tests=AWL, BAYES_05, HTML_MESSAGE, MIME_HTML_ONLY, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/147 X-Sequence-Number: 27785 Hello, and thank you to Steven and everyone else that submitted input on this issue.

After reading a few more methods of doing things, I went with the simplest one, as 1. time is of the essence, and 2. I'm stuck with PostgreSQL 7.1 on the server I have to develop for.

I set the primary key of the parent class to a serial. Children have an integer column with constraints as the primary key and foreign key to the parent primary key column.

Thanks again,

- Greg

Steve Midgley wrote:

Hi Greg,

While not in a C++ framework, you might find that it's not too hard to implement something similar in your system - It's called "Single Table Inheritance." References to the Ruby on Rails implementation here:

http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance

It's based on Martin Fowler's Patterns of Enterprise Architecture book - please find references to his original patterns here:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

The key, I believe, is simply adding a "type" and a "parent_id" to the "class" table, so you can model all your types and their hierarchical relations. Fowler's diagram is pretty clear. I think then you would store the data in another table (or tables) and link into this inheritance structure to establish ancestry for any piece of data (some people try to store the data in this table too, but I think that's a mistake personally).

If I understand what you're trying to do, you can use this design pattern in your application language to implement an inheritance scheme without any special database features (i.e. in a SQL-standard manner).

I hope this is helpful,

Steve

At 12:28 AM 3/9/2007, pgsql-sql-owner@postgresql.org wrote:

Date: Thu, 08 Mar 2007 13:01:51 -0500
From: Greg Toombs <greg.toombs@bluebottle.com>
To: pgsql-sql@postgresql.org
Subject: A form of inheritance with PostgreSQL
Message-ID: <45F04F8F.8030800@bluebottle.com>

Hello.

I'm trying to figure out how to nicely implement a C++ class-like system
with PostgreSQL. Consider the following:

Tables Fruit, Apple, Orange

I want to design the foreign key scheme such that there are relations
between fruit and apple, and fruit and orange, that imply that apple is a
fruit, and orange is a fruit.

I don't want to eliminate the existence of Apple and Orange tables,
because there will be columns specific to both Apple and Orange; if I
include these columns in Fruit, then if Fruit is an Orange, the Apple
columns will be needlessly present in Apple rows.

The different ways of implementing this scheme that I've thought of (some
uglier than others):

- Have Fruit contain foreign keys to both Apple and Orange, and write a
check constraint in Fruit specifying that exactly one of (Apple FK,
Orange FK) needs to be non-null. The disadvantage of this method is that
it isn't exactly loosely coupled. For every other fruit type table I
implemented I'd have to go back and add a foreign key in Fruit.

- Have a foreign key in Apple to Fruit, and in Orange to Fruit; then
somehow create a constraint that imposes uniqueness on the union of
foreign keys in both Apple and Orange. To figure out what type of fruit a
Fruit row is, run a query for foreign keys in Orange and Apple matching
the primary key of Fruit. You'd also want to somehow create a constraint
that the result of this query should always return exactly one row
(perhaps with a trigger?)

Any advice will be appreciated! As I'm relatively new to Postgre, I might
need some help with the actual implementation as well.

Thank you.

- Greg

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

From tgl@sss.pgh.pa.us Tue Mar 13 01:24:18 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3C3559FB580 for ; Mon, 12 Mar 2007 22:24:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 14692-04 for ; Mon, 12 Mar 2007 22:24:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 370CA9FB4CD for ; Mon, 12 Mar 2007 22:24:15 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l2D1O6uj022202; Mon, 12 Mar 2007 21:24:06 -0400 (EDT) To: Greg Toombs cc: Steve Midgley , pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL In-reply-to: <200703130008.l2D08IGK021628@mi1.bluebottle.com> References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> <200703130008.l2D08IGK021628@mi1.bluebottle.com> Comments: In-reply-to Greg Toombs message dated "Mon, 12 Mar 2007 20:08:12 -0400" Date: Mon, 12 Mar 2007 21:24:06 -0400 Message-ID: <22201.1173749046@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.063 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/148 X-Sequence-Number: 27786 Greg Toombs writes: > After reading a few more methods of doing things, I went with the > simplest one, as 1. time is of the essence, and 2. I'm stuck with > PostgreSQL 7.1 on the server I have to develop for.
Egad. *Please* do not tell us you are intending to use 7.1 for production ... if some PHB is trying to force that on you, I suggest resigning from the project before you get blamed for the inevitable disaster. regards, tom lane From greg.toombs@bluebottle.com Tue Mar 13 01:34:46 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3A1A49FB40A for ; Mon, 12 Mar 2007 22:34:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 73858-02 for ; Mon, 12 Mar 2007 22:34:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mi1.bluebottle.com (mi1.bluebottle.com [206.188.25.14]) by postgresql.org (Postfix) with ESMTP id 2AF329FB3FF for ; Mon, 12 Mar 2007 22:34:38 -0300 (ADT) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by mi1.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D1YaNv021064 for ; Mon, 12 Mar 2007 18:34:37 -0700 DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns; h=received:date:from:user-agent:mime-version:to:cc:subject: references:in-reply-to:x-enigmail-version:content-type:content-transfer-encoding; b=mrD3PKz1VzkI+B7m6xFIu+eDMzg0hOk88H0oNEBDK45elk35juH7c8viexZnbi+v0 u942O+Q0YuC8/ey2f1Ac4Rwxu4CkXQQwjnMOTRw+6RxGhl8hmH0g+DmXagL10HB Message-Id: <200703130134.l2D1YaNv021064@mi1.bluebottle.com> Received: from [10.0.0.4] (bas3-kitchener06-1096623463.dsl.bell.ca [65.93.37.103]) (authenticated bits=0) by fe1.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D1YY1W023898 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 12 Mar 2007 18:34:36 -0700 Date: Mon, 12 Mar 2007 21:34:29 -0400 From: Greg Toombs User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> <200703130008.l2D08IGK021628@mi1.bluebottle.com> <22201.1173749046@sss.pgh.pa.us> In-Reply-To: <22201.1173749046@sss.pgh.pa.us> X-Enigmail-Version: 0.94.2.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on localhost X-Virus-Status: Clean X-Trusted-Delivery: <5b21147b2ad1df99c648d206020f4e1d> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.107 tagged_above=0 required=5 tests=AWL, BAYES_40, HTML_40_50, HTML_MESSAGE, MIME_HTML_ONLY, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/149 X-Sequence-Number: 27787 What disaster do you foresee? Is that version unstable?

Tom Lane wrote:

Greg Toombs <greg.toombs@bluebottle.com> writes:

After reading a few more methods of doing things, I went with the
simplest one, as 1. time is of the essence, and 2. I'm stuck with
PostgreSQL 7.1 on the server I have to develop for.

Egad. *Please* do not tell us you are intending to use 7.1 for production
.... if some PHB is trying to force that on you, I suggest resigning from
the project before you get blamed for the inevitable disaster.

regards, tom lane

From alvherre@commandprompt.com Tue Mar 13 03:21:55 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id CCA959FB3A9 for ; Tue, 13 Mar 2007 00:21:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 53657-10 for ; Tue, 13 Mar 2007 00:21:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 1F8FF9FB320 for ; Tue, 13 Mar 2007 00:21:51 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-201-67.bk11-dsl.surnet.cl [201.221.201.67]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l2D3Ljoj000814; Mon, 12 Mar 2007 20:21:50 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 702B532A15; Mon, 12 Mar 2007 23:21:39 -0400 (CLT) Date: Mon, 12 Mar 2007 23:21:39 -0400 From: Alvaro Herrera To: Greg Toombs Cc: Tom Lane , pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL Message-ID: <20070313032139.GS7700@alvh.no-ip.org> Mail-Followup-To: Greg Toombs , Tom Lane , pgsql-sql@postgresql.org References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> <200703130008.l2D08IGK021628@mi1.bluebottle.com> <22201.1173749046@sss.pgh.pa.us> <200703130134.l2D1YaNv021064@mi1.bluebottle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200703130134.l2D1YaNv021064@mi1.bluebottle.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Mon, 12 Mar 2007 20:21:50 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.091 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/150 X-Sequence-Number: 27788 Greg Toombs wrote: > What disaster do you foresee? Is that version unstable? Yes. There are known, unfixed bugs, and architectural problems that cannot be fixed. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. From greg.toombs@bluebottle.com Tue Mar 13 03:41:29 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C50F79FB4B8 for ; Tue, 13 Mar 2007 00:41:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 10854-10 for ; Tue, 13 Mar 2007 00:41:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mi0.bluebottle.com (mi0.bluebottle.com [206.188.25.15]) by postgresql.org (Postfix) with ESMTP id 65C819FB411 for ; Tue, 13 Mar 2007 00:41:26 -0300 (ADT) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by mi0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D3fMW0010994 for ; Mon, 12 Mar 2007 20:41:22 -0700 DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns; h=received:date:from:user-agent:mime-version:to:subject: references:in-reply-to:x-enigmail-version:content-type:content-transfer-encoding; b=ZNwhnCD9hH8XhAnwy7IEoK+Tde5WgvbIWXxtVjXc/c2hcCDEjIcrIq03Dhj4kC/4z bl6edDlxh7zvgjVSwx55zE8j2cS6p7AJEwhbXJMhEalpuCz7Rk+Ck4s0FANy1CU Message-Id: <200703130341.l2D3fMW0010994@mi0.bluebottle.com> Received: from [10.0.0.4] (bas3-kitchener06-1096623463.dsl.bell.ca [65.93.37.103]) (authenticated bits=0) by fe0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l2D3fKij002603 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 12 Mar 2007 20:41:21 -0700 Date: Mon, 12 Mar 2007 23:41:14 -0400 From: Greg Toombs User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Greg Toombs , pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> <200703130008.l2D08IGK021628@mi1.bluebottle.com> <22201.1173749046@sss.pgh.pa.us> <200703130134.l2D1YaNv021064@mi1.bluebottle.com> <20070313032139.GS7700@alvh.no-ip.org> In-Reply-To: <20070313032139.GS7700@alvh.no-ip.org> X-Enigmail-Version: 0.94.2.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on localhost X-Virus-Status: Clean X-Trusted-Delivery: <076a1a751e5f16c9dd536242b45b1226> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.209 tagged_above=0 required=5 tests=AWL, BAYES_40, HTML_40_50, HTML_MESSAGE, MIME_HTML_ONLY, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/151 X-Sequence-Number: 27789

Hoookay. I'm currently negotiating with IX Webhosting to upgrade their prehistoric software. Fellows, consider this a word of warning that, if they don't upgrade, anyone wanting to use this host will be stuck with PostgreSQL 7.1.3. If they do upgrade, I'll happily revoke this warning. Anyway, thanks for the heads-up.

Alvaro Herrera wrote:

Greg Toombs wrote:

What disaster do you foresee? Is that version unstable?

Yes. There are known, unfixed bugs, and architectural problems that cannot be fixed.

From tgl@sss.pgh.pa.us Tue Mar 13 04:15:00 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 63FE39FB429 for ; Tue, 13 Mar 2007 01:15:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 63263-01 for ; Tue, 13 Mar 2007 01:14:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0E35A9FB423 for ; Tue, 13 Mar 2007 01:14:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l2D4En5b023961; Tue, 13 Mar 2007 00:14:49 -0400 (EDT) To: Alvaro Herrera cc: Greg Toombs , pgsql-sql@postgresql.org Subject: Re: A form of inheritance with PostgreSQL In-reply-to: <20070313032139.GS7700@alvh.no-ip.org> References: <20070309083514.A5B05605B0F@mx1.hub.org> <20070309163840.66FFB9FBC7D@postgresql.org> <200703130008.l2D08IGK021628@mi1.bluebottle.com> <22201.1173749046@sss.pgh.pa.us> <200703130134.l2D1YaNv021064@mi1.bluebottle.com> <20070313032139.GS7700@alvh.no-ip.org> Comments: In-reply-to Alvaro Herrera message dated "Mon, 12 Mar 2007 23:21:39 -0400" Date: Tue, 13 Mar 2007 00:14:49 -0400 Message-ID: <23960.1173759289@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/152 X-Sequence-Number: 27790 Alvaro Herrera writes: > Greg Toombs wrote: >> What disaster do you foresee? Is that version unstable? > Yes. There are known, unfixed bugs, and architectural problems that > cannot be fixed. We abandoned maintenance of the 7.1 branch in 2001. 7.2 was the first version that anyone thought was good enough to maintain over the long haul (mainly because it was the first version that wouldn't crash and burn at 4G transactions), and even that was abandoned in 2005 because of its unfixable-without-initdb security bugs. I don't know of anyone who would recommend today that you undertake new development on a server version older than 8.1.x. regards, tom lane