Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 10:33:10AM +, atrig...@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/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 11:33 AM, atrig...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
Greg Stark st...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
Kevin Grittner kevin.gritt...@wicourts.gov writes: Greg Stark st...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: 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. 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. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
Greg Stark st...@mit.edu writes: On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner kevin.gritt...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
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 behavior for what will happen if a query is executed inside a function which is called from a check statement. I also couldn't find 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 check statement subqueries, because the ordering of operations is wrong. Thanks! Ari -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
Ari Entlich atrig...@ccs.neu.edu writes: I do, however, think it should definitely be documented somewhere that functions cannot be used to get around the lack of 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
