Skip site navigation (1) Skip section navigation (2)

Re: BUG #6612: Functions can be called inside CHECK statements

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: atrigent(at)ccs(dot)neu(dot)edu, "Greg Stark" <stark(at)mit(dot)edu>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6612: Functions can be called inside CHECK statements
Date: 2012-04-25 15:46:01
Message-ID: 6552.1335368761@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Greg Stark <stark(at)mit(dot)edu> wrote:
>> Only IMMUTABLE functions can be used in CHECK constraints.
 
> It doesn't look like that to me:

No, we have never enforced that.  IIRC the idea has been discussed,
but we thought that adding the restriction would break too many
existing applications.

> A CHECK constraint using a volatile function is potentially valid
> and useful, IMO.  Think about a column which is supposed to record
> the moment of an event which has occurred.  It could make sense to
> ensure that the timestamptz value is < now();  On the other hand, an
> index entry based on now() is clearly a problem.

This example is actually stable not volatile, but if for some reason
you wanted to use clock_timestamp() then it would be volatile.

Probably a more interesting question is whether it'd ever be sane to use
a function with side-effects in a check constraint.  I find it hard to
visualize a case where it wouldn't be saner to put the actions in a
trigger, but that doesn't mean someone else might not wish to do it.
In practice, the times when check constraints are checked are
predictable enough that you should be able to get away with abusing
the system like that, if you wished.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Greg StarkDate: 2012-04-25 15:48:45
Subject: Re: BUG #6612: Functions can be called inside CHECK statements
Previous:From: Kevin GrittnerDate: 2012-04-25 15:10:05
Subject: Re: Broken Pipe Error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group