DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=ddeCySHttaWbVXCCIDQKq/IPyYvFYGZkEUCwe0RvM2jZ8+S+P03ZYGfb0eZFH/IIz6zczgmZO/CPwDH6yo9Ye+u+DIbnNw3zLl8/dCBiGrTUnOC7BeHYRx+NZOTJ8SOPzDBPb5QnbQf4x7mc6+hUjoLulj0FSp5BvAeaHu5qY9E=
	;
Message-ID: <20060701051016.94523.qmail@web31813.mail.mud.yahoo.com>
Date: Fri, 30 Jun 2006 22:10:16 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Alternative to Select in table check constraint 
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: General Postgresql List <pgsql-sql@postgresql.org>
In-Reply-To: <18959.1151727082@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> > The following codes doesn't work on PostgreSQL 8.1.4 but according to
> > the book does conform to SQL-92.
> 
> >         CHECK   ( 1 = ALL (     SELECT COUNT(STATUS)
> >                                 FROM BADGES
> >                                 WHERE STATUS = 'A'
> >                                 GROUP BY EMPNO))
> 
> Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG
> doesn't implement that.  The problem with it is that there's no clear
> way to make it perform reasonably, because the CHECK doesn't simply
> implicate the row you're currently inserting/updating --- every other
> row is potentially referenced by the sub-SELECT, and so changing row
> X might make the CHECK condition fail at row Y.  A brute-force
> implementation would be that every update of any sort to BADGES causes
> us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely
> to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work
> if there are N rows in the table).  That is certainly unworkable :-(.
> A bright person can think of ways to optimize particular cases but
> it's not easy to see how the machine might figure it out for arbitrary
> SELECTs.
> 
> The unique-index hack that Michael suggested amounts to hand-optimizing
> the sub-SELECT constraint into something that's efficiently checkable.
> 
> 			regards, tom lane
Ah.  Thanks for the clarification.

Regards,

Richard Broersma Jr.