X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 23DE95E46DF for ; Wed, 18 Aug 2004 13:51:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58623-05 for ; Wed, 18 Aug 2004 16:51:45 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 21F355E37CB for ; Wed, 18 Aug 2004 13:51:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7IGkR8G001804; Wed, 18 Aug 2004 12:46:27 -0400 (EDT) To: Jan Wieck Cc: Richard Huxton , Markus Bertheau , Josh Berkus , olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian Subject: Re: multi column foreign key for implicitly unique columns In-reply-to: <4123837C.30408@Yahoo.com> References: <1092734724.2627.4.camel@dicaprio.akademie1.de> <1092735576.28365.48.camel@linda> <1092736197.2627.12.camel@dicaprio.akademie1.de> <200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com> <1092836961.2630.3.camel@dicaprio.akademie1.de> <41236277.5060903@Yahoo.com> <22808.1092842209@sss.pgh.pa.us> <41237DA2.9090405@archonet.com> <1535.1092845917@sss.pgh.pa.us> <4123837C.30408@Yahoo.com> Comments: In-reply-to Jan Wieck message dated "Wed, 18 Aug 2004 12:27:40 -0400" Date: Wed, 18 Aug 2004 12:46:26 -0400 Message-ID: <1803.1092847586@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/204 X-Sequence-Number: 18701 Jan Wieck writes: > If we allow for a unique index, that > * it is NOT maintained (no index tuples in there) > * depends on another index that has a subset of columns > * if that subset-index is dropped, the index becomes maintained > then the uncertainty is gone. At the time someone drops the other > constraint or unique index, the data is unique with respect to the > superset of columns. So building the unique index data at that time will > succeed. My goodness this is getting ugly. The notion of having to invoke an index build as a side-effect of a DROP sounds like a recipe for trouble. (Sample problem: what you're actually trying to do is drop the entire table ... but because the subset-index happens to get visited first, you go off and build the superset-index before you let the DROP finish. User will be unhappy, if table is large. Or try this one: the superset- index build actually fails because you've already dropped something it depends on. This seems quite possible in cases involving cascading from a drop of an individual column or datatype, for instance.) I'd like to see more than one person needing it, before we go to that kind of trouble to do something that's not in the spec. regards, tom lane