Обсуждение: iscacheable for date/time?

I'm looking at pg_proc.h to adjust the cacheable attribute for date/time
functions. Can anyone recall why the interval data type would have been
considered non-cacheable? I didn't make internal changes to that type,
but istm that it should be cacheable already.

For timestamp and timestamptz, I've eliminated the "current" special
value which afaicr is the only reason timestamp had not been cacheable
in the past. Are there any functions which should *not* be considered
cacheable for those types? Apparently the _in() and _out() functions
should not be? Everything else is deterministic so would seem to be a
candidate.

Comments?
                       - Thomas

How about iscacheable for the to_char() functions? Can we recall why
those are not cacheable, even for non-date/time types?
                        - Thomas

... and how about the istrusted attribute for various routines? Should
it be always false or always true for C builtin functions? How about for
builtin SQL functions which are built on top of trusted C functions? Are
we guarding against catalog changes on the underlying C routines?
                       - Thomas

Thomas Lockhart wrote:

> ... and how about the istrusted attribute for various routines? Should
> it be always false or always true for C builtin functions? How about for
> builtin SQL functions which are built on top of trusted C functions? Are
> we guarding against catalog changes on the underlying C routines?

I have always had trouble with the "iscacheable" flag, there needs to be a
number of "cache" levels:

(1) cache per transaction, so you can use a function in a where statement
and it does not force a table scan. IMHO this should be the default for all
functions, but is not supported in PostgreSQL.

(2) nocache, which would mean it forces a tables scan. This is the current
default.

(3) global cache, which means the results can be stored in perpetuity, this
is the current intended meaning of iscacheable.

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Can anyone recall why the interval data type would have been
> considered non-cacheable?

I believe I made all functions for all datetime-related types
noncacheable, simply because I wasn't sure which of them had the
"current" behavior.

> For timestamp and timestamptz, I've eliminated the "current" special
> value which afaicr is the only reason timestamp had not been cacheable
> in the past. Are there any functions which should *not* be considered
> cacheable for those types? Apparently the _in() and _out() functions
> should not be?

in() should not be, since its result for the strings "now", "today",
"tomorrow", etc is variable.  But AFAICS there's no reason to mark out()
as noncacheable anymore.

The general rule is: if there are any fixed input values for which the
output might vary over time, then it should be noncachable.

Dunno why to_char is marked noncachable; does it perhaps have
format-string entries that pick up current time somehow?  I might just
have been worried about its response to "current", though.
        regards, tom lane

Thomas Lockhart <lockhart@fourpalms.org> writes:
> ... and how about the istrusted attribute for various routines? Should
> it be always false or always true for C builtin functions?

At the moment it seems to be true for every pg_proc entry in template1.
AFAIK the attribute is not actually being looked at, anyway.  I think
it used to be used to determine which functions needed to be executed in
a separate subprocess for safety reasons (ie, coredump of the function
wouldn't kill the backend) ... but that code's been gone for a long while.
        regards, tom lane