Re: DETERMINISTIC as synonym for IMMUTABLE
От | Troels Arvin |
---|---|
Тема | Re: DETERMINISTIC as synonym for IMMUTABLE |
Дата | |
Msg-id | pan.2004.10.18.09.52.00.650903@arvin.dk обсуждение исходный текст |
Ответ на | DETERMINISTIC as synonym for IMMUTABLE (Troels Arvin <troels@arvin.dk>) |
Список | pgsql-hackers |
On Sun, 17 Oct 2004 19:36:16 -0400, Tom Lane wrote: > Well, the spec is somewhat self-contradictory on the point, but I think > their intention is to model it after their notion of a deterministic > query: > > A <query expression> or <query specification> is possibly non- > deterministic if an SQL-implementation might, at two different > times where the state of the SQL-data is the same, produce results > that differ by more than the order of the rows due to General Rules > that specify implementation-dependent behavior. [SQL99 4.17] This section has been removed in SQL:2003. Instead, a new section 4.16 ("Determinism") has been added. The first paragraph of the new section states: In general, an operation is deterministic if that operation assuredly computes identical results when repeated with identicalinput values. For an SQL-invoked routine, the values in the argument list are regarded as the input; otherwise,the SQL-data and the set of privileges by which they are accessed is regarded as the input. In my reading of the new section, there is nothing which indicates that determinism is related to whether stored data are changed or not. > What it actually says about deterministic functions in 4.23 is: [...] > An SQL-invoked routine > is possibly non-deterministic if, during invocation of that SQL- > invoked routine, an SQL-implementation might, at two different > times when the state of the SQL-data is the same, produce unequal > results [...] This paragraph has also been altered in SQL:2003. In SQL:2003's section 4.27.2, there is still a section on deterministic vs. possibly non-deterministic routines; it doesn't say anything about the "state of the SQL-data" any more. It says: An SQL-invoked routine is either deterministic or possibly non-deterministic. An SQL-invoked function that is deterministicalways returns the identical return value for a given list of SQL argument values. [... cut stuff about sql-invokedprocedures which PostgreSQL doesn't support yet...] An SQL-invoked routine is possibly non-deterministic if itmight produce nonidentical results when invoked with the identical list of SQL argument values. > I think that they meant to define SQL functions as nondeterministic if > they act like or contain nondeterministic queries; for instance 13.5 > says > > 3) An <SQL procedure statement> S is possibly non-deterministic if > and only if at least one of the following is satisfied: > > a) S is a <select statement: single row> that is possibly non- > deterministic. > > b) S contains a <routine invocation> whose subject routine is an > SQL-invoked routine that possibly modifies SQL-data. [...] This has also been changed in SQL:2003: 4) [<SQL procedure statement>] is possibly non-deterministic if and only if S is not an <SQL schema statement> and at least one of the following is satisfied: a) S is a <select statement: single row> that is possibly non-deterministic. b) S contains a <routine invocation>whose subject routine is an SQL-invoked routine that is possibly non-deterministic. c) S generally containsa <query specification> or a <query expression> that is possibly non-deterministic. d) S generally containsa <value expression> that is possibly non-deterministic. > Anybody know whether the SQL2003 text clarifies the intent at all? I think it's fair to say that SQL:2003 is more clear on this subject, and that PostgreSQL's IMMUTABLE is equivalent to SQL:2003's DETERMINISTIC. The remaining problem now becomes if NOT DETERMINISTIC could be introduced as an alias to VOLATILE: > I don't think it's very helpful to identify NOT DETERMINISTIC with > VOLATILE. As a counterexample, now() is NOT DETERMINISTIC, but it > isn't VOLATILE. But does this have any semantic significance? - I mean: It's still safe to call a function including now() NOT DETERMINISTIC==VOLATILE; no unexpected results should result from this, except - potentially - lower performance. I think it's a common phenomenon that performance can sometimes be increased by utilizing certain product-specific expressions in stead of the standards-defined ones. -- Greetings from Troels Arvin, Copenhagen, Denmark
В списке pgsql-hackers по дате отправления: