Обсуждение: with (iscachable)

We have had a few discussions about the meaning of "iscachable," and I'd like
to nag and post this again.

The current meaning of "iscachable" is to mean that it can last forever in some
persistent cache somewhere that doesn't yet exist, in practice this seems to be
just a some basic transaction level. A function without "iscachable" is called
every time it is used.

It seems there should be 3 core function cache levels:

1) "noncacheable," this should always be called every time it is used.
2) "cachable," this should mean that it will be called only once per unique set
of parameters within a transaction.
3) "persistent," this could mean it never needs to be called twice.

With the above definitions, it would make sense to have "iscacheable" as the
default for a function.

Does this make sense?

mlw <markw@mohawksoft.com> writes:
> It seems there should be 3 core function cache levels:

There should be 3, but not defined like this:

> 1) "noncacheable," this should always be called every time it is used.
> 2) "cachable," this should mean that it will be called only once per unique set
> of parameters within a transaction.
> 3) "persistent," this could mean it never needs to be called twice.

We will *not* implement function caching as implied by #2.  What we want
is a definition that says that it's okay to omit redundant calls, not
one that promises we will not make any redundant calls.

Reasonable definitions would be:

1. noncachable: must be called every time; not guaranteed to return same
result for same parameters even within a query.  random(), timeofday(),
nextval() are examples.

2. fully cachable: function guarantees same result for same parameters
no matter when invoked.  This setting allows a call with constant
parameters to be constant-folded on sight.

3. query cachable: function guarantees same result for same parameters
within a single query, or more precisely within a single
CommandCounterIncrement interval.  This corresponds to the actual
behavior of functions that execute SELECTs, and it's sufficiently strong
to allow the function result to be used in an indexscan, which is what
we really care about.

I'm by no means wedded to those names ... maybe someone can think of
better terminology.

> With the above definitions, it would make sense to have "iscacheable" as the
> default for a function.

I'd still vote for noncachable as the default; unsurprising behavior is
to be preferred over maximum performance IMHO.
        regards, tom lane

Tom Lane wrote:
> Reasonable definitions would be:
> 
> 1. noncachable: must be called every time; not guaranteed to return same
> result for same parameters even within a query.  random(), timeofday(),
> nextval() are examples.
> 
> 2. fully cachable: function guarantees same result for same parameters
> no matter when invoked.  This setting allows a call with constant
> parameters to be constant-folded on sight.
Something like strlower() or metaphone() are perfect examples.
> 
> 3. query cachable: function guarantees same result for same parameters
> within a single query, or more precisely within a single
> CommandCounterIncrement interval.  This corresponds to the actual
> behavior of functions that execute SELECTs, and it's sufficiently strong
> to allow the function result to be used in an indexscan, which is what
> we really care about.

This is IMHO the important one. I would not presume to argue the scope, but as
someone who has spent the last year building entire systems on Postgres (and
loving every minute of it) I would really like a fairly reliable definition
from which someone can understand and predict the behavior.

Currently, it seems fairly reliable that "iscachable" functions are called once
per unique parameter within the scope of a select, and I like this behavior.
When/if it breaks I will have some work to do. (As observed with fairly simple
queries.)

> 
> I'm by no means wedded to those names ... maybe someone can think of
> better terminology.

I kinda like the terminology I presented, "cacheable, non-cacheable,
persistent" but hey, they are only words. I don't think people will be
confused, "persistent" and "non-cacheable" are pretty obvious.
> 
> > With the above definitions, it would make sense to have "iscacheable" as the
> > default for a function.
> 
> I'd still vote for noncachable as the default; unsurprising behavior is
> to be preferred over maximum performance IMHO.

I've thought about this one, a lot, and I can totally see your point of view,
but it isn't clear to me that it would be surprising that a function would only
be called once per unique set of parameters per select. What do other databases
do?