X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7FC56D1D58C for ; Tue, 23 Mar 2004 07:25:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 98981-04 for ; Tue, 23 Mar 2004 03:25:01 -0400 (AST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 4ABA4D1D478 for ; Tue, 23 Mar 2004 03:24:59 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id i2N7QVNu001756; Tue, 23 Mar 2004 09:26:31 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id i2N7QTnq001752; Tue, 23 Mar 2004 09:26:31 +0200 Date: Tue, 23 Mar 2004 09:26:29 +0200 (EET) From: Achilleus Mantzios To: Erik Thiele Cc: Bruno Wolff III , Subject: Re: special integrity constraints In-Reply-To: <20040322172121.72ecf58b.erik@thiele-hydraulik.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/230 X-Sequence-Number: 17329 O kyrios Erik Thiele egrapse stis Mar 22, 2004 : Could it be possible to convert *any* CHECK constraint problem into an equivalent DEFERRABLE FK constraint problem? That is for *every* CHECK CONSTRAINT cc create (and maintain) 2 additional 1 row tables (cc1(val),cc2(val)) in such a way that cc is met if and only if (cc1.val = cc2.val). Its kinda awkard, and i think that if your situation is (gets) any more complicated tou should delegate that task to the application. The whole idea was for you too prove that using only supported pgsql features, you can achieve the equivalent effect of deferrable check constraints. > On Mon, 22 Mar 2004 09:12:11 -0600 > Bruno Wolff III wrote: > > > On Mon, Mar 22, 2004 at 14:10:42 +0100, > > Erik Thiele wrote: > > > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > > > OK, that means my FK suggestion won't help. The other suggestion about > > putting triggers on "a" and "b" to update a count in another table > > that has a deferred check constraint on it may be your best bet. > > This will be a source of contention, but that may or may not be all > > that important depending on how often you are updating "a" and "b". > > > > there is no such thing as a deferred check constraint: > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!! > > > cu > erik > > -- -Achilleus