Re: DETERMINISTIC as synonym for IMMUTABLE

Troels Arvin <troels(at)arvin(dot)dk> writes:
> On Sun, 17 Oct 2004 18:08:00 -0400, Tom Lane wrote:
>> These do NOT mean the same thing.

> I'm having a hard time seeing the difference between DETERMINISTIC and
> IMMUTABLE.

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]

Notice that it is okay for a deterministic query to produce different
results when the content of the database changes; therefore this is not
IMMUTABLE in our terms. It is however stronger than our STABLE
condition (for example, "now()" is STABLE but is not deterministic per
the above definition). It appears to me that they are thinking of
functions like

SELECT value FROM table WHERE pkey = $1

which is deterministic per their definition and also according to (what
I think is) the common meaning of "deterministic". We could label this
function as STABLE, but not IMMUTABLE; however we have no category that
captures the notion that "it can't change as long as the database
content doesn't change".

What it actually says about deterministic functions in 4.23 is:

An SQL-invoked routine is either deterministic or possibly non-
deterministic. An SQL-invoked function that is deterministic always
returns the same return value for a given list of SQL argument
values. An SQL-invoked procedure that is deterministic always
returns the same values in its output and inout SQL parameters
for a given list of SQL argument values. 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 due to General Rules that specify implementation-dependent
behavior.

This is clearly bogus as written since it claims that there are only two
possibilities when there are more than two. Any ordinary function that
selects from the database will satisfy neither their "deterministic" nor
their "possibly non-deterministic" definitions.

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.

c) S generally contains a <query specification> or a <query
expression> that is possibly non-deterministic.

d) S generally contains a <datetime value function>, CURRENT_
USER, CURRENT_ROLE, SESSION_USER, or SYSTEM_USER.

Anybody know whether the SQL2003 text clarifies the intent at all?

In any case, whether or not you think DETERMINISTIC means IMMUTABLE,
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.

regards, tom lane