Re: DETERMINISTIC as synonym for IMMUTABLE

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 identical input 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 deterministic
always returns the identical return value for a given list of SQL
argument values.
[... cut stuff about sql-invoked procedures which PostgreSQL doesn't
support yet...]
An SQL-invoked routine is possibly non-deterministic if it might
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 contains a <query specification> or a
<query expression> that is possibly non-deterministic.
d) S generally contains a <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