Skip site navigation (1) Skip section navigation (2)

Re: iscachable settings for datetime functions

From: Thomas Lockhart <lockhart(at)fourpalms(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: iscachable settings for datetime functions
Date: 2001-10-01 22:07:57
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
> 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);
> 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

> 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

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2001-10-01 22:27:59
Subject: Re: iscachable settings for datetime functions
Previous:From: Giles LeanDate: 2001-10-01 21:57:10
Subject: Re: Spinlock performance improvement proposal

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group