Re: DETERMINISTIC as synonym for IMMUTABLE

>Tom Lane wrote
> In any case, whether or not you think DETERMINISTIC means IMMUTABLE,

Tom, Your knowledge of the confusing bits of the standard puts us all to
shame.

Troels did have a point, which was to do with standards conformance and
compatibility. The main point at issue is whether someone can run some ANSI
compliant SQL against PostgreSQL and have it work. That's a worthy goal.

AFAICS, your info shows that the standard's definition of DETERMINISTIC is
confusing and contradictory. Most people's interpretation would be that
DETERMINISTIC was the same as IMMUTABLE, so we should make the former a
synonym for the latter and document the possible difference of
interpretation. Seriously, if you can't put a blade of grass between them
then they're OK to be equated.

My understanding is that DETERMINISTIC in Oracle would work the same as
IMMUTABLE in PostgreSQL...

> 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.
>

You're spot on again with your info. NOT DETERMINISTIC means either STABLE
or VOLATILE in PostgreSQL terms, not just one of those.

IMHO we should allow the use of NOT DETERMINISTIC and document that although
it doesn't mean the same thing as VOLATILE, we should infer that meaning
because that is the mapping that is always correct. If the user wishes to
gain the possible performance advantages offered by STABLE, then they can
alter their code to do so. We're allowed to have performance enhancing
additions to the standard.

This is a similar situation to PostgreSQL's implementation of transaction
isolation levels. The implementation is both implemented according to the
standard and transactionally correct, yet READ UNCOMMITTED doesn't work
*exactly* as the standard says that level should, yet this is all clearly
documented and we are happy with that.

The standard ain't perfect, but we should get as close as possible and
document the difference - as long as there's no loss of correctness, which I
don't think is at issue here.

I'll submit a patch unless there is substantial disagreement.

Best Regards,

Simon Riggs