From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: General Postgresql List <pgsql-sql@postgresql.org>
Subject: Re: Alternative to Select in table check constraint
Date: Fri, 30 Jun 2006 22:10:16 -0700 (PDT)
Message-ID: <20060701051016.94523.qmail@web31813.mail.mud.yahoo.com> (raw)
In-Reply-To: <18959.1151727082@sss.pgh.pa.us>
> > 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Alternative to Select in table check constraint
In-Reply-To: <20060701051016.94523.qmail@web31813.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox