Thread: iscachable settings for datetime functions

I've been looking at the iscachable changes you committed recently,
and I think a lot of them need to be adjusted still.

One consideration I hadn't thought of recently (though I think we did
take it into account for the 7.0 release) is that any function whose
output varies depending on the TimeZone variable has to be marked
noncachable.  This certainly means that some (all?) of the datetime
output functions need to be noncachable.  I am wondering also if any
of the type conversion functions depend on TimeZone --- for example,
what are the rules for conversion between timestamptz and timestamp?

The functions that convert between type TEXT and the datetime types
need to be treated the same as the corresponding I/O conversion
functions.  For example, text_date is currently marked cachable
which is wrong --- as evidenced by the fact that CURRENT_DATE is
folded prematurely:

regression=# create table foo (f1 date default current_date);
CREATE
regression=# \d foo               Table "foo"Column | Type |         Modifiers
--------+------+----------------------------f1     | date | default '2001-09-29'::date

The two single-parameter age() functions need to be noncachable since
they depend on today's date.  I also suspect that their implementation
should be revised: writing 'today' with no qualifier exposes you to
premature constant folding.  Probablyselect age(current_date::timestamp, $1)
(or ::timestamptz respectively) would work better.

Why are only some of the date_part functions cachable?  Is this a
timezone dependency issue, or just an oversight?

Surely the abstime comparison functions must be cachable (if they can't
be, then indexes on abstime are nonsensical...).  Ditto for all other
within-type comparison functions.
        regards, tom lane

> I've been looking at the iscachable changes you committed recently,
> and I think a lot of them need to be adjusted still.

Sure. Me too ;)

I changed some for the areas within which I was working, and it did
occur to me that (as you mention below) anything affected as a side
effect of some other system setting such as default time zone will need
to be non-cachable.

> One consideration I hadn't thought of recently (though I think we did
> take it into account for the 7.0 release) is that any function whose
> output varies depending on the TimeZone variable has to be marked
> noncachable.  This certainly means that some (all?) of the datetime
> output functions need to be noncachable.  I am wondering also if any
> of the type conversion functions depend on TimeZone --- for example,
> what are the rules for conversion between timestamptz and timestamp?

Right. Some can be cachable (e.g. timestamp, date, and time do not have
associated time zones). I'll look at the other ones asap.

> The functions that convert between type TEXT and the datetime types
> need to be treated the same as the corresponding I/O conversion
> functions.  For example, text_date is currently marked cachable
> which is wrong --- as evidenced by the fact that CURRENT_DATE is
> folded prematurely:
> 
> regression=# create table foo (f1 date default current_date);
> CREATE
> regression=# \d foo
>                 Table "foo"
>  Column | Type |         Modifiers
> --------+------+----------------------------
>  f1     | date | default '2001-09-29'::date

Hmm. Perhaps the definition for CURRENT_DATE should be recast as a call
to now() (which happens to return timestamp) or perhaps I should have
another function call. In any case, I agree that text_date() needs to be
noncachable.

> The two single-parameter age() functions need to be noncachable since
> they depend on today's date.  I also suspect that their implementation
> should be revised: writing 'today' with no qualifier exposes you to
> premature constant folding.  Probably
>         select age(current_date::timestamp, $1)
> (or ::timestamptz respectively) would work better.
> 
> Why are only some of the date_part functions cachable?  Is this a
> timezone dependency issue, or just an oversight?
> 
> Surely the abstime comparison functions must be cachable (if they can't
> be, then indexes on abstime are nonsensical...).  Ditto for all other
> within-type comparison functions.

I stayed away from changes to abstime, since I wasn't working with that
type and wanted to limit collateral damage to the other big changes I
had made.

I'll propose that we postpone beta until after Marc, Vince, and
*everyone* agree that the servers are running smoothly (a step already
suggested by others). And I'll also ask that we allow my latest
date/time changes and the above catalog fixups, which may come about
before the servers settle down.
                           - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Hmm. Perhaps the definition for CURRENT_DATE should be recast as a call
> to now() (which happens to return timestamp)

Yes, something like date(now()) might be less subject to breakage as we
monkey around with the semantics of literals and implicit coercions.

BTW, although date(now()) seems okay, I got a rather surprising result
from

template1=# select time(now());
ERROR:  Bad time external representation '2001-10-01 18:21:53.49-04'

I haven't dug into this, but surmise that it's trying to do something
involving a conversion to text and back.  The less ambiguous form
fails completely:

template1=# select now()::time;
ERROR:  Cannot cast type 'timestamp with time zone' to 'time'

> I stayed away from changes to abstime, since I wasn't working with that
> type and wanted to limit collateral damage to the other big changes I
> had made.

Well, I'll take responsibility for fixing that, if you want to spread
the blame ;-).  It's my fault that those routines are marked cachable
to begin with --- I hadn't dug into which datetime types had "current"
and which didn't, just marked 'em all noncachable on sight.

> I'll propose that we postpone beta until after Marc, Vince, and
> *everyone* agree that the servers are running smoothly (a step already
> suggested by others). And I'll also ask that we allow my latest
> date/time changes and the above catalog fixups, which may come about
> before the servers settle down.

We clearly have got to fix the cachability issue, so I have no objection
to you applying your additional changes if you think they're ready.
        regards, tom lane

...
> Well, I'll take responsibility for fixing that, if you want to spread
> the blame ;-).  It's my fault that those routines are marked cachable
> to begin with --- I hadn't dug into which datetime types had "current"
> and which didn't, just marked 'em all noncachable on sight.

I'm happy to do it; how about saving your cycles to look at the results.
                    - Thomas