Re: Checking of constraints via subqueries?

Поиск
Список
Период
Сортировка
Искать

Re: Checking of constraints via subqueries?

От:
Michael Fuhr <mike@fuhr.org>
Дата:
On Thu, Feb 17, 2005 at 04:37:43PM +0100, Wolfgang Drotschmann wrote:

> "...CHECK expressions cannot contain subqueries nor refer to
>  variables other than VALUE."

In experiments I've defined a domain's CHECK expression to call a
function that makes queries.  It worked in simple tests; I don't
know if it'll work in the general case.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Checking of constraints via subqueries?

От:
Martijn van Oosterhout <kleptog@svana.org>
Дата:
On Thu, Feb 17, 2005 at 11:03:58AM -0700, Michael Fuhr wrote:
> On Thu, Feb 17, 2005 at 04:37:43PM +0100, Wolfgang Drotschmann wrote:
> 
> > "...CHECK expressions cannot contain subqueries nor refer to
> >  variables other than VALUE."
> 
> In experiments I've defined a domain's CHECK expression to call a
> function that makes queries.  It worked in simple tests; I don't
> know if it'll work in the general case.

The problem is basically that modifying a table elsewhere could have
the effect of invalidating the entries in the table being checked. So
while having the CHECK constraint call a function will work, it will
not guarentee that it will be checked whenever the result of the
subquery changes. The simplest example I can think of would be:

CREATE TABLE blah (
  ...
  field INT4 CHECK( field > (SELECT value FROM configtable WHERE key = 'field_limit') ),
  ...
);

If you hide the comparison within a function PostgreSQL will let you,
but it won't guarentee that it will always be true. For example, if you
modify the configtable you could invalidate the constraint for many
rows at once, yet PostgreSQL has no way of knowing that.

This case is simple but as the subqueries become more complex, it gets
harder to see a shortcut. Eventually you'd have to resort to rechecking
every row of table blah every time any query modifies any dependant
table.

Hope this helps,
-- 
Martijn van Oosterhout      http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Checking of constraints via subqueries?

От:
Michael Fuhr <mike@fuhr.org>
Дата:
On Thu, Feb 17, 2005 at 07:31:42PM +0100, Martijn van Oosterhout wrote:
> On Thu, Feb 17, 2005 at 11:03:58AM -0700, Michael Fuhr wrote:
> >
> > In experiments I've defined a domain's CHECK expression to call a
> > function that makes queries.  It worked in simple tests; I don't
> > know if it'll work in the general case.
> 
> The problem is basically that modifying a table elsewhere could have
> the effect of invalidating the entries in the table being checked.

Yeah, that's the kind of thing that concerned me.  I meant to ask
the OP the usual question:

What problem are you trying to solve?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Checking of constraints via subqueries?

От:
Wolfgang Drotschmann <drotschm@fgan.de>
Дата:
Hallo all,

in part VI (the reference), the online manual for 8.0.1 states for command 
CREATE DOMAIN for parameter
	CHECK (expression)
that currently
	 "...CHECK expressions cannot contain subqueries nor refer to
	 variables other than VALUE."

So, is this an item for the TODO list?  I can't find it there.  Or do I 
overlook something?
This is also stated in the page about CREATE TABLE.

Best regards,
	Wolfgang

Re: Checking of constraints via subqueries?

От:
Wolfgang Drotschmann <drotschm@fgan.de>
Дата:
Michael Fuhr wrote:
> On Thu, Feb 17, 2005 at 07:31:42PM +0100, Martijn van Oosterhout wrote:
> 
>>On Thu, Feb 17, 2005 at 11:03:58AM -0700, Michael Fuhr wrote:
>>
>>>In experiments I've defined a domain's CHECK expression to call a
>>>function that makes queries.  It worked in simple tests; I don't
>>>know if it'll work in the general case.
>>
>>The problem is basically that modifying a table elsewhere could have
>>the effect of invalidating the entries in the table being checked.
> 
> 
> Yeah, that's the kind of thing that concerned me.  I meant to ask
> the OP the usual question:
> 
> What problem are you trying to solve?

No problem actually.

Realising a constraint via a CHECK () is a decision made by the 
developer.  So the developer has to make sure that this fits in the whole 
picture and works the intended way.
I'm thinking about the feature, and not about whether the use of this feature 
is dangerous or not.
My starting point was the thought of using this for checking in CREATE DOMAIN.
I see that the ubiquitous triggers could help (in an indirect way, being 
called with every INSERT or UPDATE of a row with that domain, but is this 
generic?).  The other (subquery) option looks quite more elegant (and 
generic).  And that may be the reason why it is mentioned in the docs: it 
seems to be an elegant and natural way the check a domain.

Best regards,
	Wolfgang
FAQ