Thread: BUG #6612: Functions can be called inside CHECK statements
The following bug has been logged on the website: Bug reference: 6612 Logged by: Ari Entlich Email address: atrigent@ccs.neu.edu PostgreSQL version: 9.1.2 Operating system: Windows? Description:=20=20=20=20=20=20=20=20 Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it doesn't make any sense to me for it to allow function calls, since functions can perform queries. Additionally, if a function is called from a check constraint and that function executes a query, the change that caused the check constraint to fire does not appear to be "visible" to the query. Therefore, calling functions in check constraints does not have the ability to check whether that change is valid, making it mostly useless.
On Wed, Apr 25, 2012 at 10:33:10AM +0000, atrigent@ccs.neu.edu wrote:
> Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it
> doesn't make any sense to me for it to allow function calls, since functions
> can perform queries. Additionally, if a function is called from a check
> constraint and that function executes a query, the change that caused the
> check constraint to fire does not appear to be "visible" to the query.
> Therefore, calling functions in check constraints does not have the ability
> to check whether that change is valid, making it mostly useless.
I fail to see how's that a bug. Using functions in check has uses, and
the fact that you don't suit your particular case (or you don't know how
to make them suit your case) is not a bug in Pg.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wed, Apr 25, 2012 at 11:33 AM, <atrigent@ccs.neu.edu> wrote: > Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it > doesn't make any sense to me for it to allow function calls, since functions > can perform queries. This is why functions must be marked as one of VOLATILE, STABLE, or IMMUTABLE. Only IMMUTABLE functions can be used in CHECK constraints. It's a feature that expressions including subqueries are automatically detected as not being immutable and automatically barred. Functions do not have this feature and must be manually marked by the user with the correct state. This is a useful escape hatch in cases where an expression can not be proven to be immutable but the user knows that due to the design of his or her application it is in fact immutable -- for instance queries that query from tables that the user is certain will never be modified. The database cannot detect every possible erroneous usage, at least not without being less useful. It's a balancing act of providing the user with as many safety nets as possible without imposing too many restrictions. Too many safety nets and you can't do some things, too few and you spend too much time checking for or debugging problems. -- greg
Greg Stark <stark@mit.edu> wrote: > Only IMMUTABLE functions can be used in CHECK constraints. > It's a feature that expressions including subqueries are > automatically detected as not being immutable and automatically > barred. It doesn't look like that to me: test=# create function xxx() returns text volatile language plpgsql as $$ begin return 'xxx'; end; $$; CREATE FUNCTION test=# create table x (id int not null primary key, val text check (val <> xxx())); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x" CREATE TABLE test=# insert into x values (1, 'aaa'); INSERT 0 1 test=# insert into x values (2, 'xxx'); ERROR: new row for relation "x" violates check constraint "x_val_check" DETAIL: Failing row contains (2, xxx). Perhaps you're thinking of function usage in index definitions? 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. Otherwise I agree with your response -- this is clearly *not* a bug. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Greg Stark <stark@mit.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
On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > A CHECK constraint using a volatile function is potentially valid > and useful, IMO. =A0Think about a column which is supposed to record > the moment of an event which has occurred. =A0It could make sense to > ensure that the timestamptz value is < now(); =A0On the other hand, an > index entry based on now() is clearly a problem. > > Otherwise I agree with your response -- this is clearly *not* a bug. Hm. I suppose it depends on what you think a constraint is. I had always thought it was a guarantee that all the data in the table would meet that constraint. Not just a procedural definition for something to do at certain points in time. But I guess I responded based on my understanding without checking whether it was right. sorry. Hm, but this does raise the question of whether they're the right thing to be basing the partitioning constraint exclusion code on. I'll speculate without checking again that we check the immutability of the constraint before using it in constraint exclusion but that seems a ad-hoc. --=20 greg
Greg Stark <stark@mit.edu> writes:
> On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> A CHECK constraint using a volatile function is potentially valid
>> and useful, IMO.
> Hm. I suppose it depends on what you think a constraint is. I had
> always thought it was a guarantee that all the data in the table would
> meet that constraint. Not just a procedural definition for something
> to do at certain points in time.
Well, it's a guarantee that the expression evaluated to "true" at the
time of insertion or update of every row. If you confine your attention
to immutable expressions then you can presume that the expression is
true for every row in the table at any time; but I'm with Kevin that
there are reasonable use-cases that don't fit into that.
> Hm, but this does raise the question of whether they're the right
> thing to be basing the partitioning constraint exclusion code on.
As long as we only consider expressions that are immutable as usable
partition constraints, that's not a problem.
> I'll
> speculate without checking again that we check the immutability of the
> constraint before using it in constraint exclusion but that seems a
> ad-hoc.
We do, and I don't see why that's ad-hoc. In general the planner has to
check the volatility status of any expression it's going to try to
reason about.
regards, tom lane
Wow, so I guess I'm pretty much wrong about this... Sorry for the noise guys. I failed to consider different uses for functions,obviously, and it's not possible to automatically detect usages which could cause problems. Perhaps this is more of a bug in the documentation than anything else. I couldn't find any documentation for the behaviorfor what will happen if a query is executed inside a function which is called from a check statement. I also couldn'tfind anything about calling function inside check statements in general, but perhaps that's not necessary. I do,however, think it should definitely be documented somewhere that functions cannot be used to get around the lack of checkstatement subqueries, because the ordering of operations is wrong. Thanks! Ari
Ari Entlich <atrigent@ccs.neu.edu> writes:
> I do, however, think it should definitely be documented somewhere that functions cannot be used to get around the
lackof check statement subqueries, because the ordering of operations is wrong.
That statement seems to me to be complete nonsense. You can certainly
put a query into a function invoked by CHECK. It may be that there's
some particular use-case that this doesn't work for, but that does not
justify a blanket statement that it "doesn't work".
regards, tom lane