Thread: Why *exactly* is date_trunc() not immutable ?

Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
Hi all,

we (GNUmed) run a medical database on PostgreSQL. We are
very pleased with it (PostgreSQL, that is ;-) in all aspects.

The date-of-birth field in our table holding patients is of
type "timestamp with time zone". One of our patient search
queries uses the date-of-birth field to find matches. Since
users enter day, month, and year but not hour, minute, and
second of the DOB we run the query with

select
    ...
where
    ... and
     date_trunc('day', dob) = date_trunc('day', what_the_user_entered_as_dob) and
     ...
;

(appropriately escaped, of course)

The actual DOB is indeed stored with hour, minute and second
so the time information is not redundant but we don't need
it for searching.

So I figured it would make sense to add a functional index
on date_trunc('day', dob) to the patients table. Which
worked (appeared to, at least) with PG 7.4.

One of our users is on PG 8.2 and gets the warning that
date_trunc() is not immutable and can thus not be used in a
functional index. Makes sense all by itself. What I don't
understand, however, is exactly *why* date_trunc is not
immutable ? All it does is extracting part of the
information that's there anyways. One would assume it to be
the timestamp equivalent of substring(), no ? (not a good
example, perhaps, as that might depend on encoding
settings...) It *shouldn't* really depend on, say, date/time
related locale settings, should it ?

I'd be happy to provide more details if that is needed for
which I'd have to contact the user in question.

Thanks for any insight offered,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
followup to self:

On Sun, Feb 18, 2007 at 12:29:17PM +0100, Karsten Hilbert wrote:

> So I figured it would make sense to add a functional index
> on date_trunc('day', dob) to the patients table. Which
> worked (appeared to, at least) with PG 7.4.
>
> One of our users is on PG 8.2
PostgreSQL 8.1 I was to say.

> and gets the warning that
> date_trunc() is not immutable and can thus not be used in a
> functional index.
The code to create the index:

    create index idx_identity_dob_ymd on dem.identity(date_trunc('day', dob))

The exact error it emits:

    functions in index expression must be marked IMMUTABLE

Those were lifted from the error log without further editing.

I know that I could fake immutability by wrapping
date_trunc() in a stored procedure marked IMMUTABLE but
I wonder what pitfalls that might hold.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Michael Glaesemann
Date:
On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:

> What I don't understand, however, is exactly *why* date_trunc is
> not immutable ?

I believe it's because the result of date_trunc depends on the time
zone setting for the session.

test=# select date_trunc('day', current_timestamp);
        date_trunc
------------------------
2007-02-18 00:00:00+09
(1 row)

test=# set time zone 'EST5EDT';
SET
test=# select date_trunc('day', current_timestamp);
        date_trunc
------------------------
2007-02-18 00:00:00-05
(1 row)

So, given the same arguments, ('day', and current_timestamp),
date_trunc is returning two different results. (Casting to date has
the same issue.)

Michael Glaesemann
grzm seespotcode net



Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann wrote:

> >What I don't understand, however, is exactly *why* date_trunc is
> >not immutable ?
>
> I believe it's because the result of date_trunc depends on the time
> zone setting for the session.

...

> So, given the same arguments, ('day', and current_timestamp),
> date_trunc is returning two different results. (Casting to date has
> the same issue.)

Ah, I see. That makes sense.

Now, if I'd write a

    date_trunc_utc(precision, timestamp with time zone)

which converts input timestamps to UTC I could fairly safely
mark that IMMUTABLE, no ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Tom Lane
Date:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> So I figured it would make sense to add a functional index
>> on date_trunc('day', dob) to the patients table. Which
>> worked (appeared to, at least) with PG 7.4.

For the record, this was changed just before 8.0 release:
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00030.php

            regards, tom lane

Re: Why *exactly* is date_trunc() not immutable ?

From
Alban Hertroys
Date:
Michael Glaesemann wrote:
>
> On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:
>
>> What I don't understand, however, is exactly *why* date_trunc is not
>> immutable ?
>
> I believe it's because the result of date_trunc depends on the time zone
> setting for the session.

I understand the reasoning, but _under the conditions_ it is being used
by the OP it could have been immutable, right?

The index values will still match up with the queried values if they are
in the same time zone.

I'm not asking to change it back to immutable (it isn't), I just
realized that the stability of functions may actually be conditional.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Why *exactly* is date_trunc() not immutable ?

From
Michael Glaesemann
Date:
On Feb 19, 2007, at 18:04 , Alban Hertroys wrote:

> Michael Glaesemann wrote:
>>
>> On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:
>>
>>> What I don't understand, however, is exactly *why* date_trunc is not
>>> immutable ?
>>
>> I believe it's because the result of date_trunc depends on the
>> time zone
>> setting for the session.
>
> I understand the reasoning, but _under the conditions_ it is being
> used
> by the OP it could have been immutable, right?

*Under the conditions* doesn't really make sense wrt immutable
functions. Immutable means is immutable under all conditions.

Michael Glaesemann
grzm seespotcode net



Re: Why *exactly* is date_trunc() not immutable ?

From
Michael Glaesemann
Date:
On Feb 18, 2007, at 23:12 , Karsten Hilbert wrote:

> On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann wrote:
>
>>> What I don't understand, however, is exactly *why* date_trunc is
>>> not immutable ?
>>
>> I believe it's because the result of date_trunc depends on the time
>> zone setting for the session.
>
> ...
>
>> So, given the same arguments, ('day', and current_timestamp),
>> date_trunc is returning two different results. (Casting to date has
>> the same issue.)
>
> Ah, I see. That makes sense.
>
> Now, if I'd write a
>
>     date_trunc_utc(precision, timestamp with time zone)
>
> which converts input timestamps to UTC I could fairly safely
> mark that IMMUTABLE, no ?


Yeah, I think if you normalized it to UTC you could mark your new
function as immutable.

Michael Glaesemann
grzm seespotcode net



Re: Why *exactly* is date_trunc() not immutable ?

From
Alban Hertroys
Date:
Michael Glaesemann wrote:
>
> On Feb 19, 2007, at 18:04 , Alban Hertroys wrote:
>
>> Michael Glaesemann wrote:
>>>
>>> On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:
>>>
>>>> What I don't understand, however, is exactly *why* date_trunc is not
>>>> immutable ?
>>>
>>> I believe it's because the result of date_trunc depends on the time zone
>>> setting for the session.
>>
>> I understand the reasoning, but _under the conditions_ it is being used
>> by the OP it could have been immutable, right?
>
> *Under the conditions* doesn't really make sense wrt immutable
> functions. Immutable means is immutable under all conditions.

What I'm trying to say is not that it _is_ immutable, but that it
_behaves_ immutable (under said conditions).

This could imply that if a certain condition is available in a query on
which such a function operates, it would behave immutable.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Mon, Feb 19, 2007 at 10:16:12AM +0100, Alban Hertroys wrote:

> What I'm trying to say is not that it _is_ immutable, but that it
> _behaves_ immutable (under said conditions).
>
> This could imply that if a certain condition is available in a query on
> which such a function operates, it would behave immutable.
That is precisely why I didn't get the idea upfront that
date_trunc() wouldn't be immutable just so.

I'll solve it with a date_trunc_utc() wrapper.

Thanks to all who chipped in. Something new to learn every day.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Martijn van Oosterhout
Date:
On Mon, Feb 19, 2007 at 10:36:36AM +0100, Karsten Hilbert wrote:
> On Mon, Feb 19, 2007 at 10:16:12AM +0100, Alban Hertroys wrote:
>
> > What I'm trying to say is not that it _is_ immutable, but that it
> > _behaves_ immutable (under said conditions).
> >
> > This could imply that if a certain condition is available in a query on
> > which such a function operates, it would behave immutable.
> That is precisely why I didn't get the idea upfront that
> date_trunc() wouldn't be immutable just so.
>
> I'll solve it with a date_trunc_utc() wrapper.

It should be noted the date_truc(timestamptz) is not immutable, whereas
date_trunc(timestamp) is. Thus you should be able to make an index on:

date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )

OTOH, if you're only storing times in UTC, then timestamp without
timezone might be better anyway.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Mon, Feb 19, 2007 at 10:58:50AM +0100, Martijn van Oosterhout wrote:

> > I'll solve it with a date_trunc_utc() wrapper.
>
> It should be noted the date_truc(timestamptz) is not immutable, whereas
> date_trunc(timestamp) is. Thus you should be able to make an index on:
>
> date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )
Ah, that makes it clear *why* this should work.

I would assume to get meaningful results from a query using
that index I'd have to normalize input timestamps to UTC,
too, before putting them into the query, right ?

> OTOH, if you're only storing times in UTC, then timestamp without
> timezone might be better anyway.
Well, PostgreSQL itself is storing UTC anyways but we need
the timezone bit since our frontend delivers timestamps from
various timezones and they are note normalized to UTC before
they get to the database.

IOW, I want the database to force programmers to have to
think about from which timezone they deliver timestamps into
a date-of-birth field into.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Martijn van Oosterhout
Date:
On Mon, Feb 19, 2007 at 12:03:07PM +0100, Karsten Hilbert wrote:
> > date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )
> Ah, that makes it clear *why* this should work.
>
> I would assume to get meaningful results from a query using
> that index I'd have to normalize input timestamps to UTC,
> too, before putting them into the query, right ?

Well, your queries need to use the same form, ie:

SELECT blah FROM foo
WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' )

That seems a bit error prone though, so your idea of making a simple
SQL function to wrap it will probably save you much heartache. It will
also make it clearer to people reading the code *why* it is written
that way.

> Well, PostgreSQL itself is storing UTC anyways but we need
> the timezone bit since our frontend delivers timestamps from
> various timezones and they are note normalized to UTC before
> they get to the database.

Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the
index is probably easier than everywhere else. Just checking you'd
thought about it. :)

> IOW, I want the database to force programmers to have to
> think about from which timezone they deliver timestamps into
> a date-of-birth field into.

Right.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Mon, Feb 19, 2007 at 12:53:15PM +0100, Martijn van Oosterhout wrote:

> Well, your queries need to use the same form, ie:
>
> SELECT blah FROM foo
> WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' )
Thought so.

> That seems a bit error prone though, so your idea of making a simple
> SQL function to wrap it will probably save you much heartache. It will
> also make it clearer to people reading the code *why* it is written
> that way.
Yep, and the COMMENT ON FUCNTION provides for a nice place to document it :-)

> > Well, PostgreSQL itself is storing UTC anyways but we need
> > the timezone bit since our frontend delivers timestamps from
> > various timezones and they are note normalized to UTC before
> > they get to the database.
>
> Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the
> index is probably easier than everywhere else. Just checking you'd
> thought about it. :)
Thanks !

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Tom Lane
Date:
Alban Hertroys <alban@magproductions.nl> writes:
> What I'm trying to say is not that it _is_ immutable, but that it
> _behaves_ immutable (under said conditions).
> This could imply that if a certain condition is available in a query on
> which such a function operates, it would behave immutable.

Right, but we don't have any way to represent such a fact in
date_trunc's pg_proc entry, so we have to mark it as "not immutable".

There was a related discussion awhile ago when designing the current
set of "what time is it" functions --- transaction_timestamp(),
statement_timestamp(), and clock_timestamp().  The original proposal
had just a single function that took a parameter telling which value
you wanted.  The trouble with that was that it'd have had to be marked
volatile, thereby defeating any ability to optimize conditions using it.
By splitting into three functions, we were able to limit the "volatile"
label to clock_timestamp().

            regards, tom lane

Re: Why *exactly* is date_trunc() not immutable ?

From
Bruno Wolff III
Date:
On Sun, Feb 18, 2007 at 12:29:17 +0100,
  Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> The date-of-birth field in our table holding patients is of
> type "timestamp with time zone". One of our patient search
> queries uses the date-of-birth field to find matches. Since
> users enter day, month, and year but not hour, minute, and
> second of the DOB we run the query with

That seems like an odd choice. Is there some reason they didn't use a type
of date? Maybe you could get them to change it?

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Mon, Feb 19, 2007 at 12:41:11PM -0600, Bruno Wolff III wrote:

>   Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> >
> > The date-of-birth field in our table holding patients is of
> > type "timestamp with time zone". One of our patient search
> > queries uses the date-of-birth field to find matches. Since
> > users enter day, month, and year but not hour, minute, and
> > second of the DOB we run the query with
>
> That seems like an odd choice. Is there some reason they didn't use a type
> of date? Maybe you could get them to change it?

What time of day were you born ?

    http://en.wikipedia.org/wiki/Apgar

What is the technical reason that makes you wonder ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
Bruno Wolff III
Date:
On Mon, Feb 19, 2007 at 20:48:07 +0100,
  Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> What time of day were you born ?
>
>     http://en.wikipedia.org/wiki/Apgar
>
> What is the technical reason that makes you wonder ?

Because it would make doing the queries simpler.
If you aren't collecting the data, it doesn't make sense to deal with the
extra headaches involved with pretending you know what time of day someone
was born.

Re: Why *exactly* is date_trunc() not immutable ?

From
Karsten Hilbert
Date:
On Mon, Feb 19, 2007 at 03:28:01PM -0600, Bruno Wolff III wrote:

> > What is the technical reason that makes you wonder ?
>
> Because it would make doing the queries simpler.
> If you aren't collecting the data, it doesn't make sense to deal with the
> extra headaches involved with pretending you know what time of day someone
> was born.
Oh, I see. When I said that users don't enter the hour and
minute that was targetted at search time. They do enter the
time part when entering a new patient, of course.

So, it's surely collected. It's just not used for searching.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Why *exactly* is date_trunc() not immutable ?

From
"jungmin shin"
Date:
 
I have a question about the query optimizer of a postgres.
As long as I understood through a postgres manual, the postgres query optimizer is implemented using  a genetic algorithm.
 
I'm thinking to modify the query optimizer.
Are there any postgres version which uses typical dynamic programming approach for query optimization?
Also, are there any body who have tried to modify the optimizer?
 
 
jungmin
 
 

Re: Why *exactly* is date_trunc() not immutable ?

From
Alvaro Herrera
Date:
jungmin shin escribió:
> I have a question about the query optimizer of a postgres.
> As long as I understood through a postgres manual, the postgres query
> optimizer is implemented using  a *genetic algorithm.*

There is an algorithm said to be "genetic", but it only kicks in with
big joins; 12 tables on the default configuration.  On queries with less
tables, the optimizer uses exhaustive search and lots of smarts.

See
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/README?rev=1.39;content-type=text%2Fplain

> I'm thinking to modify the query optimizer.
> Are there any postgres version which uses typical dynamic programming
> approach for query optimization?
> Also, are there any body who have tried to modify the optimizer?

Sure, we have a very active optimizer hacker.  He is too clever for the
rest of us to follow though :-(   (I should speak only for myself here
of course).  He goes by the name of Tom Lane.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support